Sending an email from Google Sheets when it matches certain criteria

by shubar w   Last Updated December 06, 2018 05:03 AM

I have a Google spreadsheet that I created to track daily equipment inspections. The data in the sheet comes from a form that is filled out by whoever inspects the equipment for that day. Here's a link to the sheet:

function sendEmails() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var lastRow = sheet.getLastRow();
  var lastColumn = sheet.getLastColumn();
  var searchRow = sheet.getRange(lastRow,1,lastRow,lastColumn).getValues().toString();
  var machine = sheet.getRange(lastRow, 3).getValue().toString();
  var allResults = [];
  var badResults = [];
  var item= [];
  for (var i = 1; i < lastColumn; ++i) {
    allResults[i] = sheet.getRange(lastRow, i).getValue();
    if (allResults.indexOf ('NOT OK') > -1){
      badResults[i] = sheet.getRange(lastRow, i).getValue();
      item[i] = sheet.getRange(1, i).getValue();
  if (searchRow.indexOf("NOT OK") > -1) {
    MailApp.sendEmail('[email protected]', 'Equipment issue', 'An inspection of EWP ' + machine + ' has returned issues');

I've managed to get it to send an email when something is NOT OK. I created a loop containing an if statement to try and find the items that are NOT OK and assign them a variable, which I then want to use in my email message. The loop is returning all of the values in the row after it finds NOT OK instead of only the cells that contain NOT OK.

Related Questions

How would you improve this google sheets roster?

Updated April 27, 2019 08:03 AM

Avoiding an authorization request for google scripts

Updated November 23, 2018 16:03 PM