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.

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