Find distance and driving time between locations stored in google spreadsheet by using Distance Matrix API

Code.gs

function onOpen() {//adds new menu item to active spreadsheet
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  var entries = [
    {name : "Calculate distance", functionName : "findDistance"},
    {name : "Clear distance", functionName : "clearIDs"}];
  sheet.addMenu("Distance measure", entries);
}

function clearIDs() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet();
  sheet.getSheetByName('Sheet1').getRange("C2:D").clearContent();//clear cells that contains measured distance
}

function findDistance() {
  var spreadSheet = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = spreadSheet.getSheetByName('Sheet1');//sheet with locations
  var originColumnNumber = 1;//column with origin location
  var destinationColumnNumber = 2;//column with destination location
  var distanceColumnNumber = 3;
  var durationColumnNumber = 4;
  var range = sheet.getDataRange();
  var data = range.getValues();
  var dataLength = data.length;
  var ignoreErrors = true;//set to false to see errors
  var ignoreHeaderRow = true;
  var firstRow = ignoreHeaderRow ? 1 : 0;

  for (var i = firstRow; i < dataLength; i++) {
    var row = data[i];
    var origin = row[originColumnNumber - 1];
    var destination = row[destinationColumnNumber - 1];
    var directions, distance, duration;
    
    if(row[distanceColumnNumber - 1] && row[durationColumnNumber - 1]) {//skipping calculated row
      continue;
    }
    
    try {
      directions = DISTANCE_AND_TIME(origin, destination);//Use DM_DISTANCE_AND_TIME if Distance Matrix Web Api needed. In that case don't forget to set 'apiKey' variable
      distance = directions[0][0];
      duration = directions[0][1];
    } catch(e) {
      if(ignoreErrors) {
        distance = 'N/A';
        duration = 'N/A';
      } else {
        throw e;
      }
    }
 
    sheet.getRange(i + 1, distanceColumnNumber).setValue(distance);
    sheet.getRange(i + 1, durationColumnNumber).setValue(duration);
    
    Utilities.sleep(500);
  }
}

/**
 * Find distance and driving time.
 *
 * @param {string} origin Starting point.
 * @param {string} destination Finishing point.
 * @return {string[]} distance and driving time.
 * @customfunction
 */
function DISTANCE_AND_TIME(origin, destination) {
  var distance, duration, directions;

  if(!origin || !destination) {
    throw new Error("Invalid arguments");
  }

  directions = Maps.newDirectionFinder()
    .setOrigin(encodeURIComponent(origin))
    .setDestination(encodeURIComponent(destination))
    .setMode(Maps.DirectionFinder.Mode.DRIVING)
    .setLanguage('en')
    .getDirections();
  
  if(directions.status !== "OK") {
    throw new Error(directions.status + ": " + directions.error_message);
  }
  
  distance = directions.routes[0].legs[0].distance.text;
  duration = directions.routes[0].legs[0].duration.text;
  
  return [[distance, duration]];
}

/**
 * Find distance and driving time using Distance Matrix API.
 *
 * @param {string} origin Starting point.
 * @param {string} destination Finishing point.
 * @return {string[]} distance and driving time.
 * @customfunction
 */
function DM_DISTANCE_AND_TIME(origin, destination) {
  var apiKey, url, directions, distance, duration;

  if(!origin || !destination) {
    throw new Error("Invalid arguments");
  }
  
  apiKey = "your_api_key";
  url = "https://maps.googleapis.com/maps/api/distancematrix/json" +
      "?key=" + apiKey +
      "&origins=" + encodeURIComponent(origin) +
      "&destinations=" + encodeURIComponent(destination) +
      "&mode=driving";
  
  directions = JSON.parse(UrlFetchApp.fetch(url));
  
  if(directions.status !== "OK") {
    throw new Error(directions.status + (directions.error_message ? (": " + directions.error_message) : ''));
  }
  
  if(directions.rows[0].elements[0].status !== "OK") {
    throw new Error(directions.rows[0].elements[0].status);
  }
  
  distance = directions.rows[0].elements[0].distance.text;
  duration = directions.rows[0].elements[0].duration.text;
  
  return [[distance, duration]];
}


/**
 * Distance Matrix command line like arguments.
 *
 * @param {string} key
 * @param {string} origin
 * @param {string} destination
 * @param {string} mode
 * @param {string} language
 * @param {string} region
 * @param {string} avoid
 * @param {string} units
 * @param {string} arrival_time
 * @param {string} departure_time
 * @param {string} traffic_model
 * @param {string} transit_mode
 * @param {string} transit_routing_preference
 * @param {string} t Return time.
 * @param {string} d Return distance
 * @param {string} dv Distance value instead of text
 * @param {string} tv Duration value instead of text
 * @return {string[]} distance and driving time.
 * @example DistanceMatrix("--origins","Lutsk","--destinations","Rivne","--language","uk"). If either '-t' or '-d' is set will return only time or distance accordingly. Returns both values by default
 *
 * @customfunction
 */
function DistanceMatrix() {
  var args = [].slice.call(arguments);
  var params = minimist(args, {
    'default': {
      key: "YOUR_API_KEY_HERE" //you can set your api key here
    }
  });
  var url_params = ['origins', 'destinations', 'mode', 'language', 'region', 'avoid', 'units', 'arrival_time', 'departure_time', 'traffic_model', 'transit_mode', 'transit_routing_preference'];
  var url = "https://maps.googleapis.com/maps/api/distancematrix/json?key=" + params.key;
  var dm_data = [];
  var response;
  
  url_params.forEach(function(param) {
    if(params[param]) {
      url += '&' + param + '=' + encodeURIComponent(params[param]);
    }
  });

  response = JSON.parse(UrlFetchApp.fetch(url));  
  
  if(response.status !== "OK") {
    throw new Error(response.status + (response.error_message ? (": " + response.error_message) : ''));
  }

  response.rows.forEach(function(row) {
    row.elements.forEach(function(element) {
      var element_data = [];

      if(element.status !== 'OK') {
        throw new Error(element.status);
      }
      
      if(params.d || !params.t) {
        element_data.push(params.dv ? element.distance.value : element.distance.text);
      }

      if(params.t || !params.d) {
        element_data.push(params.tv ? element.duration.value : element.duration.text);
      }
      
      dm_data.push(element_data);
    });
  });

  return dm_data;
}

minimist.gs

//using minimist module by substack https://github.com/substack/minimist
var minimist = function (args, opts) {
    if (!opts) opts = {};
    
    var flags = { bools : {}, strings : {}, unknownFn: null };

    if (typeof opts['unknown'] === 'function') {
        flags.unknownFn = opts['unknown'];
    }

    if (typeof opts['boolean'] === 'boolean' && opts['boolean']) {
      flags.allBools = true;
    } else {
      [].concat(opts['boolean']).filter(Boolean).forEach(function (key) {
          flags.bools[key] = true;
      });
    }
    
    var aliases = {};
    Object.keys(opts.alias || {}).forEach(function (key) {
        aliases[key] = [].concat(opts.alias[key]);
        aliases[key].forEach(function (x) {
            aliases[x] = [key].concat(aliases[key].filter(function (y) {
                return x !== y;
            }));
        });
    });

    [].concat(opts.string).filter(Boolean).forEach(function (key) {
        flags.strings[key] = true;
        if (aliases[key]) {
            flags.strings[aliases[key]] = true;
        }
     });

    var defaults = opts['default'] || {};
    
    var argv = { _ : [] };
    Object.keys(flags.bools).forEach(function (key) {
        setArg(key, defaults[key] === undefined ? false : defaults[key]);
    });
    
    var notFlags = [];

    if (args.indexOf('--') !== -1) {
        notFlags = args.slice(args.indexOf('--')+1);
        args = args.slice(0, args.indexOf('--'));
    }

    function argDefined(key, arg) {
        return (flags.allBools && /^--[^=]+$/.test(arg)) ||
            flags.strings[key] || flags.bools[key] || aliases[key];
    }

    function setArg (key, val, arg) {
        if (arg && flags.unknownFn && !argDefined(key, arg)) {
            if (flags.unknownFn(arg) === false) return;
        }

        var value = !flags.strings[key] && isNumber(val)
            ? Number(val) : val
        ;
        setKey(argv, key.split('.'), value);
        
        (aliases[key] || []).forEach(function (x) {
            setKey(argv, x.split('.'), value);
        });
    }

    function setKey (obj, keys, value) {
        var o = obj;
        keys.slice(0,-1).forEach(function (key) {
            if (o[key] === undefined) o[key] = {};
            o = o[key];
        });

        var key = keys[keys.length - 1];
        if (o[key] === undefined || flags.bools[key] || typeof o[key] === 'boolean') {
            o[key] = value;
        }
        else if (Array.isArray(o[key])) {
            o[key].push(value);
        }
        else {
            o[key] = [ o[key], value ];
        }
    }
    
    function aliasIsBoolean(key) {
      return aliases[key].some(function (x) {
          return flags.bools[x];
      });
    }

    for (var i = 0; i < args.length; i++) {
        var arg = args[i];
        
        if (/^--.+=/.test(arg)) {
            // Using [\s\S] instead of . because js doesn't support the
            // 'dotall' regex modifier. See:
            // http://stackoverflow.com/a/1068308/13216
            var m = arg.match(/^--([^=]+)=([\s\S]*)$/);
            var key = m[1];
            var value = m[2];
            if (flags.bools[key]) {
                value = value !== 'false';
            }
            setArg(key, value, arg);
        }
        else if (/^--no-.+/.test(arg)) {
            var key = arg.match(/^--no-(.+)/)[1];
            setArg(key, false, arg);
        }
        else if (/^--.+/.test(arg)) {
            var key = arg.match(/^--(.+)/)[1];
            var next = args[i + 1];
            if (next !== undefined && !/^-/.test(next)
            && !flags.bools[key]
            && !flags.allBools
            && (aliases[key] ? !aliasIsBoolean(key) : true)) {
                setArg(key, next, arg);
                i++;
            }
            else if (/^(true|false)$/.test(next)) {
                setArg(key, next === 'true', arg);
                i++;
            }
            else {
                setArg(key, flags.strings[key] ? '' : true, arg);
            }
        }
        else if (/^-[^-]+/.test(arg)) {
            var letters = arg.slice(1,-1).split('');
            
            var broken = false;
            for (var j = 0; j < letters.length; j++) {
                var next = arg.slice(j+2);
                
                if (next === '-') {
                    setArg(letters[j], next, arg)
                    continue;
                }
                
                if (/[A-Za-z]/.test(letters[j]) && /=/.test(next)) {
                    setArg(letters[j], next.split('=')[1], arg);
                    broken = true;
                    break;
                }
                
                if (/[A-Za-z]/.test(letters[j])
                && /-?\d+(\.\d*)?(e-?\d+)?$/.test(next)) {
                    setArg(letters[j], next, arg);
                    broken = true;
                    break;
                }
                
                if (letters[j+1] && letters[j+1].match(/\W/)) {
                    setArg(letters[j], arg.slice(j+2), arg);
                    broken = true;
                    break;
                }
                else {
                    setArg(letters[j], flags.strings[letters[j]] ? '' : true, arg);
                }
            }
            
            var key = arg.slice(-1)[0];
            if (!broken && key !== '-') {
                if (args[i+1] && !/^(-|--)[^-]/.test(args[i+1])
                && !flags.bools[key]
                && (aliases[key] ? !aliasIsBoolean(key) : true)) {
                    setArg(key, args[i+1], arg);
                    i++;
                }
                else if (args[i+1] && /true|false/.test(args[i+1])) {
                    setArg(key, args[i+1] === 'true', arg);
                    i++;
                }
                else {
                    setArg(key, flags.strings[key] ? '' : true, arg);
                }
            }
        }
        else {
            if (!flags.unknownFn || flags.unknownFn(arg) !== false) {
                argv._.push(
                    flags.strings['_'] || !isNumber(arg) ? arg : Number(arg)
                );
            }
            if (opts.stopEarly) {
                argv._.push.apply(argv._, args.slice(i + 1));
                break;
            }
        }
    }
    
    Object.keys(defaults).forEach(function (key) {
        if (!hasKey(argv, key.split('.'))) {
            setKey(argv, key.split('.'), defaults[key]);
            
            (aliases[key] || []).forEach(function (x) {
                setKey(argv, x.split('.'), defaults[key]);
            });
        }
    });
    
    if (opts['--']) {
        argv['--'] = new Array();
        notFlags.forEach(function(key) {
            argv['--'].push(key);
        });
    }
    else {
        notFlags.forEach(function(key) {
            argv._.push(key);
        });
    }

    return argv;
};

function hasKey (obj, keys) {
    var o = obj;
    keys.slice(0,-1).forEach(function (key) {
        o = (o[key] || {});
    });

    var key = keys[keys.length - 1];
    return key in o;
}

function isNumber (x) {
    if (typeof x === 'number') return true;
    if (/^0x[0-9a-f]+$/i.test(x)) return true;
    return /^[-+]?(?:\d+(?:\.\d*)?|\.\d+)(e[-+]?\d+)?$/.test(x);
}

minimist_license

This software is released under the MIT license:

Permission is hereby granted, free of charge, to any person obtaining a copy of
this software and associated documentation files (the "Software"), to deal in
the Software without restriction, including without limitation the rights to
use, copy, modify, merge, publish, distribute, sublicense, and/or sell copies of
the Software, and to permit persons to whom the Software is furnished to do so,
subject to the following conditions:

The above copyright notice and this permission notice shall be included in all
copies or substantial portions of the Software.

THE SOFTWARE IS PROVIDED "AS IS", WITHOUT WARRANTY OF ANY KIND, EXPRESS OR
IMPLIED, INCLUDING BUT NOT LIMITED TO THE WARRANTIES OF MERCHANTABILITY, FITNESS
FOR A PARTICULAR PURPOSE AND NONINFRINGEMENT. IN NO EVENT SHALL THE AUTHORS OR
COPYRIGHT HOLDERS BE LIABLE FOR ANY CLAIM, DAMAGES OR OTHER LIABILITY, WHETHER
IN AN ACTION OF CONTRACT, TORT OR OTHERWISE, ARISING FROM, OUT OF OR IN
CONNECTION WITH THE SOFTWARE OR THE USE OR OTHER DEALINGS IN THE SOFTWARE.

Dear takvol,

Thank you so much for your code, this is exactly what I am looking for. For some reason I was able to run it but now an error comes up, do you know what could be the issue? I have added my key, spreadsheet ID and the &key command and it has worked before.

TypeError: Cannot read property "elements" from undefined. Dismiss [If I run the Spreadsheet button "Distance measure"]
TypeError: Cannot read property "elements" from undefined. (line 38, file "Code2") Dismiss [If I run "findDistance" in the script]

Thank you in advance for your help!

@Titorelli Advance Google Api Calls cost 0.014 USD each use. You might need to set up billing for your google API key.

@Titorelli @takvol you might also want to change the name of the variable result

Guys, sorry for such a late reply, I completely forgot about these code snippets.
@Titorelli, as @Goldvivi1 mentioned you might have a problem with disabled Distance Matrix API.
I've added ignoreErrors variable, so it can be set to false to see the actual error.
But just for getting distance and time you may write a custom function and use it in the spreadsheet cell like =DISTANCE_AND_TIME(A1, B1)

/**
 * Find distance and driving time.
 *
 * @param {string} origin Starting point.
 * @param {string} destination Finishing point.
 * @return {string[]} distance and driving time.
 * @customfunction
 */
function DISTANCE_AND_TIME(origin, destination) {
  var distance, duration, directions;

  if(!origin || !destination) {
    throw new Error("Invalid arguments");
  }

  directions = Maps.newDirectionFinder()
    .setOrigin(encodeURIComponent(origin))
    .setDestination(encodeURIComponent(destination))
    .setMode(Maps.DirectionFinder.Mode.DRIVING)
    .setLanguage('en')
    .getDirections();
  
  if(directions.status !== "OK") {
    throw new Error(directions.status + ": " + directions.error_message);
  }
  
  distance = directions.routes[0].legs[0].distance.text;
  duration = directions.routes[0].legs[0].duration.text;
  
  return [[distance, duration]];
}

@takvol DM_DISTANCE_AND_TIME How can I pass the arrival time ? Distance Matrix Web Api

@takvol DM_DISTANCE_AND_TIME How can I pass the arrival time ? Distance Matrix Web Api

@takvol Really appreciate if you could help :) I only need to pass the arrival time to the method you've implemented ( * Find distance and driving time using Distance Matrix API.)

@Pathuma I only need to pass the arrival time

@Pathuma, now you may pass command line like arguments, e.g.

=DistanceMatrix("--origins", "Lutsk", "--destinations", "Rivne", "--mode", "transit", "--arrival_time", 1569786972)

Very helpful. @takvol, thank you!

Thanks, guys!

@takvol Thanks so much! I copied and pasted the first set of code into google sheets and put my API key in, but I'm not getting any value results at all. I only need to find the distance and I have "distance measure" up in the toolbar but how exactly does sheets know to put the results under the column that I named distances?

And I am trying to find the driving mile distances between one address in one column and a bunch of addresses in the column next to it. How can I get the results exactly like yours?

@glee345
Note that there are two places where apiKey is set: apiKey = and

var params = minimist(args, {
    'default': {
      key: "YOUR_API_KEY_HERE" //you can set your api key here
    }
  });

next variables are responsible for column positions

  var originColumnNumber = 1;//column with origin location
  var destinationColumnNumber = 2;//column with destination location
  var distanceColumnNumber = 3;
  var durationColumnNumber = 4;

to see an error you can change ignoreErrors in the code from true to false: e.g var ignoreErrors = false;
Make sure distance matrix API is enabled
https://console.cloud.google.com/marketplace/product/google/distance-matrix-backend.googleapis.com
The function is designed to run row by row, so you need to have one starting point and one destination per row, or modify the script accordingly to your needs
You can also just use the script as regular cell function. example: =DISTANCE_AND_TIME(A5,B5) (no API required for this function) or =DM_DISTANCE_AND_TIME(A5,B5) (API required)

Dear takvol,

Thank you so much for your code, this is exactly what I am looking for. Now i need to add a state coloum and country coloum, by adding this thing will be more easy.

Thanks a lot @takvol ! Im trying to use this code to calculate straight distances between two locations, not routes. For example, I want to know the distance between New york and berlin to use for further calculation. But that calculation gives me an error "Error: ZERO_RESULTS (line 119)."

Is it even possible to calculate straight distances or is it always taking routes as defined in google maps (foot, car, transit, etc)?

Best
Micha