一括で2地点の高速道と一般道の距離を算出するGAS　

2地点の距離が分かる

交通費の水増し申請をしている社員を見つけるために作りました。
本来は10kmの会社までの距離を15kmで申請している人はいませんか？
そんな輩を見つけるコードです。
一般道と高速道でわけて算出します。

このGSの使い方

A列とB列に住所を入力して、メニューバーにある「距離計算」から「距離を計算」を押すとC列に高速道の距離、D列に一般道の距離が算出されます。
住所が有名なところなら名称でもOKです。

GASとは

GASは簡単に言えばグーグルが提供しているプログラミング言語です。無料で利用できます。

シフト表のサンプルコード

GASをある程度知っている方…下のコードをコピペすれば完了です。
GASをあまりよく分からない方…コードの下に説明を詳しく入れています。ご参照ください。

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu("距離計算")
    .addItem("距離を計算", "getDistance")
    .addToUi();
}

// 2地点間の距離を計算する関数
function calcDistance(src, dest) {
  var directionFinder = Maps.newDirectionFinder()
    .setOrigin(src)
    .setDestination(dest)
    .setMode(Maps.DirectionFinder.Mode.DRIVING)
    .setLanguage("ja");
  var directions = directionFinder.getDirections();
  var route0 = directions.routes[0]; // 一般道のルート

  directionFinder.setAvoid(Maps.DirectionFinder.Avoid.HIGHWAYS);
  directions = directionFinder.getDirections();
  var route1 = directions.routes[0]; // 高速道路を避けたルート

  if (!route1) {
    return [-1, -1]; // ルートが見つからない場合
  }

  // [一般道の距離, 高速道路の距離]を返す
  return [route1.legs[0].distance.value / 1000, route0.legs[0].distance.value / 1000];
}

// スプレッドシートに2地点間の距離とタイムスタンプを入力
function getDistance() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = ss.getActiveSheet();

  // A1, B1, C1, D1, E1のヘッダー
  if (!activeSheet.getRange("A1").getValue()) activeSheet.getRange("A1").setValue("出発地点");
  if (!activeSheet.getRange("B1").getValue()) activeSheet.getRange("B1").setValue("目的地");
  if (!activeSheet.getRange("C1").getValue()) activeSheet.getRange("C1").setValue("一般道距離 (km)");
  if (!activeSheet.getRange("D1").getValue()) activeSheet.getRange("D1").setValue("高速道路距離 (km)");
  if (!activeSheet.getRange("E1").getValue()) activeSheet.getRange("E1").setValue("タイムスタンプ");

  // シートデータを取得
  var dat = activeSheet.getDataRange().getValues();
  var routes = [];
  var now = new Date();
  var formattedTimestamp = Utilities.formatDate(now, Session.getScriptTimeZone(), "yyyy/MM/dd HH:mm");

  // タイムスタンプの欠落行をチェック
  var missingTimestampRows = [];

  // データ行ごとに距離を計算し結果を配列に追加
  for (var i = 1; i < dat.length; i++) {
    var src = dat[i][0]; // A列
    var dest = dat[i][1]; // B列
    var currentTimestamp = dat[i][4]; // E列のタイムスタンプ

    // 出発地点または目的地が空の場合、空の値を追加
    if (!src || !dest) {
      routes.push(["", "", ""]);
      continue;
    }

    // タイムスタンプがない行
    if (!currentTimestamp) {
      missingTimestampRows.push(i + 1); // 1行目はヘッダーなので、行番号を +1
    }

    var r = calcDistance(src, dest);
    var highwayDistance = r[0] >= 0 ? r[0].toLocaleString("en-US", { minimumFractionDigits: 2, maximumFractionDigits: 2 }) : "N/A";
    var regularDistance = r[1] >= 0 ? r[1].toLocaleString("en-US", { minimumFractionDigits: 2, maximumFractionDigits: 2 }) : "N/A";
    
    routes.push([regularDistance, highwayDistance, formattedTimestamp]); // 一般道をC列、高速道路をD列に
  }

  // 結果をC, D, E列に書き込む
  var outputRange = activeSheet.getRange(2, 3, dat.length - 1, 3);
  outputRange.setValues(routes);

  // タイムスタンプがない行をログに出力
  if (missingTimestampRows.length > 0) {
    Logger.log("タイムスタンプが欠けている行: " + missingTimestampRows.join(", "));
  }
}

GASの基本から

Dドライブにアクセスして、スプレッドシートを開きます。
拡張機能からAppScriptを選択します。

下のような画面が出てきたら、function myfunctionと書かれている部分をすべて消します。

次に上記のコードをすべて選択して貼り付けます。
保存を押し、実行を押します。

以下、赤印で囲んだところを選択

実行ログに実行完了と表示されたら、スプレッドシートを見て下さい。
カスタムメニューという項目が増えています。このファイルを開けば、次回以降も表示されます。他のファイルには影響はありません。