CODEWITHSUNDEEP
google-apps-script
BlueIris
BlueIris

Reputation: 149

Google Apps Script - How to change value in cell after sending an automatic email?

I would like my code to send an email based on a condition and then update a cell when that email has been sent. For instance, because row 16 in column D contains "𓀫", I want to send a particular email and then update that cell to "𓀕".

var spreadsheet   = SpreadsheetApp.getActiveSpreadsheet();
var sheet         = spreadsheet.getSheetByName('ð“€¢');
var data          = spreadsheet.getRange('ð“€¢!A2:E').getValues();
var columnToCheck = spreadsheet.getRange("D4:D").getValues();
var lastRow       = getLastRow(columnToCheck);

function eMail() {
  data.forEach((row, index) => {
    let [fName, sName, date, email, ] = row;

    if (date == "-" && email == "started") {
      var htmlBody  = HtmlService.createHtmlOutputFromFile('𓀇').getContent()
      .replace("#fName", fName)
      .replace("#sName", sName);
      
      MailApp.sendEmail ("[email protected]", "Hiya", sName, {htmlBody: htmlBody});
      sheet.getRange(2,4,lastRow).setValue("continuing");
    }
  })
}

What the code does, however, is put "continuing" in every cell in column D (not just the ones that match the if-statement, and thus who has been emailed). What am I mssing?

Upvotes: 0

Views: 467

Answers (1)

mshcruz
mshcruz

Reputation: 1987

getRange() can accept a different number of parameters. When you specify three parameters (getRange(2,4,lastRow)), you're getting the whole column starting from D2, and then you write continuing to all cells of that column.

If you want to write to just the cell in the row that meets the condition of the IF, then you can use the row's index:

sheet.getRange(index+2, 4).setValue("continuing");

Upvotes: 2

Related Questions