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

chelli — Wed, 14 Oct 2026 11:15:57 +0000

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.

Get an instant phone alert for every Google Form lead (free, 10 minutes, Apps Script)

chelli — Wed, 14 Oct 2026 08:58:10 +0000

If your business collects leads with a Google Form, you probably check the responses sheet "when you remember." Meanwhile the lead filled out three other forms and booked the first person who called back.

Speed matters. Calling a lead back within 5 minutes instead of an hour makes a huge difference in whether you reach them at all. So let's make every new form submission ping your phone the second it arrives — with full details and a tap-to-call link.

What you'll build:

A Google Apps Script trigger on your form's response sheet
A push notification to your phone (via the free ntfy app) with the lead's name, phone, and message
An email fallback, so you never miss one even if the push fails
Cost: ₪0 / $0. Time: ~10 minutes. No servers, no Zapier subscription.

Why not just turn on Google Forms email notifications?

Google Forms has a built-in "Get email notifications for new responses" option. It works, but:

The email doesn't include the answers — just "you have a new response," so you still have to open the sheet.
It lands in a busy inbox alongside newsletters.
You can't route it (e.g., urgent leads → loud alert, others → quiet).

A push notification with the actual lead details fixes all three.

Step 1: Install ntfy and pick a secret topic (2 min)

ntfy is a free, open-source push notification service. You publish to a "topic" with a simple HTTP POST, and any phone subscribed to that topic gets a notification.

Install ntfy from the App Store or Google Play.
Tap + and subscribe to a topic. Topics are public by name, so make it hard to guess, e.g. leads-mybiz-7f3k9q2x.

🔒 Anyone who knows your topic name can read and post to it. Treat it like a password. Don't use leads or your business name alone. (If you need stronger privacy, ntfy supports access tokens on a paid plan or a self-hosted server.)

Test it from any terminal:

curl -d "Hello from my form 👋" ntfy.sh/leads-mybiz-7f3k9q2x

Your phone should buzz within a second.

Step 2: Link your form to a Google Sheet (1 min)

In your Google Form → Responses tab → Link to Sheets → create a new spreadsheet. Every submission now becomes a row.

Open that sheet. Note your column headers — they're the exact question titles, e.g.:

Timestamp	Full name	Phone	What do you need?

Step 3: Add the script (4 min)

In the sheet: Extensions → Apps Script. Delete whatever is there and paste:

/**
 * Instant phone alert for every Google Form lead.
 * Trigger: "On form submit" (installable trigger, from spreadsheet).
 */

// ===== CONFIG =====
const NTFY_TOPIC   = 'leads-mybiz-7f3k9q2x';   // your secret topic from Step 1
const FALLBACK_EMAIL = '';                      // e.g. 'you@gmail.com' — leave '' to disable
const NAME_FIELD   = 'Full name';               // exact question titles from your form
const PHONE_FIELD  = 'Phone';
const DEFAULT_COUNTRY_CODE = '972';             // used to build a tap-to-call / WhatsApp link
// ==================

function onFormSubmitAlert(e) {
  // e.namedValues looks like: { "Full name": ["Dana"], "Phone": ["050-1234567"], ... }
  const answers = (e && e.namedValues) || {};

  const get = (key) => (answers[key] && answers[key][0] ? String(answers[key][0]).trim() : '');

  const name  = get(NAME_FIELD) || 'New lead';
  const phone = get(PHONE_FIELD);

  // Build the body: every answer except the timestamp
  const lines = Object.keys(answers)
    .filter((k) => k !== 'Timestamp')
    .map((k) => `${k}: ${get(k) || '—'}`);
  const body = lines.join('\n');

  const headers = {
    Title: encodeHeader(`🔔 New lead: ${name}`),
    Priority: 'high',
    Tags: 'telephone_receiver',
  };

  const intlPhone = toInternational(phone);
  if (intlPhone) {
    // Action buttons on the notification: call or WhatsApp in one tap
    headers.Actions =
      `view, Call, tel:+${intlPhone}; ` +
      `view, WhatsApp, https://wa.me/${intlPhone}`;
  }

  try {
    const res = UrlFetchApp.fetch(`https://ntfy.sh/${encodeURIComponent(NTFY_TOPIC)}`, {
      method: 'post',
      payload: Utilities.newBlob(body, 'text/plain; charset=utf-8').getBytes(),
      headers: headers,
      muteHttpExceptions: true,
    });
    if (res.getResponseCode() >= 300) {
      throw new Error(`ntfy returned ${res.getResponseCode()}: ${res.getContentText()}`);
    }
  } catch (err) {
    console.error(err);
    if (FALLBACK_EMAIL) {
      MailApp.sendEmail(FALLBACK_EMAIL, `New lead: ${name} (push failed)`, body);
    }
  }
}

/** "050-123 4567" -> "972501234567"; leaves already-international numbers alone. */
function toInternational(raw) {
  let digits = String(raw || '').replace(/\D/g, '');
  if (!digits) return '';
  if (digits.startsWith('00')) digits = digits.slice(2);
  else if (digits.startsWith('0')) digits = DEFAULT_COUNTRY_CODE + digits.slice(1);
  return digits.length >= 9 ? digits : '';
}

/** HTTP headers must be ASCII; ntfy accepts RFC 2047 encoded headers for emoji / Hebrew. */
function encodeHeader(text) {
  return '=?UTF-8?B?' + Utilities.base64Encode(text, Utilities.Charset.UTF_8) + '?=';
}

/** Run this once from the editor to send yourself a fake lead. */
function testAlert() {
  onFormSubmitAlert({
    namedValues: {
      Timestamp: [new Date().toString()],
      [NAME_FIELD]: ['Test Lead'],
      [PHONE_FIELD]: ['050-1234567'],
      'What do you need?': ['Just testing the alert 🚀'],
    },
  });
}

Edit the CONFIG block:

NTFY_TOPIC — your topic from Step 1.
NAME_FIELD / PHONE_FIELD — exactly your question titles (case and spaces matter).
DEFAULT_COUNTRY_CODE — 972 for Israel, 1 for US, 44 for UK, etc.
FALLBACK_EMAIL — optional but recommended.

Click 💾 Save.

Step 4: Test it (1 min)

In the function dropdown at the top, select testAlert and click Run.

The first time, Google asks for permissions ("connect to an external service" and "send email as you"). Click through: Review permissions → your account → Advanced → Go to (project) → Allow. That "unverified app" warning appears because it's your own private script — that's expected.

Your phone should show something like:

🔔 New lead: Test Lead
Full name: Test Lead
Phone: 050-1234567
What do you need?: Just testing the alert 🚀
[ Call ]  [ WhatsApp ]

Step 5: Turn on the trigger (1 min)

A function named onFormSubmitAlert doesn't run by itself — you need an installable trigger:

In Apps Script, click the ⏰ Triggers icon in the left sidebar.
+ Add Trigger (bottom right).
Function: onFormSubmitAlert · Event source: From spreadsheet · Event type: On form submit.
Save.

Now submit your real form from your phone. The alert should arrive within a few seconds. Done! 🎉

Troubleshooting

Symptom	Fix
Notification arrives but name says "New lead"	`NAME_FIELD` doesn't match the question title exactly. Copy it from the sheet's header row.
No Call/WhatsApp buttons	The phone answer had fewer than 9 digits, or `PHONE_FIELD` doesn't match.
Nothing arrives at all	Apps Script → Executions (left sidebar) shows each run and any error. Check the topic name matches the app exactly.
Trigger fires twice	You created two triggers. Delete one in the Triggers page.
Hebrew/emoji shows as gibberish in the title	Make sure you kept the `encodeHeader()` wrapper.

Ideas to extend it

Route by answer: if "What do you need?" contains "urgent", send Priority: 'urgent' (bypasses Do Not Disturb on Android).
Team alerts: have every salesperson subscribe to the same topic.
Mark as handled: add a "Called?" checkbox column and a daily timeDriven trigger that re-alerts about leads left unchecked for 24 hours.
Telegram instead of ntfy: swap the UrlFetchApp call for the Telegram Bot API sendMessage endpoint — same pattern.

Wrap-up

Ten minutes of setup, zero monthly cost, and you'll never again find a two-day-old lead sitting in a spreadsheet. The fastest reply usually wins the customer — now that can be you.

Want it set up for you? I do it for Israeli small businesses, ₪99, pay after it works: https://chelli.co.il/?utm_source=devto&utm_campaign=form_alert

DEV Community: chelli