Googleカレンダーの予定から工数管理を自動化！GASとLooker Studioで実現する効率的な稼働管理システム

はじめに

以前、Chrome拡張機能でGoogleカレンダーの予定登録を効率化するという記事を書きました。

今回はその発展として、Googleカレンダーに登録した情報を活用し、チーム全体の稼働工数の管理・可視化を自動化する仕組みを開発しましたのでご紹介します。

背景

弊社では、もともとスプレッドシートで稼働工数の予実管理を実施していました。
スプレッドシートへの手入力による工数実績・予実管理は、担当者にとって手間と時間がかかる作業でした。特に、実績の稼働工数を後からまとめて入力するケースが多く、その際にはGoogleカレンダー上の予定を元に工数を把握・記録しているメンバーもいました。結果として、Googleカレンダーへの入力とスプレッドシートへの入力という二重の手間が発生していました。

この課題を解決するため、GoogleカレンダーとGoogleスプレッドシート、Google Apps Script(以降GAS)、Looker Studioを組み合わせた工数管理システムを開発しました。このシステムは、Googleカレンダーの入力内容を自動でスプレッドシートに反映するものです。これにより、カレンダーに入力するだけで工数管理が完結し、作業の二重入力を解消し、工数管理を効率化しました。

さらに、スプレッドシートに反映されたデータをLooker Studioで可視化することで、チーム全体の稼働状況を直感的に把握することが可能になりました。

完成イメージ

Looker Studio上の表示

収集した工数データをLooker Studioで可視化すると、このように直感的に状況を把握できるようになります。

システムの概要と機能紹介

システムイメージ

全体の流れ

1. Googleカレンダーへの登録
   システムを利用するには、Googleカレンダーに特定の命名規則でイベントを登録します。この規則は「【カテゴリ名】タスク名：詳細」という形式です。

   • カテゴリ：【】で囲まれた部分
   • タスク：最初の：（コロン）までの部分（全角・半角コロンに対応）
   • 詳細：コロン以降の部分

   この記事に掲載されている拡張機能を活用すれば、この命名規則に沿ったイベント登録がより簡単になります。

2. GASスクリプトによるデータ取得と解析
   スクリプトは、カレンダーイベントを取得後、以下の処理を自動で行います。

   • イベントのフィルタリング：【で始まるタイトルで、かつ】の後にコロンが含まれるイベントのみを対象とします。
   • 情報抽出と工数計算：命名規則に基づいてカテゴリ、タスク、詳細を抽出し、イベントの所要時間から工数を算出します。
   • キーワードの除外：「settings」シートに設定されたキーワードを含むイベントは、工数集計から自動で除外されます。

3. スプレッドシートへの自動記録と集計
   解析されたデータは、スプレッドシートに自動で記録・集計されます。

   • 個人用シートへの記録：データは、各ユーザーのメールアドレスに対応した個別のシートに記録されます。選択した期間のデータが、最新の情報で更新（上書き）されます。
     

   • 全体集計シートへの反映：全ユーザーのデータを「summary」シートにまとめられます。これにより、Looker Studio等で可視化が容易になります。
     

   個人用と全ユーザーのシートをあえて分けているのは、意図しないGoogleカレンダーの情報を読み取ってしまった時など、スプレッドシート上での修正も許容する運用にしているためです。全ユーザーのシートはなるべくユーザーに直接触らせないために、2段階の処理としました。

4. データの可視化

   • データの可視化：スプレッドシートのデータをLooker Studioと連携させることで、工数データを分かりやすく整理し、直感的に状況を把握できます。
     

全体構造

本記事で作成したGASのフォルダ構成は以下の通りになっています。

Google Apps Script //GASの実装
├── actionFunctions.gs
├── calendarService.gs
├── combineData.gs
├── const.gs
├── dateSelector.html
├── main.gs
└── spreadsheetService.gs

利用準備

スプレッドシートの中身

初期設定をスプレッドシートに記録してあります。

• B列：各社員のGoogleカレンダーのメールアドレスを記入します。
• C列：B列のメールアドレスに対応する、各社員の記録用シート名を記入します。
• E列目以降：B列のメールアドレスに対応する行に、工数としてカウントしたくない除外キーワードを1セルずつ記入します。たとえば、「休憩」「ランチ」「私用」といったキーワードです。

これらの設定をすることで、各ユーザーのデータが適切なシートに記録され、不要なイベントが自動で除外されるようになります。

GASの実装・コードの詳細

スプレッドシートの「拡張機能」⇒「Apps Script」⇒「エディタ」から以下の7つのファイルを追加し、保存してください。

const.gs

このファイルでは、GASで使用するスプレッドシートに関する設定情報を定数として定義しています。

const.gs

const SETTING_SHEET_NAME = 'settings';
const SUMMARY_SHEET_NAME = 'summary';
const EMAIL_COLUMN_INDEX = 2;
const SHEET_NAME_COLUMN_INDEX = 3;
const SETTING_SHEET_IGNORE_PHRASE_COL_START = 5;
const TYPE_COLUMN_INDEX = 0;
const DATE_COLUMN_INDEX = 1;
const USER_ID_COLUMN_INDEX = 6;
const HEADERS = ["type", 'date', 'category', 'task', 'detail', 'working_hours', 'user_id', 'record_date', 'user_name'];

main.gs

このファイルでは、アプリケーションの主要な処理フローを管理しています。具体的には、ユーザーがスプレッドシートのメニューから操作を開始した際の、初期ダイアログ表示からデータ書き込みまでの流れを制御します。

コード

main.gs

function showDateSelectionDialog(planOrActual) {
  const template = HtmlService.createTemplateFromFile('dateSelector');
  template.planOrActual = planOrActual;

  const htmlOutput = template.evaluate()
    .setWidth(450)
    .setHeight(400)
    .setSandboxMode(HtmlService.SandboxMode.IFRAME);

  SpreadsheetApp.getUi().showModalDialog(htmlOutput, `${planOrActual}を取り込む期間の選択`);
}

function processSelectedDates(startDateStr, endDateStr, planOrActualFromHtml) {
  const planOrActual = planOrActualFromHtml;

  if (!planOrActual) {
    console.error("planOrActualがHTMLから正しく渡されませんでした。");
    return "エラー: タイプが不明です。再度メニューから選択してください。";
  }

  let startDate = new Date(startDateStr);
  startDate.setHours(0, 0, 0, 0);

  let endDate = new Date(endDateStr);
  endDate.setHours(23, 59, 59, 999);

  if (isNaN(startDate.getTime()) || isNaN(endDate.getTime())) {
    return '日付の形式が正しくありません。例：YYYY-MM-DD の形式で入力してください。';
  }
  if (startDate > endDate) {
    return '終了日は開始日以降の日付を入力してください。（開始日と同じ日付でも可）';
  }

  const today = new Date();
  today.setHours(0, 0, 0, 0);
  const todayEndOfDay = new Date();
  todayEndOfDay.setHours(23, 59, 59, 999);

  let effectiveStartDate = startDate;
  let effectiveEndDate = endDate;
  let includePastEventsForPlan = false;

  if (planOrActual === '予定') {
    if (endDate < today) {
      const confirmResponse = SpreadsheetApp.getUi().alert(
        '過去の予定を取り込むかの確認',
        '指定された期間は今日よりも前です。過去のイベントを予定として記録しますか？\n (「OK」を選択すると、期間内の全てのイベントを予定として記録します。)',
        SpreadsheetApp.getUi().ButtonSet.YES_NO
      );
      if (confirmResponse === SpreadsheetApp.getUi().Button.YES) {
        includePastEventsForPlan = true;
      } else {
        return '期間に今日以降の日付を含めるか、過去の予定を取り込むかの確認時に「OK」を選択してください。';
      }
    } else if (startDate < today) {
      const confirmResponse = SpreadsheetApp.getUi().alert(
        '過去の予定を取り込むかの確認',
        '指定された期間には今日よりも前の日付が含まれます。過去のイベントを予定として記録しますか？\n (「OK」を選択すると、期間内の全てのイベントを予定として記録します。「いいえ」を選択すると、今日以降のイベントのみを予定として記録します。)',
        SpreadsheetApp.getUi().ButtonSet.YES_NO
      );
      if (confirmResponse === SpreadsheetApp.getUi().Button.YES) {
        includePastEventsForPlan = true;
      } else {
        return '予定の取り込みを実施しませんでした。';
      }
    }
  } else if (planOrActual === '実績') {
    if (startDate > todayEndOfDay) {
      return '実績を取り込むには今日以前の期間を入力してください。';
    } else if (endDate > todayEndOfDay) {
      effectiveEndDate = todayEndOfDay;
      SpreadsheetApp.getUi().alert('実績には選択された期間のうち今日以前のデータのみを記録します。');
    }
  }

  const email = Session.getActiveUser().getEmail();
  exportCalendarByWeek(effectiveStartDate, effectiveEndDate, planOrActual, email, false);

  return `${planOrActual}の取り込み処理が完了しました。` + `\n期間: ${formatDate(effectiveStartDate)}～${formatDate(effectiveEndDate)}`;
}

function exportCalendarByWeek(startDate, endDate, planOrActual, email, auto = true) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  let today = new Date();
  today.setHours(0, 0, 0, 0);

  const settingSheet = ss.getSheetByName(SETTING_SHEET_NAME);
  if (!settingSheet) {
    SpreadsheetApp.getUi().alert(`エラー: 設定シート「${SETTING_SHEET_NAME}」が見つかりませんでした。`);
    return;
  }

  const dataObj = getDataObj(startDate, endDate, settingSheet, email);

  const exportSheetName = getSheetNameByEmail(SETTING_SHEET_NAME, settingSheet, email);
  if (!exportSheetName) {
    SpreadsheetApp.getUi().alert('エラー: 出力先のスプレッドシート名が見つかりませんでした。設定シートをご確認ください。');
    return;
  }

  writeResultsToSheet(exportSheetName, dataObj, planOrActual, today, startDate, endDate, email);

  const hasData = Object.keys(dataObj).some(dateKey => {
    const categories = dataObj[dateKey];
    return Object.keys(categories).some(catKey => {
      const tasks = categories[catKey];
      return Object.keys(tasks).length > 0;
    });
  });
  if (auto) {
    console.log(`自動取り込み：${planOrActual}を${formatDate(startDate)}～${formatDate(endDate)}の範囲で書き込みました。`)
  } else if (hasData && !auto) {
    SpreadsheetApp.getUi().alert(`${planOrActual}を${formatDate(startDate)}～${formatDate(endDate)}の範囲で書き込みました。`);
  } else {
    SpreadsheetApp.getUi().alert(`該当期間に登録されたデータがありませんでした。`);
  }
}

function exportAllUsersData(planOrActual) {
  const today = new Date();
  today.setHours(0, 0, 0, 0);
  let startDate, endDate;

  if (planOrActual === '予定') {
    startDate = new Date(today);
    startDate.setDate(today.getDate() + (1 - today.getDay() + 7) % 7);
    endDate = new Date(startDate);
    endDate.setDate(startDate.getDate() + 5);
  } else if (planOrActual === '実績') {
    startDate = new Date(today);
    startDate.setDate(today.getDate() - (today.getDay() + 6) % 7);
    endDate = new Date(startDate);
    endDate.setDate(startDate.getDate() + 5);
  } else {
    console.error('エラー: タイプが不明です。予定か実績を指定してください。');
    return;
  }
  console.log(startDate, endDate);

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const settingSheet = ss.getSheetByName(SETTING_SHEET_NAME);

  const lastRow = getLastRowInColumn(settingSheet, EMAIL_COLUMN_INDEX);
  const emails = settingSheet
    .getRange(2, EMAIL_COLUMN_INDEX, lastRow - 1, EMAIL_COLUMN_INDEX - 1)
    .getValues()
    .flat();
  console.log(emails);

  emails.forEach(email => {
    if (email) {
      exportCalendarByWeek(startDate, endDate, planOrActual, email, true);
    } else {
      console.warn('空のメールアドレスが設定シートにあります。スキップします。');
    }
  });
}

function formatDate(date) {
  return Utilities.formatDate(date, Session.getScriptTimeZone(), 'yyyy-MM-dd');
}

処理の流れは以下の通りです

1. UIから呼び出される関数の定義
   • showDateSelectionDialog 関数は、メニューから「予定」または「実績」が選択された際に呼び出されます。
   • この関数は、dateSelector.html ファイルをテンプレートとして読み込み、HtmlService を使用して日付選択用のカレンダーUIをポップアップダイアログとして表示します。
   • この際、HTMLテンプレートには「予定」か「実績」かという情報（planOrActual）を渡します。
2. HTMLからのデータ受け取りと処理
   • ユーザーがカレンダーUIで日付を選択し、「選択完了」ボタンをクリックすると、processSelectedDates 関数が呼び出されます。
   • この関数は、HTMLから受け取った開始日、終了日、そして「予定」または「実績」の情報を引数として受け取ります。
   • 受け取った日付の妥当性をチェックし、期間が過去や未来を含んでいる場合には、ユーザーに確認のためのアラートを表示します。
3. カレンダーデータの取得とスプレッドシートへの書き込み
   • processSelectedDates 関数は、最終的な処理の実行役として exportCalendarByWeek 関数を呼び出します。
   • exportCalendarByWeek は、指定された期間とユーザー情報（メールアドレス）を元に、カレンダーイベントを取得・解析し、スプレッドシートへの書き込みを行います。
4. 自動実行のハンドリング
   • main.ts には、手動実行とは別に、自動実行用の処理も定義されています。（exportAllUsersData）これにより、手動実行時と自動実行時でUIの有無を適切にハンドリングし、エラーなくスクリプトが動作するようにしています。
   • また、手動実行時と自動実行時の両方で、exportCalendarByWeek 関数を再利用することで、コードの重複を防いでいます。

actionFunctions.gs

このファイルでは、スプレッドシートのメニュー操作と自動実行を制御する関数群がまとめられています。スプレッドシートのメニュー項目を定義し、それぞれに対応する処理を呼び出す役割を担っています。

コード

actionFunctions.gs

function onOpen() {
  SpreadsheetApp.getActiveSpreadsheet().addMenu("カレンダー取り込み", [
    { name: "予定", functionName: "callShowDateSelectionDialogPlan" },
    { name: "実績", functionName: "callShowDateSelectionDialogActual" },
    null,
    { name: "全データ集計", functionName: "combineData" }
  ]);
}

function callShowDateSelectionDialogPlan() {
  showDateSelectionDialog('予定');
}

function callShowDateSelectionDialogActual() {
  showDateSelectionDialog('実績');
}

function exportAllUsersDataForAutomation() {
  exportAllUsersData('予定');
  exportAllUsersData('実績');
}

function combineDataForAutomation() {
  combineData(true);
}

• メニュー操作の制御
  • onOpen()関数は、スプレッドシートが開かれたときに自動的に実行されます。この関数の主な役割は、スプレッドシートのメニューバーに「カレンダー取り込み」というカスタムメニューを追加することです。このメニューには、以下の4つの項目が含まれています。
    • 「予定」： callShowDateSelectionDialogPlan関数を呼び出します。これにより、期間選択のUIダイアログが表示され、ユーザーが手動で予定を取り込めるようになります。
    • 「実績」：callShowDateSelectionDialogActual関数を呼び出します。これも同様に期間選択ダイアログを表示し、実績の取り込みを開始します。
    • 区切り線（null）： メニュー項目を視覚的に区切るための線です。
    • 「全データ集計」： combineData関数を呼び出します。これにより、個人の工数シートのデータを集計シートに手動でまとめることができます。
  • callShowDateSelectionDialogPlan()とcallShowDateSelectionDialogActual()は、それぞれ「予定」と「実績」という文字列を引数として、main.gsに定義されているshowDateSelectionDialog関数を呼び出すためのラッパー関数です。これにより、メニューからの呼び出しをシンプルに保っています。
• 自動実行の制御

  • exportAllUsersDataForAutomation()関数とcombineDataForAutomation()関数は、ユーザーが手動で実行するのではなく、時限トリガーなどによって自動的に実行されることを想定しています。

    • exportAllUsersDataForAutomation()は、main.tsに定義されているexportAllUsersData関数を「予定」と「実績」の両方について呼び出します。これにより、手動で操作しなくても、指定された期間（例：毎週日曜日）の実績と予定が自動的にスプレッドシートに取り込まれます。
    • combineDataForAutomation()は、combineData関数をauto = trueという引数付きで呼び出します。この引数により、自動実行時にはUI関連の処理（SpreadsheetApp.getUi()など）がスキップされ、エラーなくスクリプトが動作します。

    これらの自動実行用関数をGASのトリガー設定に登録することで、カレンダーデータの取り込みや全体集計が完全に自動化され、手動での作業が不要になります。

calendarService.gs

このファイルでは、Googleカレンダーからイベント情報を取得するための関数（getDataObj）をまとめています。

コード

calendarService.gs

function getDataObj(startDate, endDate, settingSheet, calendarId) {
  const calendar = CalendarApp.getCalendarById(calendarId);
  const events = calendar.getEvents(startDate, endDate);
  const settingSheetLastDataRow = settingSheet.getDataRange().getLastRow();
  const userEmail = calendarId;
  const allSettingsData = settingSheet.getRange(2, 1, settingSheetLastDataRow - 1, settingSheet.getLastColumn()).getValues(); 
  let ignorePhrases = [];

  for (let i = 0; i < allSettingsData.length; i++) {
    const row = allSettingsData[i];
    if (row[EMAIL_COLUMN_INDEX - 1] === userEmail) {
      ignorePhrases = row.slice(SETTING_SHEET_IGNORE_PHRASE_COL_START - 1).filter(phrase => phrase !== '' && phrase !== null).map(phrase => String(phrase).trim());
      break;
    }
  }

  const dailyEventTimeCounts = {};

  events.forEach(event => {
    const fullTitle = event.getTitle();

    const categoryMatch = fullTitle.match(/【(.+?)】/);
    if (!categoryMatch) return;
    const category = categoryMatch[1];

    const splitParts = fullTitle.split(/[:：]/);
    if (splitParts.length < 2) return;

    const afterCategoryStr = fullTitle.replace(`【${category}】`, '');
    const [task, ...detailParts] = afterCategoryStr.split(/[:：]/);
    const detail = detailParts.join('：');

    const shouldIgnore = ignorePhrases.some(phrase => fullTitle.includes(phrase));
    if (shouldIgnore) return;
    
    const startTime = event.getStartTime();
    const endTime = event.getEndTime();
    const eventDate = Utilities.formatDate(startTime, Session.getScriptTimeZone(), 'yyyy-MM-dd');
    const durationHours = (endTime.getTime() - startTime.getTime()) / (1000 * 60 * 60);

    if (!dailyEventTimeCounts[eventDate]) dailyEventTimeCounts[eventDate] = {};
    if (!dailyEventTimeCounts[eventDate][category]) dailyEventTimeCounts[eventDate][category] = {};
    if (!dailyEventTimeCounts[eventDate][category][task]) dailyEventTimeCounts[eventDate][category][task] = {};
    if (!dailyEventTimeCounts[eventDate][category][task][detail]) dailyEventTimeCounts[eventDate][category][task][detail] = { duration: 0 };

    dailyEventTimeCounts[eventDate][category][task][detail].duration += durationHours;
  });

  return dailyEventTimeCounts;
}

getDataObj関数は、Google Apps Script（GAS）でカレンダーイベントを取得し、工数管理のためにデータを整形する役割を担っています。この関数は、イベントタイトルを特定のルールで解析し、集計可能な形式に変換します。

具体的には以下のステップで処理を実行します。

1. カレンダーイベントの取得と設定情報の読み込み
   • カレンダーイベントの取得：引数として受け取ったstartDate（開始日）とendDate（終了日）の期間内にある、特定のユーザー（calendarId）のカレンダーイベントを全て取得します。
   • 設定情報の読み込み：「settings」シートから特定のユーザーの行を見つけます。そして、その行のE列以降に設定された無視する語句をリストとして取得します。
2. イベントごとのタイトル解析とフィルタリング
   取得した各カレンダーイベントに対し、以下のステップでタイトルを解析します。
   a. タイトル形式のチェック：イベントのタイトルが【カテゴリ】タスク：詳細という命名規則に従っているかを正規表現で確認します。命名規則に則っていないイベントは、以降の処理に用いずスキップされます。
   b. カテゴリ名の抽出：【】で囲まれた部分をカテゴリとして抽出します。
   c. タスク名と詳細名の抽出：タイトルからカテゴリ部分を除いた残りの文字列を：（コロン）で分割し、最初の部分をタスク、残りの部分を詳細として抽出します。
   d. 無視する語句のチェック：ユーザーが「settings」シートに設定したキーワードがタイトルに含まれている場合、そのイベントは以降の処理に用いずスキップされます。
3. 工数の計算とデータ集計
   解析とフィルタリングを通過したイベントは、以下の処理が実行されます。
   a. 所要時間の計算：イベントの開始時間と終了時間から、作業時間（durationHours, 単位：h）を計算します
   b. データ構造の構築：イベントの日付、カテゴリ名、タスク名、詳細名ごとに時間を集計するための多層的なオブジェクト（dailyEventTimeCounts）を構築します。
   c. 時間の集計：同じ日付、カテゴリ名、タスク名、詳細名のイベントが複数存在する場合、durationを合計して集計します。

最終的にdailyEventTimeCountsオブジェクトが関数の戻り値となり、このデータがスプレッドシートに書き込まれる形で利用されます。
得られるデータの形は以下のようになります。

dailyEventTimeCounts

{
  "2025-07-14": {
    "カテゴリ1": {
      "タスク1": {
        "詳細1": {
          "duration": 1.25
        }
      }
    },
    "カテゴリ2": {
      "タスク2": {
        "詳細2": {
          "duration": 2
        },
        "詳細3": {
          "duration": 1.5
        }
      }
    }
  },
  "2025-07-15": {
    "カテゴリ2": {
      "タスク2": {
        "詳細3": {
          "duration": 2.0
        }
      }
    }
  }
}

spreadsheetService.gs

このファイルでは、スプレッドシートへのデータ書き込みや、シート名の検索、特定の列の最終行の特定といった、スプレッドシート操作に関する関数をまとめています。

コード

spreadsheetService.gs

function getLastRowInColumn(sheet, columnIndex) {
  const columnValues = sheet.getRange(1, columnIndex, sheet.getLastRow(), 1).getValues();
  for (let i = columnValues.length - 1; i >= 0; i--) {
    if (columnValues[i][0] !== '' && columnValues[i][0] !== null) {
      return i + 1;
    }
  }
  return 0;
}

function getSheetNameByEmail(settingSheetName, settingSheet, email) {
  const lastRow = getLastRowInColumn(settingSheet, EMAIL_COLUMN_INDEX);

  if (lastRow < 2) {
    console.log(`設定シート「${settingSheetName}」にメールアドレスのデータがありません。`);
    return null;
  }

  const allValues = settingSheet.getRange(2, EMAIL_COLUMN_INDEX, lastRow - 1, SHEET_NAME_COLUMN_INDEX - EMAIL_COLUMN_INDEX + 1).getValues();

  let sheetName = null;

  for (let i = 0; i < allValues.length; i++) {
    if (allValues[i][0] === email) {
      sheetName = allValues[i][1];
      break;
    }
  }

  if (sheetName) {
    console.log('Found sheet name: ' + sheetName);
    return sheetName;
  } else {
    console.log('Sheet name not found for email: ' + email);
    return null;
  }
}

function writeResultsToSheet(sheetName, dataObj, planOrActual, today, startDate, endDate, email) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let sheet = ss.getSheetByName(sheetName);
  const record_date = Utilities.formatDate(today, Session.getScriptTimeZone(), 'yyyy-MM-dd');

  if (!sheet) {
    sheet = ss.insertSheet(sheetName);
    sheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
  }

  const alldata = sheet.getDataRange().getValues();
  const existingRaws = alldata.slice(1);

  const rowsToDelete = [];

  for (let i = 0; i < existingRaws.length; i++) {
    const row = existingRaws[i];
    const rowType = row[TYPE_COLUMN_INDEX];
    const rowDate = new Date(row[DATE_COLUMN_INDEX]);
    rowDate.setHours(0, 0, 0, 0);
    const rowUserId = row[USER_ID_COLUMN_INDEX];

    if (rowType === planOrActual && rowUserId === email && rowDate >= startDate && rowDate <= endDate) {
      rowsToDelete.push(i + 2);
    }
  }

  for (let i = rowsToDelete.length - 1; i >= 0; i--) {
    sheet.deleteRow(rowsToDelete[i]);
  }

  const lastRow = sheet.getLastRow();
  const startRow = lastRow < 1 ? 2 : lastRow + 1;

  const rows = [];

  Object.keys(dataObj).forEach(dateKey => {
    const categories = dataObj[dateKey];
    Object.keys(categories).forEach(categoryKey => {
      const tasks = categories[categoryKey];
      Object.keys(tasks).forEach(taskKey => {
        const details = tasks[taskKey];
        Object.keys(details).forEach(detailKey => {
          const duration = details[detailKey].duration;
          rows.push([planOrActual, dateKey, categoryKey, taskKey, detailKey, duration, email, record_date, sheetName]);
        });
      });
    });
  });

  if (rows.length > 0) {
    sheet.getRange(startRow, 1, rows.length, rows[0].length).setValues(rows);
  }
}

ここには以下の三つの関数がまとめられています。

1. getLastRowInColumn関数

   • 目的：この関数は特定のシート（sheet）の特定の列（columnIndex）に入力されているデータの最終行を取得します。GASにデフォルトで存在するgetLastRow() 関数との違いは、getLastRow() は取得される最終行が特定列の最終行と異なる可能性がある一方で、getLastRowInColumn関数は特定列の最終行を正確に取得できる点です。これは、getLastRow() 関数はシート全体の最終行を取得するためです。
     例えば下記のシートのように誤った入力があった場合、getLastRow() 関数では最終行として4行目が取得されますが、getLastRowInColumn(settingSheet, 2) （「settings」シートのB列）では2行目を取得できます。
     

   • 処理：シートの列全体を読み込み、配列を逆順にループすることで、最初に見つかった空ではないセルの行番号を返します。途中に空白のセルがある場合でも、データが入力されている最後の行を特定します。

   • 戻り値：最終行の行番号（1から始まります）を返します。列が完全に空の場合は0を返します。

2. getSheetNameByEmail関数
   • 目的：「settings」シートに登録されたメールアドレス（email）に対応する記録用シート名を取得します。
   • 処理：getLastRowInColumn 関数を使って、「settings」シートのメールアドレスが登録されている列の最終行を特定し、メールアドレスとシート名のデータを読み込みます。その後、ループで受け取ったemailと一致する行を検索し、その行にある対応するシート名を返します。
   • 戻り値：該当するシート名（文字列）を返します。メールアドレスが見つからない場合はnull を返します。
3. writeResultsToSheet関数
   • 目的：calendarService.gsファイル内のgetDataObj関数で整形された工数データを、スプレッドシートに書き込みます。
   • 処理
     a. まず書き込み先のシート（sheetName）が存在するか確認します。存在しない場合は、新しいシートを作成し、ヘッダー行を設定します。
     b. 次に、同じ期間（startDate からendDate）で、同じユーザーによってすでに記録されているデータがあれば、その行を特定して、全て削除します。これにより、データが重複することなく、常に最新のデータで上書きされます。
     c. getDataObj 関数から受け取ったdataObj オブジェクトをループ処理し、各データを1行の配列に整形します。この配列にはtype （予定/実績）date category task detail working_hours user_id rcord_date sheetName が含まれます。
     d. 最後に整形された全てのデータを、シートの最終行に一括で書き込みます。
   • 戻り値：この関数は値を返しませんが、スプレッドシートのデータを更新するという主要な役割を果たしています。

combineData.gs

このファイルでは、複数人のデータを一つのシートにまとめるための関数(combineData)をまとめています。

コード

combineData.gs

function combineData(auto = false) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  let ui;
  if (!auto) {
    ui = SpreadsheetApp.getUi();
  } else {
    ui = null;
  }
  
  let summarySheet = ss.getSheetByName(SUMMARY_SHEET_NAME);
  if (!summarySheet) {
    summarySheet = ss.insertSheet(SUMMARY_SHEET_NAME);
    summarySheet.getRange(1, 1, 1, HEADERS.length).setValues([HEADERS]);
  }

  const settingSheet = ss.getSheetByName(SETTING_SHEET_NAME);
  if (!settingSheet) {
    if (!auto) {
      ui.alert(`エラー: 設定シート「${SETTING_SHEET_NAME}」が見つかりませんでした。処理を中断します。`);
    } else {
      console.error(`エラー：設定シート「${SETTING_SHEET_NAME}」が見つかりませんでした。処理を中断します。`);
    }
    return;
  }
  const lastRow = getLastRowInColumn(settingSheet, EMAIL_COLUMN_INDEX);
  if (lastRow < 2) {
    if (!auto) {
      ui.alert(`エラー：設定用シート「${SETTING_SHEET_NAME}」にユーザー情報がありません。処理を中断します`);
    } else {
      console.error(`エラー：設定用シート「${SETTING_SHEET_NAME}」にユーザー情報がありません。処理を中断します。`);
    }
    return;
  }

  const userSettings = settingSheet.getRange(2, EMAIL_COLUMN_INDEX, lastRow - 1, SHEET_NAME_COLUMN_INDEX - EMAIL_COLUMN_INDEX + 1).getValues();
  const allCollectedRows = [];
  const processedSheets = new Set();

  for (let i = 0; i < userSettings.length; i++) {
    const email = userSettings[i][0];
    const sheetName = userSettings[i][1];

    if (!sheetName || processedSheets.has(sheetName)) {
      console.warn(`警告：　無効なシート名　${sheetName}　またはすでに処理済みのためスキップします(ユーザー: ${email})。`);
      continue;
    }
    processedSheets.add(sheetName);

    const userSheet = ss.getSheetByName(sheetName);
    if (!userSheet) {
      console.warn(`警告：　シート「${sheetName}」が見つからないためスキップします(ユーザー: ${email})。`);
      continue;
    }

    const userSheetLastRow = userSheet.getLastRow();
    if (userSheetLastRow < 2) {
      console.log(`シート「${sheetName}」にはデータがありません。スキップします(ユーザー: ${email})。`);
      continue;
    }
    
    const userData = userSheet.getRange(2, 1, userSheetLastRow - 1, userSheet.getLastColumn()).getValues();

    userData.forEach(row => {
      if (row.length >= 8) {
        allCollectedRows.push(row);
      } else {
        console.warn(`警告：　シート「${sheetName}」のデータに不備があります（列が不足）。　行をスキップ：${JSON.stringify(row)}`);
      }
    });
  }

  if (summarySheet.getLastRow() > 1) {
    summarySheet.deleteRows(2, summarySheet.getLastRow() - 1);
  }

  if (allCollectedRows.length > 0) {
    const rowsToWrite = allCollectedRows.map(row => {
      const dateString = row[DATE_COLUMN_INDEX];
      if (typeof dateString === 'string') {
        const dateObject = new Date(dateString);
        if (!isNaN(dateObject.getTime())) {
          row[1] = dateObject;
        }
      }
      return row;
    });

    summarySheet.getRange(2, 1, rowsToWrite.length, rowsToWrite[0].length).setValues(rowsToWrite);
    if (!auto) {
      ui.alert(`全ユーザーのデータを集計シート「${SUMMARY_SHEET_NAME}」にまとめました。`);
    } else {
      console.log(`全ユーザーのデータを集計シート「${SUMMARY_SHEET_NAME}」にまとめました。`);
    }
  } else {
    if (!auto) {
      ui.alert('全ユーザーのシートから集計対象のデータは見つかりませんでした。');
    } else {
      console.log('全ユーザーのシートから集計対象のデータは見つかりませんでした。');
    }
  }
}

処理の流れ

この関数の主な処理は以下の通りです。

1. 実行環境の判定： auto引数によって、手動実行か自動実行かを判断し、ui.alertの使用を切り替えます。
2. 集計シートの準備： summaryシートが存在しない場合は、新しく作成し、ヘッダーを設定します。
3. ユーザー情報の取得： settingsシートから、全ユーザーのメールアドレスと個別の記録用シート名を読み込みます。
4. データの収集： 取得したユーザー情報をもとに、各ユーザーの記録用シートにアクセスし、ヘッダー行を除いたすべてのデータをallCollectedRows配列に収集します。この際、無効なシート名やデータに不備がある場合は警告ログを出力し、スキップします。
5. 集計シートへの書き込み：
   • まず、summaryシートの既存データをすべてクリアします。
   • 収集したallCollectedRowsのデータをsummaryシートの2行目以降に一括で書き込みます。
   • 最後に、実行環境に応じて処理完了メッセージをユーザーに通知（手動実行時）またはログに出力（自動実行時）します。

dateSelector.html

このファイルでは、ユーザーがカレンダーから期間を直感的に選択するためのUIを提供し、選択された日付をGASに渡す役割を担っています。

コード

dateSelector.html

<!DOCTYPE html>
<html>
<head>
    <base target="_top">
    <meta charset="UTF-8">
    <title>期間選択</title>
    <link rel="stylesheet" href="https://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <script src="https://code.jquery.com/jquery-1.12.4.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <style>
    body {
        font-family: 'Arial', sans-serif;
        margin: 15px;
        background-color: #f8f8f8;
        color: #333;
    }
    .container {
        background-color: #fff;
        padding: 20px;
        border-radius: 8px;
        box-shadow: 0 2px 4px rgba(0,0,0,0.1);
        max-width: 400px;
        margin: auto;
    }
    label {
        display: block;
        margin-bottom: 8px;
        font-weight: bold;
        color: #555;
    }
    input[type="text"] {
        width: calc(100% - 22px);
        padding: 10px;
        margin-bottom: 15px;
        border: 1px solid #ddd;
        border-radius: 4px;
        box-sizing: border-box;
        font-size: 1em;
    }
    button {
        background-color: #4CAF50;
        color: white;
        padding: 12px 20px;
        border: none;
        border-radius: 4px;
        cursor: pointer;
        font-size: 1.05em;
        width: 100%;
        box-sizing: border-box;
        transition: background-color 0.3s ease;
    }
    button:hover {
        background-color: #45a049;
    }
    #message {
        margin-top: 15px;
        color: red;
        font-weight: bold;
        text-align: center;
    }
    .ui-datepicker {
        z-index: 9999 !important;
    }

    .ui-state-highlight-green {
        background: #aaffaa !important;
        color: #333 !important;
        border: 1px solid #66cc66 !important;
    }
    </style>
    <script>
    var planOrActualValue = '<?= planOrActual ?>';
    </script>
</head>
<body>
    <div class="container">
    <label for="start-date">開始日:</label>
    <input type="text" id="start-date" class="datepicker" placeholder="YYYY-MM-DD">

    <label for="end-date">終了日:</label>
    <input type="text" id="end-date" class="datepicker" placeholder="YYYY-MM-DD">

    <button onclick="sendDatesToGas()">実行</button>
    <div id="message"></div>
    </div>

    <script>
        let selectedStartDateObj = null;
        let selectedEndDateObj = null;

        let loadingInterval;
        let dotCount = 0;
        const messageBase = '処理中';

        function datesEqual(date1, date2) {
            if (!date1 || !date2) return false;
            return date1.getFullYear() === date2.getFullYear() && date1.getMonth() === date2.getMonth() && date1.getDate() === date2.getDate();
        }

        $( function() {
            $("#start-date").datepicker({
                dateFormat: "yy-mm-dd",
                onSelect: function(dateText, inst) {
                    selectedStartDateObj = $.datepicker.parseDate("yy-mm-dd", dateText);
                    ）
                    $("#end-date").datepicker("option", "minDate", selectedStartDateObj);
                    $("#start-date").datepicker("refresh");
                    $("#end-date").datepicker("refresh");
                },
                beforeShowDay: function(date) {
                    let highlightClass = '';
                    if (selectedEndDateObj && datesEqual(date, selectedEndDateObj)) {
                        highlightClass += ' ui-state-highlight-green';
                    }
                    return [true, highlightClass, ''];
                }
            });

            $("#end-date").datepicker({
                dateFormat: "yy-mm-dd",
                onSelect: function(dateText, inst) {
                    selectedEndDateObj = $.datepicker.parseDate("yy-mm-dd", dateText);
                    $("#start-date").datepicker("option", "maxDate", selectedEndDateObj);
                    $("#start-date").datepicker("refresh");
                    $("#end-date").datepicker("refresh");
                },
                beforeShowDay: function(date) {
                    let highlightClass = '';
                    if (selectedStartDateObj && datesEqual(date, selectedStartDateObj)) {
                        highlightClass += ' ui-state-highlight-green';
                    }
                    return [true, highlightClass, ''];
                }
            });
        });

        function startLoadingAnimation() {
            dotCount = 0; thank 
            loadingInterval = setInterval(function() {
                dotCount = (dotCount + 1) % 4;
                let dots = '';
                for (let i = 0; i < dotCount; i++) {
                    dots += '・';
                }
                $('#message').text(messageBase + dots);
            }, 500);
        }

        function stopLoadingAnimation() {
            clearInterval(loadingInterval);
            $('#message').text('');
        }

        function sendDatesToGas() {
            const startDate = $('#start-date').val();
            const endDate = $('#end-date').val();

            if (!startDate || !endDate) {
                $('#message').text('開始日と終了日を両方選択してください。');
                return;
            }
            
            if (new Date(startDate) > new Date(endDate)) {
                $('#message').text('開始日は終了日以前の日付を選択してください。');
                return;
            }
            
            startLoadingAnimation();

            google.script.run
                .withSuccessHandler(function(result) {
                    stopLoadingAnimation();
                    google.script.host.close();
                })
                .withFailureHandler(function(error) {
                    stopLoadingAnimation(); 
                    $('#message').text('エラーが発生しました: ' + error.message);
                    console.error('GAS処理エラー:', error);
                })
                .processSelectedDates(startDate, endDate, planOrActualValue);
        }
    </script>
</body>
</html>

• ファイル全体の目的

  このHTMLファイルは、以下の3つの要素から構成されています。

  1. デザイン (CSS)：ダイアログの外観を整え、選択された日付を緑色にハイライトするスタイルを定義しています。
  2. UI (HTML)：開始日と終了日を入力するテキストボックスと、日付選択用のカレンダー（Datepicker）を表示する領域、そして「実行」ボタンやメッセージ表示エリアを配置しています。
  3. 機能 (JavaScript)：ユーザーの操作に応じてカレンダーを動的に制御したり、入力された日付を検証したり、最終的にGASの関数を呼び出して処理を開始したりするロジックを実装しています。

• ファイルの主要な機能

  1. カレンダーUIのインタラクション
     このファイルの中心的な機能は、jQuery UIのDatepickerを使った日付選択です。

     • 双方向の連動：開始日と終了日の2つのカレンダーは互いに連動しています。例えば、開始日を選択すると、終了日のカレンダーではそれより前の日付が選択できなくなります。また、終了日を選択すると、開始日のカレンダーではそれより後の日付が選択できなくなります。
     • 視覚的なフィードバック： beforeShowDayという機能を使って、選択した開始日と終了日がもう一方のカレンダーで緑色にハイライトされるように工夫されています。これにより、どの期間を選択しているかが一目でわかります。

  2. 非同期処理中のアニメーション
     GASの処理は非同期で実行されるため、ユーザーは処理が完了するまで待つ必要があります。その間にUIがフリーズしているように見えないよう、JavaScriptで簡単なアニメーションを実装しています。

     • sendDatesToGas関数が実行されると、startLoadingAnimation関数が呼び出され、「処理中・」「処理中・・」「処理中・・・」のようにドットが動くアニメーションが表示されます。
     • GAS側の処理が完了すると、withSuccessHandlerまたはwithFailureHandlerによってstopLoadingAnimation関数が呼び出され、アニメーションが停止します。これにより、ユーザーは処理が正常に進行していることを視覚的に確認できます。

  3. GASとの連携とバリデーション

     • データの送信：「実行」ボタンがクリックされると、sendDatesToGas関数が実行されます。この関数は、google.script.runという特別なAPIを使って、入力された開始日、終了日、そして「予定」か「実績」かという情報 (planOrActualValue) をGAS側のprocessSelectedDates関数に非同期で送信します。
     • 入力の検証：データを送信する前に、開始日と終了日が両方選択されているか、開始日が終了日より前の日付であるかといった基本的な入力チェックを行います。不備があれば、messageエリアに赤い文字でエラーメッセージを表示し、GASへの処理を防ぎます。
     • 結果の受け取り：GASからの処理結果は、withSuccessHandler（成功時）またはwithFailureHandler（失敗時）で受け取ります。成功時には完了メッセージをアラートで表示し、ダイアログを閉じます。失敗時には、ダイアログ内にエラーメッセージを表示します。

自動集計機能の設定

ここまで実装した機能で十分使えるのですが、さらに自動化するため最後にGASの「トリガー」機能を使って、データの自動集計を設定します。

設定方法

1. スプレッドシートの「拡張機能」⇒「Apps Script」⇒「トリガー」を開き、トリガーを追加ボタンをクリックします。
   

2. 以下の設定を選択し、「保存」をクリックします。（複数人のデータを毎日自動で一つのシートにまとめます。）

   • 実行する関数：combineDataForAutomation
   • 実行するデプロイ：Head
   • イベントのソース：時間主導型
   • 時間ベースのトリガーのタイプ：日付ベースのタイマー
   • 時刻：午前1時〜2時
     

3. 新しくトリガーを追加し、以下の設定を選択して保存してください。（本記事では毎週末に今週のイベントを実績として記録し、次週のイベントを予定として記録することとします。）

   • 実行する関数： exportAllUsersDataForAutomation
   • 実行するデプロイ：Head
   • イベントのソース：時間主導型
   • 時間ベースのトリガーのタイプ：週ベースのタイマー
   • 曜日：毎週日曜日
   • 時刻：午前0時〜1時
     

動作確認と利用方法

拡張機能が完成したら、実際にスプレッドシートとGoogleカレンダーを使って動作確認をしてみましょう。

Googleカレンダーのイベント作成

1. Googleカレンダーにアクセス
2. 命名規則に従ってタイトルを記入
   

スプレッドシートのテスト

1. GASを保存したスプレッドシートにアクセス
2. 上部メニューから「カレンダー取り込み」を選択
3. 「予定」「実績」「全データ集計」が表示されていることを確認
4. それぞれをクリックし、データが正しく入力されるかを確認
   

GitHub ActionsとclaspでGASを自動デプロイ

以前以下のような記事を書きましたが、今回の仕組みでも活用しています。

GitHub Actionsとclaspを活用した自動デプロイを構築し、一つのGASコードを複数のスプレッドシート付属のGASに一括でデプロイすることが可能になりました。

自動デプロイの仕組み

1. トリガー：mainブランチのsrcディレクトリ内でファイルが変更され、プッシュされると、GitHub Actionsが自動でデプロイプロセスを開始します。
2. 認証：claspの認証トークンをGitHubのSecretsに安全に保存します。これにより、認証情報をコードに含めることなく、安全にclaspコマンドを実行できます。
3. デプロイ：カスタムデプロイスクリプト（deploy.js）が起動し、config.jsonに定義された複数のスクリプトIDに対して、clasp pushコマンドを順次実行します。これにより、srcディレクトリにあるすべてのファイルが、指定された全てのGASプロジェクトに自動でデプロイされます。

開発過程での課題と解決策

実際に開発する中で直面した具体的な課題と、それをどのように解決したかを解説します。

課題1：日付選択の煩雑さの解消

• 課題：開発の初期段階では、期間指定のためにユーザーに「YYYY-MM-DD」形式で日付を手動で入力してもらうことを想定していました。しかし、この方法は入力が面倒で、形式間違いなどのミスが起こりやすいと考えられました。
• 解決策：ユーザーエクスペリエンス（UX）を向上させるため、HTML Serviceを活用してカレンダーUIを搭載しました。これにより、ユーザーはポップアップで表示されるカレンダーから、マウス操作で直感的に開始日と終了日を選択できるようになりました。これにより、入力ミスが減り、誰でも簡単にツールを使えるようになりました。

課題2：UIとGAS間の非同期通信

• 課題：HTML Service で作成したUIダイアログとGASのサーバーサイドスクリプトの間でデータをやり取りする際、実行に時間のかかる処理（カレンダーデータの取得など）では、UIがフリーズしているように見えてしまう問題がありました。
• 解決策：処理中にアニメーションをダイアログ内に表示させることで、ユーザーに処理が正常に進行していることを視覚的に伝え、ストレスなく待てるように工夫しました。

課題3：カレンダーイベントの厳密な解析

• 課題：カレンダーイベントのタイトルに 【カテゴリ】タスク：詳細 という命名規則を設定していましたが、ルール外の記述（例：「【カテゴリ】タスク」のように詳細がない場合や、コロンが使われていない場合など）があると、集計が正しく行われないという課題がありました。
• 解決策：抽出ロジックをより厳密に修正しました。正規表現や文字列操作を駆使して、【 から始まり 】 で閉じられ、その後に：（コロン）が続く形式を厳密にチェックするロジックを実装しました。これにより、命名規則に沿っていないイベントは自動で除外されるため、集計されるデータの品質が大幅に向上しました。

まとめ

本記事では、Googleカレンダーで登録したイベントから稼働工数を自動で抽出し、スプレッドシートで集計・可視化する仕組みを紹介しました。
この仕組みによって、工数管理の手間を減らしつつ、チーム内でのルール統一や工数管理の精度向上が期待できます。

ここまでご覧いただきありがとうございました！