Send Email When New Rows Are Added (Google App Script & Google Sheets)

Question

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,
});
}
}
}

Cooper · Accepted Answer · 2022-11-15 19:19:28Z

1

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:

	A	B	C	D
1	Title	url	date	Sent
2	t1	u1	d1	Yes
3	t1	u2	d2	Yes
4	t1	u3	d3	Yes
5	t1	u4	d4	Yes
6	t1	u5	d5	Yes
7	t1	u6	d6	Yes
8	t1	u7	d7	Yes
9	t1	u8	d8	Yes
10	t1	u9	d9	Yes

It turns then all to Yes

edited Nov 15, 2022 at 19:19

answered Nov 15, 2022 at 18:56

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

Mark Leach Over a year ago

Heya, thanks Cooper. So it gets one of the newest records (in my test there are 4) and updates 1 sent field with yes.

Cooper Over a year ago

Please provide a table of data

Cooper Over a year ago

Please a table that I can copy data from

Mark Leach Over a year ago

i just put a screenshot above my code. so ive got a script that gets new records and populates that sheet (it just found 4 records. the code you kindly wrote sent me an email with the first item and updated the email sent column

Cooper Over a year ago

I can't copy the data from an image

|

Collectives™ on Stack Overflow

Send Email When New Rows Are Added (Google App Script & Google Sheets)

1 Answer 1

8 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related