0
231
ViewsGoogle Sheet / Apps script / Trigger when a new row is added
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
});
}
}
·
Juan Pablo Isaza
1 answers
Answer question0
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
·
Juan Pablo Isaza
Report
Answer question