google-spreadsheet-script.md

1. Setup the spreadsheet

Assuming that your sheet is structured as follows (adapt the script according to the specific layout needed):

Task	Email	Date/Time
Descriptive text value	[email protected]	08/14/2025 16:00

2. Set up a trigger

In the Apps Script editor, click on the clock icon in the left sidebar to open the Triggers setup.

• Click Add Trigger.
• Choose the function sendEmailNotifications.

Set the trigger to run every minute, hour, or day based on how frequently you need to check for due notifications.

3. Save and Run

Save the script and click on Run to authorize it. Note: You will need to grant permissions for the script to send emails on your behalf.

for more info https://www.goldyarora.com/blog/subscription-tracker

google-spreadsheet.gs

function sendEmailNotifications() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // const sheet = SpreadsheetApp.getActive().getSheetByName('sheet name'); // get a specific sheet by name instead of status
  
  const data = sheet.getDataRange().getValues();
  // const data = sheet.getRange(2, 1, sheet.getLastRow() - 1, 6).getValues() // limit more specifically the range
  
  // Adjust index to match your data
  const taskColumn = 0; // Column A
  const emailColumn = 1; // Column B
  const dateTimeColumn = 2; // Column C

  const currentTime = new Date();

  data.forEach( function( row ) {
  for (let i = 1; i < data.length; i++) { // Start from 1 to skip header
    const task = data[i][taskColumn];
    const email = data[i][emailColumn];
    // const email = Session.getActiveUser().getEmail(); // if you want to mail to the task runner of the sheet instead
    const dateTime = new Date(data[i][dateTimeColumn]);

    if (email && dateTime <= currentTime) {
      // Send email
      MailApp.sendEmail({
        to: email,
        subject: `Task Reminder: ${task}`,
        body: `This is a reminder for your task: "${task}" scheduled at ${dateTime}.`
      });

      // Optional: Mark the row as "notified" or delete the date/time to avoid repeat notifications
      sheet.getRange(i + 1, dateTimeColumn + 1).setValue('Notified'); // Marks the cell as notified
    }
  }
}