Tutorials

Sample code in different programming languages.

GOOGLE SHEETS

Tutorial: Google Sheets

You can use Geolake in your Google Sheets with a script. To get started, open your sheet and go to Tools, Script Editor.

In the window that opens, replace the empty dummy function with the content from Geolake.gs below

Hit File, Save. Go back to your sheet and refresh the page. Wait a few seconds after the page has loaded, you should now see a new menu 'Geolake' on the right of Help.

To use this feature, create 3 columns, 'Unstructed Address', 'Latitude' and 'Longitude'. Populate the first column with address data and select the cells from those 3 columns for the rows that have data.

With your selection, click on Geolake, 'Geocode unstructured address'. You should get a popup. Fill in your API key and hit 'Geocode'. This can take a while, and will eventually generate values in the latitude and longitude columns.

The first time you click on 'Geocode unstructured address', you will get a prompt asking for permission to run the code you just created. Allow the code to run, the link to proceed can be hidden in the 'advanced' section.

Geolake.gs

function callAPI(query, apiKey) {
  var url = 'https://api.geolake.com/v1/geocode?api_key=' + encodeURIComponent(apiKey);
  for (var key in query) {
    if (!query.hasOwnProperty(key)) {
      continue;
    }

    url += "&" + key + "=" + encodeURIComponent(query[key]);
  }

  return UrlFetchApp.fetch(url, {
    "headers": {
      "Content-Type": "application/json",
      "Accept": "application/json"
    },
    muteHttpExceptions: true
  });
}

function geocodeUnstructured() {
  geocode("addressHandler");
}

function geocodeIP() {
  geocode("ipHandler");
}

function geocode(callback) {
  var app = UiApp.createApplication().setTitle('Geolake');
  var grid = app.createGrid(3, 3);
  grid.setWidget(0, 0, app.createLabel("API key:"));
  grid.setWidget(0, 1, app.createTextBox().setName("apiKey").setId("apiKey"));
  grid.setWidget(1, 0, app.createLabel("NOTE: This can take a while for large amounts of data. See the request / second limit for your account on https://geolake.com/pricing."));

  var panel = app.createVerticalPanel().setId('geocodePanel');
  panel.add(grid);
  var button = app.createButton('Geocode').setStyleAttribute('margin-top', '10').setId('geocode');
  var handler = app.createServerClickHandler(callback);

  handler.addCallbackElement(grid);
  button.addClickHandler(handler);
  grid.setWidget(2, 1, button);
  app.add(panel);
  SpreadsheetApp.getActiveSpreadsheet().show(app);
}

function addressHandler(e) {
  return geocodeHandler(e, "q");
}

function ipHandler(e) {
  return geocodeHandler(e, "ip");
}

function geocodeHandler(e, queryColumn) {
  var apiKey = e.parameter.apiKey;
  var sheet = SpreadsheetApp.getActiveSheet();
  var cells = sheet.getActiveRange();
  if (cells.getNumColumns() != 3) {
    SpreadsheetApp.getUi().alert("Please select 3 columns: one for the input, one for latitude and one for longitude.");
    return;
  }

  var rowCount = cells.getNumRows();
  var inputValues = sheet.getRange(cells.getRow(), cells.getColumn(), rowCount, 1).getValues();

  for (i = 0; i < rowCount; i++) {
    var inputValue = inputValues[i][0];
    var queryObj = {};
    queryObj[queryColumn] = inputValue;
    var response = callAPI(queryObj, apiKey);
    var statusCode = response.getResponseCode();

    if (statusCode == 200) {
      var json = JSON.parse(response.getContentText());
      if(json && json.success) {
        cells.getCell(i + 1, 2).setValue(json.latitude);
        cells.getCell(i + 1, 3).setValue(json.longitude);
      } else {
        cells.getCell(i + 1, 2).setValue("Not found");
      }
    } else {
      cells.getCell(i + 1, 2).setValue("Geocoding problem: " + statusCode + ", " + response.getContentText());
    }
  }
  var app = UiApp.getActiveApplication();
  app.close();
  return app;
}

function buildMenu() {
  return [{
      name: "Geocode unstructured address",
      functionName: "geocodeUnstructured"
    }, {
      name: "Geocode IP",
      functionName: "geocodeIP"
    }
  ];
}

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu('Geolake', buildMenu());
}