Google Apps Script Programming tricks Tips

Custom Function to Get Place from Zip Code in Google Spreadsheet

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.

Custom Function

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:

    This will return the name of the capital if country code is provided.
    This will return phone code if country code is provided.
    Returns currency code from country code.
    Returns capital from country name
    This will return phone code from country name
    This will return currency code from country name
    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.
    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
  • 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)

Leave a Reply

Your email address will not be published. Required fields are marked *