Automation: A Monday Procurement Digest That Lists Every Order Needing a Nudge

What This Builds

Every Monday morning an email lands in your own inbox listing the orders that need you: the ones past their ship date, the ones shipping in the next 7 days, the ones with no date at all, the ones waiting on a deposit and the ones waiting on a client. Nothing else is listed. When a sofa arrives you set its status to Received and it drops off the list by itself.

The sheet does all the sorting. Zapier only carries one finished piece of text from the sheet to your inbox, so no order data goes to an AI vendor and nothing is sent to a client, vendor or contractor.

Prerequisites

• A Google account with Sheets and Gmail. A free personal account works. If the studio uses Google Workspace, check with whoever manages it that connecting Zapier is allowed.
• A Zapier account on a plan that allows multi-step Zaps. This build has a trigger and two actions, and Zapier's pricing page lists the free plan as two-step only. The entry-level paid plan in our registry is Professional ($29.99/month), so confirm on Zapier's pricing page that your plan lists multi-step Zaps.
• Total ongoing cost: the Zapier plan above, plus nothing for Sheets and Gmail on a personal Google account. There is no AI subscription in this build.
• About an hour of quiet time and the open orders from your current projects, so you can test with real rows.

The Concept

Think of the sheet as a clerk who reads every order each morning and writes one note: "Here is what needs you." Zapier is the courier who picks up that one note every Monday and drops it in your inbox. The courier never reads the orders, never sorts them and never decides anything.

That split matters for one reason. If a Zap searches for rows that match a condition ("status is not Received") and finds none, Zapier stops the Zap there, and Zap History shows it as "Safely halted". No later step runs, so no email arrives. A quiet week and a broken Zap would look the same. To avoid that, the sheet keeps a Summary tab with exactly one row that always exists. The Zap looks up that row by its Key, so it always finds something and the email goes out every week, even when the note says "Nothing needs action this week".

What Zapier sees. Zapier receives the Summary row: the Key, the two counts and the Digest Lines text. Digest Lines holds a project code, a short item label, a vendor name, a status and a ship date for each flagged order. Zap History stores the data each step received and sent, and Gmail keeps the email. Check Zapier's data retention settings for how long, and decide whether the studio is comfortable with Zapier holding that much.

What stays out of the sheet's Orders tab. Use a project code such as MAPLE, never a client name or street address. Use short item labels. Keep trade pricing, net cost and markup off this tab entirely. Those figures are the studio's own and neither the digest nor Zapier needs them.

Build It Step by Step

Part 1: Lay out the two tabs

Create a Google Sheet with two tabs named exactly Orders and Summary. The formulas below refer to those names, so spelling and capitals matter.

Orders tab, header in row 1, one order per row from row 2 down to row 200:

Set the Status dropdown with Data, then Data validation, and add the seven statuses exactly as written. Set column E to accept dates only (also under data validation). A date typed as plain text would slip past the date tests below, so reject anything that is not a date.

Summary tab, header in row 1, one data row in row 2:

Never add a second data row to Summary and never rename the word in A2. The Zap finds this row by that word.

Part 2: The Stage formula

Paste this into Orders!F2 and fill it down to row 200:

=IF(B2="","",IF(OR(D2="Received",D2="Cancelled"),"Closed",IF(D2="Awaiting client approval","Waiting on client",IF(D2="Awaiting deposit","Awaiting deposit",IF(D2<>"Ordered","OK",IF(E2="","Missing date",IF(E2<TODAY(),"PAST SHIP DATE",IF(E2<=TODAY()+7,"Ships this week","OK"))))))))

Read it from the outside in. If Item is blank, the row is empty and the result is blank. If the order is finished (Received or Cancelled), the result is Closed. Waiting on a client approval or a deposit gives those two labels. Any other status that is not Ordered, meaning Quote requested or Shipped, gives OK, because there is nothing for the sheet to chase. Only Ordered rows get the date tests.

Walk it through one row per label. Suppose today is Monday 2026-10-05, so TODAY()+7 is 2026-10-12.

Why Closed comes first. A finished row nearly always still carries its old Expected Ship date. The Console Table above has a date three weeks in the past. If any date test ran before the Closed test, that received table would show PAST SHIP DATE every Monday for good. Testing Closed first takes a finished row out of the digest before its date is ever read. Keep that order if you edit the formula later.

A limit to know. A row with an Item but no Status also shows OK. Fill the dropdown for every row.

Part 3: The Digest Line formula

Paste into Orders!G2 and fill down to row 200:

=IF(B2="","",A2&" | "&B2&" | "&C2&" | "&D2&" | ship "&IF(E2="","no date",TEXT(E2,"yyyy-mm-dd"))&" | "&F2)

Each field is joined with a vertical bar and a space on either side. The date is wrapped in TEXT so it prints as 2026-09-28 and not as a serial number such as 46293. The blank-date guard prints "no date" instead of 1899-12-30, which is what an empty date cell turns into when it is forced into text. A blank Item gives a blank line.

For the Harbor Lounge Chair row, G shows:

MAPLE | Harbor Lounge Chair | Vendor A | Ordered | ship 2026-09-28 | PAST SHIP DATE

For the Wool Rug row: MAPLE | Wool Rug | Vendor C | Ordered | ship no date | Missing date

Part 4: The Summary formulas

All three ranges point at rows 2 to 200 on the Orders tab, so every range has the same height.

Summary!B2, Action Count:

=COUNTIF(Orders!F2:F200,"PAST SHIP DATE")+COUNTIF(Orders!F2:F200,"Ships this week")+COUNTIF(Orders!F2:F200,"Missing date")+COUNTIF(Orders!F2:F200,"Awaiting deposit")+COUNTIF(Orders!F2:F200,"Waiting on client")

Summary!C2, Open Count:

=B2+COUNTIF(Orders!F2:F200,"OK")

Open Count adds the OK rows to the action rows. It counts only labels the Stage formula writes for filled rows, so the blank formula rows below your last order are never counted, and Closed rows are not counted either.

Summary!D2, Digest Lines:

=IFERROR(TEXTJOIN(CHAR(10),TRUE,FILTER(Orders!G2:G200,(Orders!F2:F200="PAST SHIP DATE")+(Orders!F2:F200="Ships this week")+(Orders!F2:F200="Missing date")+(Orders!F2:F200="Awaiting deposit")+(Orders!F2:F200="Waiting on client"))),"Nothing needs action this week")

FILTER keeps only the Digest Line cells whose Stage is one of the five action labels. TEXTJOIN stitches them into one block with a line break (CHAR(10)) between them. When no row qualifies, FILTER returns an error, and IFERROR swaps it for the sentence "Nothing needs action this week". That is why the cell is never empty and the email always has a body.

Turn on Format, then Wrapping, then Wrap for cell D2 so you can read the lines in the sheet.

Part 5: Build the Zap

You build three steps in Zapier: one trigger and two actions.

1. Trigger. Choose Schedule by Zapier and the event Every Week. Pick Monday for Day of the Week and a Time of Day such as 7 AM. Zapier runs schedule triggers in the timezone set on your Zapier account, not the Zap, so check the account timezone. Zapier also says a schedule can run a few minutes after the time you set.
2. Action 1. Choose Google Sheets and the event Lookup Spreadsheet Row. Connect your Google account and then choose the spreadsheet and the Summary worksheet. For the lookup column pick Key and type summary as the lookup value. Leave any option that creates a row when nothing is found switched off. Test the step. You should see Action Count, Open Count and Digest Lines with the values from your sheet.
3. Action 2. Choose Gmail and the event Send Email. In To, type your own address and nobody else's. For the subject, type Orders digest: and insert the Action Count field from step 2, then type need action, and insert the Open Count field, then type open. In the body, insert only the Digest Lines field. Test the step and open the email it sends.

Look at the test email. Each order should sit on its own line. If the lines run together, look in the Gmail step for a body type setting and try the other option.

Turn the Zap on. Zapier names the fields as they appear in your sheet's header row, so if you later rename a header on Summary, reopen the lookup step and refresh its fields.

Tasks. Zapier counts only successful action steps as tasks, and triggers never count. Zapier's help page says the number of tasks a search action uses depends on its settings, so budget two tasks per run, the lookup and the email. A weekly Zap runs 4 or 5 times a month, which is 8 to 10 tasks. Check your plan's task limit on Zapier's pricing page.

Part 6: Keep it current

Two habits keep the digest honest. When an item arrives, set Status to Received and the row drops out by itself. When a vendor gives you a new date, overwrite Expected Ship with it. If your orders ever pass row 200, extend the formulas and the five ranges together.

Real Example: Monday 2026-10-05 across two projects

Setup: The Orders tab holds eight rows for the MAPLE and LAKE projects, all with invented vendors. Input: The Zap fires at 7 AM on Monday 2026-10-05, so TODAY()+7 is 2026-10-12.

Counts: Five rows carry an action label (PAST SHIP DATE, Ships this week, Missing date, Awaiting deposit, Waiting on client), so Action Count is 5. Two rows are OK (Sconce Pair and Mirror), so Open Count is 5 + 2 = 7. The Console Table is Closed and is in neither count.

Output: The subject reads "Orders digest: 5 need action, 7 open" and the body reads:

MAPLE | Harbor Lounge Chair | Vendor A | Ordered | ship 2026-09-28 | PAST SHIP DATE
MAPLE | Oak Side Table | Vendor B | Ordered | ship 2026-10-09 | Ships this week
MAPLE | Wool Rug | Vendor C | Ordered | ship no date | Missing date
MAPLE | Floor Lamp | Vendor A | Awaiting deposit | ship no date | Awaiting deposit
LAKE | Dining Chairs | Vendor B | Awaiting client approval | ship 2026-10-20 | Waiting on client

You read it over coffee, then write to Vendor A about the chair and Vendor C about the rug yourself. Nothing in the automation contacts them.

Time saved: The time you would spend scanning every tab of the tracker on Monday, plus the missed dates you would have found only when a delivery failed to show up. Your own Monday check will tell you the real figure.

What to Do When It Breaks

• No email arrives on Monday (silent failure). Do not assume a quiet week. The email is sent every week by design, so no email means something stopped. Wait until a little past the scheduled time and then open Zap History in Zapier. Check four things in order: the Zap is switched on, the Zap History shows a run for that day, the cell Summary!A2 still says summary with no extra space, and the Google connection has not expired (Zapier shows a reconnect prompt on the step). A "Safely halted" run means the lookup found no row, which almost always means the Key cell was changed.
• The email arrives but says "Nothing needs action this week" and you know better. Open the Orders tab. Check that the Status dropdown is filled for the rows and that Expected Ship holds real dates (they right-align, text left-aligns). Then check the Stage column for the row in question.
• Stage shows #NAME? or a parse error. A quote mark or bracket was lost in the paste. Compare to the formula above character by character, including that each opening bracket has a closing one.
• A row is missing from the digest. Its Item cell is blank, or it sits below row 200.
• A date prints as a five-digit number. The TEXT wrapper was dropped from the Digest Line formula.
• Runs stop mid-month. Zapier may have reached your plan's task limit and held new runs. Check the usage page in Zapier.

Variations

• Simpler version: Skip the Zap and read the Summary tab each Monday. You keep the sorting and lose the push.
• Extended version: Add a Google Form for new quote requests as the input side. Form answers land on their own responses tab, so copy each request onto the Orders tab when you send it to the vendor. The Zap stays on its Monday schedule.
• Companion build: The Level 4 guide on the client decision digest uses the same pattern for decide-by dates.

What to Do Next

• This week: Build the sheet, test the Zap on real rows and delete any column you added that holds pricing.
• This month: Watch over two Mondays whether the digest matches your own sense of what is late, and then adjust the 7-day window in the Stage formula if you want a longer look-ahead.
• Advanced: Ask Claude to explain any formula in plain words before you change it. Paste only the formula, never your order rows.

Advanced guide for interior designers. This build uses Zapier, which needs a paid plan for a three-step Zap. Interfaces change, so match the step names to what your screen shows.