The Scenario
A stakeholder email arrived yesterday asking why field workers are selecting from a facility list in CommCare that hasn't been updated since last April. You're the program data manager. You maintain the master facility list in a Google Sheet — it has 47 entries now, six of which are new clinics added during the expansion. The product catalog has also changed. CommCare's lookup tables are still running on the old version of both.
This isn't an emergency. It's the fourth time it's happened because nobody built a repeatable process for syncing the reference data.
The bad version:
- Open CommCare HQ, navigate to the lookup tables section, find the health facility table, click into it, and start adding the six new entries one row at a time through the UI form
- Realize the product catalog table has a slightly different column structure than the one in your sheet, spend time reconciling before you can enter anything
- Finish updating the facility table, remember the product catalog, go back in, discover a thirteenth entry in your sheet that somehow didn't make it in because you lost count
Reference data drift is the kind of problem that's invisible until a field worker selects the wrong clinic and it ends up in a case record.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet. It reads the reference data you've been maintaining, talks to CommCare through its built-in integration, and creates or updates the lookup tables directly — no CommCare HQ navigation required.
The prompt for this task:
Create a CommCare lookup table for each unique table name in column A of this sheet using the field names in column B as the table structure. Use the domain listed in cell A1 of my 'Config' tab. Write the creation status and the CommCare lookup table ID into column C.
What You Get
- CommCare lookup tables are created in the specified domain, one per unique value in column A.
- Column B field names become the column structure of each table.
- Column C fills with the creation status and the CommCare-assigned table ID for each row.
- If a table with that name already exists in the domain, the status column indicates it was found rather than created, so you know what's net-new.
What If the Data Is Not Quite Ready
The column structure in your sheet doesn't match what CommCare expects for an existing table
Compare the field names in column B of my 'Facility Table' sheet against the current column structure of the CommCare lookup table named 'health_facilities' in the domain in cell A1. Write any mismatches into column D — fields in the sheet but not in CommCare in one section, fields in CommCare but not in the sheet in another.
You want to populate the lookup table with data rows, not just define its structure
Create a CommCare lookup table named 'health_facilities' in the domain in cell A1, using columns A through E of my 'Facility List' sheet as the row data. Use row 1 as the column headers. Write the row-level status into column F.
Some table names in your sheet have changed since the last sync and you need to detect renames
For each unique table name in column A of my 'Lookup Tables' sheet, check whether a CommCare lookup table with that exact name exists in the domain. Write "Exists" or "New" into column D. For tables marked "New", create them using the field names in column B.
Full kill chain: check what exists, reconcile mismatches, create new tables, populate data rows
For each unique table name in column A of 'Lookup Tables': check whether it exists in CommCare for the domain in 'Config' A1. For existing tables, compare the field structure and write mismatches to column D. For new tables, create them using column B field names and populate them with the corresponding data rows from the tab matching the table name. Write the final status into column E.
Keeping reference data in sync is a lot less painful when the sync is a single prompt.
Try It
Open your reference data sheet — facility list, product catalog, or any CommCare lookup table source — and Get the 7-day free trial of SheetXAI to push it into CommCare in one ask. See also: Submit Bulk Form Data to CommCare From a Google Sheet and the CommCare integration overview.