Nightly Census tract geocoder for HMDA and CRA compliance

Build a code workflow that batch-geocodes loan or service addresses to Census tracts for HMDA, CRA, and fair-lending compliance reporting. Every step is deterministic. No LLM reasoning is needed in the run.

Trigger: cron, every weekday (Mon-Fri) at 02:00 in the configured timezone.

Inputs configured on the workflow: the Google spreadsheet id, the name of the compliance tab (e.g. "Pending"), the name of the no-match tab (e.g. "Needs Review"), and the column layout. Assume the compliance tab has a header row with at least: record_id, street, city, state, zip, tract, county_fips, state_fips, block, match_status.

Step 1 - Read pending rows. Call the Google Sheets "Get Values" operation against the compliance tab. Parse the header row, then keep only rows where the tract column is empty AND record_id, street, city, state, and zip are all present. These are the rows we need to geocode.

Step 2 - Chunk and submit to Census. Split the pending rows into chunks of up to 10,000 rows each (the Census batch cap). For each chunk, build the CSV body the Census expects (Unique ID, Street address, City, State, ZIP) and call the US Census Bureau "Batch Geocode Addresses" operation with geographies enabled, benchmark Public_AR_Current and the current vintage. The response is CSV with one row per input id.

Step 3 - Parse the CSV. For each returned row capture: unique id, match status (Match / Tie / No_Match), matched address, latitude/longitude, state FIPS, county FIPS, tract, and block. Build two lists: matched rows (status = Match) keyed by record_id with their tract/county/state/block, and unmatched rows (status = Tie or No_Match) with their original address fields and the returned status.

Step 4 - Write matches back to the sheet. Use the Google Sheets "Batch Update Values" operation to write tract, county_fips, state_fips, block, and match_status into each matched row on the compliance tab. Compute the exact A1 ranges from the record_id row indexes returned in step 1 so we only touch the cells that changed.

Step 5 - Append no-matches for review. For each Tie or No_Match row, append a row to the Needs Review tab via the Google Sheets "Append Values" operation, including the record_id, the original street/city/state/zip, the returned match_status, and a timestamp. These rows should NOT be marked as geocoded on the compliance tab so a human can fix the address and the next run will retry them.

Output of the run: a small JSON summary with totals (pending found, matched, tied, no-match) and the timestamp. Surface that in the run log so compliance can see at a glance what happened overnight.

Constraints to respect. The Census batch endpoint caps at 10,000 addresses per request, so chunk correctly. The Census batch endpoint returns CSV, not JSON, so parse defensively (quoted fields, empty cells). Reruns must be idempotent: only pick up rows where tract is still blank. Never overwrite a tract that already has a value. Treat Tie the same as No_Match for routing, but preserve the status so reviewers know which is which.