Step 3 of 10

Prepare the sheet for automation state

Add operational columns to the sheet so the workflow can track the status of each lead and you can debug problems without re-running the entire Zap.

Why this matters

Without status tracking, you have no way to know which leads were processed, which failed, or which were skipped. Adding these columns turns a passive log into an operational control panel. The Reply Status column in particular prevents the Zap from sending duplicate emails if it re-triggers on an already-processed row.

Build instructions

Add the five operational columns

Click on column F header (the column right after 'Message') to start adding columns to the right of your form data.

Step 1
Click cell F1. Type 'Lead ID' and press Tab. Type 'Reply Status' in G1. Type 'Reply Timestamp' in H1. Type 'Last Error' in I1. Type 'Ops Notes' in J1. Press Enter.
Step 2
Select the entire header row (click the row 1 number on the left). Go to Format → Text wrapping → Wrap. This makes long header labels readable.
Step 3
Select F1:J1 and apply a background color to distinguish operational columns from form data columns. Go to Format → Fill color and choose a light yellow or blue.

Create the Lead ID formula

Lead ID gives each row a unique identifier you can reference in logs and error messages.

Step 1
Click cell F2. Type this formula exactly: =IF(A2<>"",TEXT(A2,"YYYYMMDD")+"-"+ROW(),""). This creates an ID like '20240415-2' from the timestamp in column A.
Step 2
Alternatively, use a simpler formula: =IF(A2<>"","LEAD-"&TEXT(ROW()-1,"000"),""). This gives IDs like 'LEAD-001', 'LEAD-002'. Choose whichever format your team prefers.
Step 3
After entering the formula in F2, click F2 again. Copy the cell (Ctrl+C). Then select F3:F1000 and paste (Ctrl+V). This pre-fills the formula for the next 998 rows so new form submissions automatically get an ID.

Document the Reply Status values

Consistent status values are critical. If you use 'Sent' in one Zap step and 'sent' in another filter, the filter fails.

Step 1
Create a new tab in the sheet. Click the '+' icon at the bottom left of the sheet. Double-click the new tab and rename it to 'Config'.
Step 2
In the Config tab, create a table starting at A1. Column A header: 'Status Value'. Column B header: 'Meaning'. Add these rows: queued | Lead received, not yet processed. sent | Email successfully delivered. failed | Processing error, see Last Error column. needs-review | Invalid data or safety flag, requires manual action.
Step 3
Go back to the main sheet. Click cell G1 (the Reply Status header). Go to Insert → Note and type: 'Allowed values: queued, sent, failed, needs-review. See Config tab for definitions.'

Set up the Prompt Config tab

This tab stores reusable variables that your Zapier AI step will read from. Centralizing them here means you can update your prompt without editing the Zap.

Step 1
Click the Config tab you just created. Add a second table starting at D1. Header D1: 'Variable'. Header E1: 'Value'.
Step 2
Add these rows to the Prompt Config section: company_name | [Your company name]. response_tone | professional and concise. max_reply_length | 150 words. forbidden_claims | pricing, timelines, guarantees.
Step 3
These values will be referenced by name when you build the AI prompt step in Step 6.

Common mistakes

Not pre-filling the Lead ID formula down the column. If you only put the formula in F2, new rows from the form won't automatically get an ID and your logs will have gaps.
Using different capitalizations for status values (e.g., 'Sent' vs 'sent'). Zapier filters are case-sensitive. Pick one casing convention and stick to it throughout the entire project.
Putting operational columns between form data columns. Always add them to the right. Inserting columns between form fields shifts all existing column references and can break the Sheets-to-Zapier mapping.

Pro tips

Add data validation to the Reply Status column (Data → Data validation → Dropdown from list) with the four allowed values. This prevents typos when you manually update rows during debugging.
Share the spreadsheet with yourself on a secondary email if you have one. This simulates what Zapier will see when connecting to the sheet.

Before you continue

Look at your sheet. Row 1 should have ten column headers: Timestamp, Full Name, Email Address, Inquiry Type, Message, Lead ID, Reply Status, Reply Timestamp, Last Error, Ops Notes. The Lead ID formula should populate automatically for any existing data rows.

Step result

The sheet now functions as an operational database, each row tracks its own processing state, making the workflow auditable, debuggable, and safe to re-run.