I have a google sheet containing 4 columns; title, url, published date and email sent (in that exact order).

When new rows are added to the sheet i want to execute a google script that will look through the 'email sent' column to see if an email has been sent and if not send an email containing the new rows and update the associated row(s) with a yes.

My current code is only getting the first row and nothing else.

Thanks in advance,

Mark

(see my current code below)

function sendemail() {

 //setup function
 var ActiveSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
 var StartRow = 2;
 var RowRange = ActiveSheet.getLastRow() - StartRow   1;
 var WholeRange = ActiveSheet.getRange(StartRow,1,RowRange,4);
 var AllValues = WholeRange.getValues();

 //iterate loop
 for (i in AllValues) {

 //set current row
 var CurrentRow = AllValues[i];

 //set subject line
 var Subject = "New Content on IG.com";

 //set HTML template for information
 var message = 
      "<p><b>Title: </b>"   CurrentRow[1]   "</p>"  
      "<p><b>Article: </b>"   CurrentRow[2]   "</p>"  
      "<p><b>Published Date: </b>"   CurrentRow[3]   "</p>";

 //define column to check if sent
 var EmailSent = CurrentRow[4];

 //define who to send grants to 
 var SendTo = "[email protected]";

 //if row has not been sent, then...  
 if (EmailSent != "Yes") {

  //set the row to look at
  var setRow = parseInt(i)   StartRow;

  //mark row as "sent"
  ActiveSheet.getRange(setRow, 4).setValue("Yes");

  //send the actual email  
  MailApp.sendEmail({
      to: SendTo,
      cc: "",
      subject: Subject,
      htmlBody: message,
});
}
}
}

CodePudding user response：

Try this:

function sendemail() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const sr = 2;
  const rg = sh.getRange(sr, 1, sh.getLastRow() - sr   1, 4);
  const vs = rg.getValues();
  vs.forEach((r, i) => {
    let Subject = "New Content on IG.com";
    let message =
      "<p><b>Title: </b>"   r[0]   "</p>"  
      "<p><b>Article: </b>"   r[1]   "</p>"  
        "<p><b>Published Date: </b>"   r[2]   "</p>";
    let EmailSent = r[3];
    let SendTo = "[email protected]";
    if (EmailSent != "Yes") {
      sh.getRange(i   sr, 4).setValue("Yes");
      MailApp.sendEmail({to: SendTo,cc: "",subject: Subject,htmlBody: message});
    }
  })
}

Test:

It turns then all to Yes