はじめに

自社で利用している勤怠管理システムは所謂タイムカード管理をWeb化したようなシステムで、工数管理には向かないため、別途工数管理用のスプレッドシート(弊社はGoogle workspaceを契約しています)で簡易的なシートを作成して運用してきました。

簡易的に行うにはスプレッドシートだけで良かったのですが、作業状況の分析のために予実管理や作業の詳細を記録する必要に迫られ、一応の対応を工数管理のスプレッドシートに施して勤怠システムと２重管理をするようになりました。

運用するとすぐにツライ面がでてきました。¹

データ連携もないので目視で１日の作業時間を合わせる必要がある
そもそも人数分のシートを毎月用意する（コピーされるスプレットシートが毎月大量に作成される）

こうなってくると日々入力するたびに ~~SAN値が削られる~~ なんとか自動化できないのかと考えはじめ、会社側に暫定的でもよいのでシステム化の提案を行おうと決意しました。ただし以下の制約がありました。

①システム利用料などは請求できない
- 使うかどうかもわからないシステムですし、あくまで提案なので０円でやることにしました。その方が自由でもありますし。
②使えるものを作るまでの期間は1ヶ月
- 工数分析の必要性は急務でできる限り早く実装する必要がありました。ツライ。
③セキュリティは当然担保しなればならない
④運用に必要な仕組みも必要（バックアップ機能など）

今回はGAS(Google Apps script)を採用しました。上記制約と対比しますが

①追加費用なし（既にGoogle workspaceを利用している為）
②スプレットシートでの現行運用とほぼ同じ
- 管理層もイメージしやすい（データ加工も自身でできるし）アドバンテージもあった
- スプレットシートへのデータ登録だけなんとかすればよいので短い時間で実装可能
③GASだとGoogleの認証を利用できる可能ので、現在のスプレットシートの運用とセキュリティレベルが変わらない
④スクリプトを定期実行する仕組みも用意されているのでバックアップも容易に実装可能。また編集履歴もスプレットシートのものを利用できる

といった点から採用を決めました。
また

共有ロック機能がある（ドキュメントレベル、スクリプトレベル、ユーザレベル）
GAS経由でのスプレットシート編集時にも、きちんと編集ユーザが記録される
バックアップすべきデータは、スプレットシートのシートなのでバックアップも容易
認証処理を自身で実装する必要もない

という点も後押しになりました。

システム全体

ざっくりですが、以下のように実装しました²。

勤怠システムの拡張

勤怠システムは出退勤時間を管理しているため、作業時間の実績を登録する機能とみなしました。勤怠システムではその日の作業時間が登録されているため、スプレットシートに登録されている工数と比較し、工数入力が完了しているかわかるようなインタフェースを拡張するためにChromeの機能拡張を利用して作成しています。

拡張した入力フォームのイメージ

入力差分はベースとなっている勤怠システムの画面から値を取得することで、勤怠システムと工数入力システムの差分に気づけるようにしています。

また、機能拡張を利用して（既存の勤怠システムには機能がない）各種データ出力のCSV出力機能も追加しました。

各機能はシンプルで機能拡張からはGASのAPIを呼び出すことで実装しています（事前にGoogleへのログインは必須）。特にGASだからという実装もないので説明は割愛します。

GAS側の実装(Webアプリ)

ざっくりではありますが、下記のように実装しました。なおデプロイは clasp を利用しています。
なんちゃってフレームワークではあります😂ちょっと長いけど一部編集して貼り付けました。

import { ProjectController } from "./controllers/project_controller";
import { WorktimeController } from "./controllers/worktime_controller";
import { PlantimeController } from "./controllers/plantime_controller";

// エンドポイント
// @see https://github.com/DefinitelyTyped/DefinitelyTyped/blob/master/types/google-apps-script/google-apps-script-events.d.ts
function doGet(event: GoogleAppsScript.Events.DoGet) {
  Logger.log(event);

  // currentUserId,controller,actionは予約語扱い
  const currentUserId = event.parameter.currentUserId;
  const controller:string = event.parameter.controller;
  const action:string = event.parameter.action;

  if (!controller || !action) {
    return ContentService.createTextOutput("不正なリクエストです");
  }

  const activeSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();

  // TODO: ここは動的に呼び出すメソッドを変えたい
  if (controller === 'projects' && action === 'gets'){
    // script.google.comにアクセスして↓を実行すると確認できます
    //
    // await (await fetch('https://script.google.com/a/macros/ruby-dev.jp/s/hogehoge/exec?controller=projects&action=gets&currentUserId=1001', {
    //   method: 'GET',
    // })).text()
    return (new ProjectController(activeSpreadSheet)).gets(event.parameter, currentUserId);
  } else if (controller === 'worktimes' && action === 'gets'){

    // await (await fetch('https://script.google.com/a/macros/ruby-dev.jp/s/hogehoge/exec?controller=worktimes&action=gets&currentUserId=1001&targetDate=2023/11/01&targetcurrentUserId=1001', {
    //   method: 'GET',
    // })).text()
    return (new WorktimeController(activeSpreadSheet)).gets(event.parameter, currentUserId);
  } else if (controller === 'worktimes' && action === 'gets_monthly'){
    // await (await fetch('https://script.google.com/a/macros/ruby-dev.jp/s/hogehoge/exec?controller=worktimes&action=gets_monthly&currentUserId=1001&targetMonth=2023/11&targetcurrentUserId=1001', {
    //   method: 'GET',
    // })).text()
    return (new WorktimeController(activeSpreadSheet)).gets_monthly(event.parameter, currentUserId);
  } else if (controller === 'plantimes' && get action ==== 'get'){
    return (new PlantimeController()).get(event.parameter, currentUserId);
  }
}


// エンドポイント
function doPost(event: GoogleAppsScript.Events.DoPost) {
  Logger.log(event);

  // currentUserId,controller,actionは予約語扱い
  const currentUserId = event.parameter.currentUserId;
  const controller:string = event.parameter.controller;
  const action:string = event.parameter.action;

  if (!controller || !action) {
    return ContentService.createTextOutput("不正なリクエストです");
  }

  const activeSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();

  // TODO: ここは動的に呼び出すメソッドを変えたい
  if (controller === 'worktimes' && action === 'create'){
    // script.google.comにアクセスして↓を実行すると確認できます
    //
    // await (await fetch('https://script.google.com/a/macros/ruby-dev.jp/s/hogehoge/exec?controller=worktimes&action=create&currentUserId=1001', {
    //   method: 'POST',
    //   headers: {
    //     "Content-Type": "application/x-www-form-urlencoded"
    //   },
    //   body: new URLSearchParams({
    //     date: '2023/11/01',
    //     currentUserId: '1001',
    //     projectId: 1,
    //     workHours: 1.75,
    //     description: 'description'
    //   })
    // })).text()
    return (new WorktimeController(activeSpreadSheet)).create(event.parameter, currentUserId);
  } else if (controller === 'worktimes' && action === 'delete'){
    return (new WorktimeController(activeSpreadSheet)).delete(event.parameter, currentUserId);
  }
}

ポイントは以下です。

VueのホスティングもAPIエンドポイントの同列の呼び出しにある
- API呼び出しではデータを返し、Vueホストのエンドポイントの場合はHTMLを直接返すイメージです
POSTのエンドポイントは必ず Content-Typeは application/x-www-form-urlencoded とする
- 詳しくは gas application/x-www-form-urlencoded などで検索してください。とにかく余計なトラブルに巻き込まれたくなければ上記が正解です。

なお、スプレットシート側のデータ取得部分はちょっと工夫してあって

  private get_worktimes(targetUserId: string, targetDate: string): Array<GetWorkTime> {
    const workTimesSheet = this._activeSpreadSheet.getSheetByName('active_work_times');
    // A1には有効行数が入ってる仕様
    const lastRowNum = workTimesSheet.getRange('A1').getValue();
    // ３行目からが有効なデータである仕様
    const range = workTimesSheet.getRange(`A3:K${lastRowNum}`);
    let values = range.getDisplayValues();

    // 対象ユーザ/対象日 で絞り込む
    values = values.filter(v => this.formatDate(v[1]) == targetDate && v[2] == targetUserId);

    const result: Array<GetWorkTime> = values.map(function(value_array){
      return {
        id:             Number(value_array[0]),
        date:           value_array[1],
        ...
      }
    });

    return result;
  }

のコメントで確認できるように

A1には有効行数が入ってる仕様
３行目からが有効なデータである仕様

とすることでデータ取得を効率化しています。

バックアップ

スプレットシート自体をバックアップするのが一番カンタンでしたが、そのままだとGASアプリケーションまで増えてしまいGAS管理画面が大変なことになった&&コードはgit管理されているので、バックアップすべきものはシートのデータだけなのでシートだけにしました³。

export function Backup() {
  const BackupSheetNameFormat = 'yyyy-MM-dd_HHmmss';

  // GCPに繋げないとローカルから実行できないのでウェブ画面からセットすること「プロジェクトの設定 -> スクリプト プロパティ」から設定できます。
  // BACKUP_FOLDER_ID に バックアップディレクトリのIDをセットしてください
  const backupFolderId = PropertiesService.getScriptProperties().getProperty('BACKUP_FOLDER_ID');
  const backupFolder = DriveApp.getFolderById(backupFolderId);

  // バックアップファイルを作成する
  Logger.log('バックアップファイルの作成');
  let backupSpreadSheet;
  (()=>{
    const fileName = Utilities.formatDate(new Date(), 'JST', BackupSheetNameFormat);
    backupSpreadSheet = SpreadsheetApp.create(fileName);

    // マイドライブにつくったバックアップファイルをバックアップフォルダに移動して、マイドライブから削除する
    const file = DriveApp.getFileById(backupSpreadSheet.getId());
    backupFolder.addFile(file);
    DriveApp.getRootFolder().removeFile(file);
  })();

  // シートをコピーする
  Logger.log('シートのコピー');
  (()=>{
    // コピー対象のSpreadSheetは自分のシート
    const originalSpreadSheet = SpreadsheetApp.getActiveSpreadsheet();

    // 全シートをコピーする
    originalSpreadSheet.getSheets().forEach((srcSheet) => {
      const newSheet = srcSheet.copyTo(backupSpreadSheet);

      newSheet.setName(srcSheet.getSheetName());
    });
  })();

  // 直近60回分までのバックアップを保持し、それ以前のものは削除
  Logger.log('バックアップのtruncate');
  (()=>{
    const maxBackupCount = 60; // 60回分のバックアップを取る
    const files = backupFolder.getFiles();
    const backupFiles = [];
    while(files.hasNext()){
      let f = files.next();
      backupFiles.push(f);
    }
    backupFiles.sort(function(a, b){
      return a.getDateCreated() < b.getDateCreated() ? 1 : -1;
    });
    for(let i = maxBackupCount; i < backupFiles.length; i++){
      backupFiles[i].setTrashed(true);
    }
  })();
  Logger.log('終了');
}

さいごに

断片的な情報だけになってしましました。パフォーマンス検証などは軽くしかできていないですし、まだ複数人での運用も始まっていないのである程度使い始めてから得たノウハウなどはまた公開しようと思います。

ただ、今回感じたのは簡易的とはいえども一応動くものをものの数日で作れたのは驚きでした。GASはかなりクセが強いので最初はハマることも多いと思いますが、一度実装が進むとロゴブロックを組み立てるように実装することができるイメージで楽しかったです。

またGASとは関係がないですが、最近のスプレットシートの新しい機能にもびっくりしました。Arrayformulaやquery、lambdaヘルパ関数などはびっくりで管理側のViewにあたるスプレットシート表の作成も面白かったです。

ということで、まとまりなかったのですがなんとか今日のアドベントカレンダーをクリアできた（現在12/4 23:20）ことを喜びつつ公開ボタンを押す。

スプレットシートの完成度を上げれば解決する問題ありますがそれってシステム会社としてどーなの？という側面もあるかなと ↩
工数予定の登録IFは現時点ではできていませんが。 ↩
バックアップ処理について参考にさせていただいたサイトがあったのですがかなり前に修正したのでリンクをなくしてしまいました。。。 ↩