Lorixen.
EN

A Daily Summary Email of New Leads From Google Sheets With Apps Script

Short answer: Open your lead sheet, go to Extensions > Apps Script, and paste a short function that reads every row, keeps only those with yesterday's date, builds a small HTML table and sends it with MailApp.sendEmail. Run it once by hand to approve the permissions prompt, then add a time-driven trigger that runs it every morning between, say, 7 and 8 a.m. Handle the empty day with a one-line "No new leads yesterday" message, and keep an eye on Google's daily email quotas if you send to many recipients.

What the sheet needs to look like

The script below assumes a tab called Leads with a header row and these columns, which is a typical layout when a website form or Zapier writes leads into Sheets:

A B C D E F
Timestamp Name Email Phone Source Notes

Column A must contain real date values, not text that looks like a date. Click a cell and check the formula bar: a proper date shows as a date-time, and Format > Number > Date time displays it consistently. If your form tool writes text, fix that first or adjust the script to parse it.

Step 1: read the rows and filter by date

Open the sheet, choose Extensions > Apps Script, delete the sample code and start with this part:

const SHEET_NAME = 'Leads';
const RECIPIENTS = 'sales@example.com,owner@example.com';

function getYesterdaysLeads() {
  const sheet = SpreadsheetApp.getActive().getSheetByName(SHEET_NAME);
  const rows = sheet.getDataRange().getValues().slice(1);
  const tz = Session.getScriptTimeZone();
  const yesterday = new Date();
  yesterday.setDate(yesterday.getDate() - 1);
  const target = Utilities.formatDate(yesterday, tz, 'yyyy-MM-dd');
  return rows.filter(r => r[0] instanceof Date &&
    Utilities.formatDate(r[0], tz, 'yyyy-MM-dd') === target);
}

What it does: getValues() reads the whole table in one call (much faster than reading cell by cell), slice(1) drops the header, and each timestamp is compared as a plain date string in the script's time zone. Check that time zone under Project Settings in the Apps Script editor; it should match where your team works, or "yesterday" will be off by some hours.

Step 2: build an HTML table

Plain text lists are hard to scan. A small table reads well on phones and desktop mail clients:

function buildTable(leads) {
  const tz = Session.getScriptTimeZone();
  const cell = 'style="border:1px solid #ddd;padding:6px;text-align:left"';
  let html = '<table style="border-collapse:collapse;font-family:Arial,sans-serif;font-size:14px">';
  html += '<tr><th ' + cell + '>Time</th><th ' + cell + '>Name</th><th ' + cell +
          '>Email</th><th ' + cell + '>Phone</th><th ' + cell + '>Source</th></tr>';
  leads.forEach(r => {
    html += '<tr><td ' + cell + '>' + Utilities.formatDate(r[0], tz, 'h:mm a') + '</td>' +
            '<td ' + cell + '>' + escapeHtml(r[1]) + '</td>' +
            '<td ' + cell + '>' + escapeHtml(r[2]) + '</td>' +
            '<td ' + cell + '>' + escapeHtml(r[3]) + '</td>' +
            '<td ' + cell + '>' + escapeHtml(r[4]) + '</td></tr>';
  });
  return html + '</table>';
}

function escapeHtml(v) {
  return String(v).replace(/&/g, '&amp;').replace(/</g, '&lt;').replace(/>/g, '&gt;');
}

Styles are inline because many email clients ignore style blocks. The escapeHtml helper stops a lead who typed angle brackets into the form from breaking your table. The notes column is left out on purpose, since long messages make the table unwieldy; add it back if your team needs it.

Step 3: send the email, including on empty days

function sendDailyLeadSummary() {
  const leads = getYesterdaysLeads();
  const count = leads.length;
  const subject = count
    ? 'New leads yesterday: ' + count
    : 'No new leads yesterday';
  const body = count
    ? '<p>' + count + ' new lead(s) came in yesterday.</p>' + buildTable(leads)
    : '<p>No leads were added to the sheet yesterday. If that seems wrong, check that the form is still sending entries.</p>';
  MailApp.sendEmail({ to: RECIPIENTS, subject: subject, htmlBody: body });
}

Sending a short email on empty days is deliberate. Silence is ambiguous: it could mean a quiet day or a broken form. A "no leads" message tells you the script ran, and several empty days in a row prompt someone to check the website form.

Step 4: run once and approve permissions

Select sendDailyLeadSummary in the function dropdown and click Run. Google shows an authorization prompt because the script will read your spreadsheet and send email on your behalf.

  • Choose your account and review the permissions.
  • For scripts you write yourself, Google may show an "app isn't verified" screen. Click Advanced, then the option to continue to the project. This is expected for private scripts used within your own account or organization.
  • If your Google Workspace admin restricts third-party apps, they may need to allow it.

Check your inbox. If the email does not arrive, open Executions in the left sidebar to read any error.

Step 5: add the time-driven trigger

In the editor, click the clock icon (Triggers), then Add Trigger:

  1. Function: sendDailyLeadSummary
  2. Event source: Time-driven
  3. Type: Day timer
  4. Time of day: for example 7am to 8am
  5. Failure notifications: daily or immediately, so a broken run reaches your inbox

Daily timers run at some point inside the hour you choose, not at an exact minute. Google's installable triggers guide explains the options. The trigger runs under the account that created it, so set it up with a company account that will not be removed when someone leaves.

Quotas and limits

Apps Script limits how many emails and recipients a script can send per day and how long it can run. Limits differ between consumer Gmail accounts and Google Workspace accounts; check the current numbers on the Apps Script quotas page. One daily email to two or three people is far below any limit. Problems start only if you reuse the script to email many customers individually.

FAQ

Can it send on weekdays only?

Yes. Add a check at the top of sendDailyLeadSummary that returns early on Saturday and Sunday, and change the filter on Monday to include the weekend's leads.

Why does yesterday's list miss leads from late evening?

Usually a time zone mismatch between the sheet, the script project and your office. Set the same time zone in File > Settings in Sheets and in the Apps Script project settings.

Will this slow down when the sheet has thousands of rows?

Reading the whole range once stays quick for a few thousand rows. For much larger sheets, archive old rows to another tab yearly.

If you would rather have the digest built around your exact sheet and CRM, send us a copy of the layout and we will adapt the script for you.

Sources

WhatsAppBook a call