Adding Email Status "Sent" in Column after email sends in google app scripts-CodePudding

I am hoping to get some assistance in figuring how to amend a cell in a column to add "Sent" after I run an email script. I can't seem to figure out how to write it when I do a for each function.

I tried using: row[status].setValue('Sent'); to go with my code, but it does not work. Here is my sample code with it built in below. Any suggestions on where I am going wrong? Thank you in advance.

function testEmail() {
  var email = 0 
  var firstName = 1
  var status = 2

  var htmltemp = HtmlService.createTemplateFromFile('template');
  var ss = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var lr = ss.getLastRow();
  var data = ss.getRange('A2:C'   lr).getValues();

  data.forEach(function(row){

    htmltemp.fName = row[firstName];

    var htmlForEmail = htmltemp.evaluate().getContent();

    GmailApp.sendEmail(
      row[email],
      "Subject",
      "Your email does not support HTML",{
        htmlBody: htmlForEmail,
      }
    )
    row[status].setValue('Sent'); //This does not work. How do I write this correctly to work line by line?  
  })
}

CodePudding user response：

Try this:

function testEmail() {
  var email = 0
  var firstName = 1
  var status = 2
  var htmltemp = HtmlService.createTemplateFromFile('template');
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var lr = sh.getLastRow();
  var data = sh.getRange('A2:C'   lr).getValues();
  data.forEach(row => {
    htmltemp.fName = row[firstName];
    var htmlForEmail = htmltemp.evaluate().getContent();
    GmailApp.sendEmail(row[email],"Subject","Your email does not support HTML", {htmlBody: htmlForEmail});
    sh.getRange(i   2 , 3).setValue('Sent');  
  })
}

If you wish to use it for not resending emails

function testEmail() {
  var email = 0
  var firstName = 1
  var status = 2
  var htmltemp = HtmlService.createTemplateFromFile('template');
  var sh = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('test');
  var lr = sh.getLastRow();
  var data = sh.getRange('A2:C'   lr).getValues();
  data.forEach(row => {
    htmltemp.fName = row[firstName];
    var htmlForEmail = htmltemp.evaluate().getContent();
    if(row[2] != "Sent") {
      GmailApp.sendEmail(row[email],"Subject","Your email does not support HTML", {htmlBody: htmlForEmail});
      sh.getRange(i   2 , 3).setValue('Sent');
    }
  })
}

CodePudding user response：

A possible solution would be to use .map() instead of .forEach() and then set all values to the sheet. This will save you time on setting the value each time.

data.map(function(row){

  htmltemp.fName = row[firstName];

  var htmlForEmail = htmltemp.evaluate().getContent();

  GmailApp.sendEmail(
    row[email],
    "Subject",
    "Your email does not support HTML",{
      htmlBody: htmlForEmail,
    }
  )

  row[status] = 'Sent';
  return row;

})

ss.getRange(2, 1, data.length, data[0].length)
  .setValues(data)