Search code examples
javascriptemailgoogle-apps-scripthtml-email

How to send html email line by line using google apps script and google sheet data

I have a script to send an email line-by-line. The problem I have is that I cannot use the data in the current line iteration in an email template. I know how to call functions from the template though in this case I cannot call the function that is being evaluated.

My solution was to create another function that would loop through the data again and send the line instance in an email then break. The problem is that now it takes to long to loop through the lines twice when im sure it can be done once.

I hope i made sense.

Code.gs

function sendMail() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('EmailList');

  // get the data as an array
  var data = sheet.getDataRange().getValues();

  // Loop over each line
  for (var i = 1; i < data.length; i+=1) {

    // Check the status of the email
    if (data[i][4] != 'Sent') {
      // get html file content
      var html =     HtmlService.createTemplateFromFile('Index').evaluate().getContent();

      // send the email
      MailApp.sendEmail({
        to: data[i][2],
        subject: 'Hi ' + data[i][0],
        htmlBody: html
          });

      // Set the status to sent
      sheet.getRange(i + 1,5).setValue('Sent');
    }
  } // end for
} // end function sendMail


function getData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('EmailList');

  // get the data as an array
  var data = sheet.getDataRange().getValues();

  // Loop over each line
  for (var i = 1; i < data.length; i+=1) {

    // Check the status of the 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 for
  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];?> , with email address <?= data[2];?>
    I have a message for you:
    <?= data[3];?> 
  </body>
</html>
Solution

  • You need to read this pushing variables to templates

    Your Code.gs 

    for (var i = 1; i < data.length; i+=1) {

 // Check the status of the 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 the email
 MailApp.sendEmail({
   to: data[i][2],
   subject: 'Hi ' + data[i][0],
   htmlBody: template
   });

  // Set the status to 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];?> , with email address <?= data[2];?>
    I have a message for you:
    <?= data[3];?> 
  </body>
</html>