Google Apps Script Patterns: Writing rows of data to Google Sheets

In the previous post in the Google Apps Script Pattern series we looked at selectively updating rows of data in a Google Sheet. For this post we will look at a couple of patterns for inserting multiple rows of data into Google Sheets. We’ll start with the writing data example on the Google Apps Script site:

function addProduct() {
  var sheet = SpreadsheetApp.getActiveSheet();
  sheet.appendRow(['Cotton Sweatshirt XL', 'css004']);
}

This will add a row at the bottom of your sheet with the data:

Cotton Sweatshirt XLcss004

This is useful for a single row of data as it combines inserting a row and adding the data. If you have multiple rows then for performance you should use .setValues(). To use .setValues() to need to provide a 2D array of values. A common pattern is to loop over the returned data and push values into an array and then write them to a sheet. An example using calendar events is shown below:

function writeCalToSheet() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var cal = CalendarApp.getDefaultCalendar();
  var evnts = cal.getEvents(new Date("March 8, 2017"), new Date("March 14, 2018"));
  var values = [];
  // loop over the return events
  for (var i=0; i < evnts.length; i++) {
    var evnt = evnts[i];
    // push a row of data as 2d array
    values.push([evnt.getStartTime(), evnt.getEndTime(), evnt.getTitle(), evnt.getDescription()])
  }
  // sheet.insertRowsAfter(1, values.length); <- uncomment this if you want to insert blank rows at the top every time
  // write data to sheet
  sheet.getRange(sheet.getLastRow()+1, 1, values.length, values[0].length).setValues(values);
}

To write the data we need to specify a range to .setValues(). In the above example it is using .getRange(row, column, numRows, numColumns) to add data after the last row in the sheet. In the example the following parameters are used:

  • row = sheet.getLastRow()+1 – to get the last row of existing data and adding one to get the next row
  • column = 1 – the starting column which in this case 1 equals column A
  • numRows = values.length – the length of the values array we’ve built to get the number of rows
  • numColumns = values[0].length – the number of items in the first row of values to get the number of columns

When using .setValues() to add data after the last row of data you don’t need to worry about inserting blank rows as the .setValues() method will handle this for us. If you wanted to insert new data after the first row you can add/uncomment the following line:

sheet.insertRowsAfter(1, values.length);

This inserts the number of rows equivalent to the amount of data we’ve got.

Note: The above snippet is using .insertRowsAfter(). Another method is .insertRows() but this will throw an error if the row you are trying doesn’t exist.This is an issue if you only have a header row and you want to insert rows after it.

To add the data after the header row, assuming this is row 1, you also need to modify the .getRange() to start the range in row 2:

sheet.getRange(2, 1, values.length, values[0].length).setValues(values);

Quite often when inserting data you want to include formula as part of the row calculation. There are a couple of ways you can do this and if you are writing data then you can include formulas when using .setValues(). For example, if you wanted to add a formula that calculated the difference between the two calendar dates you could use the line:

values.push([evnt.getStartTime(),
             evnt.getEndTime(),
             evnt.getTitle(),
             evnt.getDescription(),
             '=B'+(i+2)+' - A'+(i+2)]);

As part of this we are using the loop iterator to build the R1C1 reference, which can get messy if you are appending rows at the end of your data. Another way of doing this is using the ARRAYFORMULA() built-in to Google Sheets. There are plenty of tutorials for using these, like this one from Ben Collins, out there and I won’t go into more detail here but one example for our first code sample would be to include =ARRAYFORMULA(B2:B-A2:A) in cell E2.

The pattern for building a .setValues() array makes several assumptions including the order and position of the columns we use remains unchanged. In the case of data from services like CalendarApp I haven’t discovered any useful patterns so if you know any please share.

Writing JSON data mapped to row headings

Working with structured data like JSON creates better opportunities to flexibly handle data. Lots of APIs provide data in a JSON format and below is a typical example from the FAA Airport Delays Services API which you can view at https://services.faa.gov/airport/delays?format=json:

[
   {
      "IATA":"DAL",
      "name":"Dallas Love Field",
      "delays":[
         {
            "reason":"TM Initiatives:MIT:WX",
            "type":"General Arrival/Departure Delay"
         }
      ],
      "uri":"http://services.faa.gov/airport/DAL/status"
   },
   ...
]

In the above example there are data values that are more than one level deep. As a sheet is 2D we need to flatten the data. Bruce McPherson has a nice pattern for flattening an object with dot syntax, which he has wrapped in a Flattener library (source code on Github). Bruce also has developed the functionality of these scripts further with a Fiddler class as part of this cUseful library and provided some ‘Fiddling APIs to Google Sheets’ examples (I can’t see a ‘flatten’ method in Fiddler so you probably have to use them in combination). If you are looking for a more atomic approach the pattern below is one I often use in projects:

Update: Google Apps Script has a new V8 runtime that permits the use of modern JavaScript syntax. An updated version of the code is contained in a revised V8 post.

function writeDelays(){
  var sheet = SpreadsheetApp.getActiveSheet();
  // getting some data
  var data_obj = UrlFetchApp.fetch('https://services.faa.gov/airport/delays?format=json');
  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;
}

Starting with a sheet with a header like this:

IATAnamedelays_0_reasondelays_0_typeuri

Results in data being written like this:

Results of writing JSON to Google Sheets

Unlike Bruce’s dot syntax I prefer using underscores … it’s just my preference. It’s also worth noting that as this API includes delays as an object array when flattened the array index is used to denote the item so to record any additional delays at the airport I would need to include columns delays_1_reason, delays_1_type and so on. In this example the order of columns isn’t important as long as the header row matches the flattened JSON keys. If you’ve got your own patterns for achieving something similar in a better way please share 🙂

chevron_left
chevron_right
css.php