emailgoogle-sheetsgoogle-apps-scripthtml-email

Making Script only send to an email once?

So I have a Script working and once I press the associated button it sends an email to a list from pulled data, I have an issue though as the list is constantly updated and the script will be run multiple times but i don't want it to send to emails it has already sent to previously, is there any way to ensure this or will i have to think of another solution?

This is the Code:

function myFunction2() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let data = sheet.getRange('A2:B').getDisplayValues();
  data.filter(row => row.every(Boolean)).forEach(row => {
    let [name, address] = row;
    let subject = `Your RSVP is Confirmed!`;
    let body = `<b>Dear ${name}.</b><br><br> 

Thank you for RSVPing <b>YES</b> More Text Here.<br><br> 

<b>Event Details:</b><br><br>

<b>Date:</b> Text Here<br><br>

<b>Time:</b> Text Here<br><br>

Here's what you can look forward to:<br><br>

<b>Exclusive Open Bar:</b> Enjoy a carefully selected drinks menu with premium beverages.<br>
<b>Gourmet Sushi:</b> Indulge in a variety of delicious sushi offerings.<br>
<b>DJ:</b> Live entertainment <br><br>

Line of Text Here.<br><br>

We can't wait to celebrate this special night with you and showcase the exciting future of our venture!<br><br>

Line of text Here<br><br>



<b>Warm Regards,</b><br><br>
<img src="Image Here" alt="Image 1" style="width: 167px; height: 105px;", ALIGN="Left" HSPACE="25">
<span style='color: #C19B77;'Text Here</span><br>
<b><span style='color: #C19B77; font-size: 15px;'>Text here</span></b><br><br>
<b><span style='color: #000000;'>Mobile:</b> Text Here &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here<br>
<b><span style='color: #000000;'>Email:</b> Text Here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here<br>
<b>URL:</b> Text Here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here `;
  
    MailApp.sendEmail({to: address, subject, htmlBody: body, cc:''});
  });
}

I'm Honestly not sure what to try as this is unknown to me.

Solution

  • use column C (as checkbox) for keeping track if email was sent:

    function myFunction2() {
  let sheet = SpreadsheetApp.getActiveSheet();
  let dataRange = sheet.getRange('A2:C');
  let data = dataRange.getValues(); // Get values from columns A, B, and C

  data.forEach((row, index) => {
    let [name, address, emailSent] = row;

    // Skip rows where any cell in columns A or B is empty or where column C is TRUE
    if (!name || !address || emailSent === true) return;

    let subject = `Your RSVP is Confirmed!`;
    let body = `<b>Dear ${name}.</b><br><br> 

Thank you for RSVPing <b>YES</b> More Text Here.<br><br> 

<b>Event Details:</b><br><br>

<b>Date:</b> Text Here<br><br>

<b>Time:</b> Text Here<br><br>

Here's what you can look forward to:<br><br>

<b>Exclusive Open Bar:</b> Enjoy a carefully selected drinks menu with premium beverages.<br>
<b>Gourmet Sushi:</b> Indulge in a variety of delicious sushi offerings.<br>
<b>DJ:</b> Live entertainment <br><br>

Line of Text Here.<br><br>

We can't wait to celebrate this special night with you and showcase the exciting future of our venture!<br><br>

Line of text Here.<br><br>

<b>Warm Regards,</b><br><br>
<img src="Image Here" alt="Image 1" style="width: 167px; height: 105px;", ALIGN="Left" HSPACE="25">
<span style='color: #C19B77;'>Text Here</span><br>
<b><span style='color: #C19B77; font-size: 15px;'>Text here</span></b><br><br>
<b><span style='color: #000000;'>Mobile:</b> Text Here &nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here<br>
<b><span style='color: #000000;'>Email:</b> Text Here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here<br>
<b>URL:</b> Text Here&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp; Text Here `;

    // Send the email
    MailApp.sendEmail({to: address, subject, htmlBody: body, cc: ''});

    // Mark email as sent in column C
    sheet.getRange(index + 2, 3).setValue(true); // +2 because index is 0-based and data starts from row 2
  });
}