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.
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
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.
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?
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
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.
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.
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.
