1

Why is my OnChange trigger triggering multiple times? I have created this code to generate a pdf and email it. To run it automatically I have created an OnChange trigger. It runs good however executes multiple times. Please help...

function emailPoaAsPDF() {

  var ss =SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1xEEEiLfil1qfetSwZRhr02Q9uoXvWtCxq22JywTu5mo/edit#gid=1872480652").getSheetByName("POA Temp");

  var email = ss.getRange("a37").getValue();
  var cc_email = "xxxxxx@gmail.com";
  var name = ss.getRange("a34").getValue();
  var sub = "xxxxxxxxxxxxxxxxxxxxxxxxxxxxx of "+ name;     
  var body = "Hello "+ name + ","
    + "xxxxxxxxxxxxxxxxxxxx"

  var url='https://docs.google.com/spreadsheets/d/1xEEEiLfil1qfetSwZRhr02Q9uoXvWtCxq22JywTu5mo/export?';
  var exportOptions =
   'exportFormat=pdf&format=pdf' +
   '&size=a4' + 
   '&scale=2' + 
   '&top_margin=1' +            
   '&bottom_margin=1' +         
   '&left_margin=1.25' +        
   '&right_margin=1.25' +
   '&portrait=true' + 
   '&fitw=false' + 
   '&sheetnames=false&printtitle=false' + 
   '&pagenumbers=false&gridlines=false' + 
   '&fzr=false' + 
   '&gid=1872480652';
  var params = {method:"GET",headers:{"authorization":"Bearer "+ 
  ScriptApp.getOAuthToken()}};
  var response = UrlFetchApp.fetch(url+exportOptions, params).getBlob();
  GmailApp.sendEmail(email, sub, body, {
  htmlBody: body,
  cc: cc_email,
  attachments: [{
    fileName: "xxx for " + ss.getRange("a34").getValue().toString() +".pdf",
    content: response.getBytes(),
    mimeType: "application/pdf"
   }]
  });
  var nameFile = ss.getRange("e7").getValue().toString() + " for " + 
  ss.getRange("a7").getValue().toString() +".pdf"
Ovais Majid
  • 19
  • 5
  • I think you are making changes too fast and some of them are executed with a delay. – Marios Dec 19 '20 at 11:52
  • Everything gets updated all at once – Ovais Majid Dec 19 '20 at 11:59
  • Then you are might doing multiple changes at once. – Marios Dec 19 '20 at 12:00
  • As your code is now, it will run on any change - insert row, change sheet name, edit / delete a cell content, updating certain formulas etc. If this is not what you want - please specify on which kind of change you want to run this code? Only when the cell content of `A37` changes? – ziganotschka Dec 21 '20 at 11:11
  • @ziganotschka, Yes only when A37 changes.. Please help – Ovais Majid Dec 23 '20 at 10:51
  • @ziganotschka Hello there, I tried the active range thing in all the possible ways but it won't trigger the email at all. can u please help me in a more detailed way? My point is, I want the email & pdf to trigger automatically whenever a new data row is added to a sheet. Waiting for a reply – Ovais Majid Dec 29 '20 at 02:18

1 Answers1

0

To specify that a functionality shall be triggered only if the change is taking place in a specific cell - implement a respective conditional statement

Sample

function emailPoaAsPDF() {
  var ss = SpreadsheetApp.openByUrl("https://docs.google.com/spreadsheets/d/1xEEEiLfil1qfetSwZRhr02Q9uoXvWtCxq22JywTu5mo/edit#gid=1872480652").getSheetByName("POA Temp");
  var activeRange = ss.getActiveSheet().getActiveRange();
  if( activeRange.getA1Notation() == "A37"){
  // execute the rest of the code
  }
}

Depending in your situation (e.g. if the value changes due to an ImportRange formula), a change of cell A37 might not necessary make it the active cell.

If that is the case, you would need to take an a bit more complex approach - saving the old value in Script properties and compare the new against the old value within the conditional statement.

You should also consider either you caould use the onEdit trigger instead of onChange - this will give you access to the event object e.range.

See here for a more detailed sample.

ziganotschka
  • 25,866
  • 2
  • 16
  • 33
  • Hello there, I tried the active range thing in all the possible ways but it won't trigger the email at all. can u please help me in a more detailed way? My point is, I want the email & pdf to trigger automatically whenever a new data row is added to a sheet. Waiting for a reply – Ovais Majid Dec 29 '20 at 02:21
  • `whenever a new data row is added` is different because then the active range will not be only one cell. Do you mean an already existing row being populated or a new row inserted? Check your true active range by implementing before the `if` statement: `Logger.log(ss.getActiveSheet().getActiveRange().getA1Notation());` and see the result. – ziganotschka Dec 29 '20 at 08:55
  • A new row will be inserted – Ovais Majid Dec 29 '20 at 09:51
  • By results I mean: look at the resulting log (View->Executions). – ziganotschka Dec 29 '20 at 09:53
  • Hey, I have been trying every possible way... Nothing works. Please tell me this...How do i write this code.. if the value of a cell is more than 0, the OnChange trigger should execute. Please note we aren't changing any values manually..........................Other way that i am thinking of is..... If the timestamp of the new row inserted is equal to now() then execute the trigger....Please tell me how do i write the both.... I would be grateful to you. – Ovais Majid Jan 03 '21 at 03:32
  • " if the value of a cell is more than 0,": assuming that you mean by this the value in cell A37: `if( ss.getActiveSheet().getRange("A37").getValue() > 0){ // execute the rest of the code }` As for timestamps, it is similar, but you need to implement some more code, see e.g. [this](https://stackoverflow.com/questions/62791531/paste-the-values-for-each-column-until-the-todays-date-google-apps-scripts/62794295#62794295) – ziganotschka Jan 05 '21 at 10:18