【交通費精算シート】！Google スプレッドシート& Google Apps Scriptでシート作成

交通費を一気に確認するため、
交通費の確認部分は手動ですがマスタに入れておけば一括反映可能です
「Google カレンダーからの予定抽出」と「Yahoo!路線情報へのリンク生成」、および「料金マスタによる自動補完」のすべてを統合して、そのまま運用できる形にまとめました。

1. スプレッドシートの構造
1. シート1：精算シート（メインの作業用）
2. シート2：マスタ（料金管理用）
2. Google Apps Script (GAS)
3. 各セルに設定する数式
1. 【マスタシート】
2. 【精算シート】
4. 運用の流れ

1. スプレッドシートの構造

2つのシートを用意します。

シート1：精算シート（メインの作業用）

A列: 日付（GASで自動入力）
B列: 予定タイトル（GASで自動入力）
C列: カレンダー名（GASで自動入力）
D列: （予備）
E列: 出発駅（手入力またはプルダウン）
F列: 到着駅（手入力またはプルダウン）
G列: Yahoo検索リンク（数式で自動生成）
H列: 料金（数式でマスタから自動取得）

シート2：マスタ（料金管理用）

A列: 出発駅（数式で自動抽出）
B列: 到着駅（数式で自動抽出）
C列: 旧料金（手入力）
D列: 新料金（2026/3/1以降・手入力）

2. Google Apps Script (GAS)

スプレッドシートの「拡張機能」＞「Apps Script」に以下のコードを貼り付けて保存してください。JavaScript

/**
 * メニューの作成
 */
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const subMenu = ui.createMenu('📅 カレンダー連携')
      .addItem('予定を取得して更新', 'exportMultipleCalendarsByRange');

  ui.createMenu('🛠️ Myスクリプト')
      .addSubMenu(subMenu)
      .addToUi();
}

/**
 * カレンダーから予定を取得
 */
function exportMultipleCalendarsByRange() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('精算シート'); // シート名を合わせる
  
  const startValue = sheet.getRange("A1").getValue(); // 開始日
  const endValue = sheet.getRange("B1").getValue();   // 終了日
  
  if (!(startValue instanceof Date) || !(endValue instanceof Date)) {
    SpreadsheetApp.getUi().alert("A1とB1に正しい日付を入力してください。");
    return;
  }

  const calendarIds = [
    'primary', // メインカレンダー
    // 'example@gmail.com' // 追加があればここに入れる
  ];

  const startTime = new Date(startValue);
  const endTime = new Date(endValue);
  endTime.setHours(23, 59, 59);

  const results = [];
  calendarIds.forEach(id => {
    const calendar = CalendarApp.getCalendarById(id);
    if (calendar) {
      const events = calendar.getEvents(startTime, endTime);
      events.forEach(event => {
        const date = Utilities.formatDate(event.getStartTime(), "JST", "yyyy/MM/dd");
        const title = event.getTitle();
        results.push([date, title, calendar.getName()]);
      });
    }
  });

  if (results.length > 0) {
    results.sort((a, b) => new Date(a[0]) - new Date(b[0]));
    const lastRow = sheet.getLastRow();
    if (lastRow > 1) {
      sheet.getRange(2, 1, lastRow - 1, 3).clearContent();
    }
    sheet.getRange(2, 1, results.length, 3).setValues(results);
  }
}

3. 各セルに設定する数式

【マスタシート】

A2セルに入力（一意の区間リストを自動作成）：Excel

=UNIQUE(FILTER({'精算シート'!E2:E, '精算シート'!F2:F}, '精算シート'!E2:E <> ""))

【精算シート】

G2セルに入力（Yahoo!検索リンク生成）：Excel

=ARRAYFORMULA(IF((E2:E<>"") * (F2:F<>""), HYPERLINK("https://transit.yahoo.co.jp/search/result?from="&ENCODEURL(E2:E)&"&to="&ENCODEURL(F2:F)&"&y="&TEXT(A2:A,"yyyy")&"&m="&TEXT(A2:A,"mm")&"&d="&TEXT(A2:A,"dd")&"&hh=09&m1=0&m2=0&type=1&ticket=ic&expkind=1&userpass=1&ws=3&s=0&al=1&shin=0&ex=0&hb=0&lb=0&sr=1", "Yahoo検索"), ""))

H2セルに入力（改定日に応じた料金の自動取得）：Excel

=ARRAYFORMULA(IF((E2:E<>"") * (F2:F<>""), IFERROR(VLOOKUP(E2:E&F2:F, {マスタ!A2:A&マスタ!B2:B, マスタ!C2:D}, IF(A2:A < DATE(2026, 3, 1), 2, 3), FALSE), "料金未登録"), ""))

4. 運用の流れ

準備: 精算シートの A1 に開始日、B1 に終了日を入力します。
取得: メニューの「Myスクリプト」→「予定を取得して更新」を実行します。
入力: E列・F列に出発駅と到着駅を入力します。
確認/登録:
- 初めての区間なら、G列のリンクから料金を確認します。
- マスタシートにその区間が自動追加されているので、C列・D列に料金を入力します。
完了: 以降、同じ区間を入力すれば、H列に日付に応じた適切な料金が自動反映されます。