I have to sort a list of address by distance. I was was able to procure the LAT LONG of the addresses with this script to run in sheets.
NOTE: GEOCODE(RC[-1]) is added in Auto Compute->Spreadsheet Formula. The number in the brackets are the number of columns before( - ) of after( + ) the the column the command is in
function GEOCODE(address) {
if (address.map) {
return address.map(GEOCODE)
} else {
var r = Maps.newGeocoder().geocode(address)
for (var i = 0; i < r.results.length; i++) {
var res = r.results[i]
return res.geometry.location.lat + ", " + res.geometry.location.lng
}
}
}
I was also able to find this code
ORDERBY(
FILTER("Airfields", TRUE),
DISTANCE(HERE(), [AirfieldLatLong])
)
but I don't know what to do with it. The post I got it from said to put it in the If Valid field of a drop down list. I need to sort the view itself so the best i can figure is to make a ref list and sort it using that somehow, but I need to see all of the records so REF_ROWS() wont help.
Could anyone help me get around this one, thanks.
LinkedIn
Twitter
