Weekly Data Request Digest: A Zapier and Google Sheets Automation That Emails You Which Inventory Data Is Late

What This Builds

Every Monday morning, one email arrives in your inbox. It lists each greenhouse gas inventory data request that needs you to do something this week: requests that are overdue, due within seven days, missing a due date, received but not yet checked, or not yet sent. Everything else stays out of the email, so the list is the length of your real to-do list and not the length of the tracker.

You keep one row per data request in a Google Sheet. The sheet works out each row's stage and builds the email text with ordinary formulas. Zapier reads one summary row and sends the email to you. Nothing goes to data owners. When a reminder is due, you write it yourself, and the Level 1 data reminder prompt gives you a first draft.

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. If you are a consultant, check the engagement letter or NDA for what client data a third-party service may hold. If you are in-house, check the company's approved tools list.
• An existing list of the inventory data you need, by site and data type
• 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, which means Zapier receives and keeps the counts and the digest text, in Zap History, for each run. Anyone with access to your Zapier account can open Zap History. That digest text contains site codes, data type labels, owner team labels and due dates. Use codes and short labels only. Keep these out of every column the digest reads: street addresses, account numbers, personal names, email addresses, a client's name, emissions or energy figures, and anything unreleased. Use a code such as "SITE-03" in place of a facility address and keep your own private key from codes to real sites in a separate file that no Zap reads. For a listed company, an unreleased target or figure can be material non-public information, so it never belongs in this tracker.

The Concept

Think of the Summary tab as a noticeboard with exactly one card on it. The card always exists, whatever is going on in the tracker, and it always shows two counts and a block of text. Zapier's only job is to pick up that card on Monday morning and mail it to you.

This design matters because of how Zapier searches. If a Zapier search step finds no matching row, the Zap stops there. Zap History records it as "Safely halted", no later step runs and no email is sent. A digest built on "find rows that are overdue" would therefore go silent in your best week, when nothing is overdue, and you could not tell good news from a broken Zap. A Summary row with a fixed Key always matches. The email arrives every week, and when nothing needs action it says so.

The same reasoning explains why the sheet, not Zapier, does the date math. Zapier lookups match on text in a column. They are not a safe place for "before today" tests. Formulas in the sheet are easy to see and easy to check by hand.

Build It Step by Step

Part 1: Set Up the Requests Tab

Create a Google Sheet and name the first tab Requests. Put these headers in row 1.

Set up the Status dropdown. Select F2:F500, choose Data then Data validation, add a rule of type Dropdown, and enter the five labels exactly as written above. Turn on the option to reject other input. The Stage formula tests these exact labels, so a typo would put a row in the wrong stage.

Format D2:E500 as dates. This tab tracks status only. No figures, no bills and no account numbers go on it. When data arrives, you store it wherever you keep your inventory calculations.

Part 2: Add the Two Formula Columns

Put the formulas in G2 and H2 and fill them down to row 500. Rows below your last request stay blank, because every formula starts with a check on Data Type.

Stage, cell G2:

=IF(B2="","",IF(OR(F2="Checked",F2="Not applicable"),"Closed",IF(OR(F2="Not sent",F2=""),"Not sent",IF(F2="Received","Received, not checked",IF(E2="","Missing due date",IF(E2<TODAY(),"OVERDUE",IF(E2<=TODAY()+7,"Due this week","Waiting")))))))

Digest Line, cell H2:

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

Read the Stage formula from the outside in. The tests run in this order, and the first one that is true wins.

1. Data Type is blank: the row is empty, so Stage is blank.
2. Status is Checked or Not applicable: Closed.
3. Status is Not sent, or Status was left blank: Not sent. A row with a data type but no status needs your attention, so it is treated as not sent.
4. Status is Received: "Received, not checked".
5. Anything left is a Requested row. Due is blank: "Missing due date".
6. Due is before today: OVERDUE.
7. Due is within the next seven days, today included: "Due this week".
8. Otherwise: Waiting.

Why Closed is tested before the date tests. A row you have checked usually has a due date in the past. If the date tests came first, every finished request would be flagged OVERDUE for the rest of the year. Testing Closed first means a Checked row never reaches the date tests. The Received test sits ahead of the date tests for the same reason: a received file is waiting on you, not on the owner, so its old due date is irrelevant.

Why the digest line uses TEXT(). A date joined to text with & turns into a serial number. TEXT(E2,"yyyy-mm-dd") prints it as 2026-10-16. The blank-date guard matters too, because a blank cell passed to TEXT prints 1899-12-30. Rows with no due date print "no due date" instead.

Before you fill down, count the parentheses in G2. The formula opens seven IF functions and closes all seven at the end. Then fill G2:H2 down to row 500.

Part 3: Build the Summary Tab

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

Action Count, cell B2:

=COUNTIF(Requests!G2:G500,"OVERDUE")+COUNTIF(Requests!G2:G500,"Due this week")+COUNTIF(Requests!G2:G500,"Missing due date")+COUNTIF(Requests!G2:G500,"Received, not checked")+COUNTIF(Requests!G2:G500,"Not sent")

Open Count, cell C2:

=B2+COUNTIF(Requests!G2:G500,"Waiting")

Digest Lines, cell D2:

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Requests!H2:H500,(Requests!G2:G500="OVERDUE")+(Requests!G2:G500="Due this week")+(Requests!G2:G500="Missing due date")+(Requests!G2:G500="Received, not checked")+(Requests!G2:G500="Not sent"))),"Nothing needs action this week")

Three details keep these honest. First, the counts look at the Stage column and never at Data Type or Status, and blank formula rows return an empty string, so they match none of the labels. Open Count adds only Waiting, so the blank rows are not counted. Second, every range in the FILTER runs from row 2 to row 500, the same height, which FILTER requires. Third, when no row matches, FILTER returns an error. IFERROR catches it and returns the fallback sentence, and that is why the email still has something to say in a quiet week.

CHAR(10) is a line break, so each digest line sits on its own line in the email.

Part 4: Build the Zap

Sign in to Zapier and create a Zap with these three steps.

Step 1, trigger: Schedule by Zapier, Every Week. Choose Monday for Day of the Week and set Time of Day to early morning, before you start work. Check the timezone, and use the Timezone Override field if your Zapier account timezone is not yours.

Step 2, action: Google Sheets, Lookup Spreadsheet Row. Connect your Google account. Choose your spreadsheet in Drive and Spreadsheet, then choose Summary as the Worksheet. Set Lookup column to Key and Lookup value to summary, typed in rather than mapped. Zapier reads column names from row 1, so if you rename a header later, reselect the worksheet to refresh them. Test the step. The output should show the whole row, with Action Count, Open Count and Digest Lines.

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

• To: your own address and nobody else's
• Subject: Data request digest: followed by the Action Count field from step 2, then need action
• Body type: plain text, so the line breaks in the digest 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 arrives. Then publish the Zap.

Task use. Zapier's own help page says only successful action steps count as tasks. The trigger is not an action. Each run has two action steps, the lookup and the email, so a weekly run uses up to two tasks. Check your plan's monthly task limit on Zapier's pricing page.

Part 5: Run It Weekly

Your side of the routine takes a minute per row. When a file arrives, set Status to Received. After you have checked it against the source, set it to Checked and the row drops out of the digest. Reminders stay manual. Read the digest line and write the reminder to the owner yourself, from your own mailbox.

If the tracker is going to a second person, share the sheet with them. Do not add their address to the Zap.

Real Example: A Six-Site Inventory in October

Invented example. Say today is Monday 2026-10-12, so TODAY()+7 is 2026-10-19. Company A has seven requests on the Requests tab.

Counts on the Summary row. OVERDUE 1, Due this week 1, Missing due date 1, Received, not checked 1 and Not sent 1 make an Action Count of 5. Add the one Waiting row for an Open Count of 6. The Closed row and the blank rows are in neither count.

The Digest Lines cell holds these five lines, in sheet order:

SITE-01 | Natural gas therms FY2025 | Facilities SITE-01 | due 2026-10-02 | OVERDUE
SITE-02 | Electricity kWh FY2025 | Facilities SITE-02 | due 2026-10-16 | Due this week
SITE-03 | Fleet fuel liters FY2025 | Fleet team | no due date | Missing due date
SITE-04 | Water m3 FY2025 | Facilities SITE-04 | due 2026-10-09 | Received, not checked
SITE-05 | Waste tonnes FY2025 | Facilities SITE-05 | due 2026-10-30 | Not sent

The email subject reads "Data request digest: 5 need action" and the body ends with "Open requests in total: 6". Next Monday, if you have checked the SITE-04 file and sent the SITE-05 request, SITE-04 shows Closed, SITE-05 moves to Waiting or Due this week depending on its due date, and the email gets shorter. The week after all rows are Checked, the email says "Nothing needs action this week".

Time saved: If your weekly pass through the tracker to see what is late takes 10 to 20 minutes (your own number will differ), the email replaces that pass. The reminders themselves still take your time.

What to Do When It Breaks

• No email arrived on Monday. Silence is the failure you will not notice by itself, because the Zap sends nothing when it breaks. Open Zapier and check that the Zap is turned on. Then open Zap History and look for a Monday run. A run marked "Safely halted" means the lookup found no row: check that the Key cell on the Summary tab still says summary exactly, and that the worksheet in step 2 is still Summary. An error on step 2 or step 3 usually means the Google connection expired, so reconnect it in the Zap's step settings. Put a recurring Monday calendar event on your own calendar as a backstop: if the digest is not in your inbox by mid-morning, check Zapier.
• The digest says "Nothing needs action this week" but you know something is late. Check the Status dropdown values on the late rows. A row with a blank Data Type is skipped entirely. A row whose Due cell holds text instead of a date will not compare correctly, so reformat the column as dates.
• A date prints as 1899-12-30 or a five-digit number. The TEXT() wrapper or the blank-date guard was lost when the formula was edited. Copy H2 from this guide again.
• A formula shows #REF! or #NAME?. A tab was renamed. The formulas on the Summary tab refer to Requests by name.
• The count does not match what you see. Rows below row 500 are not read. Extend every range, both formulas and the Summary ranges, together.
• Zapier history shows names you did not mean to share. Someone typed a person's name into Owner. Replace it with a team label. Earlier runs may still hold the name in Zap History, so check how long your plan keeps run data and delete old runs if you can.

Variations

• Simpler version: Skip Zapier and read the Summary tab yourself on Monday. The sheet does the same work, and nothing leaves Google.
• Extended version: Add a second Zap on Thursday with the same Summary row for a mid-week nudge, at the cost of two more tasks a week. If you also track suppliers or questionnaires, the two related guides in this set use the same pattern with different stages.

What to Do Next

• This week: Enter your real requests using codes and labels, build the two formula columns and test them with one row for each stage, including a blank row.
• This month: Agree status labels with the data owners' teams so everyone means the same thing by Received and Checked.
• Advanced: Add a Google Form that data owners can use to confirm they have uploaded a file. Form answers go to their own responses tab, never straight into the Requests tab, and you copy confirmed ones across 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 tracker.