A friend on the LiveCode mailing list asked how to read Google Sheet data with LiveCode. In this post we’ll learn how to fetch that data as a CSV dump.
How does it work
I’ve prepared a demo spreadsheet which is viewable by anyone with the link. Each sheet has a key and multiple tabs (which they call sheets). If you know the key and tab you want, then, you can craft a URL to return the data as a CSV or JSON like:
Source: I’ve learned all of this from this amazing answer on Stack Overflow.
So in LiveCode, from a given Google Sheet URL, we can fetch the CSV using code like:
on mouseup put fld "sheet url" into tURLToSheet put "Sheet1" into tSheetName set the itemdel to "/" put item 6 of tURLToSheet into tKey put "https://docs.google.com/spreadsheets/d/[[tKey]]/gviz/tq?tqx=out:csv&sheet=[[tSheetName]]" into tTemplate put the merge of tTemplate into tURLForSheetAsCSV put url tURLForSheetAsCSV into tCSV replace comma with tab in tCSV put tCSV into field "sheet" end mouseup
I’ve built a sample stack with this code which you can see on the shot below:
I hope this helps everyone wanting to fetch data from Google Sheet and process it in a better language :-)