Accessing Google Sheets in Standalone – Inclusions needed?

[kaveh1000]

Hi Richmond

First of all, you have solved my problem, with

Code: Select all

textdecode(...)

Thanks a million!

I am not using CSV because of the anomalies that you have come up with. I believe CSV is a terrible format, using a commonly used character (comma) to separate cells. For this it has to automatically put in quotation marks (but not always!) Then you get the problem of quotations that might occur inside cells. Then they have to be escaped!! Somehow I got JSON output to work and it does not have these problems.

Thanks again and I am watching this thread still.

[richmond62]

On my way home for lunch I bumped into a professional programmer (the type who earns $20,000 a month)
and decided to be a pain-in-the-bum (surely not, Richmond?): he told me the reason I was probably having
difficulties with returns in dealing with .csv files was because I was importing
them as text files rather than binary files . . .

. . . unfortunately the fact that my wife is going to arrive any minute now exhausted from an early start and ravenously hungry
meant that I cooked her some lunch rather than experimenting with a binfile method.

So; this evening . . .

Unfortunately kaveh1000 my brain has taken over (spent the night hallucinating about spreadsheets), so, while
the fact that you have solved your problem via JSON, I am still going to give .csv my best shot.
-

Screenshot 2019-09-26 at 14.01.48.png (11.01 KiB) Viewed 5948 times

[richmond62]

I would, however, be extremely grateful if you could tell me (as well as sharing it
with all these folk who are watching my lunacy) how you got hold of a JSON
document from Google Sheets.

[kaveh1000]

I had some great offline help from Thierry (author of sunnYrex), and somehow I managed to grab the JSON. Please see stack in this post:

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

I had to do some more massaging after that.

[richmond62]

more massaging

Well; just so far as it only involves only massaging: I draw a line at whips and chains.

This looks "kinky", but may only work with MSOffice on Windows:

https://superuser.com/questions/1249898 ... -json-file

This seems more universal:

https://www.youtube.com/embed/HnFwl-8aT ... autoplay=1

mind you, it would involve the LiveCode stack sending some Javascript to the Google sheet . .

[kaveh1000]

missing all the fun sire!

[richmond62]

What a b*mmer, my routine to strip cr chars from the data removes both the in-cell cr codes AND the ones that tells us to
move down a line in our spreadsheet.
-

[kaveh1000]

sorry! But CSV is a horrible format and there is always a surprise lurking!

[richmond62]

CSV is a horrible format

So it seems.

However, you don't need to apologise as I do not hold you personally responsible for the CSV format.

[richmond62]

Screenshot_20190927_130033.png (5.91 KiB) Viewed 5825 times

-
Hey-Ho: htmlText provides its own kind of fun.

[kaveh1000]

Now you're really asking for trouble.

[richmond62]

asking for trouble

I dunno: I seem to have spent most of my life either getting into trouble
or causing other people trouble: so messing around with stuff coming out
of spreadsheets just feels like business as usual.

"Nothing ventured, nothing gained."

[AndyP]

What about using a service like this?

https://sheetapi.co/

Turn Google Sheet into RESTful API's

They have a free option..

[richmond62]

That's kinda groovy: but the original OP was asking about "sucking" something
directly from an open Google sheet into something in Livecode without any intermediate steps.

Also: that lot will start grabbing your moolah before you are halfway done.
-

2bucks.jpg (14.12 KiB) Viewed 5699 times

-
Note my name is "right there."
-
Far, far better to find a way without any intermediate steps, whether 'pay' or otherwise.

[kaveh1000]

I agree with you Richmond. I am almost there with a solution. I am sorry this has split across two threads now, probably because I referred to the other one from here. See Bernd's response here:

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

textdecode worked for me as as Bernd said not all unicode chars are translated correctly. I have tried his method for converting \u2022 etc and almost all chars work.

If you have any comments on that I suggest you post in that thread.

Thanks to all for suggestions.

Kaveh