0
97
VistasUse a custom formula with arrayformula for distances between two columns of cities in Google Sheets with App Scripts
I have a custom formula in Google Apps Scripts and Google Sheets that gives the distance between two cities. EXAMPLE SHEET
=MILEAGE(A2,B2)
function MILEAGE(origin,destination) {
Utilities.sleep(0);
var directions = Maps.newDirectionFinder()
.setRegion('US')
.setOrigin(origin)
.setDestination(destination)
.getDirections();
var route = directions.routes[0].legs[0];
var distance = (route.distance.value) * 0.000621371;
return distance
}
The formula works great for two cells, but I need it to work like an arrayformula for two rows of cities for each row independently. I have weighed the pros and cons of running the script once with an array and returning an array and it is not effective in this application because I do not want the entire range to re-calculate every time one row is added/changed.
The objective is for it to work like this:
=arrayformula(if(A2:A<>"", MILEAGE(A2:A, B2:B), ""))
Any suggestions on how to do this, or better ways to accomplish the same goal will be appreciated.
2 Respuestas
Responde la pregunta0
The issue is that, when you use pass a range in a custom function, it doesn't pass the range, but the values.
What I recommend is that you use a trigger specifically onEdit so when you are editing a specific cell, it only recalculates that row. I also made it so that if you remove or add multiple location, they update the corresponding rows.
Script:
function onEdit(e) {
var sheet = e.source.getActiveSheet();
var range = e.range;
var row = range.getRow();
var column = range.getColumn();
var lastRow = range.getLastRow();
for(var i = 0; i <= lastRow - row; i++)
if (row + i > 1 && (column == 1 || column == 2) && sheet.getName() == "Sheet1") {
var origin = sheet.getRange(row + i, 1).getValue();
var destination = sheet.getRange(row + i, 2).getValue();
if(origin && destination)
sheet.getRange(row + i, 3).setValue(calculateMileage(origin, destination));
else
sheet.getRange(row + i, 3).clearContent();
};
}
function calculateMileage(origin, destination) {
var directions = Maps.newDirectionFinder()
.setRegion('US')
.setOrigin(origin)
.setDestination(destination)
.getDirections();
var route = directions.routes[0];
if (route) {
var legs = route.legs[0];
var distance = (legs.distance.value) * 0.000621371;
return distance;
}
else {
return "no route found"
}
}
Output:
Note:
- I found a cell that returns error due to invalid location or no route found so I tried catching it.
- If a row doesn't have a value on either cell, it will delete remove the distance column.
0
To make this function array compatible,
Check if it's a array and map it's elements
If it's not a array, directly pass the elements
Cache service may also be used to avoid making repeated calls to maps.
/**
* @param {string} origin
* @param {string} destination
*/
function MILEAGE_(origin, destination) {
try {
const sCache = CacheService.getScriptCache();
const key = `${origin}_${destination}`;
const cached = sCache.get(key);
if (cached) return Number(cached);
Utilities.sleep(1);
const directions = Maps.newDirectionFinder()
.setRegion('US')
.setOrigin(origin)
.setDestination(destination)
.getDirections();
const route = directions.routes[0].legs[0];
const distance = route.distance.value * 0.000621371;
sCache.put(key, String(distance), 21600);
return distance;
} catch {
return '#ERROR';
}
}
/**
* @param {A1:D1} arr
* @param {A1} param1
* @param {C1} param2
* @customfunction
*/
const mileage = (...arr) =>
Array.isArray(arr[0]) ? arr[0].map(e => MILEAGE_(...e)) : MILEAGE_(...arr);
Usage:
=MILEAGE("origin","destination")
=MILEAGE(A1,B1)
=MILEAGE(A1:B100)