Use gviz to
get or query PRIVATE
google sheet data
INTRO
In a previous tutorial, we saw how to use the google visualization API to get or query data from "public" / "Anyone with the link" google sheets. It is also possible to use the google visualization API on "private" google sheets. We will need to use an intermediary google apps script web app to facilitate this.
WARNING:
IT IS STRONGLY RECOMMENDED THAT YOU HARD CODE THE SPREADSHEET ID INSIDE THE WEB APP, AND DO NOT PROVIDE USERS THE OPTION TO ADD A SPREADSHEET ID. DOING SO COULD EXPOSE ALL YOUR PRIVATE FILES.
To further minimise risk, in production, the developer could use a separate google account, then if a parameter is used for fileID, only the private files in that account would potentially be exposed. To be honest, it is not all that difficult to setup separate web apps for each spreadsheet, I would recommend that method.
SETUP
We will have our three elements:
Spreadsheet (private - only available to the owner)
Web App (created by the same owner as the spreadsheet)
Note: a new interface for google apps script editor is currently being rolled out, things may be in different places if you have the new one (we are using the old interface)
App Inventor app
SPREADSHEET
Nothing really special or different about the spreadsheet, other than the fact that access is restricted to the owner.
WEB APP
We will setup the web app as a doGet(), and we have to do some work with the manifest file. The web app will be bound to the private sheet to make it easier to find, and so that it becomes linked to the spreadsheet, avoiding the temptation to ignore the warning provided above!
(If you are unsure about setting up a web app, visit this HOWTO)
Open up the Script Editor from the spreadsheet and give your script a name.
Paste in the following, overwriting the default text:
function doGet(e) {
var access_token = ScriptApp.getOAuthToken();
var request_options = {
"headers": {
"Authorization": "Bearer " + access_token,
"contentType": "text/csv"
}
};
var id = '<YOUR SPREADSHEET ID HERE>';
var sh = e.parameter.sheet;
var query = encodeURIComponent(e.parameter.query);
var data = UrlFetchApp.fetch('https://docs.google.com/spreadsheets/d/' + id + '/gviz/tq?tqx=out:csv&sheet=' + sh + '&tq=' + query, request_options).getContentText();
return ContentService.createTextOutput(data);
}
Now replace '<YOUR SPREADSHEET ID HERE>' with the spreadsheet ID for your private spreadsheet. And Save. Publish the web app, executing as "Me" and providing access for "Anyone, even anonymous". You will be asked to give permissions and authenticate. Do so. capture the script url provided, and paste it somewhere safe for use in your App Inventor app.
Next, click on View in the File Menu, and select Show Manifest File, our one looks like this:
{
"timeZone": "Europe/London",
"dependencies": {
},
"webapp": {
"access": "ANYONE_ANONYMOUS",
"executeAs": "USER_DEPLOYING"
},
"exceptionLogging": "STACKDRIVER",
"runtimeVersion": "V8"
}
We need to add some more permissions in order to allow the script access to our private spreadsheet:
"oauthScopes": ["https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/drive"]
Having added this line, our manifest file should look like this:
{
"timeZone": "Europe/London",
"dependencies": {
},
"webapp": {
"access": "ANYONE_ANONYMOUS",
"executeAs": "USER_DEPLOYING"
},
"exceptionLogging": "STACKDRIVER",
"oauthScopes": ["https://www.googleapis.com/auth/script.external_request", "https://www.googleapis.com/auth/drive"],
"runtimeVersion": "V8"
}
Save and republish the web app to a new version. You should be asked to authenticate again. Do so.
That is the web app all done.
APP INVENTOR app
The demo app is a fairly simple affair, a textbox to enter the query, a web component, a button to run the query, and a label to display the output. It just works :)
BLOCKS and SCREEN
AIA and FILES
NOTES
My thanks to "Peter", who got in touch to tell me that the query was not working on an Android 6 device, returning a bad/malformed request error. The fix for this was to insert a UriEncode block before the query.
