# 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](https://hawksey.info/blog/tag/google-apps-script-patterns/) we finished with an example of how you can [read and use row data from a Google Sheet](https://hawksey.info/blog/2018/02/google-apps-script-patterns-using-the-destructuring-assignment-syntax-and-object-arrays-to-process-google-sheet-rows/). 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](https://hawksey.info/wp-content/uploads/2018/02/null-1.png)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.