Weekly Proposal Deadline Digest: A Zapier Automation That Emails You Every Monday

What This Builds

Every Monday morning an email lands in your own inbox listing the proposals that need you this week: the ones past their internal deadline, the ones with no internal date at all, and the ones due inside a window you choose. The subject line carries two counts, so you know before opening it whether the week starts quietly or badly.

The sheet does all the thinking. Formulas flag each proposal, a Summary tab collects the flagged lines into one cell, and Zapier carries that cell to your inbox. There is no AI step, so no proposal information goes to an AI vendor. Nothing is sent to a PI, a sponsor or a department. The email goes to you, and you decide who gets a nudge.

Prerequisites

• A Google account that holds the tracker, and a Gmail address you are allowed to send from. If your institution runs Google Workspace, ask IT whether outside apps such as Zapier may connect to it.
• A Professional plan or better ($29.99/month). The Zap has a trigger plus two actions, which is three steps, and Zapier's free plan has been limited to two-step Zaps. Check Zapier's current plan page before you build.
• Total ongoing cost of the finished build: the Zapier plan above. Google Sheets and Gmail cost nothing extra on a Google account you already have. Each weekly run uses two tasks (the lookup and the email), so roughly 8 to 10 tasks a month. Zapier counts only successful action steps as tasks.
• Comfort writing a spreadsheet formula and copying it down a column.
• Your institution's AI and data guidance read, and an answer on whether Zapier is an approved tool for even this coded data (see "What Zapier Sees and Keeps" below).

The Concept

Think of the Summary tab as a noticeboard with one card pinned to it. The sheet rewrites the card every time anything changes: how many proposals need action, and a list of them. Zapier is the person who walks to the noticeboard every Monday, copies the card and drops it in your inbox.

The card is always there. That matters because of how Zapier's search step behaves: a lookup that finds zero rows halts the Zap ("Safely halted" in Zap History), and no later step runs, so no email is sent. If the Zap searched the Proposals tab for "rows due soon", a quiet week would produce no rows and no email, and you could not tell a quiet week from a broken Zap. By searching for one Summary row whose Key is always "summary", the lookup always finds a row, and the email arrives every week even when the card says nothing is due.

What Zapier Sees and Keeps

Zapier handles whatever the Zap reads, and Zap History stores the data of each step. The lookup step stores the Summary row. The Gmail step stores the subject and body. In this build that means proposal codes, PI codes, short sponsor program codes, dates, short missing-item labels and status values.

Keep these out of the sheet entirely: proposal titles, anyone's name, amounts, salaries, effort, and any text from a proposal. Use codes such as "P-104" and "PI A", and keep the code key in your office's own files. Even coded data is institutional data, so check your institution's AI and data guidance and whether Zapier is an approved tool before you connect it. If it is not approved, stop at Part 2 and use the sheet alone: the Summary tab works as a dashboard you open on Monday.

Build It Step by Step

Part 1: Build the Proposals Tab

Create a new spreadsheet or open your tracker. Name one tab exactly Proposals and another exactly Summary. Tab names are case-sensitive in formulas, so match them.

On Proposals, row 1 holds the headers and each proposal gets one row from row 2 down.

Add the Status dropdown with Data, then Data validation, then Add rule, pick Dropdown, and enter the four values on G2:G201. Format D and E as dates and H as a plain number.

On Summary, row 1 holds these headers and row 2 holds the one data row.

Look Ahead Days is your own setting. Choose it from how much lead time your office needs, not from any sponsor rule. Your office's practice decides it.

Part 2: Add the Formulas

Paste each formula into row 2 of its column and then copy it down to row 201. That gives room for 200 proposals. Every range below ends at row 201, so extend them all together if you ever need more rows.

Days to Internal, cell H2

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

Blank when there is no proposal code or no internal date. Otherwise the internal date minus today, so a date in the past gives a negative number.

Flag, cell I2

=IF(A2="","",IF(OR(G2="Submitted",G2="Withdrawn"),"Closed",IF(G2="Routed","Routed",IF(D2="","Missing date",IF(H2<0,"PAST INTERNAL",IF(H2<=Summary!$B$2,"DUE SOON","Later"))))))

The formula reads top to bottom and stops at the first test that is true.

• Blank row (A2 empty): returns blank. This is why a formula filled down to row 201 stays silent on empty rows.
• Submitted or Withdrawn: "Closed".
• Routed: "Routed".
• Internal Deadline empty: "Missing date".
• Days to Internal below 0: "PAST INTERNAL".
• Days to Internal at or below Look Ahead Days (Summary!$B$2): "DUE SOON".
• Anything else: "Later".

Closed and Routed come first on purpose. A proposal that is already submitted or already in the central office's hands has an internal date in the past, and if the date tests ran first, every finished proposal would shout PAST INTERNAL forever. Testing the status first lets a finished or routed proposal drop out of the alerts whatever its dates say. A blank Status with a proposal code falls through all the way down and is treated as still in progress.

Digest Line, cell J2

=IF(A2="","",A2&" | "&B2&" | internal "&IF(D2="","no date",TEXT(D2,"yyyy-mm-dd")&", "&IF(H2<0,-H2&IF(H2=-1," day"," days")&" past","in "&H2&IF(H2=1," day"," days")))&" | sponsor "&IF(E2="","no date",TEXT(E2,"yyyy-mm-dd"))&IF(F2="",""," | missing: "&F2)&" | "&I2)

Each date is wrapped in TEXT so it prints as 2027-02-17 and not as a serial number such as 46435. The internal date has a blank guard that prints "no date", and so does the sponsor date. The missing part appears only when Missing Items is not blank. A blank proposal row returns blank.

Action Count, Summary!C2

=COUNTIF(Proposals!$I$2:$I$201,"PAST INTERNAL")+COUNTIF(Proposals!$I$2:$I$201,"Missing date")+COUNTIF(Proposals!$I$2:$I$201,"DUE SOON")

Past Internal Count, Summary!D2

=COUNTIF(Proposals!$I$2:$I$201,"PAST INTERNAL")

Both count the labels the Flag column writes for filled rows. Empty rows return blank, so they are never counted.

Digest Lines, Summary!E2

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Proposals!$J$2:$J$201,(Proposals!$I$2:$I$201="PAST INTERNAL")+(Proposals!$I$2:$I$201="Missing date")+(Proposals!$I$2:$I$201="DUE SOON"))),"Nothing due in the look-ahead window")

The three ranges inside FILTER all run from row 2 to row 201, so they are the same height. FILTER keeps the Digest Line of every row whose Flag is one of the three action labels. TEXTJOIN stacks those lines with a line break between them. When nothing matches, FILTER returns an error and IFERROR substitutes the fallback sentence, so the cell is never empty.

Re-read each formula once after pasting. Count the opening and closing parentheses, confirm every Proposals range ends at row 201, and confirm the tab names match.

Part 3: Keep Calculation on Automatic

TODAY() recalculates as the sheet is used. Google's calculation settings page lists only two choices, Automatic and Manual, with no scheduled recalculation. In the sheet, open File, then Settings, then the Calculation tab, and keep the mode on Automatic. If the counts in a Monday email look stale, open the sheet before the run and check that Days to Internal matches today's date.

Part 4: Build the Zap

In Zapier, create a new Zap with three steps.

1. Trigger. Choose the app Schedule by Zapier and the event Every Week. Pick Monday and a morning time, for example before you start work.
2. Action. Choose Google Sheets and the event Lookup Spreadsheet Row. Connect your Google account and then choose the spreadsheet and the Summary tab. Set the lookup column to Key and the lookup value to summary. Do not use any other condition. The sheet already did the work, and you want a lookup that always finds its row.
3. Action. Choose Gmail and the event Send Email. In To, enter your own address and nobody else's. In Subject, build the line in this order: the text Proposal digest: , the Action Count field from step 2, the text actions,, the Past Internal Count field and the text past internal. In Body, insert the Digest Lines field from step 2. If the action offers a body type, choose plain text so the line breaks from the sheet survive.

Zapier lists the Summary columns as fields named after your row 1 headers. If a header is missing from the field list, run the lookup test again so Zapier reloads the headers.

Test each step. The lookup test should return the summary row with the counts and the digest text. The email test should reach your inbox. Then turn the Zap on.

Part 5: Test and Refine

Add a dummy proposal row with a past internal date and Status In progress. Confirm that Flag reads PAST INTERNAL, that both counts rise by one, and that the line appears in Digest Lines. Then change its Status to Submitted and confirm that it disappears. Delete the dummy row when done.

Real Example: A Monday in February

Invented setup: today is Monday 2027-02-22 and Look Ahead Days is 14, so a proposal is DUE SOON when its internal date falls on or before 2027-03-08. All codes and dates are invented.

Working through the Flag formula:

• P-101: Days to Internal is 2027-02-17 minus 2027-02-22, which is -5. Not closed or routed, date present, below 0, so PAST INTERNAL.
• P-102: 2027-02-26 minus 2027-02-22 is 4. Not below 0, and 4 is at most 14, so DUE SOON.
• P-103: no internal date, so Days to Internal is blank and the Flag is Missing date.
• P-104: 2027-03-08 minus 2027-02-22 is 14 (6 days left in February plus 8 in March). 14 is at most 14, so DUE SOON. The window includes its last day.
• P-105: 2027-03-09 minus 2027-02-22 is 15. Above 14, so Later.
• P-106: the internal date is 3 days past, but Status is Routed, so Routed. It is not an alert.
• P-107: 12 days past, but Status is Submitted, so Closed.
• Row 9: no proposal code, so every formula returns blank and nothing is counted.

Counts: PAST INTERNAL is 1 (P-101), Missing date is 1 (P-103), DUE SOON is 2 (P-102 and P-104). Action Count is 1 + 1 + 2 = 4. Past Internal Count is 1.

The Digest Lines cell holds four lines in sheet order:

P-101 | PI A | internal 2027-02-17, 5 days past | sponsor 2027-03-01 | missing: biosketch | PAST INTERNAL
P-102 | PI B | internal 2027-02-26, in 4 days | sponsor 2027-03-05 | DUE SOON
P-103 | PI C | internal no date | sponsor 2027-04-15 | missing: letters | Missing date
P-104 | PI D | internal 2027-03-08, in 14 days | sponsor 2027-03-19 | missing: data plan | DUE SOON

The email arrives with the subject "Proposal digest: 4 actions, 1 past internal" and those four lines in the body. If every proposal were Later, Routed or Closed, both counts would be 0 and the body would read "Nothing due in the look-ahead window". The email still arrives.

Setup: one spreadsheet, one Zap, three steps. Input: the Monday schedule and the rows you keep current. Output: one email to you with two counts in the subject and the flagged proposals in the body. Time saved: the weekly scan of the whole tracker, which depends on your portfolio size. The sheet is only as current as your last edit, so the habit of updating Status and dates is the price.

What to Do When It Breaks

• No email arrived on Monday. The failure you will not see is a Zap that stopped running. Check that the Zap is switched on and then open Zap History for the Monday run. A "Safely halted" lookup means no row had Key equal to summary. Someone renamed the Key cell, changed its text or moved the Summary tab. Restore the text summary in A2 exactly. A run that never appears at all usually means the Zap was turned off or the Google connection expired. Reconnect it under your Zapier connected accounts and re-test the lookup.
• Put a standing reminder in your calendar. If no digest has arrived by mid-morning on Monday, treat that as the alarm and check the Zap that day.
• Counts look wrong or stale. Open the sheet and confirm Calculation is on Automatic. Then check that Days to Internal shows today's date arithmetic.
• The email says nothing is due, but you know a deadline is close. The digest only reflects the rows. Check that the proposal is in the tab, that the internal date is a real date and not text, and that the Status is not Routed, Submitted or Withdrawn.
• Dates show as long numbers. A date was joined without TEXT. Re-paste the Digest Line formula exactly.
• A formula shows #REF! or #NAME?. A tab was renamed. Rename it back to Proposals or Summary.
• Line breaks collapse in the email. Switch the Gmail step to plain text if it offers a body type and then re-test.

Variations

• Simpler version: skip Zapier and open the Summary tab each Monday. The counts and lines are the same.
• Extended version: add a Google Form for new proposals. Form answers land on their own responses tab, and you copy each one into Proposals yourself. Never point a Form directly at the Proposals tab, because it would overwrite your formulas. The Zap still runs on its schedule.
• Twice a week: copy the Zap and set the second one to run on a different day.

What to Do Next

• This week: build the tab and test with dummy rows. Then switch the Zap on. Send your first real nudges by hand.
• This month: tune Look Ahead Days after you see how many lines each Monday produces.
• Advanced: build the sister digests for award reporting and subaward follow-up, each in its own spreadsheet with its own Summary tab.

Advanced guide for sponsored programs administrators. The digest flags dates you have typed. It does not read a solicitation, decide a deadline or contact anyone. Your office's routing rules and the sponsor's current instructions set the dates.