DRIVEDIST is throwing error: invalid argument for 'origin'

Question

Trying to implement this into my google sheets document. I keep getting an 'invalid argument' message for line 8, 'origin'. How do I fix this? Also, Google Sheets is not automatically calling this function. Is this because the function itself is not completing without errors?

// Distance functions modified from originals by github.com/rvbautista // DRIVEDIST modified from https://www.bpwebs.com/calculate-the-distance-between-two-addresses/ // GEODIST modified from https://www.labnol.org/code/19735-find-distance-in-google-sheets // Please make attribution to my github and to the listed websites above when using this code. Thank you

function DRIVEDIST(origin, destination, unit) {
try {
var directions = Maps.newDirectionFinder()
.setOrigin(origin)
.setDestination(destination)
.setMode(Maps.DirectionFinder.Mode.DRIVING)
.getDirections();

var distance = directions.routes[0].legs[0].distance.value;
switch (unit){
  case "km":
    distance = distance / 1000;
    return Number(distance.toFixed(2));
  case "m": 
    return Number(distance.toFixed(2));
  case "mi":
    distance = distance / 1609.34;
    return Number(distance.toFixed(2));
  case "ft":
    distance = distance * 3.28084;
    return Number(distance.toFixed(2));
  case "nm":
    distance = distance / 1852;
    return Number(distance.toFixed(2));
  default:
    throw new Error("Invalid unit. Supported units: km, m, mi, ft, nm.");
}

} catch (error) {
Logger.log("Error in DRIVEDIST: " + error.message);
return null;
}
}

function GEODIST(origin, destination, unit) {
try {
var geocoder = Maps.newGeocoder();
var start = geocoder.geocode(origin);
if (start.status != 'OK' || start.results.length == 0) {
throw new Error("Unable to geocode origin: " + origin);
}
var coords1 = \[start.results\[0\].geometry.location.lat, start.results\.         
[0\].geometry.location.lng\];

var lat1 = (coords1[0] * Math.PI) / 180;
var lng1 = (coords1[1] * Math.PI) / 180;

var endd = geocoder.geocode(destination);
if (endd.status != 'OK' || endd.results.length == 0) {
  throw new Error("Unable to geocode destination: " + destination);
}
var coords2 = [endd.results[0].geometry.location.lat, endd.results[0].geometry.location.lng];

var lat2 = (coords2[0] * Math.PI) / 180;
var lng2 = (coords2[1] * Math.PI) / 180;

var dLng = lng2 - lng1;
var dLat = lat2 - lat1;

var a = Math.sin(dLat / 2) * Math.sin(dLat / 2) + Math.sin(dLng / 2) * Math.sin(dLng / 2) * Math.cos(lat1) * Math.cos(lat2);
var c = 2 * Math.atan2(Math.sqrt(a), Math.sqrt(1 - a));

var radius = 6371; // Radius of Earth in kilometers
switch (unit){
  case "km":
    return Number((radius * c).toFixed(2));
  case "m": 
    return Number((radius * c * 1000).toFixed(2));
  case "mi":
    return Number((radius * c / 1.60934).toFixed(2));
  case "ft":
    return Number((radius * c * 3280.84).toFixed(2));
  case "nm":
    return Number((radius * c / 1.852).toFixed(2));
  default:
    throw new Error("Invalid unit. Supported units: km, m, mi, ft, nm.");
}

} catch (error) {
Logger.log("Error in GEODIST: " + error.message);
return null;
}
}

Tried ChatGPT, and this is a refined code of the original.

Answer 1

You are using a known script and getting an error message:

• invalid argument' message for line 8

This error messages is generated when the function DRIVEDIST() is run from the script editor. However, the script is designed as a custom function. it is intended to be entered into the spreadsheet like this:

=DRIVEDIST("Sydney","Melbourne","km") which returns 877.64 being, according to Google, the number of kilometres between Sydney and Melbourne Australia.

1. Refer to the documentation on custom functions.

2. If you insert the following lines of code immediately before the script, then the function will appear formula autocomplete list.

   /**

   • Returns the driving distance between two towns
   • @param {string} origin Starting town
   • @param {string} destination Ending town
   • @param {string} unit Supported units of measure: km, m, mi, ft, nm.
   • @customfunction */

---

Sample

---

Autocomplete - 01

---

Autocomplete - 02