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 web app returns the output as a stringified Javascript array which is then converted to an AI2 list.

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.

SCRIPT

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.

function doGet(e) {

var ss = SpreadsheetApp.openById(e.parameter.ID);

var sh = ss.getSheetByName(e.parameter.SH);

var rg = sh.getName() + "!" + sh.getDataRange().getA1Notation();

var sql = e.parameter.SQL;

var qry = '=query(' + rg + ';\"' + sql + '\";1)';

var ts = ss.insertSheet();

var setQuery = ts.getRange(1,1).setFormula(qry);

var getResult = ts.getDataRange().getValues();

ss.deleteSheet(ts);

return ContentService.createTextOutput(JSON.stringify(getResult));

}

BLOCKS

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' .

AIA

queryAnyGoogleSheet.aia

A computer browser url would look something like this:

https://script.google.com/macros/s/AKfycbyyy6QcNzv2d4TQZ629GBBH3Fjzf-wUDlQVwpPfT1701rZdxzM/exec?ID=15NcxMHF1_6uasov30k4fFoBArc-tzMNhyMqx_A2eSFg&SH=Student_Points_List&SQL=select B,C,D where D matches 'Maroon'

SCREEN