See llms.txt for all machine-readable content.

Back to Templates

Track USCIS case statuses and RFE deadlines with Google Sheets and email

Created by

Created by: Dele Tosh @ Protomated || deletosh
Dele Tosh @ Protomated

Last update

Last update 3 days ago

Categories

Quick Overview

This workflow runs daily to read an immigration case tracker in Google Sheets, poll USCIS case-status pages for active receipt numbers, email clients when statuses change (after a compliance sub-workflow check), alert staff about approaching RFE deadlines, and write the latest results back to the sheet.

How it works

  1. Runs every morning at 6am on a schedule.
  2. Reads all case rows from a Google Sheets “Case Tracker” sheet and branches into status polling and RFE-deadline monitoring.
  3. Filters to active (not closed) cases, posts each receipt number to the USCIS case status endpoint, and parses the returned HTML to extract the current status text.
  4. Compares the newly polled status to the last saved status, detects RFE issuance/resolution, and calculates RFE deadline dates and last-checked timestamps.
  5. When a status change is detected, prepares a client email, runs it through an Execute Workflow compliance/opt-out guardrail, and sends the email via SMTP only if approved.
  6. Identifies any unresolved RFEs with deadlines within the configured lead time and emails an internal alert to the firm attorney address via SMTP.
  7. Appends or updates the matching Google Sheets row (by receipt number) with the latest status, RFE fields, and check timestamps regardless of whether an email was sent.

Setup

  1. Add Google Sheets credentials and set the IMMIGRATION_CASE_TRACKER_SHEET_ID variable to the spreadsheet that contains a “Case Tracker” sheet.
  2. Configure an SMTP email credential and set FIRM_FROM_EMAIL and FIRM_ATTORNEY_EMAIL variables for client updates and internal RFE alerts.
  3. Provide a compliance/guardrail workflow and set GUARDRAIL_WORKFLOW_ID so status-update emails can be approved or blocked (for opt-outs).
  4. Set USCIS_CASE_STATUS_ENDPOINT (optional) and USCIS_STATUS_REGEX (optional) to match the live USCIS HTML response format.
  5. Review and set RFE_RESPONSE_WINDOW_DAYS and RFE_ALERT_LEAD_DAYS variables to match how you want RFE deadlines calculated and when alerts should trigger.