The Scenario
Someone on the risk team left three weeks ago. In their handoff notes was a half-finished Excel file, a link to the Ambee docs, and a sticky note that said "run this quarterly." The file is now a Google Sheet. Column A has 50 country codes. The quarterly risk index—the one that feeds the underwriting model—needs 12 months of natural disaster events per country before it can run. It's due Thursday.
The bad version:
- Dig through the Ambee disaster history endpoint docs, figure out the required date parameters, construct a query for the first country code, parse the JSON array of events, figure out which fields map to "event type," "date," and "location."
- Paste three values for the first event into columns B, C, D—then realize there were 14 events for that country and you only captured the first one, so the row needs to expand into 14 rows somehow.
- Spend twenty minutes deciding whether to use a flat table or a summary row, make a choice, realize halfway through country 4 it was the wrong choice, and start over.
Nobody assigned you this data pipeline job. It came with the handoff, and now it's blocking the model that three actuaries are waiting on.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent inside your Google Sheet. It reads your country code list, calls the Ambee historical disasters API for each country, and flattens the event records into the rows and columns you need—without you parsing a single JSON array.
Here is the prompt for this task:
For each country code in column A, fetch the historical disaster events from the past 12 months and list event type, date, and location in columns B–D
What You Get
- One row per disaster event, with the originating country code in column A
- Column B: Event type (earthquake, flood, hurricane, wildfire, etc.)
- Column C: Event date in ISO format
- Column D: Affected location or region name
- Countries with no recorded events in the period show a single row with "No events" in column B
What If the Data Is Not Quite Ready
The country codes in column A are full names rather than ISO codes
The values in column A are full country names (e.g., "United States," "Brazil"). Convert each to its ISO 3166-1 alpha-2 code, then fetch Ambee disaster history for the past 12 months and list event type, date, and location in columns B–D.
The team needs the output aggregated by country rather than one row per event
For each country code in column A, fetch all disaster events from the past 12 months, then write a summary row showing: country code, total event count, most common event type, and date of most recent event into columns B–E rather than expanding to individual event rows.
The dataset needs filtering to only include high-magnitude events
For each country code in column A, fetch historical disaster events from the past 12 months from Ambee, filter to only events where magnitude or severity is classified as "High" or above, and write event type, date, location, and magnitude into columns B–E. Flag countries with more than 3 qualifying events in column F.
Kill-chain: normalize codes, fetch events, build risk index, rank countries
Take the country identifiers in column A (mix of ISO codes and full names), normalize all to ISO alpha-2, fetch 12-month disaster history from Ambee for each, count total events per country, weight by event type (earthquake: 3pts, flood: 2pts, wildfire: 2pts, storm: 1pt), write the weighted risk score into column B, and sort the sheet by risk score descending.
One prompt, one run—cleanup and analysis in the same pass.
Try It
Open any Google Sheet with a country list and get the 7-day free trial of SheetXAI. Ask it to pull 12 months of Ambee disaster history for each country into adjacent columns, and the data your underwriting model needs lands before Thursday without a single API query on your end. See also: appending fire risk forecast scores or the full Ambee integration overview.