Send yourself a WhatsApp-free daily lead digest from Google Sheets (free, Apps Script, 10 minutes)

#googlesheets #automation #tutorial #productivity

In part 1 we made every new Google Form lead ping your phone instantly. Instant alerts are great, but on a busy day some leads still slip. This part adds a safety net: one email every morning listing every lead that hasn't been handled yet.

What you'll build

A Status column in your responses sheet (new / called / closed)
A time-driven Apps Script trigger that runs at 08:00
One digest email: new leads from the last 24 h + anything still new after 24 h, each with a tel: link
Cost: $0. Time: ~10 minutes.

Step 1: Add a Status column (1 min)

Open the form's response sheet. In the first empty column header type Status. Leave it empty; the script treats empty as new.

Step 2: Paste the script (3 min)

Extensions → Apps Script, paste:

const SHEET_NAME = 'Form Responses 1';
const PHONE_COL_NAME = 'Phone';
const NAME_COL_NAME = 'Name';
const TO = Session.getEffectiveUser().getEmail();

function dailyDigest() {
  const sh = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  const rows = sh.getDataRange().getValues();
  const head = rows.shift();
  const iTime = 0, iName = head.indexOf(NAME_COL_NAME),
        iPhone = head.indexOf(PHONE_COL_NAME), iStatus = head.indexOf('Status');
  const now = Date.now(), DAY = 864e5;
  const fresh = [], stale = [];
  rows.forEach(r => {
    const st = String(r[iStatus] || 'new').toLowerCase();
    if (st !== 'new') return;
    const age = now - new Date(r[iTime]).getTime();
    (age < DAY ? fresh : stale).push(r);
  });
  if (!fresh.length && !stale.length) return; // nothing to do, no email
  const line = r => `<li>${r[iName]} – <a href="tel:${r[iPhone]}">${r[iPhone]}</a> (${new Date(r[iTime]).toLocaleString()})</li>`;
  const html = `<h3>New in last 24h: ${fresh.length}</h3><ul>${fresh.map(line).join('')}</ul>` +
               `<h3>Still not called (24h+): ${stale.length}</h3><ul>${stale.map(line).join('')}</ul>`;
  MailApp.sendEmail({to: TO, subject: `Leads digest: ${fresh.length} new, ${stale.length} waiting`, htmlBody: html});
}

Change NAME_COL_NAME / PHONE_COL_NAME to match your form's question titles exactly.

Step 3: Schedule it (2 min)

Triggers (clock icon) → Add Trigger → dailyDigest → Time-driven → Day timer → 8am–9am. Approve the permissions once.

Step 4: Test (2 min)

Run dailyDigest manually. You should get one email. Mark a row called and run again: it disappears from the list.

Why it works

Instant alerts get attention; a daily digest catches what attention missed. Together they close the "I forgot to call back" gap that costs small businesses most of their form leads.

Want it done for you?

If you'd rather not touch code, the free tools at https://chelli.co.il/?utm_source=devto&utm_medium=article&utm_campaign=t169_part2 are where I keep these small automations.

DEV Community