Automation Recipe: Turn a Low-Stock Alert Into a Drafted Reorder Email

What This Builds

A background check that watches your inventory tracker every day and, the moment a SKU crosses your reorder line, drafts a ready-to-send reorder email to that supplier and drops it in your Gmail drafts folder. You stop being the one who has to remember to check stock levels before a bestseller runs out ahead of Black Friday or Prime Day.

Prerequisites

• A Google Sheet you already use (or are willing to build) to track stock by SKU
• Comfortable writing prompts in ChatGPT for supplier emails (Level 1)
• Professional plan ($29.99/month): multi-step Zaps with a filter step need a paid tier, the free tier only runs single-step Zaps
• An OpenAI API key, created at platform.openai.com, to connect the AI step inside Zapier. This is billed separately from a Plus subscription and is not included with one, so you need the key even if you already pay for ChatGPT. API usage is charged per request rather than monthly, and a daily check on a small catalog uses very little
• A Gmail or Google Workspace account for the drafted reorder emails
• Total ongoing cost for this build: $29.99/month for Zapier, plus whatever your OpenAI API usage comes to. Check your current rates on OpenAI's pricing page before you build, since per-request pricing changes

The Concept

Right now, catching a low-stock SKU depends on you remembering to look. This automation flips that. A spreadsheet becomes a tripwire: once a product's stock number drops below the line you set, a robot notices before you do, pulls together everything a supplier needs to hear, and writes the email for you. You still decide when to hit send. The automation just makes sure the email exists and is sitting there waiting instead of getting written three days after you notice the stockout.

Build It Step by Step

Part 1: Build a Reorder Tracker sheet

Create a new Google Sheet called "Reorder Tracker" with these columns:

• A: SKU
• B: Product Name
• C: Current Stock
• D: Reorder Threshold (the stock level that should trigger a reorder, given your supplier's lead time)
• E: Reorder Quantity (your standard reorder amount for that SKU)
• F: Supplier Name
• G: Supplier Email
• H: Lead Time (days)
• I: Needs Reorder (formula: =IF(C2<=D2,"YES","NO"))

Update column C whenever you check stock, either from your platform dashboard or from your fulfillment tool. If you already keep a spreadsheet for this, add column I and the formula to it instead of starting a new sheet.

Part 2: Set the Zapier trigger and filter

1. In Zapier, click Create Zap
2. Trigger app: Schedule by Zapier, event: Every Day, and pick a time you're usually near your inbox (for example 8:00am)
3. Add a step: Google Sheets, action: Lookup Spreadsheet Row(s), connect your account, select the Reorder Tracker sheet, and search column I for the value "YES"
4. Add a Filter by Zapier step right after: only continue if the Lookup step returned at least one row

What you should see: when you test this step with a dummy row where Current Stock is below the threshold, Zapier returns that row's data, including supplier name and email.

Part 3: Draft the reorder email with ChatGPT

1. Add a step: search for ChatGPT, action Conversation or Send Prompt (naming varies by Zapier version)
2. Connect your OpenAI account
3. Model: GPT-5.6 Sol
4. Prompt (paste this, keeping the bracketed fields mapped to your sheet columns):

You are drafting a reorder email on behalf of a small online store owner to a supplier they already work with.

Product: [Product Name from Column B]
SKU: [SKU from Column A]
Current stock: [Current Stock from Column C]
Standard reorder quantity: [Reorder Quantity from Column E]
Supplier's usual lead time: [Lead Time from Column H] days

Write a short, professional reorder email. Confirm the SKU and quantity clearly, ask for a ship date, and mention this is a standard reorder based on our usual quantity. Keep it under 120 words. Sign off as "Thanks" with no name, I'll add my own signature.

Part 4: Create the Gmail draft, not a sent email

1. Add a final step: Gmail, action: Create Draft (not "Send Email"). Keeping this as a draft means every reorder still gets a human look before it goes to a supplier.
2. To: the Supplier Email column from your sheet
3. Subject: "Reorder Request: [Product Name] - [SKU]"
4. Body: the ChatGPT output from Part 3
5. Test the full Zap with one dummy low-stock row, then check your Gmail Drafts folder for the result
6. Turn the Zap on

Real Example: Cedar & Sage Candle Co.

Setup: A solo Etsy and Shopify seller runs a 22-SKU candle line. The bestselling Lavender Cedar 8oz jar has a reorder threshold of 40 units and a standard reorder quantity of 200, with a 12-day lead time from her wax and vessel supplier.

Input: During a Tuesday stock count, she updates Current Stock to 35, which trips the "YES" flag in column I.

Output: By 8am Wednesday, a Gmail draft is waiting, addressed to her supplier's order desk, confirming SKU LC-8OZ, 200 units, referencing the usual lead time and asking for a ship date. She adds her name and hits send in under a minute.

Time saved: Manually tracking reorder timing across a spreadsheet of SKUs eats up to 1.5 hours a week (per this role's workflow analysis). This replaces the daily scanning part of that task with a five-minute review once a flagged row shows up.

What to Do When It Breaks

• The Zap silently stops running → Zapier automatically pauses a Zap after repeated errors (for example, if your Google account token expires or a column gets renamed) without a loud warning unless you've turned it on. Go to Zapier account settings and turn on "Notify me by email when this Zap has errors" the first time you build this, so a stalled reorder check doesn't go unnoticed for weeks.
• Draft email is missing details or sounds generic → the sheet row is probably missing data in one of the mapped columns. Fill in every column for a SKU before it's likely to trip the threshold.
• Zap fires every day even when nothing changed → that's expected. The Filter step in Part 2 should stop it from creating a new draft unless something is actually flagged "YES". If drafts are piling up for the same SKU, add a second filter that checks whether a draft was already created in the last 7 days.
• Supplier email bounces → double-check column G before turning the Zap on. A stale or misspelled supplier email is the most common reason a "successful" Zap doesn't actually reach anyone.

Variations

• Simpler version: Skip the AI drafting step entirely and just have Zapier send you (not the supplier) a plain alert email or Slack message when a SKU crosses the threshold. You still write the reorder email yourself, but you never miss the moment to do it.
• Extended version: Add a second Filter branch for "critical" stock (say, under 10 days of inventory left based on your average daily sales) that sends you a text message through Zapier's SMS integration in addition to the drafted email, for the SKUs where a delay actually risks a stockout before a peak-season window.

What to Do Next

• This week: Build the Reorder Tracker sheet for your 10-15 highest-volume SKUs first, not your whole catalog.
• This month: Run it for a full month and compare the reorder timing it catches against what you'd have caught on your own.
• Advanced: Once this is reliable, connect it to your Q4 inventory prep checklist so the threshold tightens automatically in the 8-10 weeks before your platform's peak-season receiving windows.

Advanced guide for e-commerce business owner professionals. Requires a paid Zapier plan and an OpenAI API key, which is billed separately from any ChatGPT subscription. Supplier contact details and your reorder quantities move through Zapier and OpenAI's API to build this automation; if any of that pricing or quantity data is under an NDA with your supplier, check your agreement before automating it.