# Nightly Census tract geocoder for HMDA and CRA compliance > Every weeknight, take pending loan or service addresses from a Google Sheet and fill in the Census tract, county, and state codes your compliance team needs. - Workflow type: code - Services: Google Sheets, US Census Bureau - Categories: Finance, Operations - Published: 2026-06-30 ## What it does - Runs every weekday at 2am and picks up any new loan or service addresses from your compliance tab in Google Sheets. - Sends them in big batches to the free official US Census geocoder and reads back the Census tract, block, county, and state codes. - Writes the matched codes straight back into the same row, with a status of Match, Tie, or No_Match so reviewers know what to look at. - Moves anything the geocoder cannot resolve into a separate Needs Review tab, so your team can clean up bad addresses without losing them. ## What you'll need - A Google Sheet with a compliance tab that has columns for record id, street, city, state, ZIP, plus blank columns for tract, county, state code, and status. - A Google account that can edit that sheet. - A free API key from the US Census Bureau (takes a couple of minutes by email). ## Prompt 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. ## How to customize - Change the schedule. 2am every weekday is the default, but you can run it hourly, only on Mondays, or right after your loan origination system finishes its nightly export. - Point it at a different sheet or tab name, or rename the columns to match the layout your compliance team already uses. - Adjust how no-matches are handled. Move them to a different tab, email them to a reviewer, or post a summary to Slack at the end of each run. ## FAQ ### Why use the US Census geocoder instead of Google Maps or a paid service? The Census Bureau geocoder is the free official source banks and credit unions reach for when they need tract-level results that line up with HMDA and CRA reporting. It is free, has no per-day cap once you have an API key, and returns the exact Census geography fields examiners expect. ### How many addresses can it handle in one run? The Census batch geocoder accepts up to 10,000 addresses per request. The workflow chunks your pending rows automatically, so a nightly run can comfortably handle tens of thousands of addresses. ### What happens to addresses the geocoder cannot match? Any row that comes back as No_Match or Tie gets copied to a separate Needs Review tab with the original address fields, so a human can correct typos or look up the tract manually without blocking the rest of the run. ### Does this overwrite rows that have already been geocoded? No. The workflow only picks up rows where the tract column is blank, so reruns are safe and you do not double-charge any downstream reporting. ### Can I run this on demand instead of overnight? Yes. The schedule is just a default. You can switch it to a webhook, a manual run button, or have it kick off the moment your loan system drops new rows into the sheet. Use this prompt in General Input: https://www.generalinput.com/prompts/nightly-census-tract-geocoder-for-hmda-and-cra-compliance