Re: Reading data from Google Sheets

[Mike Kerner via use-livecode]

why aren't you using merggoogle again?  It has issues but it would be
easier to get going.

On Thu, Nov 15, 2018 at 3:06 PM JJS via use-livecode <
use-livecode@lists.runrev.com> wrote:

> i used this 3 years ago, found some examples and altered it a bit, still
> works ok.
>
> we used this to find info back on what others had added to the list.
>
> you have to select the columns you want to read in the SELECT statement
> in the script, you have to play with this
>
> the script shows you that i used some buttons and fields
>
>
> cardscript: (add your key!)
>
> *local*sBrowserId
>
> *function*browserId
>
> *return*sBrowserId
>
> *end*browserId
>
> *on*browserBeforeNavigate pId, pUrl
>
> *global*browserCancel
>
> *if* "spreadsheets.google.com"isnotinpURL *then*
>
> *answer*warning"Won't allow you to go to a domain outside this page!"
>
> *put*trueintobrowserCancel
>
> *end* *if*
>
> *end*browserBeforeNavigate
>
> *on*closeCard
>
> *#if the hilite of btn "Browser ON" then*
>
> *#unhilite btn "Browser ON"*
>
> *#end if*
>
> *#set the vScroll of fld "theText" to 0*
>
> *pass*closeCard
>
> *end*closeCard
>
> *on*openCard
>
> altBrowserOn
>
> *pass*openCard
>
> *end*openCard
>
> *on*altBrowserOn
>
> *local*tWinID
>
> *put*thewindowidofthisstackintotWinID
>
> *local*tBrowserId
>
> *put*revBrowserOpen(tWinId,
> "
> https://spreadsheets.google.com/tq?tqx=out:html=SELECT%20A%2C%20B%2C%20C%2C%20I%2C%20J%2C%20K%2C%20L=YOURKEYGOESHERE=0;)
>
> intotBrowserId
>
> *if* tBrowserId isnotainteger*then*
>
> *answer*"Failed to open browser: "& tBrowserId
>
> *exit* altBrowserOn
>
> *end* *if*
>
> *put*tBrowserId intosBrowserId
>
> revBrowserSet sBrowserId, "showborder", true
>
> revBrowserSet sBrowserId, "rect", therectoffield"veld"
>
> *end*altBrowserOn
>
> *on*altBrowserOff
>
> *if* sBrowserId isaninteger*then*
>
> revBrowserClose sBrowserId
>
> *end* *if*
>
> *put*emptyintosBrowserId
>
> *end*altBrowserOff
>
>
> button refresh:
>
> *on*mouseUp
>
> *local*tBrowserId
>
> *put*browserId() intotBrowserId
>
> *if* tBrowserId isnotaninteger*then*
>
> *exit* mouseUp
>
> *end* *if*
>
> revBrowserRefresh browserId()
>
> *end*mouseUp
>
>
> button to find something in the list:
>
> *local*tVind
>
> *on*mouseUp
>
> *local*tBrowserId
>
> *put*browserId() intotBrowserId
>
> *if* tBrowserId isnotaninteger*then*
>
> *exit* mouseUp
>
> *end* *if*
>
> *put*field"findthis"intotVind
>
> revBrowserSet tBrowserId, "selected", tVind
>
> *Put*emptyintofield"findthis"
>
> *end*mouseUp
>
>
> Op 15-11-2018 om 19:04 schreef dunbarxx via use-livecode:
> > No good. Somewhere the last char of the text of a google sheet is
> inserted
> > three times in the body of the html  tag suite. So adding a delimiting
> char
> > of any kind is futile.
> >
> > I still think I am overthinking this.
> >
> > Craig
> >
> >
> >
> > --
> > Sent from:
> http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html
> >
> > ___
> > use-livecode mailing list
> > use-livecode@lists.runrev.com
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
>
>
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
>


-- 
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[JJS via use-livecode]

i used this 3 years ago, found some examples and altered it a bit, still 
works ok.


we used this to find info back on what others had added to the list.

you have to select the columns you want to read in the SELECT statement 
in the script, you have to play with this


the script shows you that i used some buttons and fields


cardscript: (add your key!)

*local*sBrowserId

*function*browserId

*return*sBrowserId

*end*browserId

*on*browserBeforeNavigate pId, pUrl

*global*browserCancel

*if* "spreadsheets.google.com"isnotinpURL *then*

*answer*warning"Won't allow you to go to a domain outside this page!"

*put*trueintobrowserCancel

*end* *if*

*end*browserBeforeNavigate

*on*closeCard

*#if the hilite of btn "Browser ON" then*

*#unhilite btn "Browser ON"*

*#end if*

*#set the vScroll of fld "theText" to 0*

*pass*closeCard

*end*closeCard

*on*openCard

altBrowserOn

*pass*openCard

*end*openCard

*on*altBrowserOn

*local*tWinID

*put*thewindowidofthisstackintotWinID

*local*tBrowserId

*put*revBrowserOpen(tWinId, 
"https://spreadsheets.google.com/tq?tqx=out:html=SELECT%20A%2C%20B%2C%20C%2C%20I%2C%20J%2C%20K%2C%20L=YOURKEYGOESHERE=0;) 
intotBrowserId


*if* tBrowserId isnotainteger*then*

*answer*"Failed to open browser: "& tBrowserId

*exit* altBrowserOn

*end* *if*

*put*tBrowserId intosBrowserId

revBrowserSet sBrowserId, "showborder", true

revBrowserSet sBrowserId, "rect", therectoffield"veld"

*end*altBrowserOn

*on*altBrowserOff

*if* sBrowserId isaninteger*then*

revBrowserClose sBrowserId

*end* *if*

*put*emptyintosBrowserId

*end*altBrowserOff


button refresh:

*on*mouseUp

*local*tBrowserId

*put*browserId() intotBrowserId

*if* tBrowserId isnotaninteger*then*

*exit* mouseUp

*end* *if*

revBrowserRefresh browserId()

*end*mouseUp


button to find something in the list:

*local*tVind

*on*mouseUp

*local*tBrowserId

*put*browserId() intotBrowserId

*if* tBrowserId isnotaninteger*then*

*exit* mouseUp

*end* *if*

*put*field"findthis"intotVind

revBrowserSet tBrowserId, "selected", tVind

*Put*emptyintofield"findthis"

*end*mouseUp


Op 15-11-2018 om 19:04 schreef dunbarxx via use-livecode:

No good. Somewhere the last char of the text of a google sheet is inserted
three times in the body of the html  tag suite. So adding a delimiting char
of any kind is futile.

I still think I am overthinking this.

Craig



--
Sent from: 
http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode



___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[dunbarxx via use-livecode]

No good. Somewhere the last char of the text of a google sheet is inserted
three times in the body of the html  tag suite. So adding a delimiting char
of any kind is futile.

I still think I am overthinking this.

Craig



--
Sent from: 
http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[dunbarxx via use-livecode]

Well, heck. Even I know that the string "><" is ubiquitous in html tag sets.

Wasn't thinking.

So for now, I have to include an arcane char at the end of my sheet file,
and lose it in LC.

Craig



--
Sent from: 
http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[dunbarxx via use-livecode]

Thanks, Colin. I found my way through that setting.

Now I do indeed get the data in the sheet, but followed by a massive array
of tags. This would not be an issue, except that the very start of that tag
dataSet is appended the the text in the last cell of the actual sheet data.

In other words, if the bottomMostCell in my data is a "W", then the tab
delimited text that comes over in that cell reads as:

W"><" is both reliable and
unique in what is appended to actual the sheet information, and I could set
the itemDel to that. But I suspect there is a better way.

Thanks again...

Craig



--
Sent from: 
http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[Colin Holgate via use-livecode]

On a Google Sheets page there is a Share button in the upper right. If it has a 
padlock it hasn’t been set up for sharing. Once you click on it you can set 
various options, including one where anyone with the link can view without 
having to sign in.
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[dunbarxx via use-livecode]

Hi.

Thanks for taking the time with me.

I did indeed paste the url into a separate private window in Safari, and it
asked me to sign in. I did, and the sheet popped up. A standard window goes
directly, without issue.

This is not a forum for teaching me how to use google sheets. But do you
know how I change the sharing properties?

Craig



--
Sent from: 
http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[Pi Digital via use-livecode]

I’m a big fan of Monte’s mergGoogle for reading sheets. With the exception of 
an exceptional failure in service midway through this year due to an oAuth 
issue (now fixed) it made reading and writing sheet data remarkably easy. I’d 
thoroughly recommend adopting this if you are able. 

Sean Cole
Pi Digital

> On 14 Nov 2018, at 22:15, Andre Alves Garzia via use-livecode 
>  wrote:
> 
> Craig,
> 
> Check the sharing options on the spreadsheet. Remember LiveCode is not logged 
> as your user, so it can only access spreadsheets that are viewable by anyone 
> with the link. In doubt, pick the link you used, open a private window on 
> your favorite browser and check to see if you can view it as an unlogged user.
> 
> best
> 
>> On 11/14/2018 10:08 PM, dunbarxx via use-livecode wrote:
>> All.
>> 
>> I tried the sample stack on a simple google sheet, and it returns a universe
>> of tags, not the actual contents of the sheet.
>> 
>> What am I doing wrong? I only changed the target url to point to the sheet I
>> created.
>> 
>> Craig Newman
>> 
>> 
>> 
>> --
>> Sent from: 
>> http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html
>> 
>> ___
>> use-livecode mailing list
>> use-livecode@lists.runrev.com
>> Please visit this url to subscribe, unsubscribe and manage your subscription 
>> preferences:
>> http://lists.runrev.com/mailman/listinfo/use-livecode
> 
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your subscription 
> preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[Andre Alves Garzia via use-livecode]

Craig,

Check the sharing options on the spreadsheet. Remember LiveCode is not 
logged as your user, so it can only access spreadsheets that are 
viewable by anyone with the link. In doubt, pick the link you used, open 
a private window on your favorite browser and check to see if you can 
view it as an unlogged user.


best

On 11/14/2018 10:08 PM, dunbarxx via use-livecode wrote:

All.

I tried the sample stack on a simple google sheet, and it returns a universe
of tags, not the actual contents of the sheet.

What am I doing wrong? I only changed the target url to point to the sheet I
created.

Craig Newman



--
Sent from: 
http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[dunbarxx via use-livecode]

All.

I tried the sample stack on a simple google sheet, and it returns a universe
of tags, not the actual contents of the sheet. 

What am I doing wrong? I only changed the target url to point to the sheet I 
created.

Craig Newman



--
Sent from: 
http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[Mike Kerner via use-livecode]

There's also the mergGoogle external.

On Wed, Nov 14, 2018 at 12:37 AM Kaveh Bazargan via use-livecode <
use-livecode@lists.runrev.com> wrote:

> Thank you so much to both. And Andre, if you could write a blog post for
> every question I have it would help me a lot. ;-)
>
> Actually I think I had looked at the very helpful StackExchange page before
> for reading Google Sheets directly in R. I will try it and report back with
> any follow ups.
>
> Regards
> Kaveh
>
> On Wed, 14 Nov 2018 at 02:12, Andre Alves Garzia 
> wrote:
>
> > Kaveh,
> >
> > There is a much easier way. I've just posted about it since it can be of
> > interest for others (there is a demo stack included):
> >
> >
> >
> http://andregarzia.com/2018/11/reading-google-sheets-data-from-livecode.html
> >
> > Basically, there are special URLs for retrieving a Google Sheet as a CSV
> > or a JSON. Much easier than parsing HTML or visible text.
> >
> > Cheers
> >
> > andre
> >
> > On 11/14/2018 12:46 AM, dunbarxx via use-livecode wrote:
> > > Hi.
> > >
> > > well the (very) short answer is that you can:
> > >
> > > put url "yourWebpageAddressHere" into temp
> > >
> > > What you get is the entirety of the html contents of a page. Others
> will
> > > tell both of us how to get the "visible" text, that is, the tab and
> > return
> > > delimited data, buried in all those tags.
> > >
> > > Craig Newman
> > >
> > >
> > >
> > > --
> > > Sent from:
> >
> http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html
> > >
> > > ___
> > > use-livecode mailing list
> > > use-livecode@lists.runrev.com
> > > Please visit this url to subscribe, unsubscribe and manage your
> > subscription preferences:
> > > http://lists.runrev.com/mailman/listinfo/use-livecode
> >
>
>
> --
> Kaveh Bazargan
> Director
> River Valley Technologies  • Twitter
>  • LinkedIn
> 
> ___
> use-livecode mailing list
> use-livecode@lists.runrev.com
> Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> http://lists.runrev.com/mailman/listinfo/use-livecode



-- 
On the first day, God created the heavens and the Earth
On the second day, God created the oceans.
On the third day, God put the animals on hold for a few hours,
   and did a little diving.
And God said, "This is good."
___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[Kaveh Bazargan via use-livecode]

Thank you so much to both. And Andre, if you could write a blog post for
every question I have it would help me a lot. ;-)

Actually I think I had looked at the very helpful StackExchange page before
for reading Google Sheets directly in R. I will try it and report back with
any follow ups.

Regards
Kaveh

On Wed, 14 Nov 2018 at 02:12, Andre Alves Garzia 
wrote:

> Kaveh,
>
> There is a much easier way. I've just posted about it since it can be of
> interest for others (there is a demo stack included):
>
>
> http://andregarzia.com/2018/11/reading-google-sheets-data-from-livecode.html
>
> Basically, there are special URLs for retrieving a Google Sheet as a CSV
> or a JSON. Much easier than parsing HTML or visible text.
>
> Cheers
>
> andre
>
> On 11/14/2018 12:46 AM, dunbarxx via use-livecode wrote:
> > Hi.
> >
> > well the (very) short answer is that you can:
> >
> > put url "yourWebpageAddressHere" into temp
> >
> > What you get is the entirety of the html contents of a page. Others will
> > tell both of us how to get the "visible" text, that is, the tab and
> return
> > delimited data, buried in all those tags.
> >
> > Craig Newman
> >
> >
> >
> > --
> > Sent from:
> http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html
> >
> > ___
> > use-livecode mailing list
> > use-livecode@lists.runrev.com
> > Please visit this url to subscribe, unsubscribe and manage your
> subscription preferences:
> > http://lists.runrev.com/mailman/listinfo/use-livecode
>


-- 
Kaveh Bazargan
Director
River Valley Technologies  • Twitter
 • LinkedIn

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[Andre Alves Garzia via use-livecode]

Kaveh,

There is a much easier way. I've just posted about it since it can be of 
interest for others (there is a demo stack included):


http://andregarzia.com/2018/11/reading-google-sheets-data-from-livecode.html

Basically, there are special URLs for retrieving a Google Sheet as a CSV 
or a JSON. Much easier than parsing HTML or visible text.


Cheers

andre

On 11/14/2018 12:46 AM, dunbarxx via use-livecode wrote:

Hi.

well the (very) short answer is that you can:

put url "yourWebpageAddressHere" into temp

What you get is the entirety of the html contents of a page. Others will
tell both of us how to get the "visible" text, that is, the tab and return
delimited data, buried in all those tags.

Craig Newman



--
Sent from: 
http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode


___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Re: Reading data from Google Sheets

[dunbarxx via use-livecode]

Hi.

well the (very) short answer is that you can:

put url "yourWebpageAddressHere" into temp

What you get is the entirety of the html contents of a page. Others will
tell both of us how to get the "visible" text, that is, the tab and return
delimited data, buried in all those tags.

Craig Newman



--
Sent from: 
http://runtime-revolution.278305.n4.nabble.com/Revolution-User-f278306.html

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode

Reading data from Google Sheets

[Kaveh Bazargan via use-livecode]

Can someone point me to how I can read data from Google Sheets
into LiveCode? For now I just need to read, not write.

Thanks.

-- 
Kaveh Bazargan
Director
River Valley Technologies  • Twitter
 • LinkedIn

___
use-livecode mailing list
use-livecode@lists.runrev.com
Please visit this url to subscribe, unsubscribe and manage your subscription 
preferences:
http://lists.runrev.com/mailman/listinfo/use-livecode