Reading Google Sheets data from LiveCode

Posted Wednesday, November 14th, 2018 

development

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:

• CSV: https://docs.google.com/spreadsheets/d/{key}/gviz/tq?tqx=out:csv&sheet={sheet_name}
• JSON: https://docs.google.com/spreadsheets/d/{key}/gviz/tq?tqx=out:json&sheet={sheet_name}

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 :-)