Google Sheet / Apps script / Trigger when a new row is added

user_17160047 0
Viewed 1443

I am currently working on an Apps Script on a google sheet file. I made a script that sends mail to contacts. In my sheet the contacts are in the form of a line which is automatically added with a zapier. I have installed an onChange trigger but I would like it to act only when a new row is added to my sheet, I have looked everywhere but no solutions work or correspond to my problem. So if anybody have an idea to solve it, it would be helpfull (I put you a copy of my script below) thanks in advance '

function envoie_mail (){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet1=ss.getSheetByName('testmail');
  var lr = sheet1.getLastRow(); 
  var eligible = sheet1.getRange(lr,4).getValue();
  var emailadresse = sheet1.getRange(lr,1).getValue();
  var subject = sheet1.getRange(lr,2).getValue();

  if (eligible === "non"){
  var htmlTemplate = HtmlService.createTemplateFromFile("body.html");
  var modele = htmlTemplate.evaluate().getContent();
  var reference = sheet1.getRange(lr,3).getValue();
  modele = modele.replace("<ref>",reference).replace("<ref2>",reference);
 MailApp.sendEmail({
    to: emailadresse,
    subject: subject, 
    htmlBody: modele
  });
}

   }

zapier google-apps-script google-sheets javascript
1 Answers

I don't know how zapier works, howerver try to put an indicator and test as follows

function createSpreadsheetChangeTrigger() {
    var ss = SpreadsheetApp.getActive();
    ScriptApp.newTrigger('onChange')
      .forSpreadsheet(ss)
      .onChange()
      .create();
} 

function onChange(e) {

  var f = SpreadsheetApp.getActiveSheet()
  var data = f.getRange(1,f.getLastColumn(),f.getLastRow(),1).getValues().join().split(',')
  for (var i=0;i<data.length;i++){
    if (data[i]=='') {
      Browser.msgBox('envoi mail ligne ' + (i+1))
      f.getRange((i+1),f.getLastColumn()).setValue('ok')
    }
  }

}

https://docs.google.com/spreadsheets/d/1TEWS1e3uOnYybgbuScWb9plA9J7rgVjt16g9Ucg0x3M/copy

enter image description here

user_150193800
Related