Subaward Follow-Up Digest: A Zapier Automation That Shows What You Are Still Waiting For

What This Builds

Chasing subrecipients means remembering which invoice, report or signed modification is still outstanding and for how long. This build keeps that list in a sheet and emails you once a week with the items that have been waiting longer than your own nudge window, plus any item with no expected date.

The sheet does the work, so no subaward information goes to an AI vendor. The automation never contacts a subrecipient. The email goes to you alone. You write and send each nudge yourself and then record Received in the sheet so the item drops out of the next digest.

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 and two actions, so 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 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.
• Your subaward agreements and your office's monitoring procedure to hand. What a subrecipient must send and when comes from the agreement and that procedure. This guide states no rule about it.
• 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

Think of a whiteboard in the office with one line at the top: "Waiting on N things." Every time you update the list, someone rewrites the line and the list under it. Zapier is the colleague who photographs the whiteboard each week and puts the photo in your inbox.

The whiteboard has to exist every week. A Zapier search step 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 Subawards tab for late items, a week with nothing late would find nothing and send nothing, and you could not tell good news from a broken Zap. Looking up one Summary row whose Key is always "summary" means the search always succeeds, so the email arrives weekly even when the answer is "nothing waiting".

What Zapier Sees and Keeps

Zap History stores each step's data. Here that is the Summary row and the email: subaward codes, award codes, short item labels, dates and status values.

Keep out of the sheet: subrecipient names, contact details, amounts, invoice totals and any text from an agreement. Use codes such as "S-12" and "A-27" and short labels such as "Invoice Q3". Amounts 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 Parts 1 and 2 and read the Summary tab by hand.

Build It Step by Step

Part 1: Build the Subawards Tab

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

On Subawards, row 1 holds headers and each thing you expect from a subrecipient gets one row from row 2 down. A subaward waiting on an invoice and a progress report uses two rows.

Add the Status dropdown with Data, then Data validation, then Add rule, on E2:E201. Format D as a date and F as a plain number.

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

Nudge After Days is your own setting, taken from your office's monitoring practice. It is not a deadline from any agreement.

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 items. Every range ends at row 201, so extend them all together if you need more.

Days Waiting, cell F2

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

Blank when there is no item or no expected date. Otherwise today minus the expected date, so a date already passed gives a positive number and a future date gives a negative one. This column runs the opposite way to the deadline digests, because here the count measures how long you have waited.

Flag, cell G2

=IF(C2="","",IF(OR(E2="Received",E2="Not needed"),"Closed",IF(D2="","Missing date",IF(F2<0,"Not due yet",IF(F2>=Summary!$B$2,"NUDGE","Waiting")))))

The formula stops at the first true test.

• Blank row (Item empty): blank, so unused rows stay silent.
• Status Received or Not needed: "Closed".
• Expected Date empty: "Missing date".
• Days Waiting below 0, meaning the expected date is still ahead: "Not due yet".
• Days Waiting at least Nudge After Days (Summary!$B$2): "NUDGE".
• Anything else: "Waiting", meaning the date has passed but not yet by your nudge window.

Closed is tested before the date tests so an item you already have never nags. "Not due yet" is tested before NUDGE because a future date gives a negative number that would otherwise fall through to "Waiting". Nothing is late yet for that item, and the label should say so.

Digest Line, cell H2

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

The date goes through TEXT so it prints as 2027-02-05. The blank-date guard prints "no date". A date still ahead reads "in N days", and a date past reads "N days ago". A blank row returns blank.

Action Count, Summary!C2

=COUNTIF(Subawards!$G$2:$G$201,"NUDGE")+COUNTIF(Subawards!$G$2:$G$201,"Missing date")

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

Digest Lines, Summary!D2

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Subawards!$H$2:$H$201,(Subawards!$G$2:$G$201="NUDGE")+(Subawards!$G$2:$G$201="Missing date"))),"Nothing waiting past your nudge window")

Both ranges inside FILTER run from row 2 to row 201, the same height. 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 exactly 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 digest shows counts that look stale, open the sheet before the run and check that Days Waiting 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. Pick a day and a morning time.
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 Subaward follow-up: , insert the Action Count field from step 2 and finish with the text waiting. In Body, insert the Digest Lines field. If the action offers a body type, pick plain text so 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: Close the Loop by Hand

When a digest line tells you an item is overdue for your window, write the nudge yourself and send it from your own mail. The Level 1 prompt for subaward and report reminder emails can draft the wording. When the item arrives, set Status to Received in the sheet. The next digest will not mention it. The automation never writes to a subrecipient.

Real Example: A Monday in February

Invented setup: today is Monday 2027-02-22 and Nudge After Days is 10. All codes and dates are invented.

Working through the Flag formula:

• S-12 Invoice Q3: Days Waiting is 2027-02-22 minus 2027-02-05, which is 17. Not closed, date present, not negative, and 17 is at least 10, so NUDGE.
• S-12 Progress report: 2027-02-22 minus 2027-02-12 is 10. 10 is at least 10, so NUDGE. The boundary day counts.
• S-14 Signed modification: 2027-02-22 minus 2027-02-15 is 7. Above 0 and below 10, so Waiting.
• S-14 Invoice Q4: 2027-02-22 minus 2027-03-05 is -11 (6 days left in February plus 5 in March). Negative, so Not due yet.
• S-15 Progress report: no expected date, so Days Waiting is blank and the Flag is Missing date.
• S-16 Invoice Q3: Received, so Closed, even though it was 33 days past its expected date.
• Row 8: no item, so every formula returns blank and nothing is counted.

Counts: NUDGE is 2 and Missing date is 1, so Action Count is 3.

The Digest Lines cell holds three lines in sheet order:

S-12 | A-27 | Invoice Q3 | expected 2027-02-05, 17 days ago | NUDGE
S-12 | A-27 | Progress report | expected 2027-02-12, 10 days ago | NUDGE
S-15 | A-35 | Progress report | no date | Missing date

The email arrives with the subject "Subaward follow-up: 3 waiting". Your next steps are by hand: send two nudges for S-12 and look up the expected date for S-15 in its agreement. With nothing flagged, the body would read "Nothing waiting past your nudge window" and the email would still arrive.

Setup: one spreadsheet, one Zap, three steps. Input: the weekly schedule and the expected dates you keep current. Output: one email to you with the items worth a nudge. Time saved: the weekly read through every subaward file for what is outstanding. The digest is only as accurate as the dates and statuses you maintain.

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, because 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 the lookup.
• 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.
• The digest keeps listing an item you already received. Status is still Waiting. Set it to Received.
• An item you are waiting on never appears. Check that the Expected Date is a real date and not text, that Status is Waiting and that the date is at least Nudge After Days in the past.
• Counts look stale. Confirm Calculation is Automatic and open the sheet before the run.
• #REF! or #NAME? appears. A tab was renamed. Rename it back to Subawards or Summary.

Variations

• Simpler version: skip Zapier and read the Summary tab on a set day.
• Extended version: add a Google Form that you use to log new expected items. Answers land on a separate responses tab and you copy each into Subawards yourself. Never point a Form at the Subawards tab, which would overwrite the formulas.
• Two windows: add a second setting and a second flag, such as an escalation label, and then add it to both the Action Count and the FILTER.

What to Do Next

• This week: build the sheet from your open subawards and test with dummy rows.
• This month: tune Nudge After Days to your office's practice.
• Advanced: pair this with the proposal deadline digest and the award reporting digest, each in its own spreadsheet.

Advanced guide for sponsored programs administrators. The digest lists items you recorded as expected. It does not read an agreement, decide what a subrecipient owes or contact anyone. The subaward agreement and your office's monitoring procedure set what is due and when.