Google Apps Script Patterns: Conditionally updating rows of Google Sheet data by reading and writing data once

In the previous post in this Google Apps Script Patterns series we finished with an example of how you can read and use row data from a Google Sheet. In this post we are going to extend this pattern so that as well as reading the data we also update rows.

To recap the previous pattern used a sheet similar to the image below, using the email_address and message columns to compose an email. For this example I’ve added an extra column, email_sent, where we’ll record if/when the email was sent. We’ll also use this column to only send emails to recipients that haven’t been sent an email.

Google Sheet setup

To handle the send condition and row updating we can use the following pattern:

function sendEmails() {
  var subject = "Sending emails from a Spreadsheet";
  var sheet = SpreadsheetApp.getActiveSheet();
  // Fetch the range of cells A1:B3
  var dataRange = sheet.getDataRange();
  // Fetch values for each row in the Range.
  var data = dataRange.getValues();
  var header = data.shift();
  // get the index of column named 'email_sent' (Assume header names are unique)
  // http://ramblings.mcpher.com/Home/excelquirks/gooscript/arrayfunctions
  var email_sent_col = header.indexOf("email_sent");
  // convert 2d array into object array
  // https://stackoverflow.com/a/22917499/1027723
  // for pretty version see https://hawksey.info/blog/?p=17869/#comment-184945
  var obj = data.map(function(values) {
    return header.reduce(function(o, k, i) {
      o[k] = values[i];
      return o;
    }, {});
  });
  // loop through all the data
  obj.forEach(function(row, rowIdx){
    // only send emails is email_sent cell is blank
    if (row.email_sent === ''){
      try {
        MailApp.sendEmail(row.email_address, subject, row.message);
        // modify cell to record email sent date
        data[rowIdx][email_sent_col] = new Date();
      } catch(e) {
        // modify cell to record error
        data[rowIdx][email_sent_col] = e.message;
      }
    }
  });
  // updating the sheet with new data
  // .offset(rowOffset, columnOffset, numRows)
  dataRange.offset(1, 0, data.length).setValues(data);
}

The key design feature is it is limiting calls to sheet services, in particular, our data is read and written only once. Working in a batch approach has a huge performance benefits particularly as Google Apps Script has some tight quotas on run and execute time.

For example, when we get data we are getting a 2D array of all the cell values in the sheet. This is then used to extract header an array of the column headers which is used to get the index of the email_sent column and also used to build an object array representation of data called obj. We then use obj to iterate across the rows using .forEach(). As part of the .forEach() iteration as well as using the current value row, we also use the index of the current element being processed in the array, rowIdx. The rowIdx as well as the column index of the email_sent column is used to update the data 2D array. Finally, our updated 2D data array is written back to the sheet with .setValues().

This pattern is easy to extend, for example, if you want to update additional columns these can be specified by creating additional variables using header.indexOf() and updated using the defined variable in data[rowIdx][your_col_var]. The same is true if you want to update data based on more conditions by including them in the if() statement. There are likely to be other scenarios where a different approach is required but hopefully this is a useful pattern for you to use.

chevron_left
chevron_right
css.php