Automate a Weekly Supplier Scorecard Digest Draft with Zapier

What This Builds

Right now, the weekly supplier check-in either happens on the drive home or it doesn't happen until the quarterly QBR forces it. This build changes that pattern. Every Monday morning, a Zap pulls the prior week's receiving exceptions from the shared tracker your team already updates, drafts a short digest ranking suppliers by fill rate variance and quality misses, and drops that draft into your own Outlook or Gmail drafts folder. You open your inbox, skim a paragraph per supplier, edit whatever needs editing, and decide what happens next. The Zap never emails a supplier, never touches the ERP, and never sends anything on its own.

Prerequisites

• Comfortable prompting Claude or ChatGPT for drafting tasks (Level 3). See the Level 3 guide "Build a Claude Project for Supplier QBR and Scorecard Narratives"
• A Zapier account on a plan that supports multi-step Zaps (Professional, $29.99/month)
• The weekly ERP export (receiving exceptions, fill rate, quality holds) landing in a shared Excel Online or Google Sheets workbook your team already maintains
• An Outlook or Gmail account you control, since every draft lands there for your review and is never sent automatically
• A quick check with whoever owns your company's data policy that a weekly internal digest built this way is acceptable, since supplier names and performance figures will pass through a third-party automation platform

Total ongoing cost: $29.99/month for the Zapier plan. This build uses Zapier's own built-in AI drafting step, so no separate ChatGPT or Claude subscription is required to run it, though you likely already have one for the Level 3 work above.

The Concept

Picture a standing Monday-morning intern who already knows exactly what format you want, reads the same tracker every week, and never gets tired of writing the same kind of paragraph. That intern hands you a draft and waits by your desk. They never mail it, post it, or forward it anywhere themselves. That is what this Zap does: it watches a spreadsheet, turns rows into sentences, and stops at your drafts folder until a human decides the next step.

Build It Step by Step

Part 1: Get the week's data into Zapier

1. Trigger app: Schedule by Zapier. Trigger event: Every Week. Set it to fire Monday morning, whatever time you actually open email first.
2. Action app: Microsoft Excel (or Google Sheets). Action event: the action that returns many rows at once (Google Sheets calls it Get Many Spreadsheet Rows (Advanced)). Point it at a "This Week" tab that your Friday ERP refresh overwrites, so the Zap only ever sees the past seven days without needing a date filter.

What Zapier receives and keeps: the entire row set for that week comes into Zapier at this step, every column, not only the fields the digest ends up using. That full record lands in Zap History and stays there for as long as your plan's data retention window covers, whether or not a later step ever reads every field. Check the current window on your Zapier plan page, since Zapier has changed retention terms before. If the tab carries cost figures, open PO numbers or buyer names the digest never uses, move them to another tab so they never reach Zapier at all.

Part 2: Turn rows into one block of supplier lines

1. Action app: Formatter by Zapier. Action event: Utilities, then Line-item to Text. Map the supplier name, fill rate variance and quality miss count fields from Step 2 into it so each supplier becomes one line of a single text block.

Do not reach for Looping by Zapier here. Every step placed after a loop runs once per loop pass, and Zapier has no way to add a step that runs after the loop ends. A loop at this point would draft one digest per supplier instead of one digest for the week. The Line-item to Text step does the joining in a single pass.

Part 3: Draft the digest and route it for review

1. Action app: AI by Zapier. This is one AI call per weekly run. Prompt it with something close to this, adjusted to your own scorecard categories:

You are drafting an internal weekly supplier scorecard digest for a supply
chain manager. Using the supplier lines below, rank suppliers from worst to
best by fill rate variance. Write one short paragraph per supplier in plain
language, no more than two sentences each. Flag any supplier with a quality
miss count above two as needing follow-up. Do not invent numbers that are
not in the input.

Supplier lines:
{text block from step 3}

The supplier names and figures in that block reach AI by Zapier and the model provider behind it, so this is the step your data-policy check above has to cover. If policy says supplier names cannot leave approved tools, add a supplier code column to the tracker and map the code instead of the name.

1. Action app: Outlook (or Gmail). Action event: Create Draft, addressed to yourself. Subject: "Weekly Supplier Scorecard Digest, draft for review". Body: the AI output from Step 4.

Use Create Draft. Never use Send Email here. The whole point of this build is that a human reads the digest before it goes anywhere near a supplier or a QBR deck.

Part 4: Test and refine

1. Run the Zap once against last week's real tracker data using Zapier's test feature.
2. Read the draft. Spot check two or three supplier numbers in the digest against the source spreadsheet directly. If the fill rate variance for one supplier looks off, trace it back through the Line-item to Text step before trusting the rest.
3. Confirm exactly one draft landed in Drafts, not Sent.
4. Turn the Zap on and let it run live the following Monday.

Real Example: Meridian Manufacturing Co.

Setup: A supply chain manager at a mid-market fastener manufacturer with 24 active suppliers tracked in a shared Excel Online workbook, refreshed every Friday from the ERP.

Input: The Monday trigger pulls 24 rows covering the prior week's receiving exceptions and quality holds.

Output: A digest lands in Outlook drafts by 6:15 AM ranking three suppliers with fill rate variance worse than 10 percent at the top, each with a short note, followed by a clean bill of health for the rest in one combined line.

Time saved: 30 to 45 minutes a week that previously either got spent rebuilding the same view from scratch or, more often, skipped entirely until the numbers surfaced at the quarterly QBR.

What to Do When It Breaks

• The digest repeats last week's numbers, but the Zap reports success → Nobody ran the Friday refresh, so the This Week tab still holds the previous week. This is a silent failure: the Zap runs cleanly and produces a plausible draft. Add an "As of" date cell to the tab, map it into the draft subject line, and check it before you read the rest.
• Some suppliers are missing from the digest → The rows action returns a limited number of rows per call, and Zapier sets that limit per app. If your supplier count grows past it, split the tab or raise the row count in the action settings.
• No draft ever appears, and no error email arrives either → Your Outlook or Google connection likely lost its authorization silently between runs. Zapier normally emails you when it disables a Zap after repeated errors, but that email is easy to miss in a full inbox. Put a recurring personal reminder on the Tuesday after go-live to confirm last Monday's draft actually showed up.
• The AI paragraph reads generic or repeats the same phrasing for every supplier → Add one real example paragraph to the prompt as a style reference, and tell it explicitly to vary sentence structure supplier to supplier.

Variations

• Simpler version: If you track fewer than ten suppliers, skip the Formatter step and map the three columns from Step 2 straight into the AI prompt.
• Extended version: Add a Filter by Zapier step before the draft is created so only suppliers below a fill rate threshold generate a follow-up task in your project tool, alongside the full digest.

What to Do Next

• This week: Watch the first two live runs closely and tune the prompt's tone until it reads the way you would actually write it.
• This month: Feed a month of digests into your next QBR prep as ready-made talking points instead of rebuilding commentary from scratch.
• Advanced: Pair this with the disruption alert automation below so both scorecard and disruption drafts land in the same review routine each week.

Advanced guide for Supply Chain Manager professionals. These techniques use more sophisticated AI features that may require paid subscriptions.