Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Anything beyond the basics in using the LiveCode language. Share your handlers, functions and magic here.

Moderators: Klaus, FourthWorld, heatherlaine, robinmiller, kevinmiller

Post Reply
kaveh1000
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 396
Joined: Sun Dec 18, 2011 7:23 pm
Location: London
Contact:

Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Post by kaveh1000 » Fri Aug 16, 2019 5:25 pm

Hi all

Andre Garzia has given a nice tutorial of importing data from a Google Sheets here:
https://andregarzia.com/2018/11/reading ... ecode.html
Works nicely by exporting the sheet as (the dreaded) CSV. You can download the demo stack and try it. My problem is that my data has carriage returns inside the cells (e.g. by pressing command return on Mac). This messes up the data (not surprisingly).

I have created a minimal sheet here:
https://docs.google.com/spreadsheets/d/ ... edit#gid=0
that is visible by anyone. When I use Andre's method, the returns are mixed with the returns in the sheet. So I thought a tab separated output might solve it (.tsv). Indeed manually downloading the tsv results in a pretty clean file and I think it keeps all the data nicely. I modified the stack (attached) by putting a button and changing all "csv" to "tsv". In particular, in the url to fetch, I changed:

Code: Select all

...tqx=out:csv...
to

Code: Select all

...tqx=out:tsv...
I have added a text field so the full text imported can be seen. It imports the file somehow but it is not clean.

Can anyone help me get clean data into LiveCode? thanks.
Attachments
Google_Sheets_DEMO_Kaveh_modfied.livecode.zip
(2.21 KiB) Downloaded 32 times
Kaveh

dunbarx
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 6347
Joined: Wed May 06, 2009 2:28 pm
Location: New York, NY

Re: Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Post by dunbarx » Fri Aug 16, 2019 10:02 pm

Hi.

Tried it out of the box, and the CSV import works fine,

Craig

kaveh1000
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 396
Joined: Sun Dec 18, 2011 7:23 pm
Location: London
Contact:

Re: Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Post by kaveh1000 » Fri Aug 16, 2019 10:53 pm

Craig, pls see the original cells in Google Sheets and the imported version. Attaching screenshot.
Attachments
Google Sheets.png
Kaveh

AxWald
Posts: 372
Joined: Thu Mar 06, 2014 2:57 pm

Re: Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Post by AxWald » Sat Aug 17, 2019 10:21 am

Hi,

look here.

Have fun!
Livecode programming until the cat hits the fan ...

kaveh1000
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 396
Joined: Sun Dec 18, 2011 7:23 pm
Location: London
Contact:

Re: Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Post by kaveh1000 » Sat Aug 17, 2019 10:31 am

Not my idea of fun for the weekend. ;-)

I think I have seen that and probably other posts regarding CSV. What a nutty idea to have a comma separating pieces of text!!

I was hoping for an easy solution, and was hoping tsv would solve it.
Kaveh

SparkOut
Posts: 2053
Joined: Sun Sep 23, 2007 4:58 pm

Re: Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Post by SparkOut » Sat Aug 17, 2019 1:31 pm

Tab separated formatting is much saner, but is still going to need adjusting to deal with returns in the data as well as returns being the record separator.

In that other thread you have a link to Richard's article. In that article is a link to the current repository for the Tweedly function to parse csv to tab.

I would take the exported data, apply the Tweedly function and then view it in a spreadsheet. That will make your weekend a lot brighter.

kaveh1000
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 396
Joined: Sun Dec 18, 2011 7:23 pm
Location: London
Contact:

Re: Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Post by kaveh1000 » Sat Aug 17, 2019 1:45 pm

Thank you. That is a great pointer. Will not be working on it weekend but weekend already brighter. ;-)
Kaveh

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 698
Joined: Fri Jun 27, 2008 9:00 pm

Re: Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Post by Mikey » Sat Aug 17, 2019 3:15 pm

So merggoogle is out, right?
How about generating another sheet using the QUERY and IMPORTDATA functions? I would use the FORMAT clause with the QUERY to surround each column value with another delimiter character.
The original csv syntax was for each cell to be surrounded by quotes and separated by commas. You may be able to use FORMAT in QUERY to do the same thing or to assign a different delimiter.

Mikey
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 698
Joined: Fri Jun 27, 2008 9:00 pm

Re: Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Post by Mikey » Sat Aug 17, 2019 5:44 pm

looking at this more closely, i'd say export as csv and parse. when i do that, b2 gets quotes around it, which tells you it needs special handling.
https://github.com/macMikey/csvToText

Thierry
VIP Livecode Opensource Backer
VIP Livecode Opensource Backer
Posts: 664
Joined: Wed Nov 22, 2006 3:42 pm
Location: France
Contact:

Re: Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Post by Thierry » Sun Aug 18, 2019 11:45 am

kaveh1000 wrote:
Can anyone help me get clean data into LiveCode? thanks.

Hi Kaveh,
As you have reported 2 interesting bugs in sunnYrex last week,
you win half an hour of my rainy sunday morning time and here is the result:

A proof of concept and a working 20 lines of pure Livecode with a couple of regex.

Screenshot 2019-08-18 at 12.24.50.png

As I'm not a google spreadsheet user, I have no idea how to set up better google,
so to have a more useful and easy export datas to be parsed by LC; but I can't believe
there is not something...

Finally, your problem has nothing to do with XVS !!!
Replace X by comma, tab, space, cheese or whatever; that won't make any difference.

Contact me on the usual channel for details...

Kind regards,

Thierry
Regex LiveCode sunnYrex
https://sunny-tdz.com

kaveh1000
Livecode Opensource Backer
Livecode Opensource Backer
Posts: 396
Joined: Sun Dec 18, 2011 7:23 pm
Location: London
Contact:

Re: Importing data from Google Sheets (problem with returns in cells) [wrong attachment, now updated]

Post by kaveh1000 » Sun Aug 18, 2019 12:34 pm

Thank you so much Thierry. Very generous of you and I will contact you offline for details.
Kaveh

Post Reply

Return to “Talking LiveCode”