Excel/Google Sheets Apps Script Automation Builder

I will build a Google Apps Script that identifies overdue leads and sends email reminders.

Solution

The script will perform a batch read of the 'Leads' sheet, filter for rows where 'Status' is 'Pending' and the 'Created Date' is older than 3 days, then send emails using MailApp.

Assumptions and data mapping

• Platform: Google Sheets (Apps Script).
• Sheet Name: Leads.
• Header Mapping: 'Status' (Column C), 'Email' (Column B), 'Date' (Column A).
• Trigger: Time-driven (Daily between 8 AM and 9 AM).

Code

/**
 * Processes overdue leads and sends email reminders.
 */
function processOverdueLeads() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('Leads');
  if (!sheet) return;

  const data = sheet.getDataRange().getValues();
  const headers = data[0];
  const statusIdx = headers.indexOf('Status');
  const emailIdx = headers.indexOf('Email');
  const dateIdx = headers.indexOf('Date');

  const now = new Date();
  const threeDaysAgo = new Date(now.getTime() - (3 * 24 * 60 * 60 * 1000));

  data.slice(1).forEach((row, index) => {
    const status = row[statusIdx];
    const email = row[emailIdx];
    const date = new Date(row[dateIdx]);

    if (status === 'Pending' && date < threeDaysAgo && email) {
      MailApp.sendEmail({
        to: email,
        subject: 'Follow-up: Your Lead Status',
        body: 'Your request is still pending. Please check in with us.'
      });
      console.log(`Email sent to ${email}`);
    }
  });
}

function createTimeTrigger() {
  ScriptApp.newTrigger('processOverdueLeads')
    .timeBased()
    .everyDays(1)
    .atHour(8)
    .create();
}

Setup

1. Open your Google Sheet, go to Extensions > Apps Script.
2. Paste the code above into Code.gs.
3. Run createTimeTrigger once to authorize and set the schedule.

Permissions and operational notes

• Scopes: https://www.googleapis.com/auth/spreadsheets, https://www.googleapis.com/auth/script.send_mail.
• Quotas: Gmail daily limit is 100 recipients for free accounts (2,000 for Google Workspace).

Next steps

1. Run a test by setting one lead date to 4 days ago and clicking 'Run' on processOverdueLeads.
2. Check the Apps Script Executions log to confirm the trigger was created.