How to plot Latitude and Longitude from Google Sheets into Looker?
- Looker FAQs
- January 20, 2018
This post shows how to use Google Sheets to get latitude or longitude and then map them into Looker.
To achieve this, first, we can deploy a script in Google Sheets that would convert an address into Latitude and Longitude.
- In your Google Spreadsheet, enter the address in the first column as follows:
- Now go to Tools and then click on Script Editor.
- Copy and paste the below code in the editor:
function geo_function_test() {
var geo_sheet = SpreadsheetApp.getActiveSheet();
var data_range = geo_sheet.getDataRange();
var geo_cells = data_range.getValues();
var latitudes = [];
var longitudes = [];
for (var z = 0; z < geo_cells.length; z++) {
var geo_address = geo_cells[z][0];
var geo_coder = Maps.newGeocoder().geocode(geo_address);
var res = geo_coder.results[0];
var lat = lng = 0;
if (res) {
lat = res.geometry.location.lat;
lng = res.geometry.location.lng;
}
latitudes.push([lat]);
longitudes.push([lng]);
}
geo_sheet.getRange('B1')
.offset(0, 0, latitudes.length)
.setValues(latitudes);
geo_sheet.getRange('C1')
.offset(0, 0, longitudes.length)
.setValues(longitudes);
}
- Save this as a new project.
- Run this script by hitting the run button.
- The coordinates should appear in the next two columns as follows:
Once you have the Longitude / Latitude coordinates, you can load this data into a table within your choice database. Using the table, we can create a view called location and join this view with any table containing area codes, which would allow us to plot the areas on a map easily.
Up Next:
Read Is it possible to tie a dashboard filter in Looker to multiple fields in the same Look?