GAS(Google Apps Script)とスプレッドシート Part3(クイックスタート編)

function onOpen() {
  var spreadsheet = SpreadsheetApp.getActive();
  var menuItems = [
    {name: 'Prepare sheet...', functionName: 'prepareSheet_'},  // prepareSheet_...下記で定義
    {name: 'Generate step-by-step...', functionName: 'generateStepByStep_'}  // generateStepByStep_...下記で定義
  ];
  spreadsheet.addMenu('Directions', menuItems);
}

function prepareSheet_() {
  // アクティブなシートの名称を変更
  var sheet = SpreadsheetApp.getActiveSheet().setName('Settings');

  // 各列の見出し
  var headers = [
    'Start Address',
    'End Address',
    'Driving Distance (meters)',
    'Driving Distance (miles)'];
  
  // 初期データ
  var initialData = [
    '350 5th Ave, New York, NY 10118',
    '405 Lexington Ave, New York, NY 10174'];
  
  // セルへのデータの入力とフォーマットの整形
  sheet.getRange('A1:D1').setValues([headers]).setFontWeight('bold');  // 見出しの入力とフォントウェイトの設定
  sheet.getRange('A2:B2').setValues([initialData]);                    // 初期データの入力
  sheet.setFrozenRows(1);                                              // 固定表示列の設定
  sheet.autoResizeColumns(1, 4);                                       // 列サイズの変更
}

function generateStepByStep_() {
  var spreadsheet = SpreadsheetApp.getActive();
  var settingsSheet = spreadsheet.getSheetByName('Settings');
  settingsSheet.activate();

  // ユーザーに行番号の記入を促す
  var selectedRow = Browser.inputBox('Generate step-by-step',
      'Please enter the row number of the addresses to use' +
      ' (for example, "2"):',
      Browser.Buttons.OK_CANCEL);  // OKとキャンセルの両方のボタンを表示する
  
  // キャンセルの場合('cancel'と入力) => 何も返さない
  if (selectedRow == 'cancel') {
    return;
  }
  var rowNumber = Number(selectedRow);  // 数値型に変換
  
  // 入力値がNaNである場合 or 2未満である場合 => 何も返さない
  if (isNaN(rowNumber) || rowNumber < 2 ||
      rowNumber > settingsSheet.getLastRow()) {
    Browser.msgBox('Error',
        Utilities.formatString('Row "%s" is not valid.', selectedRow),  // promptのテキストの生成
        Browser.Buttons.OK);  // OKボタンのみ
    return;
  }

  // 指定された行の住所を取得
  var row = settingsSheet.getRange(rowNumber, 1, 1, 2);
  var rowValues = row.getValues();
  var origin = rowValues[0][0];  // 出発地
  var destination = rowValues[0][1];  // 目的地
  
  // 出発地 or 目的地のどちらかが入力されていない場合 => エラーメッセージを出し終わり
  if (!origin || !destination) {
    Browser.msgBox('Error', 'Row does not contain two addresses.',
        Browser.Buttons.OK);
    return;
  }

  // 列の出発地、目的地情報を取得(getDirections_関数の実行)
  var directions = getDirections_(origin, destination);

  // 新しいシートを作り、出発地から目的地までの細かなルートを入力
  var sheetName = 'Driving Directions for Row ' + rowNumber;
  var directionsSheet = spreadsheet.getSheetByName(sheetName);
  
  if (directionsSheet) {  // directionSheetが存在する場合
    directionsSheet.clear();
    directionsSheet.activate();
  } else {                // directionSheetが存在しない場合
    directionsSheet = spreadsheet.insertSheet(sheetName, spreadsheet.getNumSheets());
  }
  var sheetTitle = Utilities.formatString('Driving Directions from %s to %s',
      origin, destination);
  var headers = [
    [sheetTitle, '', ''],  // 1行目
    ['Step', 'Distance (Meters)', 'Distance (Miles)']  // 2行目
  ];
  // 各経路を格納するための配列
  var newRows = [];
  
  // 経路のステップの数だけ繰り返し、それぞれの距離を求める
  for (var i = 0; i < directions.routes[0].legs[0].steps.length; i++) {
    var step = directions.routes[0].legs[0].steps[i];
    // html_instructionsからHTMLタグを削除し、
    var instructions = step.html_instructions.replace(/<br>|<div.*?>/g, '\n')
        .replace(/<.*?>/g, '');
    // 経路を格納する配列に追加
    newRows.push([
      instructions,
      step.distance.value
    ]);
  }
  // シートに経路を入力
  directionsSheet.getRange(1, 1, headers.length, 3).setValues(headers);
  directionsSheet.getRange(headers.length + 1, 1, newRows.length, 2)
      .setValues(newRows);
  directionsSheet.getRange(headers.length + 1, 3, newRows.length, 1)
      .setFormulaR1C1('=METERSTOMILES(R[0]C[-1])');

  // シートの整形
  directionsSheet.getRange('A1:C1').merge().setBackground('#ddddee');
  directionsSheet.getRange('A1:2').setFontWeight('bold');
  directionsSheet.setColumnWidth(1, 500);
  directionsSheet.getRange('B2:C').setVerticalAlignment('top');
  directionsSheet.getRange('C2:C').setNumberFormat('0.00');
  var stepsRange = directionsSheet.getDataRange()
      .offset(2, 0, directionsSheet.getLastRow() - 2);
  setAlternatingRowBackgroundColors_(stepsRange, '#ffffff', '#eeeeee');  // 行の背景色を奇数と偶数で入れ替える(下記で定義)
  directionsSheet.setFrozenRows(2);
  SpreadsheetApp.flush();
}

function getDirections_(origin, destination) {
  //
  var directionFinder = Maps.newDirectionFinder();
  directionFinder.setOrigin(origin);
  directionFinder.setDestination(destination);
  var directions = directionFinder.getDirections();
  if (directions.status !== 'OK') {
    throw directions.error_message;
  }
  return directions;
}

{"routes":[
    {"summary":"E 32nd St and Third Ave",
     "copyrights":"Map data ©2019 Google",
     "legs":[
        {"duration":{"text":"10 mins",
                     "value":581},
         "start_location":{"lng":-73.98481629999999,
                           "lat":40.74811270000001},
         "distance":{"text":"1.1 mi",
                     "value":1814},
         "start_address":"350 5th Ave, New York, NY 10118, USA",
         "end_location":{"lng":-73.9757362,
                         "lat":40.7519078},
         "end_address":"405 Lexington Ave, New York, NY 10174, USA",
         "via_waypoint":[],
         "steps":[
            {"duration":{"text":"1 min",
                         "value":30},
             "start_location":{"lng":-73.98481629999999,
                               "lat":40.74811270000001},
              "distance":{"text":"394 ft",
                          "value":120},
              "travel_mode":"DRIVING",
              "html_instructions":"Head southwest on 5th Ave toward W 33rd St",
              "end_location":{"lng":-73.985504,
                              "lat":40.7471648},
              "polyline":{"points":"uruwFbdqbMbAp@xBtA"}
             },
             
             (中略)

         ],
         "traffic_speed_entry":[]
        }
    ],
    "warnings":[],
    "bounds":{"southwest":{"lng":-73.985504,
                           "lat":40.744452},
              "northeast":{"lng":-73.9739561,
                           "lat":40.7521288}
             },
    "overview_polyline":{"points":"uruwFbdqbM|DfCfCcIjFoPzBgHl@iByByAcBkAgDeAiD{BmGgEeDyB_Am@M^s@zB}@pCGRRNVP"},
    "waypoint_order":[]
   }
 ],
 "geocoded_waypoints":[
     {"types":["street_address"],
      "geocoder_status":"OK",
      "place_id":"ChIJn6wOs6lZwokRLKy1iqRcoKw"
     },
     {"types":["street_address"],
      "geocoder_status":"OK",
      "place_id":"ChIJAe73SQJZwokRHTRcoQpAhF8"
     }
 ],
 "status":"OK"
}

function setAlternatingRowBackgroundColors_(range, oddColor, evenColor) {
  var backgrounds = [];
  // 変数rowが実際の行数になるまで繰り返し
  for (var row = 1; row <= range.getNumRows(); row++) {
    var rowBackgrounds = [];
    // 変数columnが実際の列数になるまで繰り返し
    for (var column = 1; column <= range.getNumColumns(); column++) {
      // 列の背景色情報を配列rowBackgroundsに追加
      if (row % 2 == 0) {
        rowBackgrounds.push(evenColor);
      } else {
        rowBackgrounds.push(oddColor);
      }
    }
    // 行の背景色情報を配列backgroundsに追加
    backgrounds.push(rowBackgrounds);
  }
  range.setBackgrounds(backgrounds);
}

function drivingDistance(origin, destination) {
  var directions = getDirections_(origin, destination);  // プライベート関数のgetDirections_関数を実行
  return directions.routes[0].legs[0].distance.value;
}

function metersToMiles(meters) {
  if (typeof meters != 'number') {
    return null;
  }
  return meters / 1000 * 0.621371;
}