Categories: GIS Learning

How to Populate a Cell with the ZIP Code Based on an Address in Google Sheets

If you have a concatenated field containing address information, you can set up a simple function in Google Sheets to be able to populate a separate column with ZIP code data.  This function will take the address information found in one cell and populate a second cell with the associated ZIP code.

Ad

For this tutorial, I have a simple spreadsheet that contains the first ten entries for the 2017 Fortune 1000 list.  As you can see in the screenshot below, I have the company name, location, and state.  What I want to add is a field with ZIP code information for each record.

Set up the geocoding script

The first thing we want to do is to set up the geocoding function in a short script.  We will use this script to call the function for each of the cells we want to populate with the ZIP code.

To start, go to Tools –> Script editor.

In the new script window that opens, paste the following (remove the default function template first):


Advertisement


function geo2zip(a) {
  var response=Maps.newGeocoder()
    .reverseGeocode(lat(a),long(a));
  return response.results[0].formatted_address.split(',')[2].trim().split(' ')[1];
}
function lat(pointa) {
 var response = Maps.newGeocoder()
     .geocode(pointa);
  return response.results[0].geometry.location.lat
}
function long(pointa) {
  var response = Maps.newGeocoder()
     .geocode(pointa);
  return response.results[0].geometry.location.lng
}

Click on the save icon and give the project a name.

Now we are ready to run the function.  Click on the tab where your spreadsheet is located.  Select the first cell that you want to populate the ZIP code for.  You are going to now call the function which in the script window we named “geo2zip”.  In that cell paste =geo2zip() and in the parentheses type in the cell containing the concatenated address information.  In the example below, the first record has the full address in cell E2 so I will paste in cell F2 this function: =geo2zip(E2).

Hit the return key and you should see that first selected cell now populated with the associate ZIP code.  To populate the rest of the cells, select the cell and use the cursor to drag down to the remaining cells by pulling down the little blue box that appears in the lower right corner.

Ad

You should see a loading… message indicating that the function is running.  Then the cells with populate one by one with the ZIP code value.  As a caution, any formatting issues with the full address value may result in errors (see the Francisco error in the screenshot below).

As you clean up your address data, those cells will recalculate and populate the cell with the actual ZIP code.  This function limits you to about 300 records before you will start getting an error message that the geocoding service has been invoked too many times.

Watch the video tutorial

See Also

How to quickly add latitude and longitude coordinates to a spreadsheet using Google Sheets

Ad
Share
Published by

Recent Posts

Mapping Every Tree

The California Forest Observatory, created by Salo Sciences, has applied a new artificial intelligence tool…

September 22, 2020

Self-employment in GIS

Self-employment in the GIS industry may seem daunting, but a recent MapScaping podcast with Kurt Menke, founder…

September 21, 2020

Survey of GIS Professionals

A quick look at GIS professionals, their educational background, and the software tools and programming…

September 20, 2020

Using GIS to Improve Hurricane Evacuation and Preparedness

As hurricane season gets underway in the Atlantic, GIS is being use to better prepare…

September 16, 2020

Using GIS to Monitor the West Coast Wildfires

With fires raging in California, Oregon, and Washington, scientists, activists, geospatial companies, and others are…

September 13, 2020

Earthquake Detection Using Smartphones

Using aggregated accelerometer data across many smartphones, many of us can now better access a…

September 8, 2020