Track mechanics lien deadlines per job with Google Sheets and Gmail

Quick overview

This workflow uses n8n Forms, Google Sheets, and Gmail to track mechanics lien notice and filing deadlines per job, calculate due dates from a configurable Rules sheet, keep a Deadlines sheet updated, and email you new-job deadline lists, update confirmations, daily alerts, and a weekly summary.

How it works

1. Triggers when you submit the “Add a job” form, submit the “Update a job” form, the daily schedule runs each morning, or you run the one-time manual setup.
2. Loads your configuration (email, spreadsheet ID, warning-day offsets, timezone, and n8n base URL) and reads the Jobs, Rules, Work log, and Deadlines tabs from Google Sheets when needed.
3. When you add a job, it generates a unique job ID, saves the job and an initial work-log entry to Google Sheets, calculates all applicable deadlines from the Rules tab, writes them to the Deadlines tab, and emails you the job ID plus a deadline table.
4. When you update a job, it finds the matching job in Google Sheets, logs work/completion/recorded notices/lien/payment or marks a step done, recalculates deadlines while preserving “done/skip” statuses, updates Google Sheets, and emails you what remains open.
5. Each morning, it recalculates deadlines for every open job, updates the Deadlines tab, and compiles alerts for deadlines that match your warning-day offsets, are due today, or appear missed.
6. Sends the compiled alert email(s) via Gmail and, on your configured weekday, also emails a 60-day summary plus items that are waiting on missing job dates.

Setup

1. Connect Google Sheets credentials (OAuth2) and Gmail credentials for sending emails.
2. Run the “Create my spreadsheet” manual trigger once to create the Jobs, Work log, Deadlines, and Rules tabs, then copy the returned spreadsheet ID into the workflow settings.
3. Fill in “Your settings” with your owner_email (alert recipient), spreadsheet_id, timezone, and optional warn_days/summary_weekday, and set n8n_url so email links point to your hosted forms.
4. Review and customize the Rules tab in Google Sheets to match your states, roles, and project types, then delete the example/header rows if you don’t want them.
5. Activate the workflow and use the generated n8n Form URLs (“/form/add-lien-job” and “/form/update-lien-job”) to add and update jobs.