Abstract
This article examines Google Apps Script approaches that can be used to retrieve data from a Google Spreadsheet.
We will primarily focus on two techniques:
- Google SpreadsheetApp (Apps Script Class) - (further referred to as SheetApp)
- Google Visualization API (Query) - (further referred to as GViz)
While a query via GViz will serve JSON or HTML output-formats, the SheetApp can retrieve data as an array.
The data retrieved from a GViz query has to be parsed. One advantage of the GViz Service is that
a query language
exists that allows for detailed "SQL-like" queries.
Hypothesis
The hypothesis is that GViz will be faster due to a lower overhead.
Remark: Even if SheetApp should be faster than GViz, there might be one benefit that is worth to be
considered. SheetApp has a limit of 50 concurrent users accessing the sheet. This number includes script access and event
(at least for a while) multiple instances of one and the same sheet and the same user.
Setup
We have prepared a Google Sheet
that contains 1,000 key-value-pairs in random order. Next, we will try to
retrieve the value for a given key (randomly) and will measure the runtime needed to perform this task. We will loop
the process 50 times in order to get a more reliable value on average.
Process
We have prepared the following code for either method:
Source Code Sheet App
var SSID = '18xxzBls3FCijHHW9JqIpmE6t5tHU10pt1arFB9cLy3c';
function data_via_sheetapp(key) {
var ret = 'not found';
var ss = SpreadsheetApp.openById(SSID);
var s = ss.getSheetByName("data");
var data = s.getDataRange().getValues();
for (i = 0; i < data.length; i++) {
if (data[i][3] == key) {
ret = data[i][14];
continue;
}
}
//Logger.log(ret);
return ret;
}
Source Code GViz
function data_via_gviz(key) {
var ret = '';
var ts = new
Date().getTime();
var access_token = ScriptApp.getOAuthToken();
var request_options = {
"headers": {
"Authorization": "Bearer "
+ access_token,
"contentType": "application/json"
}
};
var query = encodeURIComponent("SELECT B WHERE A='"
+ key + "'");
var d = UrlFetchApp.fetch('https://docs.google.com/spreadsheets/d/'
+ SSID
+ '/gviz/tq?tqx=out:json&tq=' + query).getContentText();
// for private files add the access token as option
/*
var d = UrlFetchApp.fetch('https://docs.google.com/spreadsheets/d/' + SSID
+ '/gviz/tq?tqx=out:json&tq=' + query, request_options).getContentText();
*/
// parse result
d = d.replace("/*O_o*/", "");
d = d.replace("google.visualization.Query.setResponse(", "");
d = d.substr(0, d.length - 2);
d = JSON.parse(d);
// I did not know better how to parse the result ... /*O_o*/ has to be removed...
if (d.table.rows.length == 0)
ret = 'not found';
else
ret = d.table.rows[0].c[0].v;
//Logger.log(ret);
return ret;
}
Then both procedures are called via a Google Apps Script (Script as Web App).
Method 1: https://script.google.com/a/macros/consulity.com/s/AKfycbyS_bqrgiR0-VmPQCoYrw0zZInQ7kjwEzzWejW2u0GLVrtvM_g/exec?method=sheetapp
Method 2: https://script.google.com/a/macros/consulity.com/s/AKfycbyS_bqrgiR0-VmPQCoYrw0zZInQ7kjwEzzWejW2u0GLVrtvM_g/exec?method=gviz
Remark: Please do not forget the URL parameter method
Results
Surprisingly the GViz method took longer than the SheetApp approach.
| Technique |
Time (ms) |
| SheetApp |
3,113 |
| GViz |
5,654 |
Conclusion
When speed matters - and at least when handling key-value-pairs - the SheetApp method should be the preferred way
to retrieve data from Google Spreadsheets.
We assume, that SheetApp performs better than GViz due to server side caching. This has to be verified.
For a larger amount of data the result may be different and also parsing an html-result was not yet evaluated (only JSON).
Finally we should at least keep in mind that GViz might be an option to overcome the 50 concurrent users limit.
The SheetApp method outperforms the GViz API approach.
