Daily Questionnaire Due-Date Digest: A Zapier and Google Sheets Automation That Emails You What Is Due Soon

What This Builds

Customer questionnaires, ratings requests and investor requests arrive from different people with different due dates, and each one is waiting on a different team for input. This build gives you one email at the start of every working day that lists only the requests that need you: past due, due within the number of days you choose, missing a due date, or waiting on someone else for input. Each line also says who owes the input.

A Google Sheet holds the log and works out every stage with formulas. Zapier reads one summary row and emails it to you. The Zap never writes to a customer, a ratings organization or a colleague. When a nudge is due, you write it.

Prerequisites

• A Google account with Google Sheets and Gmail. If your firm uses Google Workspace, the plan is Business Standard and its pricing is on Google's own page.
• A Zapier account on a plan that allows multi-step Zaps. Zapier's free plan allows two-step Zaps only, and this build has three steps (the trigger plus two actions), so you need Professional or higher ($29.99/month at the entry level). That is the total ongoing cost of the finished build, because Sheets and Gmail come with your Google account.
• Permission to run this through Zapier. A consultant should check the engagement letter or NDA for what client information a third-party service may hold. An in-house analyst should check the approved tools list.
• A habit of logging each request the day it arrives
• About 90 minutes for the first build

What goes through Zapier. Zapier does not read your Requests tab. The lookup step returns the whole Summary row, so Zapier receives and keeps the counts and the digest text in Zap History for each run, and anyone with access to your Zapier account can open it. The digest text holds request labels, the request type, due dates, team labels and stages. Use a code for a customer instead of its name if the customer relationship is confidential, for example "CUST-A supplier survey 2026". Use team labels such as "Facilities" or "Legal" in Waiting On, never a person's name or email address. Keep answers, figures, scores and anything unreleased out of every column the digest reads. A ratings request or investor request for a listed company can touch material non-public information, so describe it by label only.

The Concept

Picture a noticeboard with one card on it. The card is always there, and it always shows a count and a block of text. Zapier's job is to pick the card up every morning and mail it to you.

Why not search for the late requests directly? Because when a Zapier search step finds no matching row, the Zap halts. Zap History marks the run "Safely halted", no later step runs and no email goes out. A Zap built on "find requests that are past due" would go silent on the very days when everything is fine, and you could not tell a good day from a broken Zap. The Summary row has a fixed Key, always matches and always sends. On a quiet day the email says nothing needs action.

The sheet does all the date arithmetic. Zapier lookups match text in a column, and they are not a reliable place for "due within 10 days" tests.

Build It Step by Step

Part 1: Set Up the Requests Tab

Create a Google Sheet and name the first tab Requests. Row 1 holds these headers.

Create both dropdowns: select B2:B500 (then E2:E500), choose Data then Data validation, add a Dropdown rule, enter the labels exactly as written and reject other input. The formulas test these labels character for character. Format C2:C500 as dates.

Part 2: Add the Settings Cell and the Summary Tab

Add a second tab named Summary. Row 1 holds headers and row 2 holds the one data row.

Type your chosen number into Summary!B2. Change it any time and the next digest follows. Do not leave it blank. A blank Warn Days is read as zero, and then only requests due today are flagged as soon.

Part 3: Add the Three Formula Columns on Requests

Enter these in F2, G2 and H2 and fill each down to row 500.

Days Left, cell F2:

=IF(OR(A2="",C2=""),"",C2-TODAY())

Stage, cell G2:

=IF(A2="","",IF(OR(E2="Submitted",E2="Declined"),"Closed",IF(C2="","Missing date",IF(F2<0,"PAST DUE",IF(F2<=Summary!$B$2,"Due soon",IF(E2="Waiting on input","Waiting on input","Open"))))))

Digest Line, cell H2:

=IF(A2="","",A2&" | "&B2&" | "&IF(C2="","no due date","due "&TEXT(C2,"yyyy-mm-dd"))&IF(D2="",""," | waiting on "&D2)&" | "&G2)

Walk the Stage formula through its cases. The first true test wins.

1. Request is blank: the row is empty, so Stage is blank. This is what keeps the empty rows out of every count.
2. Status is Submitted or Declined: Closed. This comes before the date tests, otherwise a request you submitted last month would show as PAST DUE forever.
3. Due is blank: "Missing date". The Days Left cell is also blank here, which is why this test comes before the Days Left tests.
4. Days Left is below 0: PAST DUE.
5. Days Left is at most Warn Days: "Due soon". Due today has Days Left 0, so it counts.
6. Status is Waiting on input: "Waiting on input". This sits below the date tests on purpose, so a request that is both nearly due and waiting on a team shows Due soon, the more urgent label. Its digest line still reads "waiting on Facilities", because the Digest Line adds Waiting On whenever that cell is filled, whatever the Stage is.
7. Anything else: Open.

Two other points. Days Left is a plain number, so comparing it to Warn Days needs no TEXT(). The Digest Line does need TEXT(C2,"yyyy-mm-dd"), because a date joined to text with & turns into a serial number, and the IF guard prints "no due date" instead of 1899-12-30 for a blank. The Stage formula opens six IF functions and closes six at the end. Check that count before you fill down.

Part 4: Add the Summary Formulas

Action Count, cell C2:

=COUNTIF(Requests!G2:G500,"PAST DUE")+COUNTIF(Requests!G2:G500,"Due soon")+COUNTIF(Requests!G2:G500,"Missing date")+COUNTIF(Requests!G2:G500,"Waiting on input")

Open Count, cell D2:

=C2+COUNTIF(Requests!G2:G500,"Open")

Digest Lines, cell E2:

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Requests!H2:H500,(Requests!G2:G500="PAST DUE")+(Requests!G2:G500="Due soon")+(Requests!G2:G500="Missing date")+(Requests!G2:G500="Waiting on input"))),"No questionnaires need action")

The counts read the Stage column only, and the blank formula rows return an empty string, so they match no label. Every range in the FILTER is rows 2 to 500, the same height. When no row matches, FILTER returns an error and IFERROR substitutes the fallback sentence, so the email still has a body. CHAR(10) puts each line on its own line.

Part 5: Build the Zap

Create a Zap with these three steps.

Step 1, trigger: Schedule by Zapier, Every Day. Choose the time of day, early enough that the email is waiting when you start. Every Day also has a Trigger on weekends? field. Set it to match your working week if you do not want weekend emails. There is a Timezone Override field if your Zapier account is on another timezone.

Step 2, action: Google Sheets, Lookup Spreadsheet Row. Connect your Google account, choose the spreadsheet in Drive and Spreadsheet, and choose Summary as the Worksheet. Set Lookup column to Key and Lookup value to summary, typed in. Test the step. The output shows the whole row with Warn Days, Action Count, Open Count and Digest Lines. If you rename a header later, reselect the worksheet so Zapier refreshes the column names.

Step 3, action: Gmail, Send Email. Connect Gmail and fill in:

• To: your own address only
• Subject: Questionnaires: followed by the Action Count field from step 2, then need action
• Body type: plain text, so the line breaks survive
• Body: the Digest Lines field from step 2, then a blank line and Open requests in total: followed by the Open Count field

Test the step and check that the email lands in your inbox. Then publish the Zap.

Task use. Zapier's help page says only successful action steps count as tasks, and the trigger is not an action. This Zap has two action steps, so each run uses up to two tasks. A daily Zap therefore uses tasks every day, roughly 60 a month with weekends included. Check your plan's monthly task limit on Zapier's pricing page before you rely on it.

Real Example: Six Requests on a Tuesday

Invented example. Today is Tuesday 2026-10-13 and Warn Days is 10, so the "Due soon" cutoff is 2026-10-23.

Rows 8 to 500 are blank, so Days Left, Stage and Digest Line are all blank there.

Row 3 is the case to study. It is both due soon (1 day left, at most 10) and waiting on input. Stage shows Due soon because the date tests come first, and the digest line still names Facilities. Row 4 has 17 days left, which is above 10, so it falls through to its status and shows Waiting on input. Row 7 is 12 days past its due date and shows Closed, because Submitted is tested first. Row 5 has no due date, so Days Left is blank and the Missing date test catches it before any date comparison runs.

Counts. PAST DUE 1, Due soon 1, Missing date 1 and Waiting on input 1 give an Action Count of 4. Add one Open row for an Open Count of 5. Closed and blank rows are in neither.

The Digest Lines cell, in sheet order:

CUST-A supplier survey 2026 | Customer questionnaire | due 2026-10-09 | waiting on Legal | PAST DUE
CUST-F water questionnaire | Customer questionnaire | due 2026-10-14 | waiting on Facilities | Due soon
INV-C investor ESG request | Investor request | due 2026-10-30 | waiting on Facilities | Waiting on input
CUST-D energy questionnaire | Customer questionnaire | no due date | Missing date

The subject reads "Questionnaires: 4 need action". If the log were empty or every request were closed, the Digest Lines cell would hold "No questionnaires need action" and the email would still arrive.

Time saved: The email replaces the morning scan of the log. If that scan takes you 5 to 10 minutes a day (your own number will differ), the email gives that time back.

What to Do When It Breaks

• No email arrived this morning. Nothing will tell you the Zap broke, so look for the gap. Open Zapier and confirm the Zap is on. Then open Zap History for today's run. "Safely halted" on step 2 means the lookup found no row: check that the Key cell still says summary exactly and that the worksheet is still Summary. An error on step 2 or 3 usually means the Google connection expired, so reconnect it. A free calendar reminder at your usual digest time that asks "did the digest arrive?" is a cheap backstop. Also check whether Trigger on weekends? is set the way you think it is.
• A request you expected is missing from the digest. Its Stage is Open or Closed. Check Status, and check that Due holds a real date and not text. A row with a blank Request is skipped.
• Everything shows Due soon. Warn Days is too large, or Summary!B2 holds text. Type a plain number.
• A date prints as 1899-12-30. The blank-date guard was lost in an edit. Copy H2 from this guide.
• #REF! errors after renaming a tab. The formulas refer to Requests and Summary by name. Rename the tab back.
• Rows past row 500 are ignored. Extend every range together.
• A customer's name appears in Zap History. Someone typed it into Request. Replace it with a code and check how long Zapier keeps run data on your plan.

Variations

• Simpler version: Skip the Zap and look at the Summary tab each morning. The sheet does the same work.
• Extended version: Add a second Zap at a different time with the same Summary row, for a late-afternoon check during a crunch. It doubles the task use.

What to Do Next

• This week: Log every open questionnaire, choose your Warn Days and test each stage with a row of its own.
• This month: Ask each team that owes input to use the same labels you use in Waiting On.
• Advanced: Add a Google Form so teams can tell you an input is ready. Answers land on their own responses tab, never straight in Requests, and you update Status yourself.

Advanced guide for sustainability consultant and ESG analyst professionals. This build needs a paid Zapier plan and uses your own spreadsheet formulas, so no AI vendor sees your log.