Javascript - Google App Script: Loop through column, email if Date = Today with corresponding row info

Gabi
January 8, 2024
268 views
0 votes
2 Answers

I am new to JavaScript and I need help with App Script, please.
I have a Google Sheet with three columns: Date (Data type ‘Date’), Greeting and Message (both Data Type ‘String’).
I would like to loop through the sheet and if the column date is equal to today’s date, send an email with the Greeting and Message from the corresponding row.
Would anybody, please, be able to shine a little for me?
Thanks

Answers

You’re going to need quite a bit of code for this, but here a starting point:

function sendAllEmails() {
  var ids = inputSheet.getRange("A2:A").getValues();
  var dates = inputSheet.getRange("B2:B").getValues();
  var numberOfRecords = ids.length;

  // Loop through Ids in inputSheet
  for (var i = 0; i < numberOfRecords; i++) {
    // Check if date is today (unfinished!)
    if(dates[i] == ...) {
      customEmailSendingFunction(i+2);
    }
  }
}

function customEmailSendingFunction (rowNumber) {
 subject = inputSheet.getRange('C' + rowNumber);
 body = inputSheet.getRange('D' + rowNumber);
 .. other data you need from your sheet
  // create a function to send an email

}

- Saddles
- January 8, 2024 at 2:27 pm
- 0 votes
0
Google Apps Script: Send Emails on Today’s Date

This sample script checks today’s date on your Google Sheet and sends an email to your specified email address.

I have also added some explanations of what the code does
```
const myFunction = () => {
  // Gets your current active spreadsheet
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // Declaration of the range to be used in the spreadsheet
  var range = ss.getRange(2, 1, ss.getLastRow(), 3);
  // Gets the values of the declared range
  var values = range.getValues();
  // Loops through the rows and sends emails if the date matches today's date
  values.forEach(row => {
    var date = new Date(row[0]);
    var greeting = row[1];
    var message = row[2];
    // Compares the date in the row to today's date
    (date.toDateString() == new Date().toDateString()) ?
      MailApp.sendEmail("[email protected]", greeting, message) : null;
  });
}
```
Make sure to change [email protected] to the email address that you’ll send the email to before running the code.

The sample sheet looks like this:

OUTPUT

REFERENCES
Login or Signup to reply.