0

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") 
}
5
  • Welcome to Web Applications. Start by reading developers.google.com/apps-script/guides/sheets you might also would have to learn about the basics of JavaScript Once you have done that, please edit you question to show what you tried. – Rubén Dec 9 '20 at 14:25
  • Hi,I am new to script so have no idea where to start, I was hoping for help and see a script to get a better understanding. – Dmcn2020 Dec 14 '20 at 9:58
  • 1
    Hi. There are two issues. #1) You want to get an email when any cell changes in column V2:V; there are many precedents and I do understand it is hard to know where to start/how to find them. I'm sure we can/will help. #2) I am new to script so have no idea where to start. With respect, that is an excuse, not a problem. You need to prioritise development of skills. As a starting point, @Rubén gave a link to the Guide to Apps Script, and I'd also recommend the Google Tutorials. – Tedinoz Dec 15 '20 at 10:46
  • Hi, I have had a go and got the below, which 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, Can you give me some help on this ? 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") } – Dmcn2020 Dec 16 '20 at 11:16
  • You must have also set an installable trigger (onEdit (or possibly an OnChange with this script) - that's okay if the script is doing what you want it to do. Regarding I would like to have an formula/arrayformula in this column but when I do this I stop receiving email notifications, would you please explain the exact problem that you are having. But note that An installable edit trigger runs when a user modifies a value in a spreadsheet. The script will not execute because a formula value changes. – Tedinoz Dec 16 '20 at 14:35
0

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;

}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.