Zip Codes to City, Region & Geocode in Google Sheets

ZIP codes are a very useful and common way to gather location data. But zip codes alone are often less useful than the city, state, latitude and longitude that correspond to the zip code. City and state/region, allow you to group the data with a pivot table while

City and state/region, allow you to group and aggregate numerical data with pivot tables, while latitude and longitude offer sensible ways to determine areas and distance.

Here is a simple custom function that provides easy access to the free ZIP code converter API.


ZIP Code Custom Function

ZIPLOC( zip code [, country abbrv] )

To translate ZIP codes to geographic info, just supply the ZIPLOC function with a ZIP code. The corresponding two-letter country code is optional but necessary for countries other than the US.

For example, if you want to get geographic data about the 1000 ZIP code of Bulgaria, the function would look like this:

ZIPLOC( 1000, BG)

The function will return the following horizontal array of data:

Country, Region, City, Latitude, Longitude

Bulgaria, София (столица) / Sofija (stolica), София / Sofija, 42.6833, 23.3167


Google Apps Script Code

The code makes use of the UrlFetchApp function to make a call to the RESTful APi and parses the requested JSON data and converts it to an array. To see what the JSON data looks like, check out this URL:

To use the code, just copy and paste it into the Google Apps Script Code editor and click Save. The ZIPLOC function will be available on the corresponding Google Spreadsheet.



March 8, 2017

Posted In: Custom Functions, Google Spreadsheets

Leave a Comment

Copy Multiple Google Sheets with Template Variables

Business and education administration often involves an overwhelming amount of repetitive tasks. We work in cycles of days, weeks, months, and years with multiple students, clients, employees, or team members. This adds up to multiples of multiple tasks. Luckily, automating repetitive and work-intensive tasks is just one of the many things Google Apps Script was made for.

In this Google Apps Script example, let’s automate all but the individualized aspects of working with Google Spreadsheets. We will create a template sheet and make multiple copies of that sheet with individualized variables for each sheet.

With a click of a button, this script will copy multiple versions of a sheet for each time period, each project, or each person.


How to Use the Script

Using the script is just as easy as using any other. Just copy the code below and paste it into the Google Apps Script code editor. For a working example, make a copy of this demo sheet.

  • Save the script
  • Run the onOpen() function or refresh the spreadsheet to instantiate the drop down menu
  • Select Create template variables sheet if the Template Variables Sheet does not already exist. 

Google Spreadsheet template copy

  • Fill in your template variables. The template variable names can any combination of letters, numbers, and underscores (_) enclosed with curly braces  eg: {{1st_place}}

Google Spreadsheets template sheet copy

  • To add more template variables, click Add Template Variable. To remove template variables, just delete that template variable’s column.
  • Before you make copies, ensure that the template variables sheets are completely filled out, including the {{sheet_name}} variable (the name of the tab). Else the variables will be left blank. Also, make sure that all cells outside of the template variable names columns are left empty.
  • To create copies, click the Create copies on the Sheet drop-down menu. This will create a new copy of the template sheet with unique variables for each row of template variable definitions.


How The Script Works

There are three parts to this script: the menu, the template variables sheet, and the sheet copier.

The menu follows a common and obvious pattern; it is built by the onOpen function. The onOpen function is called every time the spreadsheet is opened so whenever you open the spreadsheet, the menu is ready to use. Learn more about menus in Google Apps Script.

To make it as easy as possible for the user to avoid error, there is a function called createTemplateVarsSheet() that adds a sheet labeled “Template Variables” and adds an example header row and an example variable row. This function is called whenever the user clicks Create template variables sheet on the menu.

With the same intent to avoid any user error, the function addTemplateVariable() adds a new template variable column to the template variable sheet. This is can be done manually quite easily, but this function, called when the user clicks Add template variable, walks the user through the process and keeps the same consistent formatting or the Template Variables sheet.

The real value of this script comes in the templateCopier() function. This function iterates through each row of the Template Variables range and creates a copy of the template sheet with the values for each row.

The magic happens in a call to the replaceTags() function. This function is called in every cell of the copied template sheet and uses a regular expression to find and replace a template tag with its corresponding value in for the copy of the template.

Once every cell in every copied sheet has been updated the script is done and just like that, there are a number of new replica sheets.

Here is the script:


Google Spreadsheet Copier

I hope you find it useful for automating your work in Google Spreadsheets. If you think it could be improved to serve your purpose, let me know in the comments.

March 7, 2017

Posted In: Google Spreadsheets

Leave a Comment