Example JSON output

Google Apps Script Patterns: Writing rows of data to Google Sheets the V8 way

Currently one of the most popular posts on this site is my Google Apps Script Patterns: Writing rows of data to Google Sheets. This post covers a range of techniques and includes a typical pattern for writing structured data like JSON responses from third party sources. Given Google’s recent announcement of the general availability of the new V8 runtime I thought it was worth revisiting this particular pattern and using the modern syntax now available to Google Apps Script developers.

For some context for seasoned Google Apps Script developers like me until V8 we’ve been working in a bit of an old school JavaScript bubble. With V8 there is an opportunity to write less verbose and more succinct code. To see some of the differences below I’ve copied the Writing JSON data mapped to row headings example from my original post and updated this with a V8 version and will highlight the changes.

The following example originally used the FAA Airport Delays Services API which recently moved to a new endpoint. Given the current COVID-19 pandemic there aren’t many flights to test this so I’m using the Hypothesis API endpoint (search/get) – HT Stian Håklev for the recommendation for this API.

Example data response

{
   "total":30,
   "rows":[
      {
         "id":"E_pFQF31Eeq_OAeqP4VzJA",
         "created":"2020-03-04T08:49:37.029398+00:00",
         "updated":"2020-03-04T08:49:48.718670+00:00",
         "user":"acct:[email protected]",
         "uri":"https://towardsdatascience.com/an-implementation-guide-to-word2vec-using-numpy-and-google-sheets-13445eebd281",
         "text":"實作這篇看看",
         "tags":[
         ],
         ...
         "document":{
            "title":[
               "A line-by-line implementation guide to Word2Vec using Numpy"
            ]
         },
         "links":{
            "html":"https://hypothes.is/a/E_pFQF31Eeq_OAeqP4VzJA",
            "incontext":"https://hyp.is/E_pFQF31Eeq_OAeqP4VzJA/towardsdatascience.com/an-implementation-guide-to-word2vec-using-numpy-and-google-sheets-13445eebd281",
            "json":"https://hypothes.is/api/annotations/E_pFQF31Eeq_OAeqP4VzJA"
         },
         ...
      },
      ...
   ]
}

Example output

In this example I want to selectively record the data returned from the API call based on the headings in the first row. In the case of nested array values the headings use underscores for child items and the array index, for example:

         "document":{
            "title":[
               "A line-by-line implementation guide to Word2Vec using Numpy"
            ]
         },

Can be included in the Google Sheet data with document_title_0.

Example JSON output

You can make a copy of the ‘Writing data V8’ Google Sheet which includes the new code.

Original code (with modified API endpoint)

function writeData(){
  var sheet = SpreadsheetApp.getActiveSheet();
  // getting some data
  var data_obj = UrlFetchApp.fetch('https://api.hypothes.is/api/search?any=Google%20Sheets');
  var data = JSON.parse(data_obj.getContentText());
   
  // flatten the json
  var rows = data.map(function(d){
    return flatten_(d);
  });
  // insert new rows
  sheet.insertRowsAfter(1, rows.length);
 
  // getting our headers
  var heads = sheet.getDataRange()
                   .offset(0, 0, 1)
                   .getValues()[0];
     
  // convert object data into a 2d array
  var tr = rows.map (function (row) {
    return heads.map(function(cell){
      return row[cell] || "";
    });
  });
  // write result
  sheet.getRange(2, 1, tr.length, tr[0].length).setValues(tr);
}
 
/**
 * Flatten an object.
 * https://stackoverflow.com/a/19101235/1027723
 *
 * @param {Object} data object to flatten
 * @return {Object} the flattened result
 */
function flatten(data) {
    var result = {};
    function recurse (cur, prop) {
        if (Object(cur) !== cur) {
            result[prop] = cur;
        } else if (Array.isArray(cur)) {
             for(var i=0, l=cur.length; i<l; i++)
                 recurse(cur[i], prop ? prop+"_"+i : ""+i);
            if (l == 0)
                result[prop] = [];
        } else {
            var isEmpty = true;
            for (var p in cur) {
                isEmpty = false;
                recurse(cur[p], prop ? prop+"_"+p : p);
            }
            if (isEmpty)
                result[prop] = {};
        }
    }
    recurse(data, "");
    return result;
}

V8 Version

Below is a modified version of the script which uses V8 runtime syntax:

function writeData(){
  const sheet = SpreadsheetApp.getActiveSheet();
  // getting some data
  const data_obj = UrlFetchApp.fetch('https://api.hypothes.is/api/search?any=Google%20Sheets');
  const data = JSON.parse(data_obj.getContentText());
  // flatten the json
  const rows = data.rows.map(d => flatten_(d));
  // getting our headers
  const heads = sheet.getDataRange()
                   .offset(0, 0, 1)
                   .getValues()[0];
  // convert object data into a 2d array
  const tr = rows.map(row => heads.map(key => row[String(key)] || ''));
  // write result
  sheet.getRange(sheet.getLastRow()+1, 1, tr.length, tr[0].length).setValues(tr);
}
// Based on https://stackoverflow.com/a/54897035/1027723
const flatten_ = (obj, prefix = '', res = {}) =>
  Object.entries(obj).reduce((r, [key, val]) => {
    const k = `${prefix}${key}`;
    if(typeof val === 'object' && val !== null){
      flatten_(val, `${k}_`, r);
    } else {
      res[k] = val;
    }
    return r;
  }, res);

Variables using const or let instead of var

First thing to note is a change from var variable declarations to const. You can read more about using const or let instead of var in this great summary post by Adam Morris. In particular I’m going with Adam’s advice:

There is nothing on its own wrong with var, but I think it’s worth “overcompensating” when transitioning to V8 and to exclusively use const or let instead for a while, to get the hang of it.

Arrow functions

Similarly V8 enables some other syntax changes including arrow functions (=>) which let you pass an argument to another function as data, in one line. There are a couple of examples of arrow functions in the new script, in particular, for map methods:

// Old way to flatten the json
var rows = data.rows.map(function(d){
return flatten_(d);
});
// New way to flatten the json
const rows = data.rows.map(d => flatten_(d));

Flattening nested JSON and template literals

In terms of modifying a nested JSON object to a single depth I’ve used a slightly modified Stack Overflow solution posted by Matt Way. Of note in this new flatten function is the use of template literals (also known as template strings):

Template literals are enclosed by the backtick (` `) (grave accent) character instead of double or single quotes.

Template literals can contain placeholders. These are indicated by the dollar sign and curly braces (${expression}). The expressions in the placeholders and the text between the backticks (` `) get passed to a function.

For example In the flatten function the const k is a string made by combining the variables prefix and key:

const k = `${prefix}${key}`;

Summary

The new V8 runtime for Google Apps Script opens up a number of other syntax goodies not covered in this post. If you are looking for Google Apps Script specific resources to look at to get started with the V8 syntax I highlighly recommend the following:

chevron_left
chevron_right
css.php