Accessing Google Sheets in Standalone – Inclusions needed?

[kaveh1000]

Hi all

I have an application that reads data from Google Sheets. See e.g. André's post here:

https://andregarzia.com/2018/11/reading ... ecode.html

When I create a standalone the data is not read in. I am wondering if I need an inclusion that I have not checked. I tried OAuth2, but that does not help.

Regards
Kaveh

[bn]

Hi Kaveh,

I compiled André's example and chose "search for required inclusions..." in the standalone settings. It included the tsNet external. The standalone worked just as well as in the IDE.

May be either try to include tsNet external or choose "search for required inclusions..."

But then I am not an expert on network questions, let alone Google Sheets.

Kind regards
Bernd

[kaveh1000]

Got it. I am importing JSON so needed to add JSON library.

[kaveh1000]

Thanks bn. I should have said I am doing Json import. For some reason I switched to manually adding inclusions a while ago...

[bogs]

I suspect that reason might have been the automatic search had a failure at some point. Lots of others *only* use the manual inclusions methods.

Not me of course, but I only do such simple things

[richmond62]

Are you trying to read the information from Google Sheets from a web address?
or from a downloaded file?

Personally, I'm a lazy slob and download the Google Sheets as CSV documents
and just go for importing comma-delimited text files into
a Table Field: no special inclusions or additional twiddly bits at all.

As far as I can tell from Andre's 'thing' what it does is extract a CSV document from the online Google Sheet thereby obviating the need for the
end-user to just do this:
-

[kaveh1000]

Hi richmond. I need to import the data automatically and on the fly, not manual download. A problem with csv was that it did not respect returns *within* cells. Somehow this was fixed by getting json. Needed some expert outside help with that...

The only problem I have now is that Unicode chars are corrupted on import into LiveCode. See this lonely post:

https://forums.livecode.com/viewtopic.p ... on#p182782

[richmond62]

on the fly, not manual download.

Aha; now I see where the difficulty lies.

The problem with Unicode characters: is that via CSV or via JSON, or both?

Be aware that, inspite of the "ballyhoo" LiveCode is not completely 'there' when it comes to Unicode.

I, personally, know that LiveCode can import .txt and .html files containing Unicode text without
everything ending up like a jam sandwich.

[richmond62]

I exported a sheet from Google Sheets with some Unicode Text in it to a .csv file on my Macintosh and
attempted to bring it into a Table field:

Code: Select all

on mouseUp
   answer file "Choose a text file to import"
   if the result = "cancel" 
   then exit mouseUp
   else
      put URL ("file:" & it) into SeeEssVee
      --replace commas with tabs
         replace "," with numToChar(9) in SeeEssVee
      set the text of fld "champs" to SeeEssVee
   end if
end mouseUp

and it DID muck up the Unicode.

"Just for fun" I opened the .csv file in LibreOffice where it, usefully, told me that it was in UTF-8 encoding.

So I popped over here: https://forums.livecode.com/viewtopic.php?t=31205

And modified my code accordingly:

Code: Select all

on mouseUp
   answer file "Choose a text file to import"
   if the result = "cancel" 
   then exit mouseUp
   else
      put URL ("file:" & it) into SeeEssVee
      put textdecode(SeeEssVee,"UTF-8") into Jumping
      --replace commas with tabs
      replace "," with numToChar(9) in Jumping
      set the text of fld "champs" to Jumping
   end if
end mouseUp

and the thing worked.
-

[richmond62]

A problem with csv was that it did not respect returns *within* cells.

DO you mean that when you exported a sheet to a CSV document the returns were not respected
(i.e. ignored or replaced with something else)?

Because to me this makes little or no sense as when I attempted a return in Google Sheets it
changed focus from a cell to the next one vertically below it.

So my first question has to be:

#1 How did you manage to prepare a Google Sheets document that included returns in cells?

(The only way that seems possible to me is that you imported data that contained returns into cells from
somewhere else.)

If your CSV document replaced any returns consistently
with another character it should be easy enough to replace that character once again
with returns once it has been imported into LiveCode
in exactly the same way as I replaced commas with tabs.

[richmond62]

Thanks for all that: because of your provocation I learnt how to use
replace; a term I didn't even know about until about 30 minutes ago.

The whole replace thing makes me feel rather stupid,
having used repeat loops chewing through texts char by char for years.

[kaveh1000]

Hi Richmond. Thank you for the multiple contributions. While I am going through them, putting a return into a cell in Google Sheets is achieved by Option-return (or Command-return). I know you are a mac user. I am guessing control for other operating systems.

[richmond62]

I know you are a mac user.

Well; sometimes, but my "other car is a Lamborghini," . . . I use Linux at least as much as I use Macintosh.

Not that that, frankly, is terribly relevant here.

So, I added 2 cells to my Google sheet with returns: here's what the exported .csv file looks like
in LibreOffice:
-

-
So, lets' see what happens if I import it into my stack:
-

Screenshot 2019-09-25 at 22.58.49.png (7.58 KiB) Viewed 8444 times

-
Well, that's a load of old toadstools for 2 reasons:

#1 views return in the same way as it view tab.

#2 bungs quotes round English words.

So: it is probably time to be clever by replacing the standard linefeed char in a .csv file: numToChar(10)
with another of the Unicode linefeed possibilities and see if that works:
-

Screenshot 2019-09-25 at 23.02.47.png (31.69 KiB) Viewed 8444 times

-
HOWEVER, in my timezone it is well past my bedtime, so,
either you'll have to have a bash at that, or wait until about 20 hours from now
when I am home from tomorrow's teaching.

[kaveh1000]

Your replies will be worth waiting for!! By the way did you see this thread:

https://forums.livecode.com/viewtopic.p ... eh#p182337

regarding the returns

[richmond62]

The situation where importing data from a .csv document bungs quotes round text is inconsistent
as it does NOT do that for "Джон", so is presumably (?) confined only to the old, old ASCII range
of characters. It is also unasked for and not required.

HOWEVER, doing this:

Code: Select all

on mouseUp
   answer file "Choose a text file to import"
   if the result = "cancel" 
   then exit mouseUp
   else
      put URL ("file:" & it) into SeeEssVee
      put textdecode(SeeEssVee,"UTF-8") into Jumping
      --replace commas with tabs
      replace "," with numToChar(9) in Jumping
      --remove quotes
    replace numToChar(34) with numToChar(0)
      set the text of fld "champs" to Jumping
   end if
end mouseUp

replace numToChar(34) with numToChar(0) in Jumping

does something that is also problematic:
-

Screenshot_20190926_112953.png (3.68 KiB) Viewed 8390 times

-

Screenshot_20190926_112928.png (3.54 KiB) Viewed 8390 times

-
It seems that every word that has a double quote preceding it gets deleted!