Google Apps Script Patterns: Using the destructuring assignment syntax and object arrays to process Google Sheet rows

In my last post I shared a Google Apps Script Pattern for getting a Google Sheet header row. In this post we are going to extend this to look at some patterns for processing rows of data from Google Sheets, which is quite a common operation, for example, using a row of data to populate a document, send emails or something else.
There are a number of ways you can do this and Google Apps Script has evolved over time to make this even easier. In one of the old Google Apps Script tutorials for sending emails a suggested pattern for a sheet that looks like the one below is:

Example Google Sheets Data
function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2;  // First row of data to process
  var numRows = 2;   // Number of rows to process
  // Fetch the range of cells A2:B3
  var dataRange = sheet.getRange(startRow, 1, numRows, 2)
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  for (i in data) {
    var row = data[i];
    var emailAddress = row[0];  // First column
    var message = row[1];       // Second column
    var subject = "Sending emails from a Spreadsheet";
    MailApp.sendEmail(emailAddress, subject, message);
  }
}

As part of this in the loop for each row variables are set for emailAddress and message, which is then used to send an email to the recipient. This can get a bit messy if you start inserting columns into your data. Another way to do this, and a coding pattern that can be useful in other situations, is using a destructuring assignment syntax:

The destructuring assignment syntax is a JavaScript expression that makes it possible to unpack values from arrays, or properties from objects, into distinct variables. [Ref]

This was recently highlighted in a post in the Google Apps Script G+ community by Dimu Designs. As their example of destructuring assignment syntax is so clear I’m reposting here, with permission.

var a, b, c;
[a, b, c] = [10, 20, 30];
Logger.log(a); // 10
Logger.log(b); // 20
Logger.log(c); // 30

Now this may seem like trivial syntactic sugar but this feature can be quite useful in certain contexts and can be a boon with respect to readability. For example, say you have a sheet with a list of contacts with columns for name, address, phone, email and you want to reference these by name. You can do something like this:

var name, address, phone, email;
var values = SpreadsheetApp.openById("").getRange("sheetName!A1:D").getValues();
values.forEach(function(row){
   [name, address, phone, email] = row;
   // do stuff with row data using named references...
})

So using the send email example it can be rewritten like this:

function sendEmails() {
  var emailAddress, message;
  var subject = "Sending emails from a Spreadsheet";
  var sheet = SpreadsheetApp.getActiveSheet();
  // Select all the data from the sheet
  var dataRange = sheet.getDataRange();
  // Fetch all the values in the Range.
  var data = dataRange.getValues().shift(); // removes the first row from data
  data.forEach(function(row){
    [emailAddress, message] = row;
    MailApp.sendEmail(emailAddress, subject, message);
  });
}

This pattern uses a .forEach() array iterator to loop through the rows. Google Apps Script is a bit of a Frankenstein syntax and officially based on JavaScript 1.6 but using features from 1.7 and 1.8. Whilst .forEach() was included in JavaScript 1.6 I don’t recall it being available until after 2014 and think it’s one of those methods that has snuck in over the years, as has destructuring assignments. As with the previous example one consideration when using this pattern is the order of variables is important.. So again if you insert a column in your sheet between the columns for emailAddress and message it will screw up your script.
An alternative pattern you can use is converting the 2d array returned with .getValues() into an object array. There are lots of resources if you need to learn more about object arrays and I won’t go into details here but essentially we can turn this:

[["email_address", "message"],
["[email protected]", "This is a test"],
["[email protected]", "This is a second test"]]

into this:

[{email_address:"[email protected]", message:"This is a test"},
{email_address:"[email protected]", message:"This is a second test"}]

With this we can set up the script to reference the data by column name. There are lots of snippets and libraries out there that can handle converting a .getValues() array into an object array e.g. James Ferreira’s ObjService and Bruce Mcpherson’s Fiddler. These can handle things like punctuation and illegal characters in column names. If your column names are suitable there are a lot of code snippets if you want a quick and dirty method. My favourite is from this Stackoverflow answer which uses .map() and .reduce(). If you are not familiar with these again there are lots of resources exampling them, including this explanation from Bruce Mcpherson on array operations.

Using an object array the ‘send email’ example would look like this (Update: Andrew Roberts has kindly provided a version of this with a more readable version of the 2d to object array in the comments):

function sendEmails() {
  var subject = "Sending emails from a Spreadsheet";
  var sheet = SpreadsheetApp.getActiveSheet();
  // Select all the data from the sheet
  var dataRange = sheet.getDataRange();
  // Fetch all the values in the Range.
  var data = dataRange.getValues();
  // convert 2d array into object array
  // https://stackoverflow.com/a/22917499/1027723
  var header = data.shift();
  var obj = data.map(function(values) {
    return header.reduce(function(o, k, i) {
      o[k] = values[i];
      return o;
    }, {});
  });
  // Loop rows and send emails
  obj.forEach(function(row){
    MailApp.sendEmail(row.email_address, subject, row.message);
  });
}

With this method we are using object notation to reference each row of data e.g. row.email_address. Doing this it doesn’t matter if new columns are entered into the sheet as long as the column header names remain unchanged. If you are looking for a method that can handle header name changes you need have a look at Google Sheets Metadata (Bruce has posts on this as well).

I hope you’ve found this tour of row processing patterns useful. If you have suggestions for Google Apps Script Patterns please get in touch :).

chevron_left
chevron_right

Join the conversation

comment 6 comments
  • Damion Murray

    Nice read! And thanks for the shout out! (Dimu Designs here)
    You might want to update the article though.
    Found out we can combine variable declaration with assignment as follows:
    var [a,b,c] = [10,20,30]

  • Andrew

    I found the map/reduce a bit dense to follow, so I’ve expanded it into something a little more readable:

    function sendEmail() {
      var sheet = SpreadsheetApp.getActiveSheet();
      // Select all the data from the sheet
      var dataRange = sheet.getDataRange();
      // Fetch all the values in the Range.
      var data = dataRange.getValues();
      // convert 2d array into object array
      // https://stackoverflow.com/a/22917499/1027723
      var header = data.shift();
      // var initialAccumulatorValue = {}  // Get rid of this line (see next post comment)
      // Convert each row into an object
      var object = data.map(function(row) {
        // Create a new object for next row using the header as a key
        var nextRowObject = header.reduce(function(accumulator, currentValue, currentIndex) {
          accumulator[currentValue] = row[currentIndex];
          return accumulator;
        }, {}) // Use {} here rather than initialAccumulatorValue (see next post comment)
        return nextRowObject;
      });
      // Loop rows and send emails
      object.forEach(function(row){
        MailApp.sendEmail(row.email_address, subject, row.message);
      });
    }
    • Martin Hawksey

      Thanks Andrew – it was a little black box so the more readable version is a nice addition (I’ve edited you comment slightly to reference from the post)

    • Andrew

      Just found a bug in that snippet. The initialAccumulatorValue variable can’t be used, it has to be {} each time or else a reference to the same variable get used each time and the object is filled with the last row’s data.

      // var initialAccumulatorValue = {}  // Get rid of this line
        // Convert each row into an object
        var object = data.map(function(row) {
          // Create a new object for next row using the header as a key
          var nextRowObject = header.reduce(function(accumulator, currentValue, currentIndex) {
            accumulator[currentValue] = row[currentIndex];
            return accumulator;
          }, {}) // Use {} here rather than initialAccumulatorValue
          return nextRowObject;
        });
      
      • Martin Hawksey

        I missed that one – updated your original snippet as well to avoid confusion

  • Andrew

    /*
    Here's another variation on this theme where the first column is used as the
    key and the value of each key is another object using the remaining headers
           city    pop    height
           ----    ---    ------
      1  | city1   111    1111
      2  | city2   222    2222
      3  | city3   333    3333
      {
        city1: {
          pop: 111,
          height: 1111
        },
        city2: {
          pop: 222,
          height: 2222
        },
        city3: {
          pop: 333,
          height: 3333
        },
      }
    */
    function createObjectFromSpreadsheet2(sheet) {
      var data = sheet.getDataRange().getValues(),
          header = data.shift();
      // Convert each row into an object
      var object = {}
      data.forEach(function(row) {
        // Create a new object for next row using the city name as the key, and the
        // value another object holding the other values
        var nextRowObject = header.reduce(function(accumulator, currentValue, currentIndex) {
          var key = row[0];
          if (currentIndex === 0) {
            object[key] = {};
          } else {
            object[key][currentValue] = row[currentIndex];
          }
          return accumulator;
        }, {}) // initialAccumulatorValue
        });
      return object;
    } // createObjectFromSpreadsheet2
    

Comments are closed.

css.php