Google SpreadsheetApp vs. Google Visualization API

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:

  1. Google SpreadsheetApp (Apps Script Class) - (further referred to as SheetApp)
  2. 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.


Dieter Neumann