Skip to content
P
AI for Facilities Manager

Automation: Build a Compliance Deadline Tracking System

For Facilities Managers ·

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 ($29.99/month/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) + $29.99/month/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

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

  2. 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, and set it 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]
  1. 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 and 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.