Google-sheets – How to trigger onChange trigger on inserts only

google sheetsgoogle-apps-scriptgoogle-apps-script-triggers

I am trying to create a trigger that sends emails when lines are appended or inserted to a sheet. The problem I have is that the python script that is connected to the google sheet purges sheet "new_strikes" then inserts new information in the same Google sheet. I have a onChange trigger like so:

function trigFunc(e){
  var activeSheet = e.source.getActiveSheet();
  if(activeSheet.getName() != 'new_strikes') return;
  Utilities.sleep(30);
  sendEmail();
}

The problem is when I purge then insert data into this sheet, the script runs twice–once for the purge and once for the edit. Since the trigger sends an email, I get two emails. How do I modify the code or trigger so that it only tracks the insertion of data and therefore sends one email?

Best Answer

Instead of calling trigFunc(e) directly from the on change trigger, add the following function to your project and set your trigger to call it:

function respondToOnChange(e){
  if(e.changeType === 'INSERT_ROW') trigFunc(e);
}

Resources

https://developers.google.com/apps-script/guides/triggers/events

Related Solutions

Google-sheets – How to use triggers with functions that require parameters from the spreadsheet

How to use triggers with functions that require parameters from the spreadsheet?

AFAIK, you cannot. The solution is to create one single function, for example called RefreshAll, inside which you call all the wrapper functions (GetValue) containing the actual parameters which need to be passed to the function that actually does the job (RealFunctionThatDoesTheJob).

Note: Sorry if this sounds complicated. If you only have one set of parameters (as opposed to 3, in the example below), you can do away with GetValue and call RealFunctionThatDoesTheJob directly from within the function that is being triggered (RefreshAll).

Lastly, to have your triggers send to your spreadsheet those values that are dependent on parameters, you need to have the script push the values to the spreadsheet using (currentSheet.getRange(Row,Column).setValue(Value);)

Here is the example:

function RefreshAll() {

  var currentSheet = SpreadsheetApp.openById("ID").getSheetByName("MySheet");

  var Value1 = GetValue1();
  currentSheet.getRange(Row,Column).setValue(Value1);

  var Value2 = GetValue2();
  currentSheet.getRange(Row,Column).setValue(Value2);   

  var Value3 = GetValue3();
  currentSheet.getRange(Row,Column).setValue(Value3);

}

function GetValue1() {
  return RealFunctionThatDoesTheJob("Parameter1","Parameter2","Parameter3");
}

function GetValue2() {
  return RealFunctionThatDoesTheJob("Parameter4","Parameter5","Parameter6");
}

function GetValue3() {
  return RealFunctionThatDoesTheJob("Parameter7","Parameter8","Parameter9");
}

Gmail – Utilizing noreply option on Google Script SendEmail function

According to the documentation

this option [noreply] is only possible for Google Apps accounts, not Gmail users

Best Answer

Related Solutions

Google-sheets – How to use triggers with functions that require parameters from the spreadsheet

Gmail – Utilizing noreply option on Google Script SendEmail function

Related Topic