google sheets e-mail reminder

code.gs

function sendEmailOnDate() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 2; // Start checking for dates from row 2
  var numRows = sheet.getLastRow() - 1; // Number of rows to check
  var dataRange = sheet.getRange(startRow, 2, numRows, 3); // Range of dates, email body, and number of days to check
  var data = dataRange.getValues(); // Get values of the range
  var today = new Date(); // Get current date
  for (var i = 0; i < data.length; ++i) {
    var row = data[i];
    var date = new Date(row[0]); // Convert the cell value to a date object
    var daysBefore = row[2]; // Get the number of days before the event from column D
    var sendDate = new Date(date.getTime() - daysBefore * 24 * 60 * 60 * 1000); // Calculate the date when the email should be sent
    var diff = Math.floor((sendDate - today) / (1000 * 60 * 60 * 24)); // Calculate the difference between the send date and the current date in days
    
    if (diff == 0) { // If the send date is today
      var emailAddress = "[email protected]"; // Replace with the recipient's email address
      var subject = "תזכורת חידוש דרכון של מישהו במשפחה";
      var message = row[1]; // Get the email body from column C
      MailApp.sendEmail(emailAddress, subject, message);
    }
  }
}

google_sheet_format

| whose passport           | expire     | mail body                                                                            | send email x days before |
| ----------------------   | -----------| -------------------------------------------------------------------------------------| ------------------------ |
| Lily's Israeli Passport  | 15/1/2025  | =CONCATENATE("this is a reminder that in ",D2, " days you need to renew ", A2)       |   360                    |

how_to_add_time_trigger.md

Step 1: Open the Script Editor Click on "Extensions" in the top menu of your Google Sheets document, and then select "Apps Script" from the dropdown menu. This will open the Apps Script editor in a new tab.

Step 2: Paste the Script In the Apps Script editor, delete any existing code if present, and paste the following code:

Step 3: Save your Script After pasting the script, click on the floppy disk icon or go to "File" and select "Save" to save your script. Give your script a name and click on "OK".

Step 4: Run and Test your Script To test your script, go back to the Google Sheets document. You can run your script by clicking on the green play button in the Apps Script editor. This will execute the sendEmailOnDate function, which will check the date in the first column of your sheet and send an email if it matches the current date. The email recipient, subject, and message should be entered in the corresponding columns (2nd, 3rd, and 4th columns respectively) of the same row.

Step 5: Add a Trigger To add a trigger that runs the sendEmailOnDate function automatically every day at a specific time, follow these steps:

In the Apps Script editor, click on the "Triggers" icon (clock with a lightning bolt) in the toolbar. Click on the "+ Add Trigger" button in the bottom right corner of the Triggers dialog. In the "Choose which function to run" dropdown, select the sendEmailOnDate function. Set the "Select event source" to "Time-driven". Set the "Select type of time-based trigger" to "Day timer". Set the desired time range for the trigger. Click on the "Save" button to save the trigger. Step 6: Close the Script Editor Once you have finished pasting the script, testing it, and setting up the trigger, you can close the Apps Script editor tab. Your script will remain attached to the Google Sheets document, and the trigger will run the sendEmailOnDate function automatically every day at the specified time.

That's it! You have successfully added an Apps Script to your Google Sheets document with the sendEmailOnDate function and a trigger to run it automatically. Remember to save your changes in both the Apps Script editor and the Google Sheets document as you continue working on your script.