Google-sheets – Email notification when any cell in specific column is changed

google sheetsgoogle-apps-script

I have a Google Sheet in which I would like to get an email notification when any cell changes in a specific column.
I am new to scripting so it is mind blowing, I have no idea on how to do this and would appreciate any assistance on this.

Column U2:U

Updated script

This works when I manually edit any cell in column U. However I would like to have an formula/arrayformula in this column but when I do this I stop receiving email notifications.

function sendMailEdit(e){
if (e.range.columnStart != 21 ) return; 
GmailApp.sendEmail("email.address@gmail.com","Subject", "New Name Added - See Cell U" + e.range.rowStart + " Sheet Name") 
}

Best Answer

You want to send an email when a value chnages in Column U2:U. Your onEdit script works but... You have formula/arrayformula in Column U and the script is not being triggered by changes in those values.

An onEdit script is on triggered when a user changes a value in a spreadsheet. Changes due to formula do not trigger the script.

Workaround

An option is to use a time-driven trigger.

The challenge is to establish whether a value in Column U has changed since the last time the script was run. The solution is to make a copy of the values in Column U and to compare these against the actual values when the script is executed.

The solution consists of two scripts:

  • function duplicaterange():
    • run once only - no trigger.
    • creates the initial reference copy of the values in Column U.
    • for sake of simplicity, the script uses Column V for the reference data. You can change this to a different column/sheet.
  • function sendemail()
    • script must be installed as a time-driven trigger; the frequency is at your option.
    • compares the current/actual values of cells in Column U against the reference copy.
    • where the current/actual value <> reference copy, then an email is sent.
    • regardless of whether any/many emails are sent, the last step in this script is to update the reference copy of the values in Column U. This, the reference copy is always up-to-date.
function duplicaterange(){

  // needs to be run ONLY ONCE to initiate a reference range

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet1"
  var sheet = ss.getSheetByName(sheetname);
  
  // parameters to get a data range
  var datafirstrow = 2;
  var dataCol = 21; // Column U = 21
  // get the last row
  var LR = sheet.getLastRow();
  // Logger.log("DEBUG: the last row = "+LR);
  if (LR <= datafirstrow){
    // Logger.log("DEBUG: no data")
    return;
  }
  
  // get range for column U
  var sourcerange = sheet.getRange(datafirstrow, dataCol, LR-datafirstrow+1)
  // Logger.log("DEBUG: the range = "+sourcerange.getA1Notation())
  var sourcevalues = sourcerange.getValues();
  
  // create target row (assume column V)
  var targetrange = sheet.getRange(datafirstrow, dataCol+1, LR-datafirstrow+1)
  // Logger.log("DEBUG: the target range = "+targetrange.getA1Notation());
  
  // copy source to target
  targetrange.setValues(sourcevalues);
  SpreadsheetApp.flush();
  return;
  
}

function sendemail(){

  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheetname = "Sheet1"
  var sheet = ss.getSheetByName(sheetname);
  
  // parameters to get a data range
  var datafirstrow = 2;
  var dataColU = 21; // Column U = 21
  var dataColTarget = 22; // Column V = 22
  
  // get the last row
  var LR = sheet.getLastRow();
  // Logger.log("DEBUG: the last row = "+LR)
  if (LR <= datafirstrow){
    // Logger.log("DEBUG: no data")
    return;
  }
  
  // get range for column U
  var sourcerange = sheet.getRange(datafirstrow, dataColU, LR-datafirstrow+1)
  // Logger.log("DEBUG: the range = "+sourcerange.getA1Notation())
  var sourcevalues = sourcerange.getValues();
  
  // get the target range (assume column V)
  var targetrange = sheet.getRange(datafirstrow, dataColTarget, LR-datafirstrow+1)
  // Logger.log("DEBUG: the target range = "+targetrange.getA1Notation())
  var targetvalues = targetrange.getValues();

  // calculate the number of rows of data
  var numrows = LR-datafirstrow+1;
  // Logger.log("DEBUG: the number of rows = "+numrows)
 
  for (var i=0;i<numrows;i++){
    if (sourcevalues[i][0] != targetvalues[i][0]){
      // values do not match, so Column U has changed
      // so send email
      var rownumber = i+datafirstrow;
      //GmailApp.sendEmail("email.address@gmail.com","Subject", "New Name Added - See Cell U" + rownumber + " Sheet Name") 
      //Logger.log("DEBUG: i="+i+", send an email for row number "+rownumber);
     }
  }
  
 
  // update the revised source data to target range
  targetrange.setValues(sourcevalues);
  SpreadsheetApp.flush();
  
  return;

}
Related Topic