Thomas Thomas - 1 month ago 6
JSON Question

Filtering JSON data with the same name

I'm using JSON in Google Sheets to lookup a Zip and find the county. I only want the county returned. I can get it to return every value so the ImportJSON function is working.

Here's my formula. I've tried all permutations of the reference but I just don't know how to format it.

=ImportJSON(CONCATENATE("https://maps.googleapis.com/maps/api/geocode/json?address="92660), "results/address_components/long_name[3]", "noHeaders")


Here's the JSON data from Google Maps Geocoding API. I only want the county long name. In this example it's, "Orange County".

{
"results" : [
{
"address_components" : [
{
"long_name" : "92660",
"short_name" : "92660",
"types" : [ "postal_code" ]
},
{
"long_name" : "Newport Beach",
"short_name" : "Newport Beach",
"types" : [ "locality", "political" ]
},
{
"long_name" : "Orange County",
"short_name" : "Orange County",
"types" : [ "administrative_area_level_2", "political" ]
},
{
"long_name" : "California",
"short_name" : "CA",
"types" : [ "administrative_area_level_1", "political" ]
},
{
"long_name" : "United States",
"short_name" : "US",
"types" : [ "country", "political" ]
}
],
"formatted_address" : "Newport Beach, CA 92660, USA",
"geometry" : {
"bounds" : {
"northeast" : {
"lat" : 33.671823,
"lng" : -117.841337
},
"southwest" : {
"lat" : 33.6040739,
"lng" : -117.909447
}
},
"location" : {
"lat" : 33.6301328,
"lng" : -117.8721676
},
"location_type" : "APPROXIMATE",
"viewport" : {
"northeast" : {
"lat" : 33.671823,
"lng" : -117.841337
},
"southwest" : {
"lat" : 33.6040739,
"lng" : -117.909447
}
}
},
"place_id" : "ChIJRdSajSne3IAR8T4A2x-wgrE",
"types" : [ "postal_code" ]
}
],
"status" : "OK"
}

Answer

There are 3 things that are preventing it to properly import:

  1. specifically in the concatenate function you have address="92660

which should be address=",92660

or you can eliminate the concat function altogether and format the url like this:

"https://maps.googleapis.com/maps/api/geocode/json?address="&"92660"

or technically point to the cell such as A1 with the 92660 value e.g. "https://maps.googleapis.com/maps/api/geocode/json?address="&A1

  1. your missing the beginning / in front of results

  2. In order to get the 3rd item, instead of using [3] , wrap your formula in the index function and reference the index of 3

the full thing:

=index(importjson("https://maps.googleapis.com/maps/api/geocode/json?address="&A1,"/results/address_components/long_name","noHeaders"),3)

enter image description here

Comments