Automatic geocoding using Google Scripts

Spreadsheet Magic 🧙‍♂️
#4

What is your script trigger? A Form Submission event?

Gracias @ThinhDinh

#5

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

1 Like
#6

We got a problem Houston! :pleading_face: @ThinhDinh

image
image958×175 12.9 KB

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
#7

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

2 Likes
#8

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
#9

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
#10

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:

image
image685×538 23.3 KB

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!

9 Likes
#11

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

#12

someone has some code to get the automatic coordinates when they add a new line in a form

#13

You can try the code Gustavo added above. Set up a trigger so it will run automatically.

#14

Yes

#15

I’ve actually used a similar mechanism before.
I would like to inform you of the points to note at that time.

This mechanism causes trouble in geocoding such as multi-tenant buildings in urban areas.
If there are multiple targets in a building, the pins will overlap in the same position because the addresses are the same.
As a result, even if there are actually two stores, only one store may be displayed on the map, so please be careful.

2 Likes
#16

that’s a good point to keep in mind thanks men

#17

This function only allows a certain limit per day, I think it is around 1000.

If I would like to perform more than that, I would need Google Geoode API.

Could anyone here help to share the script on how to incorporate the API in Apps Script?

I’ve been figuring out for many weeks but couldn’t find out how to code it. :sob:

#18

You either re-do the code to use a service URL, or maybe you can try using the Fetch column inside Glide to do this. Make sure you do not expose your API key anywhere in your app.

1 Like
#19

How do you skip empty addresses XD

*when you remove something in glide, the row is cleared but not emptied, hence my question

(not a coder, haha, many thanks in advance)

#20

Thanks @gvalero and @ThinhDinh

1 Like
#21

It depends on how you define the logic of an empty row, but basically it should be an if to check that logic and stop the process if the logic is true (the row is indeed empty).

#22

:frowning: Mine doesn’t work

Error An unknown error has occurred, please try again later.

#23

Please use this instead.