Query Any Google Sheet with a Web App
Note: you may need to set your spreadsheet locale to UK English for the script to work. Thhis script has been set with semi-colons in the sheet formula used, instead of commas, this should work in all locales....
Google Apps Script, and aia project/blocks updated January 2022 (from November 2019)
It has more or less always been possible to query a google sheet directly from AI2, but this has required the sheet to be shared with at least "Anyone with the link". This guide provides a method for the developer to allow users access to any google sheet they own (the developer), private / shared or public. To do this we use a google apps script web app, created and run by the google account owner, with permission for anyone, even anonymous to use it. This should also make it easier to run such a query, you have one url to the google apps script web app to worry about, for any sheet you want to query.
The web app requires three things:
the spreadsheet file ID
the sheet name
the google sql query string
The script can also be run in a computer browser, and will return the output to the screen.
In this example, I used a standalone google apps script project, but it can just as easily be a bound script to a spreadsheet.
The script gets the parameters, creates a new sheet on the target spreadsheet, and pastes the query to a cell, which generates the output on the sheet. This output is then grabbed by the script and returned as a stringified JSON array. The headers have been set to always on, when converting to an AI2 list if you do not want the headers, then remove the first index item.
In my aia project I added a short list of spreadsheet names, file IDs and associated sheet names to work with, the work with the listpicker is there to feed the procedure in the button click event, and then a procedure to fetch the data.
For the query, the user only has to start with "select", and then use the requirements as set out in the google query language. To begin with, if one cannot remember the layout in the sheet, a simple select * can be used to return all the data, then a more detailed query can be written. One uses the columns letters - e.g. A,B,C,D. If using a comparator such as contains or matches, it is important to enter the search term in single quotes - e.g. select A,B,D where B contains 'Yellow' .