Award Reporting Digest: A Zapier Automation That Flags Report Dates and End Dates

What This Builds

Post-award work runs on dates scattered across award notices: progress reports, financial reports, closeout reports and the end date itself. This build collects them in one sheet and sends you a single email each week that lists what is overdue, what has no date, what is due inside your window and which award end dates are close with no extension decision recorded.

The sheet does the thinking, so no award information goes to an AI vendor. Nothing is sent to a PI or a sponsor. The email goes to you alone, and you send any reminder yourself (the Level 1 reminder email prompt helps with the wording).

Prerequisites

• A Google account for the sheet and a Gmail address you may 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, so three steps, and Zapier's free plan has been limited to two-step Zaps. Check Zapier's current plan page first.
• Total ongoing cost of the finished build: the Zapier plan above. Google Sheets and Gmail add nothing on an account you already have. Each weekly run uses two tasks, so roughly 8 to 10 a month. Zapier counts only successful action steps as tasks.
• The award notices open in front of you, since every date in the sheet is typed from them by hand.
• Your institution's AI and data guidance read, and an answer on whether Zapier is an approved tool for coded data like this.

The Concept

Picture a single index card pinned to a board. The sheet rewrites the card whenever a date or status changes: a count of items needing action and a list of them. Each week Zapier reads the card and mails you a copy.

The card must always exist. A Zapier search step that finds zero rows halts the Zap ("Safely halted" in Zap History), and nothing after it runs, so no email goes out. If the Zap searched the Reports tab for overdue rows, a clean week would find none and send nothing, which looks exactly like a broken Zap. Searching a Summary row whose Key is always "summary" means the lookup always succeeds and the email arrives every week, even when the card says nothing is due.

What Zapier Sees and Keeps

Zap History stores the data of every step. Here that is the Summary row and the email: award codes, PI codes, item types, dates, extension decisions and status values.

Keep out of the sheet: award titles, anyone's name, amounts, salaries, effort, and any text from an award notice. Use codes such as "A-27" and "PI A". Amounts and spending stay in the financial system. Even coded data is institutional data, so check your institution's AI and data guidance and whether Zapier is an approved tool before connecting it. If it is not, build only Parts 1 and 2 and read the Summary tab by hand.

Build It Step by Step

Part 1: Build the Reports Tab

Create a new spreadsheet just for this digest. Name one tab exactly Reports and another exactly Summary.

On Reports, row 1 holds headers and each dated obligation of an award gets its own row from row 2 down. An award with a progress report and an end date uses two rows.

Add the dropdowns with Data, then Data validation, then Add rule, on C2:C201, E2:E201 and F2:F201. Format D as a date and G as a plain number.

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

Both windows are your own settings. This guide states no sponsor rule about how early an extension must be requested or what a report requires. Set End Date Warning Days from your office's practice and the award terms you hold, and ask the central office or the sponsor's grants officer when in doubt.

Part 2: Add the Formulas

Paste each formula into row 2 of its column and copy it down to row 201, which leaves room for 200 obligations. Every range ends at row 201, so extend them all together if you need more.

Days Left, cell G2

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

Blank when there is no award code or no due date. Otherwise the due date minus today, negative once the date has passed.

Flag, cell H2

=IF(A2="","",IF(F2="Done","Closed",IF(D2="","Missing date",IF(G2<0,"OVERDUE",IF(AND(C2="Award end date",E2="",G2<=Summary!$C$2),"DECIDE EXTENSION",IF(G2<=Summary!$B$2,"DUE SOON","Later"))))))

The formula stops at the first true test.

• Blank row (A2 empty): blank, so rows below your data stay silent.
• Status Done: "Closed".
• Due Date empty: "Missing date".
• Days Left below 0: "OVERDUE".
• Item Type is Award end date, Extension Decision is empty and Days Left is at most End Date Warning Days (Summary!$C$2): "DECIDE EXTENSION".
• Days Left at most Look Ahead Days (Summary!$B$2): "DUE SOON".
• Anything else: "Later".

DECIDE EXTENSION is tested before DUE SOON because an end date can fall inside both windows. With a 30-day look-ahead and a 90-day warning, an end date 20 days away is inside both. If DUE SOON came first, that row would read DUE SOON and the open question about an extension would never surface. Putting DECIDE EXTENSION first keeps the more specific warning. Once you record any decision in column E, the DECIDE test no longer matches and the row falls through to DUE SOON or Later, which is how a settled end date stays visible without nagging you for a decision. Closed comes first so a finished item never alerts.

Digest Line, cell I2

=IF(A2="","",A2&" | "&B2&" | "&C2&" | "&IF(D2="","no date","due "&TEXT(D2,"yyyy-mm-dd")&", "&IF(G2<0,-G2&IF(G2=-1," day"," days")&" overdue","in "&G2&IF(G2=1," day"," days")))&" | "&H2)

The date goes through TEXT so it prints as 2027-02-15. The blank-date guard prints "no date". A blank row returns blank.

Action Count, Summary!D2

=COUNTIF(Reports!$H$2:$H$201,"OVERDUE")+COUNTIF(Reports!$H$2:$H$201,"Missing date")+COUNTIF(Reports!$H$2:$H$201,"DECIDE EXTENSION")+COUNTIF(Reports!$H$2:$H$201,"DUE SOON")

It counts only labels that the Flag column writes for filled rows, so empty rows never add to it.

Digest Lines, Summary!E2

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Reports!$I$2:$I$201,(Reports!$H$2:$H$201="OVERDUE")+(Reports!$H$2:$H$201="Missing date")+(Reports!$H$2:$H$201="DECIDE EXTENSION")+(Reports!$H$2:$H$201="DUE SOON"))),"Nothing due in your windows")

All four ranges inside FILTER have the same height, rows 2 to 201. When nothing matches, FILTER errors and IFERROR returns the fallback sentence.

After pasting, re-read each formula: matching parentheses, every range ending at row 201, and tab names spelled as above.

Part 3: Keep Calculation on Automatic

Google's calculation settings page lists only Automatic and Manual, with no scheduled recalculation. Open File, then Settings, then Calculation, and keep the mode on Automatic. If a Monday email shows counts that look stale, open the sheet first and check that Days Left matches today's date.

Part 4: Build the Zap

Create a new Zap with three steps.

1. Trigger. App Schedule by Zapier, event Every Week. Choose a day and a morning time that suits your week.
2. Action. App Google Sheets, event Lookup Spreadsheet Row. Choose this spreadsheet and the Summary tab. Set the lookup column to Key and the lookup value to summary. Add no other condition.
3. Action. App Gmail, event Send Email. In To, enter your own address only. In Subject, type Award reporting digest: , insert the Action Count field from step 2 and finish with the text items need action. In Body, insert the Digest Lines field. If the action offers a body type, pick plain text so the line breaks survive.

Zapier names the lookup fields after your Summary row 1 headers. If one is missing, re-run the lookup test to reload the headers. Test each step and check the email. Then turn the Zap on.

Part 5: Test and Refine

Add dummy rows covering each label, for example a Progress report with a date last week, an Award end date inside your warning window with no decision, and a row with no date. Confirm each flag and that Action Count matches your count by eye. Delete the dummy rows afterward.

Real Example: A Monday in February

Invented setup: today is Monday 2027-02-22, Look Ahead Days is 30 (so DUE SOON reaches 2027-03-24) and End Date Warning Days is 90 (so DECIDE EXTENSION reaches 2027-05-23). All codes and dates are invented.

Working through the Flag formula:

• A-27 progress report: 2027-02-15 minus 2027-02-22 is -7. Open, date present, below 0, so OVERDUE.
• A-27 end date: 2027-05-01 minus 2027-02-22 is 68 (6 days left in February, 31 in March, 30 in April, 1 in May). Not overdue. It is an Award end date, the decision is empty and 68 is at most 90, so DECIDE EXTENSION. At 68 days it is outside the 30-day look-ahead, so only the end date warning catches it.
• A-31 financial report: 2027-03-15 minus 2027-02-22 is 21. Not an end date, and 21 is at most 30, so DUE SOON.
• A-31 end date: 2027-03-20 minus 2027-02-22 is 26. It is an Award end date with a decision recorded (Not needed), so the DECIDE test fails and the next test applies: 26 is at most 30, so DUE SOON. Had the decision been empty, this row would have matched both windows and shown DECIDE EXTENSION first.
• A-35 closeout report: no due date, so Days Left is blank and the Flag is Missing date.
• A-38 progress report: 2027-05-10 minus 2027-02-22 is 77. Above 30, and not an end date, so Later.
• A-38 end date: 2027-08-31 minus 2027-02-22 is 190. Above 90, so DECIDE does not match. Above 30, so Later.
• A-40 financial report: Done, so Closed, even though 2027-02-01 is 21 days past.
• A blank row 10: every formula returns blank and nothing is counted.

Counts: OVERDUE 1, Missing date 1, DECIDE EXTENSION 1, DUE SOON 2. Action Count is 1 + 1 + 1 + 2 = 5.

The Digest Lines cell holds five lines in sheet order:

A-27 | PI A | Progress report | due 2027-02-15, 7 days overdue | OVERDUE
A-27 | PI A | Award end date | due 2027-05-01, in 68 days | DECIDE EXTENSION
A-31 | PI B | Financial report | due 2027-03-15, in 21 days | DUE SOON
A-31 | PI B | Award end date | due 2027-03-20, in 26 days | DUE SOON
A-35 | PI C | Closeout report | no date | Missing date

The email arrives with the subject "Award reporting digest: 5 items need action". With nothing flagged it would arrive with the body "Nothing due in your windows".

Setup: one spreadsheet, one Zap, three steps. Input: the weekly schedule and the dates you typed from award notices. Output: one email to you listing what to chase and which end dates need an extension decision. Time saved: the weekly pass through every award file looking for dates. The digest is only as good as the dates you typed, so check each against its award notice when you enter it.

What to Do When It Breaks

• No email arrived. A stopped Zap is silent. Check that the Zap is on and then open Zap History for the expected run. "Safely halted" at the lookup means no row had Key equal to summary, so someone renamed A2 or moved the tab. Restore the text summary exactly. No run at all means the Zap was turned off or the Google connection expired, so reconnect it and re-test.
• Set a calendar reminder. If no digest has arrived by mid-morning on the scheduled day, treat that as the alarm and check the Zap that day.
• Counts look stale. Confirm Calculation is Automatic and open the sheet before the run.
• An end date never shows DECIDE EXTENSION. Check that Item Type reads exactly Award end date, that Extension Decision is truly empty (a stray space counts as filled) and that Days Left is inside your End Date Warning Days.
• A report shows Later but you know it is close. Check that the Due Date is a real date and not text, and that your Look Ahead Days is large enough.
• #REF! or #NAME? appears. A tab was renamed. Rename it back to Reports or Summary.

Variations

• Simpler version: skip Zapier and read the Summary tab each week.
• Extended version: add a Google Form for new obligations. Answers land on a separate responses tab and you copy them into Reports yourself. Never point a Form at the Reports tab, which would overwrite the formulas.
• Shorter cycle: switch the trigger to Every Day when many reports cluster near a deadline. That uses more tasks.

What to Do Next

• This week: build the sheet, enter your next 60 days of obligations from the award notices and test with dummy rows.
• This month: tune the two windows against what the digest actually flags.
• Advanced: pair this with the proposal deadline digest and the subaward follow-up digest, each in its own spreadsheet.

Advanced guide for sponsored programs administrators. The digest flags dates you typed from award notices. It does not read an award, interpret a term or contact anyone. The award notice, your central office and the sponsor's grants officer decide what is due and when.