The Scenario
A market analyst at a B2B data company has been asked to build a geographic breakdown dashboard for a client. The underlying data is already in a Google Sheet: 80 ISO-2 country codes in column A, representing the client's active customer base across regions.
The dashboard template needs official country names, currency codes, continent labels, capital cities, and international dialing codes — five additional columns, one per field. Without them, the chart formulas have nothing to reference and the client presentation next week shows placeholder text.
Nobody on the analyst team has worked with BigDataCloud's country info API before. The documentation exists. So does the afternoon.
The bad version:
- Open BigDataCloud's API reference, find the country metadata endpoint, authenticate, run a test call for one country code.
- Write a loop across all 80 codes, parse the JSON response for each, extract the five fields, and paste them into the sheet in the right column order.
- Realize the API returns the currency as a nested object with both code and symbol, and the template only wants the code — go back through the output and strip the symbol field.
The dashboard is due Friday. There are 80 rows and five fields each. That's 400 cells of API output to place correctly.
The Easy Way: One Prompt in SheetXAI
SheetXAI is an AI agent that lives inside your Google Sheet and calls BigDataCloud's country info endpoint on your behalf. You describe what you need — it handles the API calls, the field extraction, and the column layout.
Open SheetXAI from the Extensions menu and type:
For each ISO country code in column A, fetch country name, currency code, continent, capital, and dialing code from BigDataCloud and write to columns B through F
What You Get
- Column B: official country name (e.g., "Germany", "Brazil", "Japan")
- Column C: ISO 4217 currency code (e.g., "EUR", "BRL", "JPY")
- Column D: continent label (e.g., "Europe", "South America", "Asia")
- Column E: capital city
- Column F: international dialing code (e.g., "+49", "+55", "+81")
- Rows where BigDataCloud returns no match for the country code get a blank row; SheetXAI notes any unresolved codes in the sidebar
What If the Data Is Not Quite Ready
Some entries in column A are ISO-3 codes instead of ISO-2
Enrich the country codes in column A using BigDataCloud — some entries are ISO-3 format (e.g., DEU, BRA) instead of ISO-2 (DE, BR), handle both formats and write country name, currency code, continent, capital, and dialing code into columns B through F
The sheet uses full country names instead of codes, and you need the metadata to match
Column A contains full country names, not ISO codes. For each country name, look up the ISO-2 code via BigDataCloud, then fetch country name, currency code, continent, capital, and dialing code, and write all six columns B through G (including the ISO-2 code you resolved)
The output needs to land on a separate reference sheet, not inline
Fetch country metadata from BigDataCloud for each ISO-2 code in column A on the "Raw Data" sheet — write a clean reference table (code, country name, currency code, continent, capital, dialing code) to a new sheet called "Country Reference" sorted alphabetically by country name
Full dashboard-ready enrichment in one pass
For each country code in column A, fetch all BigDataCloud country metadata (name, currency, continent, capital, dialing code) into columns B through F — then on a "Dashboard" sheet write a pivot counting customers per continent, sorted by count descending, and flag any continent with fewer than 3 countries represented
One prompt handles the enrichment, the summary, and the flagging.
Try It
Get the 7-day free trial of SheetXAI and open any Google Sheet with a column of ISO country codes — paste the prompt above and have your full geographic reference table ready before the dashboard deadline. See the IP geolocation spoke if you also need to derive countries from IP addresses rather than codes.