Geocoding with Google Sheets and Google Apps Script

Revisiting Functions

Open up your Google Sheet from part 1.

Go to column for Location and remember what column it is, for me it is C:

Next, add two columns, one for lat and another for lng:

Now click on Extensions

Now click on Apps Script

When you first launch, you will see a blank myFunction() select it and get ready to paste over it:

Copy and paste the following code into the entire script:

function myFunction() {
  let sheet = SpreadsheetApp.getActiveSheet();

  let range = sheet.getDataRange();
  let cells = range.getValues();

  let latitudes = [['lat']]; //(1)!
  let longitudes = [['lng']]; //(2)!

  for (let i = 0; i < cells.length; i++) {
    // change cells[i][2] if your address is not in column 'C', for example cells[i][1] for column 'B' or cells[i][3] for column D
     addressColumn = cells[i][2] //(3)!
     let lat = lng = 0;
    if (i > 0) {
      if (addressColumn){
    let address = addressColumn;
    console.log(address)

    if(address){
      let geocoder = Maps.newGeocoder().geocode(address);
      let res = geocoder.results[0];
        if (res) {
          lat = res.geometry.location.lat;
          lng = res.geometry.location.lng;
        }
      }
    }
    latitudes.push([lat]);
    longitudes.push([lng]);
    }
  }
  sheet.getRange('D1') // this is your latitude column //(4)!
  .offset(0, 0, latitudes.length)
  .setValues(latitudes);
  sheet.getRange('E1') // this is your longitude column //(5)!
  .offset(0, 0, longitudes.length)
  .setValues(longitudes);
  Utilities.sleep(5000)
}

1. This defines the column as latitude to be lat
2. This defines the column as longitude to be lng
3. cells[i][2] the 2 is number that your address column is in minus 1!! You have to subtract 1 because JavaScript starts counting at 0!!! For example, column A is 0!
4. Make sure this column letter matches your **latitude** column!!
5. Make sure this column letter matches your **longitude** column!!

JavaScript arrays start at 0

Most programming have two types of indexing, 0 indexed or 1 indexed, which means the number that they start counting lists from. JavaScript is 0 indexed, meaning a list with 4 things starts from 0 and ends at 3, like this: [0,1,2,3].

This is important when we call items from a list and want to get the right item from it!! For example, we have to get the fourth item in the example array like this, let theFourthItem = myArray[3].

Click on the Save Icon to save your script:

Click on the Run button to test the script:

You should get a prompt asking for Authorization, click Review permissions:

Select your Google Account to continue:

Click on Advanced:

Click on Go to Untitled Project (unsafe)

Click on Allow

Do you trust yourself?

Essentially, anyone can write Google Scripts, so Google is making sure that the script is associated with your account before giving it access to this particular spreadsheet.

Click on Save:

If it ran successfully then you should now see latitude and longitude filled in the Google Sheet!

Geocoding on each submission with a trigger

Go back to the Google Scripts and click on the triggers icon

Click on Add Trigger:

Click on Select event type:

Change to On Form Submit so that everytime the the form gets submitted a new record gets latitude/longitude added too!

Click Save:

A pop-up should appear, but if you have a pop-up blocker like on FireFox, then you may have to click on Options:

Then Allow this particular popup to appear.

Authorization again?

This is the same authorization as before, but it is for the Trigger not the application!

Select your Google Account to continue:

Click on Advanced:

Click on Go to Untitled Project (unsafe)

Click on Allow

Click on Save:

Congratulations, now each time a form gets submitted you will be able to map the locations:

⚽In-class Exercise #2 - Test your form!

Tasks

1. Add 2-3 locations to your Google Form and see if the new locations work.
2. Check to see if the locations are accurate or not!
3. What type of locations do not show up?

Answer

Locations that do not show up are those that are blank or are not able to be located by the Google Geocoder.

Publishing your survey

Now that our data is able to be geocoded, the final step is to publish the spreadsheet so we can can bring it into our HTML file through JavaScript next week.

Go to File:

Click on Sharing:

Click on Publish to web:

Click on Webpage:

Choose CSV:

Click Publish:

If this warning pops-up click on OK:

Copy the URL in the address bar:

Lets add a dataUrl variable

Create a new dataUrl variable to store the URL of the data, first we can test this by assigning it to map.geojson and see if it works:

js/init.js

const dataUrl = "map.geojson";

// When the map is fully loaded, start adding GeoJSON data
map.on('load', function() {
    fetch(dataUrl)
        .then(response => response.json())
        .then(data => {
            processData(data); // Call processData with the fetched data
        });
});

using the URL you copied from the Google Sheet:

js/init.js

const dataUrl = "https://docs.google.com/spreadsheets/d/e/2PACX-1vSNq8_prhrSwK3CnY2pPptqMyGvc23Ckc5MCuGMMKljW-dDy6yq6j7XAT4m6GG69CISbD6kfBF0-ypS/pub?output=csv"

And let’s change the map.geojson to dataUrl:

🏁 Checkpoint

Your init.js should look like the following:

// declare variables
let mapOptions = {'centerLngLat': [-118.444,34.0709],'startingZoomLevel':5}

const map = new maplibregl.Map({
    container: 'map', // container ID
    style: 'https://api.maptiler.com/maps/streets-v2-light/style.json?key=wsyYBQjqRwKnNsZrtci1', // Your style URL
    center: mapOptions.centerLngLat, // Starting position [lng, lat]
    zoom: mapOptions.startingZoomLevel // Starting zoom level
});

function addMarker(lat,lng,title,message){
    let popup_message = `<h2>${title}</h2> <h3>${message}</h3>`
    new maplibregl.Marker()
        .setLngLat([lng, lat])
        .setPopup(new maplibregl.Popup()
            .setHTML(popup_message))
        .addTo(map)
    createButtons(lat,lng,title);
    return message
}

function createButtons(lat,lng,title){
    const newButton = document.createElement("button");
    newButton.id = "button"+title;
    newButton.innerHTML = title;
    newButton.setAttribute("lat",lat);
    newButton.setAttribute("lng",lng);
    newButton.addEventListener('click', function(){
        map.flyTo({
            center: [lng,lat],
        })
    })
    document.getElementById("contents").appendChild(newButton);
}

const dataUrl = "https://docs.google.com/spreadsheets/d/e/2PACX-1vSNq8_prhrSwK3CnY2pPptqMyGvc23Ckc5MCuGMMKljW-dDy6yq6j7XAT4m6GG69CISbD6kfBF0-ypS/pub?output=csv"

// When the map is fully loaded, start adding GeoJSON data
map.on('load', function() {
    fetch(dataUrl)
        .then(response => response.json())
        .then(data => {
            processData(data); // Call processData with the fetched data
        });
});

function processData(results){
    //console.log(results) //for debugging: this can help us see if the results are what we want
    results.features.forEach(feature => {
        //console.log(feature) // for debugging: are we seeing each feature correctly?
        // assumes your geojson has a "title" and "message" attribute
        let coordinates = feature.geometry.coordinates;
        let longitude = coordinates[0];
        let latitude = coordinates[1];
        let title = feature.properties.title;
        let message = feature.properties.message;
        addMarker(latitude,longitude,title,message);
    });
};

If you see an error like this:

Then, suprisingly, you are on the right track!

We can now move onto looping through the data and adding markers to the map!