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

Updated November 05, 2018 23:03 PM

Updated November 23, 2018 16:03 PM

Updated August 14, 2018 16:03 PM

Updated October 22, 2018 02:03 AM