Automatic geocoding using Google Scripts

Hi guys, I think this would be helpful to many of us who wants a Geocoding solution in the sheets to display accurate pins on the map.

I also tried Geocoding with Awesome Table but it seems like it does not trigger automatically when new rows are added. It has an option to link with Form submit, but it’s not the case here with Glide as far as I aware.

So I have a script to do that for me, that will trigger automatically on new inputs as you can see in the GIF below. Note that the columns in the script does not match with the GIF as after this test I changed it.

The script is:

  function AddressToPosition() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("User profiles");
  var cells = sheet.getRange("M2:O");
  
  var addressColumn = 1;
  var addressRow;
  
  var latColumn = addressColumn + 1;
  var lngColumn = addressColumn + 2;
  
  var geocoder = Maps.newGeocoder().setRegion('US');
  var location;
  
  for (addressRow = 1; addressRow <= cells.getNumRows(); ++addressRow) {
    var address = cells.getCell(addressRow, addressColumn).getValue();
    
    // Geocode the address and plug the lat, lng pair into the 2nd and 3rd elements of the current range row.
    location = geocoder.geocode(address);
   
    // Only change cells if geocoder seems to have gotten a 
    // valid response.
    if (location.status == 'OK') {
      lat = location["results"][0]["geometry"]["location"]["lat"];
      lng = location["results"][0]["geometry"]["location"]["lng"];
      
      cells.getCell(addressRow, latColumn).setValue(lat);
      cells.getCell(addressRow, lngColumn).setValue(lng);
    }
  }
};

What you need to change:

  • var sheet = ss.getSheetByName(“User profiles”) => Change “User profiles” to the sheet that contains the address. Remember to add a latitude column, a longitude column and a combined lat/lon column (using ARRAYFORMULA) as you can see in the GIF.

  • var cells = sheet.getRange(“M2:O”); => Change M2:O to the range where you have the address, latitude & longitude columns.

Hope this helps.

9 Likes

Fantastic will give it a go. Thanks for putting this together

1 Like

Also noted that the setRegion is set to US. You can change that based on your use case (UK, FR or whatever).

The abbreviations are country code Top-Level Domains (ccTLDs), which I think is well documented here.

1 Like

What is your script trigger? A Form Submission event?

Gracias @ThinhDinh

Hi Gustavo, it’s an “On change” event.

1 Like

We got a problem Houston! :pleading_face: @ThinhDinh

and this is understandable because you use onChange() event and it is very sensible: any change in any cell will fire your script and at the same time, your script will evaluate each cell’s address “cells.getNumRows()” times.

I think that every time this sentence is executed

geocoder.geocode(address)

Google adds 1 to our daily queries counter and this is what we need to avoid if we have 40+ rows.

My test data has only 50 rows and filling out 30-35 rows Google punished me for 24 hours :upside_down_face:

Do you agree or I am seeing anything wrong?

Gracias Thinh

1 Like

You’ll need to alter your script so it only executes the geocoding only when the coordinates column is empty.

2 Likes

Thanks Jeff & Gustavo, that’s a good point. I don’t have time to alter the script now but might have to come up with a solution that satisfies two conditions:

  • Executes whenever a new row is added (the coordinates column is empty)
  • Executes whenever there’s a change in address from the user’s side or my side to recalculate.
1 Like

Right,

Let me wait for Google in order to modify a little the Thinh’s code. Meanwhile, I’m going to watch TV, my Google’s penalty ends at 4-5 pm :sleepy:

Nos vemos!

2 Likes

Hola @ThinhDinh
I modified your code to make easier and faster the geocoding operations.
In my case (this code) does not use a loop and not read all rows to find those addresses without coordinates either. Instead, the code works by “exception” … I mean, only new inserted rows are processed to find their coodinates.

I used 3 columns like you but changed their function a little:

AK: where the addresses are
AL: a flag to notify that the address is ready and has coordinates (using @Jeff_Hager’s idea)
AM: the address’ coordinates

How does it work?

  1. Everytime the sheet detects a change or a row is inserted, it will fire the script and look for through AL column the row marked as “changed” and generate its coordinates and avoid a loop.
    Once the row is found, the script reads the AL column and if this isn’t TRUE, the script will read the address from AK column, generate the coordinates by writing it in AM column and later write TRUE in AL column as a check mark to avoid it in future

  2. If any user (me) wants to have a new coordinates manualy, just by cleaning the AL column’s value is enough to get a new coordinates if the addres has changed previously of course.

     function AddressToPosition() {
      var ss = SpreadsheetApp.getActiveSpreadsheet(); 
      var sheet = ss.getSheetByName("Ordenes");
      var cells = sheet.getRange("AK2:AM");
      
      var addressRow = ss.getCurrentCell().getRow() -1;
      var location;
      var addressColumn = 1;
      var CheckColumn = addressColumn + 1;
      var CoordColumn = addressColumn + 2;
     
       // Browser.msgBox("# Fila que Cambio.. *" + addressRow + "*"); 
     
      var CheckCoord = cells.getCell(addressRow,CheckColumn).getValue();    
      if (CheckCoord != true ) {                                              // the address was not already checked? (ALx)   
         var geocoder = Maps.newGeocoder()
          .setRegion('VE')
          .setLanguage('en-GB') 
         var address = cells.getCell(addressRow, addressColumn).getValue();   // get new address in cell to process (AKx) 
        
        // Geocode the address and plug the lat, lng pair into the 2nd and 3rd elements of the current range row.
        location = geocoder.geocode(address);      
        // Only change cells if geocoder seems to have gotten a 
        // valid response.
        if (location.status == 'OK') {
          lat = location["results"][0]["geometry"]["location"]["lat"];
          lng = location["results"][0]["geometry"]["location"]["lng"];
              
          cells.getCell(addressRow, CheckColumn).setValue('TRUE');              // mark address as ready! (ALx)
          cells.getCell(addressRow, CoordColumn).setValue(lat + ", " + lng);    // write coordenates to cell (AMx)   
        }
      }
    };
    

Disclaimer
If the sheet has too many movements in a short time and many cells change their values, it is probably that the script won’t run always. I think Google has problems trying to handle the queue events when these are very often or almost simultaneous.

Thanks for your code Thinh!

Saludos a todos!

6 Likes

Excellent Gustavo, thank you for taking time to do this!