Weekly Supplier Engagement Digest: A Zapier and Google Sheets Automation That Emails You Which Suppliers Need a Follow-Up

What This Builds

Supplier data requests go quiet in a predictable way. You send thirty of them, a handful answer, and the rest drift while you work on something else. This build sends you one email every week that lists the suppliers who need you: a follow-up date has arrived, an answer is waiting for your review, a follow-up date was never set, or the request was never sent. Each line shows how many days ago you sent the request.

A Google Sheet holds one row per supplier request and works out every stage with formulas. Zapier reads one summary row and emails it to you. Nothing is sent to any supplier. You write each follow-up yourself, and the Level 1 supplier request prompt gives you wording to start from.

If you built the weekly data request digest from the companion guide, you do not need a new spreadsheet. Add this as a second tab and a second Summary row with a different Key, and run a second Zap that looks up that Key.

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. Supplier lists are often covered by supplier agreements and by a client's engagement letter or NDA, so check what a third-party service may hold before you start.
• A separate private list that links each supplier code to the supplier and its contact, kept somewhere no Zap reads
• About 90 minutes for the first build

What goes through Zapier. Zapier does not read your Suppliers 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. That text contains supplier codes, short request labels, day counts and stages. Use a code such as "SUP-014" in place of the supplier's name, and never put a contact person's name, an email address or a phone number in the sheet. Keep supplier answers, emissions figures, spend, scorecards and anything unreleased out of the columns the digest reads. A supplier's answers may be confidential under your agreement with them, so this tab tracks status and dates only.

The Concept

Imagine a noticeboard with one card pinned to it. The card is always there, and it always shows two counts and a block of text. Every Monday Zapier takes the card and mails it to you.

The reason for a fixed card is how Zapier behaves when a search finds nothing. A search step that finds zero rows halts the Zap. Zap History shows "Safely halted", no later step runs and no email goes out. If the Zap searched for suppliers that need a follow-up, it would stay silent in the one week when no supplier needs one, and a silent Zap looks exactly like a broken Zap. The Summary row has a fixed Key and always exists, so the email always arrives.

The sheet also does all the date arithmetic. Zapier lookups match text in a column, and they are not a safe place for date comparisons.

Build It Step by Step

Part 1: Set Up the Suppliers Tab

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

Create the Status dropdown: select E2:E500, choose Data then Data validation, add a Dropdown rule with the six labels exactly as written, and reject other input. Format C2:D500 as dates. Supplier answers and figures never go on this tab. Store them with your inventory data.

Part 2: Add the Three Formula Columns

Enter these in F2, G2 and H2 and fill each down to row 500. Rows below your last supplier stay blank.

Days Since Sent, cell F2:

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

Stage, cell G2:

=IF(A2="","",IF(OR(E2="Reviewed",E2="Declined",E2="Not in scope"),"Closed",IF(OR(E2="Not sent",E2=""),"Not sent",IF(E2="Answer received","Answer to review",IF(D2="","Missing follow-up date",IF(D2<=TODAY(),"FOLLOW UP NOW","Waiting"))))))

Digest Line, cell H2:

=IF(A2="","",A2&" | "&B2&" | "&IF(C2="",IF(G2="Not sent","not sent yet","sent date missing"),"sent "&F2&IF(F2=1," day"," days")&" ago")&" | "&G2)

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

1. Supplier is blank: Stage is blank, so empty rows are never counted.
2. Status is Reviewed, Declined or Not in scope: Closed. This is tested first so that a supplier you finished with never reappears because an old follow-up date has passed.
3. Status is Not sent, or blank: Not sent. A row with a supplier and no status is treated as not sent so you notice it.
4. Status is Answer received: "Answer to review". The ball is in your court, whatever the follow-up date says.
5. What remains is Status Sent. Follow-up Due is blank: "Missing follow-up date". This must come before the date test because a blank cell compares as zero, which is always on or before today, and it would otherwise show FOLLOW UP NOW.
6. Follow-up Due is today or earlier: "FOLLOW UP NOW".
7. Otherwise: Waiting.

The Digest Line prints "sent 21 days ago", or "sent 1 day ago" for a single day. When Sent On is blank, it says "not sent yet" on a Not sent row and "sent date missing" on any other row. Days Since Sent is a plain number, so it joins to text safely. If you ever join a date, wrap it in TEXT(date,"yyyy-mm-dd") with a blank guard, or it prints as a serial number. The Stage formula opens six IF functions and closes six at the end.

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(Suppliers!G2:G500,"FOLLOW UP NOW")+COUNTIF(Suppliers!G2:G500,"Answer to review")+COUNTIF(Suppliers!G2:G500,"Missing follow-up date")+COUNTIF(Suppliers!G2:G500,"Not sent")

Open Count, cell C2:

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

Digest Lines, cell D2:

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Suppliers!H2:H500,(Suppliers!G2:G500="FOLLOW UP NOW")+(Suppliers!G2:G500="Answer to review")+(Suppliers!G2:G500="Missing follow-up date")+(Suppliers!G2:G500="Not sent"))),"No supplier follow-ups this week")

The counts read only the Stage column. Blank rows return an empty string and match no label. All ranges in the FILTER are rows 2 to 500, the same height. With no matching rows FILTER returns an error, IFERROR swaps in the fallback sentence, and the email still has a body.

Adding this to the data request workbook. Keep that workbook's Summary row 2 as it is, because the data request Zap looks it up. Type the word suppliers in A3 of the same Summary tab. Then type the three formulas above into B3, C3 and D3. In C3 the Open Count formula must add to B3 and not B2, so write it as =B3+COUNTIF(Suppliers!G2:G500,"Waiting"). The other ranges stay as written. Both Summary rows have the same four columns, so one tab serves both Zaps. This Zap then looks up suppliers.

Part 4: Build the Zap

Create a Zap with three steps.

Step 1, trigger: Schedule by Zapier, Every Week. Choose the day and the time. Monday morning works well, because supplier follow-ups go out best early in the week. Check the timezone, and use the Timezone Override field if your account is on another one.

Step 2, action: Google Sheets, Lookup Spreadsheet Row. Connect your Google account, choose the spreadsheet and choose Summary as the Worksheet. Set Lookup column to Key and Lookup value to summary (or suppliers on a shared tab), typed in. Test the step. The output should show the whole row. If you rename a header later, reselect the worksheet to refresh the column names.

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

• To: your own address only
• Subject: Supplier digest: 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 supplier requests in total: followed by the Open Count field

Test the step and confirm the email arrives. Then publish the Zap.

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

Part 5: Work the Digest

Read the email and act on each line yourself. For FOLLOW UP NOW, write the follow-up from your own mailbox. If you resend, update Sent On and set a new Follow-up Due. For Answer to review, check the answer against your data checks and set Status to Reviewed when you are done. For Missing follow-up date, choose a date. For Not sent, send the request and change Status to Sent.

Real Example: Five Suppliers in October

Invented example. Today is Monday 2026-10-12.

The day differences. From 2026-09-21 to 2026-10-12 is 9 days to the end of September plus 12 in October, which is 21. From 2026-10-01 it is 11. From 2026-09-28 it is 2 plus 12, which is 14. From 2026-10-08 it is 4. From 2026-09-14 it is 16 plus 12, which is 28.

Why each row lands where it does. SUP-014 has Follow-up Due 2026-10-05, which is before today, so it shows FOLLOW UP NOW. SUP-022 is due on 2026-10-15, after today, so it waits. SUP-031 is Sent with no follow-up date, so the blank test fires before any date test. SUP-040 has an answer and its blank follow-up date does not matter, because Answer received is tested first. SUP-053 was never sent, so the line says "not sent yet". SUP-060 is Reviewed and shows Closed, even though its follow-up date passed two weeks ago.

Counts. FOLLOW UP NOW 1, Answer to review 1, Missing follow-up date 1 and Not sent 1 give an Action Count of 4. Add one Waiting row for an Open Count of 5. The Closed row and the blank rows are in neither.

Digest Lines, in sheet order:

SUP-014 | 2025 emissions data request | sent 21 days ago | FOLLOW UP NOW
SUP-031 | 2025 emissions data request | sent 14 days ago | Missing follow-up date
SUP-040 | 2025 supplier targets question | sent 4 days ago | Answer to review
SUP-053 | 2025 emissions data request | not sent yet | Not sent

The subject reads "Supplier digest: 4 need action" and the body ends with "Open supplier requests in total: 5". After you send the SUP-053 request, mark it Sent, set a follow-up date and it joins the Waiting group. The week every supplier is Reviewed, Declined or Not in scope, the email reads "No supplier follow-ups this week".

What to Do When It Breaks

• No email arrived on Monday. The Zap sends nothing when it breaks, so you have to look for the gap. Open Zapier and check that the Zap is on. Then open Zap History for Monday's run. "Safely halted" at step 2 means the lookup found no row, so check that the Key cell on the Summary tab still says summary exactly and that the worksheet is still Summary. An error at step 2 or 3 usually means the Google connection expired, so reconnect it. A recurring Monday calendar reminder asking "did the supplier digest arrive?" is a cheap backstop.
• A supplier you chased is still listed. You did not change Follow-up Due after the follow-up. A row stays FOLLOW UP NOW until the date moves into the future or the Status changes.
• A supplier is listed as "sent date missing". Fill in Sent On. The digest line and Days Since Sent both depend on it.
• #REF! errors. A tab was renamed. The formulas refer to Suppliers by name.
• Rows past row 500 are ignored. Extend every range together.
• A name or address appears in Zap History. Someone typed a contact name into Supplier or Request. Replace it with a code, and check how long Zapier keeps run data on your plan.

Variations

• Simpler version: Skip the Zap and read the Summary tab on Monday.
• Extended version: Add a Google Form that supplier contacts can use to confirm a submission. Form answers land on a separate responses tab, never directly in the Suppliers tab, and you update Status yourself after you look at the answer. Supplier answers must not flow into the digest columns.

What to Do Next

• This week: Enter your open supplier requests using codes and test each stage with one row, including a blank row.
• This month: Agree a follow-up gap with yourself, such as two weeks after sending, and set Follow-up Due the day you send.
• Advanced: Build the second Summary row so one workbook feeds both the data request digest and this one.

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 supplier tracker.