Functions/formula are the most friendly way to quickly calculate something in a spreadsheet. There are a lot of in-built functions available in Google Spreadsheet, check here the list of all functions available. Google spreadsheet has also an option called custom function. When a calculation is repeatedly required at multiple places we can create a custom function for it. Just like an in-built function a custom function can be directly invoked from a cell and can receive a parameter. The returned value from the function is displayed in the cell.

Copy the App

Geo data custom functions

We have created a list of custom functions to easily drag some frequently used geo data such as capital from country name, address from zip etc. Here are the list of functions you have:

1. CAPITAL_FROM_COUNTRY_CODE
   This will return the name of the capital if country code is provided.
2. PHONE_FROM_COUNTRY_CODE
   This will return phone code if country code is provided.
3. CURRENCY_FROM_COUNTRY_CODE
   Returns currency code from country code.
4. CAPITAL_FROM_COUNTRY_NAME
   Returns capital from country name
5. PHONE_FROM_COUNTRY_NAME
   This will return phone code from country name
6. CURRENCY_FROM_COUNTRY_NAME
   This will return currency code from country name
7. PLACE_FROM_ZIP_CODE
   Returns place from zip code. It may return multiple places so you can append country name with the zip code to get a place in a particular country.
8. COORDINATE_FROM_PLACE
   This will return the coordinate (latitude and longitude), if the place name is provided.

How to setup

• Make a copy of this spreadsheet
• Go to tools → script editor, you can see all the custom functions
• For testing, you can play with the spreadsheet you just created. To use in other spreadsheets, you can copy paste the code in script editor.
• The first line contains the API_KEY. This is the api key to get geocoding data which uses Google API. Get an api key from here https://developers.google.com/maps/documentation/geocoding/start
• If you are using it for the first time, you need to authorise the script. Just select the test() function in the script editor and click run.

The last two functions PLACE_FROM_ZIP_CODE, COORDINATE_FROM_PLACE uses zip and place as parameters respectively. You can always provide zip code in addition to country name or place to get more specific results. You can also pass a cell as a parameter like usual spreadsheet functions. Example: =CURRENCY_FROM_COUNTRY_CODE(A9)