Send dormant matter and renewal reminders with Clio, Google Sheets, and email

Quick Overview This workflow runs two daily schedules to flag dormant matters from Clio and upcoming/overdue renewal dates from Google Sheets, logs each notification via a separate audit workflow, and sends internal reminder emails to the responsible attorneys. How it works 1. Runs every morning at 8am to fetch all Open matters from the Clio API. 2. Checks each matter’s last updated date against a configurable dormancy threshold and deduplicates repeat alerts using workflow static data. 3. Builds a dormant-matter notification email, logs the message details by calling a separate compliance/audit workflow, and emails the responsible attorney. 4. Runs every morning at 8am to read renewal-date rows from a Google Sheets “Renewal Dates” ledger. 5. Calculates days remaining to each renewal date, keeps only items inside the reminder window (or overdue), and deduplicates non-overdue reminders using workflow static data. 6. Builds a renewal reminder email, logs it via the same compliance/audit workflow, and emails the assigned attorney (or a firm fallback address). Setup 1. Add Clio OAuth2 credentials, set the CLIOBASEURL variable, and ensure the Clio token has access to read matters. 2. Add Google Sheets credentials, set RENEWALDATESSHEETID, and ensure the “Renewal Dates” sheet exists with columns like matterref, clientname, description, renewaldate, and attorneyemail. 3. Configure email sending (SMTP/Email credentials) and set FIRMFROMEMAIL (and optionally FIRMEMAIL) for the sender and fallback recipient. 4. Set GUARDRAILWORKFLOWID to an existing audit/logging workflow that accepts the provided inputs (channel, message, matterid, recipient, templateid). 5. Optionally set DORMANTTHRESHOLDDAYS and RENEWALREMINDERDAYS to match your firm’s dormancy and reminder policies.