0
95
ViewsUse una fórmula personalizada con fórmula de matriz para distancias entre dos columnas de ciudades en Hojas de cálculo de Google con App Scripts
Tengo una fórmula personalizada en Google Apps Scripts y Google Sheets que da la distancia entre dos ciudades. HOJA DE EJEMPLO
=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 }La fórmula funciona muy bien para dos celdas, pero necesito que funcione como una fórmula de matriz para dos filas de ciudades para cada fila de forma independiente . He sopesado los pros y los contras de ejecutar el script una vez con una matriz y devolver una matriz y no es efectivo en esta aplicación porque no quiero que todo el rango se vuelva a calcular cada vez que se agrega/cambia una fila.
El objetivo es que funcione así:
=arrayformula(if(A2:A<>"", MILEAGE(A2:A, B2:B), ""))
Cualquier sugerencia sobre cómo hacer esto, o mejores formas de lograr el mismo objetivo, será apreciada.
2 answers
Answer question0
El problema es que, cuando usa pasar un rango en una función personalizada, no pasa el rango, sino los valores.
Lo que recomiendo es que use un disparador específicamente onEdit , de modo que cuando edite una celda específica, solo vuelva a calcular esa fila. También lo hice para que si elimina o agrega varias ubicaciones, actualicen las filas correspondientes.
Guion:
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" } }Producción:
Nota:
- Encontré una celda que devuelve un error debido a una ubicación no válida o no se encontró una ruta, así que intenté detectarla.
- Si una fila no tiene un valor en ninguna de las celdas, eliminará la columna de distancia.
0
Para que esta matriz de funciones sea compatible,
Compruebe si es una matriz y asigne sus elementos
Si no es una matriz, pasa directamente los elementos.
El servicio de caché también se puede utilizar para evitar realizar llamadas repetidas a los mapas.
/** * @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);Uso:
=MILEAGE("origin","destination") =MILEAGE(A1,B1) =MILEAGE(A1:B100)