Reading Google Sheets data from LiveCode

:: LiveCode

By: Andre Alves Garzia

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:{key}/gviz/tq?tqx=out:csv&sheet={sheet_name}
  • JSON:{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 "[[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:

Google Sheets DEMO stack

Google Sheets DEMO stack

I hope this helps everyone wanting to fetch data from Google Sheet and process it in a better language :-)

Comments? Questions? Feedback?

You can reach out to me on Twitter, or Mastodon, or Secure Scuttlebutt