javascript - How to send html email line by line using google apps script and google sheet data -
i have script send email line-by-line. problem have cannot use data in current line iteration in email template. know how call functions template though in case cannot call function being evaluated.
my solution create function loop through data again , send line instance in email break. problem takes long loop through lines twice when im sure can done once.
i hope made sense.
code.gs
function sendmail() { var ss = spreadsheetapp.getactivespreadsheet(); var sheet = ss.getsheetbyname('emaillist'); // data array var data = sheet.getdatarange().getvalues(); // loop on each line (var = 1; < data.length; i+=1) { // check status of email if (data[i][4] != 'sent') { // html file content var html = htmlservice.createtemplatefromfile('index').evaluate().getcontent(); // send email mailapp.sendemail({ to: data[i][2], subject: 'hi ' + data[i][0], htmlbody: html }); // set status sent sheet.getrange(i + 1,5).setvalue('sent'); } } // end } // end function sendmail function getdata() { var ss = spreadsheetapp.getactivespreadsheet(); var sheet = ss.getsheetbyname('emaillist'); // data array var data = sheet.getdatarange().getvalues(); // loop on each line (var = 1; < data.length; i+=1) { // check status of email if (data[i][4] != 'sent') { var firstname = data[i][0]; var lastname = data[i][1]; var email = data[i][2]; var message = data[i][3]; break; } } // end var returndata = [firstname, lastname, email, message]; return returndata; } // end function getdata
index.html
<!doctype html> <html> <head> <base target="_top"> <? var data = getdata(); ?> </head> <body> hi <?= data[0];?> <?= data[1];?> , email address <?= data[2];?> have message you: <?= data[3];?> </body> </html>
you need read pushing variables templates
your code.gs
for (var = 1; < data.length; i+=1) { // check status of email if (data[i][4] != 'sent') { var firstname = data[i][0]; var lastname = data[i][1]; var email = data[i][2]; var message = data[i][3]; var returndata = [firstname, lastname, email, message]; var html = htmlservice.createtemplatefromfile('index'); html.data = returndata ; var template = html.evaluate().getcontent(); // send email mailapp.sendemail({ to: data[i][2], subject: 'hi ' + data[i][0], htmlbody: template }); // set status sent sheet.getrange(i + 1,5).setvalue('sent'); }
your index.html
<!doctype html> <html> <head> <base target="_top"> </head> <body> hi <?= data[0];?> <?= data[1];?> , email address <?= data[2];?> have message you: <?= data[3];?> </body> </html>
Comments
Post a Comment