Zapier Automation: Close Checklist Tracker and Reminder System

What This Builds

Instead of manually checking which close tasks are overdue at 5pm each day and sending follow-up emails by hand, you'll build a Zapier automation that monitors your close checklist in Google Sheets, identifies overdue items at a set time each day, drafts tailored reminder emails using AI, and sends them to the responsible person. Close week becomes less reactive: tasks get flagged automatically and people are reminded without you having to chase them manually.

Prerequisites

• {{tool:Zapier.plan}} account at {{tool:Zapier.price}} (the AI step requires a paid plan)
• A Google account (for Google Sheets, free)
• Gmail for sending (or adapt for Outlook using Microsoft's connector in Zapier)
• A close checklist you're willing to migrate to Google Sheets
• Time to build: 1–2 hours
• Cost: {{tool:Zapier.price}} + Google Sheets (free)

The Concept

Think of this as a close coordinator who runs at 5pm every day during close week. They look at the checklist, find every task that was supposed to be done today but isn't marked complete, draft a short reminder email for the person responsible, and send it. You wake up the next morning knowing reminders went out without you touching anything after the close kickoff.

Build It Step by Step

Part 1: Set Up Your Google Sheets Close Checklist

First, create the checklist in a format Zapier can read:

1. Open Google Sheets and create a new spreadsheet called "Month-End Close Checklist: [Month]"

2. Create these columns with exact header names:

   • Task (description of the close task)
   • Owner (first name or full name of the person responsible)
   • Owner Email (their exact email address)
   • Due Day (the close day number, e.g., "1" for day 1 of close, "3" for day 3)
   • Status (values: "Pending", "In Progress", "Complete", "Blocked")
   • Notes (optional: any context the owner should know)

3. Fill in your close tasks for the month. Example rows:

   • | Accrue unbilled AR | Sarah | sarah@company.com | 2 | Pending | Check with billing team |
   • | Complete bank recs all accounts | Mike | mike@company.com | 3 | Pending | |
   • | Post depreciation journal entry | Sarah | sarah@company.com | 3 | Pending | |
   • | Final review of all account recs | Controller | you@company.com | 5 | Pending | |

Part 2: Create the Zapier Account and Set the Trigger

1. Go to {{tool:Zapier.url}} and sign up or log in
2. Click Create Zap
3. For the Trigger, select Schedule by Zapier
4. Set: Every day at 5:00 PM on your close days (or every day during close week; you can filter by date in a later step)

What you should see: A schedule trigger that fires at 5pm daily.

Part 3: Look Up Overdue Tasks in Google Sheets

Add the first action step:

1. Click + to add an action
2. Select Google Sheets
3. Choose action: Lookup Spreadsheet Row (find matching rows)
4. Connect your Google account
5. Select your close checklist spreadsheet and the correct sheet tab
6. Set the lookup: Find rows where Due Day = [today's close day] AND Status does not equal "Complete"

Technical note: Zapier's native lookup finds one row at a time. For multiple rows, use Google Sheets: Get Many Spreadsheet Rows and filter by status. For simplicity starting out, you can also manually trigger the Zap at the start of close and process each row separately.

Alternative simpler approach: Use Formatter by Zapier with the "Utilities" action to compare today's date to your close day number, and only continue if it matches.

Part 4: Draft the Reminder Email with AI

After identifying overdue tasks:

1. Add an action: ChatGPT or AI by Zapier (Zapier's built-in AI)
2. Use this prompt template (with Zapier variable substitution):

Draft a professional but brief reminder email for a month-end close task that is overdue.

Task: [Task column from Google Sheets]
Responsible person: [Owner column]
This task was due today (Day [Due Day column] of close).
Any notes: [Notes column, if not empty]

The email should:
- Be 3-4 sentences
- Be firm but not aggressive
- Remind them the close deadline is Day [final close day — hardcode this]
- Ask them to update the status or flag any blockers
- Sign as "Controller"

Output only the email body — no subject line.

What you should see: A personalized, professional reminder email drafted for each overdue task.

Part 5: Send the Email

Add the final action:

1. Click + to add an action
2. Select Gmail (or Microsoft Outlook)
3. Choose action: Send Email
4. Map the fields:

   • To: [Owner Email from Google Sheets]
   • Subject: "Close Reminder: [Task from Google Sheets] - Due Today"
   • Body: [AI-generated email body from previous step]
   • From Name: Controller
5. Turn on the Zap and test it

What you should see: A test email sent to yourself simulating a real close reminder.

Part 6: Add a Daily Summary to Yourself (Optional)

Create a second Zap that runs at 5:01pm (after the reminders go out):

1. Trigger: Same 5pm schedule
2. Google Sheets: Get all rows for today's due date
3. Formatter: Compile the list of all incomplete tasks into a bulleted list
4. ChatGPT: "Summarize this list of overdue close tasks into a 5-bullet status update I can share with my CFO. Note which ones are blocked vs. just pending."
5. Gmail: Send the summary to yourself

Real Example: Day 3 Close Reminder

Checklist row: Task = "Complete bank reconciliations (all accounts)", Owner = "Mike", Due Day = 3, Status = "Pending"

Zapier fires at 5pm on Day 3 of close.

AI-generated email sent to mike@company.com:

Mike opens the email at 5:30pm and completes the recs. You never had to manually follow up.

What to Do When It Breaks

• Zapier can't read the Google Sheets data → Check that the column headers exactly match what's configured in the Zapier step. Any typo in a column name breaks the lookup.
• AI drafts a reminder that's too harsh or too casual → Edit the prompt in your AI step. Add "match the tone of a professional accounting environment" or "keep it warm but direct".
• Reminders fire on non-close days → Add a filter step after the trigger: "Only continue if [today's date] is between [close start date] and [close end date]" using Zapier's Filter app
• Multiple reminders fire for the same person → Zapier processes rows one at a time. Each overdue task generates a separate email. If this creates too many emails, consolidate to a daily digest format instead

Variations

• Simpler version: Skip the AI drafting. Send a simple template reminder with the task details filled in by Zapier. Less personalized but zero AI configuration needed.
• Extended version: Add a Slack notification step. After sending emails, post a summary in your accounting team Slack channel so everyone can see what's pending without checking their email.

What to Do Next

• This week: Build the Google Sheets checklist and manually run through the first close before turning on automation
• This month: Activate the automation for the next close cycle; track whether follow-up time decreases
• Advanced: Add a "Day 1 kickoff" Zap that sends personalized task assignments to each team member at the start of close, with their specific tasks for the week

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