Geocode with Mapzen search and Google Spreadsheets

README.md

How to set up a quick geocoding system on google spreadsheets.

1. Create a new spreadsheet
2. Open the Scripts editor and paste the script attached
3. Use on your spreadsheet this new formula

=searchMapzen(place_cell,mapzen_api_key)

So you need to register at Mapzen developers site and get your own search API Key. Take this as an exercise, you can interact with the search API in many ways.

Enjoy!

script.js

function searchMapzen(place,api_key){
  // return nothing if no data
  if (!place) return null;
  
  // set up url and fetch info
  var url = "https://search.mapzen.com/v1/search?api_key=" + api_key + "&text=" + encodeURIComponent(place);
  var response = UrlFetchApp.fetch(url,{
    "headers" : {
      "Content-Type":"application/json",
      "Accept"      :"application/json"
    }
  });  
  
  // parse results and return the first one
  var results = JSON.parse(response.getContentText());
  if (results.features && results.features.length >= 1){
    var first = results.features[0];
    return [
      [
        first.properties.label,
        first.geometry.coordinates[0],
        first.geometry.coordinates[1],
        first.properties.confidence
      ]
    ];      
  }
}

This is great! The only small suggestion is to add some sleep cycles to avoid hitting the requests per second rate limit when refreshing a sheet. Something like this on line 4 in the script could do the trick.

  // add a sleep period to stagger the requests in order to avoid hitting the requests per second rate limit
  // which currently defaults to 6
  // NOTE: if you have to process a large amount of data this will be terribly slow and might still hit
  // the requests per day limit of 30K
  Utilities.sleep(Math.random() * 1000);

Ey thanks for the suggestion!!