Magazine

本記事の概要

Googleカレンダーに予定を手動で登録するのが面倒くさい…
そんな悩みを解決するために、GoogleスプレッドシートとApps Scriptを使った「予定管理ツール」を自作しました！
本記事では、複数招待者の空き状況を自動で取得してくる機能について解説します。

ターゲット

• Google Workspaceを活用している企業の管理者・エンジニア
• 社内予定調整・会議予約の工数を削減したい方
• Google App Script(GAS)で業務効率化をしたい方

Googleカレンダー連携予定管理ツールとは？

この「予定管理ツール」は、GoogleスプレッドシートとApps Script（GAS）を組み合わせて作った業務自動化ツールです。

予定の入力はスプレッドシート、
登録・通知はGoogleカレンダー、
全てをワンクリックで完結できます。

シート構成と役割

シート名	役割
予定管理	登録予定の一覧（手入力 or 空き時間から自動反映）
登録一覧	カレンダーに登録済みの予定履歴
招待者マスタ	社員名とメールアドレスの対応表（自動取得）
会議室マスタ	会議室名とGoogleカレンダーIDの一覧（自動取得）
空き状況	招待者の空き時間を自動出力するためのワークシート

機能要件

①スプレッドシートUI機能

機能	内容
カスタムメニュー追加	シート起動時にメニューバーへ「空き状況確認」メニューを追加
空き状況取得	招待者の予定を確認し、空いている時間を30分単位で可視化
予定反映	空き時間の候補を選択して、予定管理シートへ自動入力

②Googleカレンダー連携機能

機能	内容
カレンダー登録	シート上の予定情報からGoogleカレンダーにイベント登録（Meetリンク付き）
招待者追加	招待者マスタを参照してメールアドレスに変換し、ゲストとして自動追加
会議室予約	会議室マスタのメールを使用してイベントに会議室を招待
イベントID保存	作成されたイベントのIDを履歴としてスプレッドシートに記録
入力行削除	カレンダー登録完了後、予定管理シートから該当行を削除

③履歴メンテナンス機能

機能	内容
終了イベント削除	終了日時が過去のイベントは履歴シートから自動削除
カレンダー削除検知	カレンダー上から削除されたイベントも履歴から削除
毎日トリガー実行	上記処理を毎日午前2時に実行するトリガーを自動作成

④マスタメンテナンス機能

機能	内容
招待者マスタ更新	Google Workspace のユーザー一覧を取得してシートに自動反映
会議室マスタ更新	会議室リソースを一覧取得し、メールアドレスとともにシートに反映
自動トリガー作成	毎日午前3時にマスタを更新するトリガーを自動生成

実装コード解説

①カスタムメニューの追加(onOpen())

function onOpen() {
  SpreadsheetApp.getUi()
    .createMenu('空き状況確認')
    .addItem('選択行の空き状況取得', 'showFreeBusyForRow')
    .addItem('選択スロットを予定管理に反映', 'applyFreeSlotsToInput')
    .addToUi();
}

解説

• onOpen()はGASにおけるスプレッドシートの「カスタムメニュー追加」などに使う代表的なトリガー関数。ユーザーがスプレッドシートを開いたときに自動的に実行される
• SpreadsheetApp.getUi()→スプレッドシートのUIにアクセス
• createMenu→ユーザー定義のメニューに追加
• GAS独自のカスタムメニュー機能により、クリックで関数実行が可能になる

実装後イメージ

②空きスロットの取得(Googleカレンダー連携)

function showFreeBusyForRow() {
  const ss    = SpreadsheetApp.getActiveSpreadsheet();
  const input = ss.getSheetByName(SHEET_INPUT);
  const ui    = SpreadsheetApp.getUi();
  const sel   = input.getActiveRange();
  if (!sel || sel.getSheet().getName() !== SHEET_INPUT) {
    ui.alert('予定管理シートでデータ行を選択してください');
  return;
  }
  const row = sel.getRow();
  if (row < 2) {
    ui.alert('データ行を選択してください');
    return;
  }

  // デフォルト設定
  const defaultStart = new Date(); defaultStart.setHours(0,0,0,0);
  const defaultDays  = 7;
  const defaultFromH = 9, defaultFromM = 0;
  const defaultToH   = 20, defaultToM   = 0;

  // 開始日時(I3) と 期間日数(J3)
  let rawStart = input.getRange('I3').getValue();
  let timeMin  = rawStart instanceof Date? new Date(rawStart): rawStart? new Date(rawStart): defaultStart;
  let rawDays  = input.getRange('J3').getValue();
  let days     = rawDays? Number(rawDays): defaultDays;
  let timeMax  = new Date(timeMin); timeMax.setDate(timeMin.getDate()+days);

  // 時間帯(K3,L3)
  const parseHM = (v,defH,defM)=>{
    if (v instanceof Date) return [v.getHours(),v.getMinutes()];
    if (typeof v==='string' && v.includes(':')) {
      const [h,m]=v.split(':').map(x=>parseInt(x,10)||0);
      return [h,m];
    }
    return [defH,defM];
  };
  let [fh,fm] = parseHM(input.getRange('K3').getValue(), defaultFromH, defaultFromM);
  let [th,tm] = parseHM(input.getRange('L3').getValue(), defaultToH,   defaultToM  );
  const fromTotalMin = fh * 60 + fm;
  const toTotalMin   = th * 60 + tm;

  // 招待者取得 (G列)
  const inviteeStr = input.getRange(row,7).getValue();
  if (!inviteeStr) { ui.alert('招待者セルが空です'); return; }
  const names      = inviteeStr.toString().split(',').map(s=>s.trim()).filter(s=>s);
  const inviteeMap = buildMap(ss, INVITEE_MASTER,1,2);
  const attendees  = names.map(n=>inviteeMap[n]).filter(e=>e);
  if (!attendees.length) { ui.alert('招待者マスタに該当がありません'); return; }

  // FreeBusy.query
  const fbReq  = { timeMin: timeMin.toISOString(), timeMax: timeMax.toISOString(), items: attendees.map(e=>({id:e})) };
  const fbResp = Calendar.Freebusy.query(fbReq);

  // busy→free slots
  const busy      = attendees.flatMap(e=>fbResp.calendars[e].busy||[]).map(p=>({start:new Date(p.start),end:new Date(p.end)}));
  const slotIntervalCell = input.getRange('M3').getValue();
  const slotMin = slotIntervalCell
    ? Number(slotIntervalCell)
    : 30;
  const freeSlots = calcFreeSlots(busy, timeMin, timeMax, slotMin)
  .filter(s=>{
  const m  = s.start.getHours()*60 + s.start.getMinutes();
  return m>=fromTotalMin && m+slotMin<=toTotalMin;
  });

  // 出力
  let out = ss.getSheetByName('空き状況');
  if (!out) out = ss.insertSheet('空き状況');
  // ヘッダー以外の値をまるっとクリア
  const maxRow = out.getMaxRows();
  if (maxRow > 1) {
    // A2:C<最終行> の内容を消す
    out.getRange(2, 1, maxRow - 1, 3).clearContent();
    // A2:A<最終行> のチェックボックス設定を消す
    out.getRange(2, 1, maxRow - 1, 1).clearDataValidations();
  }

  // フリー枠を書き込む
  if (freeSlots.length > 0) {
    const outputValues = freeSlots.map(s => [
      false,
      s.start,
      s.end
    ]);
    out.getRange(2, 1, outputValues.length, 3).setValues(outputValues);
    // ③ 書き込んだ行だけチェックボックスを再設定
    out.getRange(2, 1, outputValues.length, 1).insertCheckboxes();
  }
  ui.alert(`空きスロットを${freeSlots.length}件出力しました`);
}

解説１：基本定数の定義と選択行のチェック

const ss    = SpreadsheetApp.getActiveSpreadsheet();
const input = ss.getSheetByName(SHEET_INPUT);
const ui    = SpreadsheetApp.getUi();
const sel   = input.getActiveRange();

• ss：現在アクティブなスプレッドシートを取得
• input：予定管理用のシート（例：‘予定管理’）を取得
• ui：UIダイアログ用のオブジェクト
• sel：現在選択されているセルの範囲

if (!sel || sel.getSheet().getName() !== SHEET_INPUT) {
  ui.alert('予定管理シートでデータ行を選択してください');
  return;
}
const row = sel.getRow();
if (row < 2) {
  ui.alert('データ行を選択してください');
  return;
}

• 選択セルが正しいシート・データ行であることをチェック。間違っていると警告を出して処理終了。

解説２：検索範囲の開始日時・終了日時の取得(I3,J3)

const defaultStart = new Date(); defaultStart.setHours(0,0,0,0);
const defaultDays  = 7;

• デフォルトは「今日0:00」から7日間。

let rawStart = input.getRange('I3').getValue();
let timeMin  = rawStart instanceof Date ? new Date(rawStart) : defaultStart;

let rawDays  = input.getRange('J3').getValue();
let days     = rawDays ? Number(rawDays) : defaultDays;
let timeMax  = new Date(timeMin); timeMax.setDate(timeMin.getDate() + days);

• I3 に開始日、J3 に日数が入力されていれば使用し、未入力ならデフォルト使用。

解説３：時間帯の取得(K3:開始時刻、L3：終了時刻)

const parseHM = (v,defH,defM)=>{
    if (v instanceof Date) return [v.getHours(),v.getMinutes()];
    if (typeof v==='string' && v.includes(':')) {
      const [h,m]=v.split(':').map(x=>parseInt(x,10)||0);
      return [h,m];
    }
    return [defH,defM];
  };

引数の意味

• v… 時刻情報(Date型または”10:30″などの文字列、または未入力)
• defH… デフォルトの「時」(vが無効だった時に使用)
• defM… デフォルトの「分」(vが無効だった時に使用)

処理の流れ

1. v instanceof Date

• 入力 v が Date オブジェクトなら、getHours() と getMinutes() で [時, 分] を返します。

例：
parseHM(new Date(“2025-08-05T14:45:00”), 9, 0)  // → [14, 45]

2. typeof v === ‘string’ && v.includes(“:”)

• 入力 v が “14:30” のような文字列なら、それを : で分割して、parseInt で整数に変換します。
• 不正な文字列でも parseInt(x, 10) || 0 によって0に変換されるため、安全です。

例：
parseHM(“10:45”, 9, 0) // → [10, 45]
parseHM(“abc:99″, 9, 0) // → [0, 99]（”abc” は parseIntでNaN → 0 に）

3. それ以外はデフォルト値を返す

return [defH, defM];

• 日時が未入力だったり、文字列が “abc” のように完全に無効だった場合など。

例：
parseHM(“”, 9, 0) // → [9, 0]

解説４：招待者(G列)を取得し、メールアドレスに変換

const inviteeStr = input.getRange(row, 7).getValue();
if (!inviteeStr) { ui.alert('招待者セルが空です'); return; }

const names = inviteeStr.toString().split(',').map(s => s.trim()).filter(s => s);
const inviteeMap = buildMap(ss, INVITEE_MASTER, 1, 2);
const attendees = names.map(n => inviteeMap[n]).filter(e => e);
if (!attendees.length) { ui.alert('招待者マスタに該当がありません'); return; }

• G列には 山田太郎, 佐藤花子 のように招待者名が入っている想定。
• buildMap() で「名前 → メールアドレス」マップを作り、各招待者のアドレスを抽出。
• マスタに存在しない名前は無視。

解説５：GoogleカレンダーAPIでFreeBusy(予定あり)取得

const fbReq  = {
  timeMin: timeMin.toISOString(),
  timeMax: timeMax.toISOString(),
  items: attendees.map(e => ({ id: e }))
};
const fbResp = Calendar.Freebusy.query(fbReq);

• 複数人のカレンダーの「予定あり時間（busy）」を取得。

解説６：空きスロットの計算

const busy = attendees.flatMap(e => fbResp.calendars[e].busy || [])
  .map(p => ({ start: new Date(p.start), end: new Date(p.end) }));

const slotIntervalCell = input.getRange('M3').getValue();
const slotMin = slotIntervalCell ? Number(slotIntervalCell) : 30;

• 予定のある時間（busy）をまとめて、30分単位などで区切った空き時間（free）を計算。

const freeSlots = calcFreeSlots(busy, timeMin, timeMax, slotMin).filter(s => {
  const m = s.start.getHours() * 60 + s.start.getMinutes();
  return m >= fromTotalMin && m + slotMin <= toTotalMin;
});

• 指定時間帯の中に収まるスロットだけ抽出。

解説７：結果を「空き状況」シートに出力

let out = ss.getSheetByName('空き状況');
if (!out) out = ss.insertSheet('空き状況');

• 出力先の 空き状況 シートが存在しない場合は新規作成。

if (freeSlots.length > 0) {
  const outputValues = freeSlots.map(s => [false, s.start, s.end]);
  out.getRange(2, 1, outputValues.length, 3).setValues(outputValues);
  out.getRange(2, 1, outputValues.length, 1).insertCheckboxes();
}

// A2:C<最終行>の値とチェックボックスをクリア
const maxRow = out.getMaxRows();
if (maxRow > 1) {
  out.getRange(2, 1, maxRow - 1, 3).clearContent();
  out.getRange(2, 1, maxRow - 1, 1).clearDataValidations();
}

• 空きスロットを A列：チェックボックス、B列：開始時刻、C列：終了時刻として出力。
• チェックボックスは再度挿入されるので再利用可能。

解説８：完了ダイアログ

ui.alert(`空きスロットを${freeSlots.length}件出力しました`);