P
AI for Facilities Manager

Automation: Build a Compliance Deadline Tracking System

Zapier

For Facilities Managers

Tools: Zapier + Google Sheets + Email | Time to build: 1.5–2 hours | Difficulty: Intermediate-Advanced Prerequisites: Comfortable using Google Sheets; see Level 3 guide: "Research Building Compliance Requirements with Perplexity"

What This Builds

An automated compliance tracking system that monitors your building's inspection and certification deadlines in a Google Sheet and sends you email reminders at 90, 30, and 7 days before each deadline — so you never miss a compliance filing or certification renewal again. Set it up once; it watches your compliance calendar forever.

Prerequisites

  • Google account (free — for Google Sheets)
  • Zapier account at zapier.com — Zapier Starter plan ({{tool:Zapier.price}}/month) needed for multi-step Zaps
  • Your compliance calendar (use Perplexity to build this if you don't have one — see Level 3 guide)
  • Email address where you want reminders delivered
  • Time to build: 1.5–2 hours
  • Cost: Google Sheets (free) + {{tool:Zapier.price}}/month

The Concept

You keep your compliance calendar in a Google Sheet with deadline dates. Zapier reads that sheet automatically and sends email alerts when deadlines are approaching. Instead of manually checking a spreadsheet and hoping you notice an upcoming deadline, the system comes to you. Think of it like a bill payment reminder service — but for your building's regulatory obligations.

Build It Step by Step

Part 1: Build Your Compliance Tracking Spreadsheet

  1. Open Google Sheets (sheets.google.com) and create a new spreadsheet named "Building Compliance Tracker"

  2. Create these columns:

    • A: Requirement — description of the inspection/certification
    • B: System/Equipment — what it applies to
    • C: Frequency — annual, semi-annual, quarterly, etc.
    • D: Due Date — the next upcoming deadline (YYYY-MM-DD format is important for Zapier)
    • E: Agency/Inspector — who performs/files with
    • F: Contact — vendor or agency contact info
    • G: Last Completed — date last done
    • H: Status — Active / Complete / Scheduled
    • I: Notes — any special requirements or file locations

  3. Populate with your compliance requirements (use the Perplexity research guide to find them)

  4. Example rows:

    • Annual fire extinguisher inspection | Fire suppression | Annual | 2026-10-15 | City Fire Marshal | ABC Fire Protection | 2025-10-15 | Active
    • Elevator annual inspection | Elevators | Annual | 2026-08-01 | State Elevator Division | Otis Service | 2025-08-10 | Active
    • Emergency generator load test | Generator | Semi-annual | 2026-06-01 | Internal | FM team | 2025-12-01 | Active

Part 2: Create a Date Calculation Column

Add these calculated columns to show how many days until each deadline:

  • Column J header: Days Until Due
  • In cell J2: =D2-TODAY()
  • This calculates the number of days until the deadline

Add conditional formatting: select column J, Format → Conditional formatting:

  • 0–7 days: red background
  • 8–30 days: orange background
  • 31–90 days: yellow background

Part 3: Create Your First Zapier Automation

  1. Go to zapier.com, sign in, click Create Zap
  2. Trigger: Search for "Schedule by Zapier" — choose trigger: Every Day — set to run at 7am in your timezone
  3. Click Continue

Part 4: Add the Google Sheets Lookup

  1. Action 1: Search for "Google Sheets" and select it
  2. Action event: Lookup Spreadsheet Row
  3. Connect your Google account when prompted
  4. Select your spreadsheet ("Building Compliance Tracker") and sheet
  5. Lookup column: Column J (Days Until Due)
  6. Lookup value: 90 (this Zap catches 90-day warnings)

This step searches your sheet for rows where 90 days remain.

Note: The basic Zapier lookup finds one row at a time. For multiple deadlines, set up separate Zaps for the 90-day, 30-day, and 7-day thresholds, and consider running a search rather than an exact lookup — Zapier's "Find Many" option in Google Sheets works better for this use case.

Part 5: Add the Email Notification

  1. Action 2: Search for "Gmail" (or Email by Zapier if you don't use Gmail)

  2. Action event: Send Email

  3. Configure:

    • To: Your email
    • Subject: ⚠️ Compliance Deadline in 90 Days: {{Requirement}}
    • Body:
    Copy and paste this
    Building Compliance Reminder — 90 Days Notice

Requirement: {{Requirement}}
System: {{System/Equipment}}
Due Date: {{Due Date}}
Agency/Inspector: {{Agency/Inspector}}
Contact: {{Contact}}

Action needed: Schedule this inspection/filing in the next 30 days to avoid rushing.

Update the spreadsheet when complete: [link to your Google Sheet]

  4. Test and Activate the Zap

Part 6: Duplicate for 30-Day and 7-Day Alerts

Copy the Zap (Zapier allows duplication) and change the lookup value to 30 for a 30-day warning and 7 for a final reminder. Adjust the email subject line and body urgency accordingly.

What you should see: Daily automated emails arriving whenever a compliance deadline hits 90, 30, or 7 days out — with all the information you need to take action

Real Example: The System Working

Setup: 18 compliance items in spreadsheet covering fire, elevators, generator, HVAC, environmental

October 10: Email arrives — "90 days until annual fire extinguisher inspection (due January 8)." You email ABC Fire Protection to schedule the service. 2 minutes of work.

December 10: Email arrives — "30 days until fire extinguisher inspection." You confirm the scheduled date with ABC Fire Protection and make sure the building is accessible.

January 2: Email arrives — "7 days until fire extinguisher inspection." You verify the date is on the calendar and access has been arranged.

January 8: Inspection done. You update the spreadsheet "Last Completed" column to January 8 and "Due Date" to January 8, 2027. Automated reminders shift to the new date automatically.

Result: Zero missed compliance deadlines. Zero surprise phone calls from inspectors wondering where you are.

What to Do When It Breaks

  • No emails arriving: Check the Zap is turned on (green toggle in Zapier dashboard). Test the trigger manually using Zapier's "Run test" button.
  • Wrong dates in spreadsheet: Make sure dates are formatted as YYYY-MM-DD — Google Sheets and Zapier sometimes misinterpret other date formats
  • Multiple deadlines missed at once: The lookup Zap finds exact matches; if two deadlines hit 90 days on the same day, you may only get one email. Upgrade to a Zapier Multi-Step Zap or use a "Find Many" approach to catch all rows matching the criteria
  • Zapier task limit exceeded: Reduce Zap frequency to weekly instead of daily if you have many compliance items

Variations

  • Simpler version: Just use the Google Sheet with conditional formatting as a visual dashboard — check it weekly. No automation, but the color-coding makes deadlines visible.
  • Extended version: Add a Zapier step that posts to a shared Slack channel instead of (or in addition to) email — useful when you manage a team and want everyone to see upcoming compliance deadlines

What to Do Next

  • This week: Build the spreadsheet and populate all compliance requirements (use Perplexity for research)
  • Next week: Set up the Zapier automations for 90 and 30-day alerts
  • This month: Test the system by manually setting a test deadline 3 days out and confirming the email arrives

Advanced guide for facilities manager professionals. Compliance requirements are legally mandated — this system supplements but does not replace your professional judgment and regulatory knowledge. Always verify deadlines with the official AHJ.