How to plot Latitude and Longitude from Google Sheets into Looker?

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.

1. In your Google Spreadsheet, enter the address in the first column as follows:
   
   
2. Now go to Tools and then click on Script Editor.
3. 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);
   
   }

   

4. Save this as a new project.
5. Run this script by hitting the run button.
   
   
6. 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?