【Power Automate Desktop × GAS × ChatGPT】60代店長が現場を巻き込みながら育てる時間外勤務管理システムを作ってみた

～プロトタイプVer.1 開発記録～

はじめに

「システムは完成しました。」

……そう言いたいところですが、実はまだ完成ではありません。

今回ご紹介するのは、私の職場で実際に運用することを目指して作成した時間外勤務管理システムのプロトタイプ Ver.1です。

私が勤務しているスーパーマーケットでは、時間外勤務が発生した場合、事前または事後に申請を行うルールがあります。

しかし実際には、計画外勤務実績はあるものの申請が提出されていないケースが毎日のように発生していました。

後方担当者は勤務実績CSVと申請内容を照合し、一人ひとりへ確認を行っています。

誰かが悪いわけではありません。

確認する人も、確認される人も、それぞれ自分の仕事をしています。

ただ、その間をつなぐ「仕組み」がありませんでした。

💡 関連記事
今回のシステムを作るきっかけになった考え方は、noteで公開した以下の記事にまとめています。

今回のプロトタイプは、その考え方を実際の業務へ落とし込むための第一歩です。

---

なぜこの技術を選んだのか

今回使用した主な技術は次のとおりです。

• Googleフォーム
• Googleスプレッドシート
• Google Apps Script（GAS）
• Power Automate Desktop（PAD）
• ChatGPT

システム概要

今回のシステムは

ツールを使うことが目的ではありません。

それぞれの得意分野を組み合わせ、

現場の負担を減らすこと

を目的として採用しました。

---

システム全体の流れ

Googleフォームから提出された申請内容は管理台帳へ登録されます。

一方、Power Automate Desktopで取得した勤務実績CSVをGoogle Apps Scriptで照合し、

一致すれば「照合済」、

一致しなければ「未申請一覧」へ登録する仕組みです。

毎日行っていた確認作業を自動化することが今回のプロトタイプの目的です。

---

プロトタイプ開発スタート

最初に取り組んだのはGoogleフォームの作成でした。

社員番号、勤務日、開始時間、終了時間、理由など、

現場で実際に必要となる項目を整理し、

Googleフォームから入力された内容をGoogleスプレッドシートへ自動登録するところからスタートしました。

Googleフォーム項目一覧

📷Googleフォーム

---

続いてGoogle Apps Scriptを利用し、

社員マスタと照合して

• 氏名
• 所属
• 承認者

を自動補完する仕組みを追加しました。

これにより入力ミスを減らし、

管理台帳への転記作業も不要になりました。

📷管理台帳

思っていた以上に苦戦したPower Automate Desktop

Power Automate Desktopは今回初めて本格的に使用しました。

「CSVを読み込んでGoogleへ送るだけ」

そう考えていましたが、実際にはそう簡単ではありませんでした。

CSVの文字コード。

ZIPファイルの展開。

CurrentRowとは何か。

JSONとは何か。

一つ解決すると、また次の壁が現れます。

特に初心者の私にとっては、「エラーとの付き合い方」を学ぶ時間だったように思います。

📷Power Automate Desktopのフロー作成中画面

📷PADフロー作成中画面

---

ChatGPTとの共同開発

今回の開発で最も印象に残ったのは、ChatGPTとの開発スタイルでした。

私はプログラマーではありません。

そのため、

「動きません。」

「エラーが出ました。」

「この画面の意味が分かりません。」

そんな質問ばかりでした。

そのたびにスクリーンショットを送り、

ChatGPTから

「次はこの画面を見せてください。」

「ここを修正しましょう。」

「原因はこの部分です。」

と、一つずつ問題を切り分けながら進めました。

まるで隣に開発者が座って、一緒に画面を見ながら作業しているような感覚でした。

ChatGPTとの実際のやり取りの一例

---

最後まで悩んだ社員番号の「0」

今回、一番時間を費やしたのが社員番号でした。
当社において社員番号は、正社員は 頭に０を付けた7桁 、パート・アルバイト社員は 通常の7桁 が現在の基本フォーマットとなっております。

CSVには

0295009

と記録されています。

ところがPower Automate Desktopで読み込むと

295009

となり、先頭の「0」が消えてしまいます。

Googleフォーム側は「0295009」

CSV側は「295009」

当然、照合できません。

CSVを開き直したり、

Google Apps Scriptを書き換えたり、

Power Automate Desktopを見直したり…。

何度も原因を探しました。

最終的には、

Google Apps Script側で社員番号を正規化する関数を作成し、

さらに未申請一覧へ登録する際には文字列として保存するよう修正しました。

社員番号比較に使用した関数

function normalizeEmployeeNo_(no) {
  return String(no).trim().replace(/^0+/, '');
}

この問題が解決したときは、本当にホッとしました。

GASコード
※長文なので不要な方はスルーしてください。

実際のGASコード①

const EMPLOYEE_MASTER_ID = '1SyGb9uG8axZwZf3I0uRjYSq4fNfDXhfyuD3QR7cYguU';
const SHEET_EMPLOYEE = '社員マスタ';

function onFormSubmit(e) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const formSheet = ss.getSheetByName('Form_Responses');
  const ledgerSheet = ss.getSheetByName('管理台帳');

  const lastRow = formSheet.getLastRow();
  const row = formSheet.getRange(lastRow, 1, 1, formSheet.getLastColumn()).getDisplayValues()[0];

  const employeeNo = String(row[1]).trim();
  const workDate = row[2];
  const overtimeType = row[3];
  const startTime = row[4];
  const endTime = row[5];
  const businessContent = row[6];
  const remarks = row[7];
  const applicationType = row[8];
  const managerMessage = row[9];
  const reason = row[10];

  const applyId = createApplyId_(workDate);
  const employee = getEmployeeInfo_(employeeNo);

  ledgerSheet.appendRow([
    applyId,
    applicationType,
    employeeNo,
    employee.name,
    employee.department,
    workDate,
    overtimeType,
    startTime,
    endTime,
    businessContent,
    reason,
    remarks,
    '',
    '',
    '',
    '',
    '未照合',
    '未承認',
    employee.approver,
    '',
    managerMessage,
    '',
    '未通知',
    '',
    '未出力',
    new Date(),
    employee.found ? '受付' : '社員番号要確認'
  ]);
}

実際のGASコード②

function createApplyId_(workDate) {
  const date = new Date(workDate);
  const ymd = Utilities.formatDate(date, 'Asia/Tokyo', 'yyyyMMdd');

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('管理台帳');

  const lastRow = sheet.getLastRow();
  const rangeRows = Math.max(lastRow - 1, 1);
  const ids = sheet.getRange(2, 1, rangeRows, 1).getDisplayValues().flat();

  const todayIds = ids.filter(id => String(id).startsWith('OT' + ymd));
  const nextNo = todayIds.length + 1;

  return 'OT' + ymd + String(nextNo).padStart(4, '0');
}

function getEmployeeInfo_(employeeNo) {
  const masterSS = SpreadsheetApp.openById(EMPLOYEE_MASTER_ID);
  const masterSheet = masterSS.getSheetByName(SHEET_EMPLOYEE);
  const values = masterSheet.getDataRange().getDisplayValues();

  for (let i = 1; i < values.length; i++) {
    const row = values[i];
    const masterNo = String(row[0]).trim();

    if (masterNo === employeeNo) {
      return {
        found: true,
        name: row[1],
        department: row[2],
        approver: row[6],
        approverEmail: row[7]
      };
    }
  }


  return {
    found: false,
    name: '社員番号未登録',
    department: '',
    approver: '',
    approverEmail: ''
  };
}

実際のGASコード③

function onEdit(e) {
  const sheet = e.source.getActiveSheet();

  if (sheet.getName() !== '管理台帳') return;

  const row = e.range.getRow();
  const col = e.range.getColumn();

  // 1行目は見出しなので対象外
  if (row === 1) return;

  // R列「承認状態」が編集された時だけ動く
  if (col !== 18) return;

  const approvalStatus = e.range.getValue();

  // S列：承認者
  sheet.getRange(row, 19).setValue(Session.getActiveUser().getEmail());

  // T列：承認日時
  sheet.getRange(row, 20).setValue(new Date());

  // Z列：最終更新日時
  sheet.getRange(row, 26).setValue(new Date());

  // AA列：レコード状態
  if (approvalStatus === '承認') {
    sheet.getRange(row, 27).setValue('承認済');
  } else if (approvalStatus === '差戻し') {
    sheet.getRange(row, 27).setValue('差戻し');
  } else if (approvalStatus === '修正依頼') {
    sheet.getRange(row, 27).setValue('修正依頼');
  } else {
    sheet.getRange(row, 27).setValue('承認待ち');
  }
}

/**
 * 社員番号と勤務日で管理台帳を検索する
 */
 

実際のGASコード④

 
function checkApplication(employeeNo, workDate) {

  const sheet = SpreadsheetApp.getActiveSpreadsheet()
    .getSheetByName("管理台帳");

  const values = sheet.getDataRange().getDisplayValues();

  // 1行目は見出しなので2行目から検索
  for (let i = 1; i < values.length; i++) {

    const row = values[i];

    // C列：社員番号
    const ledgerEmployee = normalizeEmployeeNo_(row[2]);
    // F列：勤務日
    const ledgerDate = row[5];

    if (
      ledgerEmployee === normalizeEmployeeNo_(employeeNo) &&
ledgerDate === workDate
    ) {

      return {
        found: true,
        row: i + 1,
        status: row[17],     // 承認状態
        applyId: row[0]      // 申請ID
      };

    }

  }

  return {
    found: false
  };

}

実際のGASコード⑤

function doPost(e) {
  const data = JSON.parse(e.postData.contents);

  if (data.mode === "update") {
    return updateAttendance(e);
  }

  const employeeNo = data.employeeNo;
  const workDate = data.workDate;

  const result = checkApplication(employeeNo, workDate);

  return ContentService
    .createTextOutput(JSON.stringify(result))
    .setMimeType(ContentService.MimeType.JSON);
}

/**
 * PADから受け取った照合結果を管理台帳へ反映する
 */

実際のGASコード⑥

 
function updateAttendance(e) {

  const sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("管理台帳");
  const unApplied = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("未申請一覧");

  const data = JSON.parse(e.postData.contents);

  const employeeNo = String(data.employeeNo);
  const workDate = data.workDate;
  const overtime = data.overTime;

  const values = sheet.getDataRange().getValues();

  let found = false;

  // 2行目から検索
  for (let i = 1; i < values.length; i++) {

    const emp = normalizeEmployeeNo_(values[i][2]);     // C列 社員番号
    const date = Utilities.formatDate(new Date(values[i][5]), Session.getScriptTimeZone(), "yyyy/M/d");

    if (emp == normalizeEmployeeNo_(employeeNo) && date == workDate) {

      // Q列（照合結果）
      sheet.getRange(i + 1, 17).setValue("照合済");

      // Z列（検索更新日時）
      sheet.getRange(i + 1, 26).setValue(new Date());

      found = true;
      break;
    }
  }
  

実際のGASコード⑦

  if (!found) {

  const displayEmployeeNo = normalizeEmployeeNo_(employeeNo).padStart(7, '0');
  const nextRow = unApplied.getLastRow() + 1;

  // B列（社員番号）を文字列形式にする
  unApplied.getRange(nextRow, 2).setNumberFormat('@');

  unApplied.getRange(nextRow, 1, 1, 7).setValues([[
    new Date(),
    displayEmployeeNo,
    workDate,
    overtime,
    "未申請",
    "未通知",
    ""
  ]]);

}

  return ContentService
    .createTextOutput(JSON.stringify({success:true, found:found}))
    .setMimeType(ContentService.MimeType.JSON);

}

function normalizeEmployeeNo_(no) {
  return String(no).trim().replace(/^0+/, '');
}

Apps Script 一覧

ここまでは比較的順調に進みました。

しかし、本当の苦労はここから始まります。

---

ここからが本当の開発だった

Googleフォームと管理台帳までは比較的順調に進みました。

しかし、本当に苦労したのはここからでした。

勤務実績は会社の勤怠システムからZIP⇒CSV形式で出力されます。

そのCSVをGoogleスプレッドシートの申請データと照合する必要があります。

そこで採用したのが Power Automate Desktop（PAD） でした。

勤務実績CSVの取得から照合までを自動化することで、毎朝行っている確認作業を少しでも短縮したいと考えました。

📷Power Automate Desktop 全体フロー

ZIPファイル取得・展開

勤務実績照合

勤務実績照合のRobin
※PADのフローをテキストに書き出したものです。スルーしても大丈夫です！

Folder.GetFiles Folder: $'''C:\\Users\\user\\Desktop\\時間外勤務管理システム\\04_勤務実績\\展開CSV''' FileFilter: $'''*.csv''' IncludeSubfolders: False FailOnAccessDenied: True SortBy1: Folder.SortBy.NoSort SortDescending1: False SortBy2: Folder.SortBy.NoSort SortDescending2: False SortBy3: Folder.SortBy.NoSort SortDescending3: False Files=> CsvFiles
File.ReadFromCSVFile.ReadCSV CSVFile: CsvFiles[0].FullName Encoding: File.CSVEncoding.SystemDefault TrimFields: True FirstLineContainsColumnNames: False ColumnsSeparator: File.CSVColumnsSeparator.SystemDefault CSVTable=> AttendanceTable
SET CurrentLine TO 0
Variables.CreateNewList List=> UnappliedList
LOOP FOREACH CurrentRow IN AttendanceTable
    Variables.IncreaseVariable Value: CurrentLine IncrementValue: 1
    IF CurrentLine <= 3 THEN
        NEXT LOOP
    END
    SET EmployeeNo TO CurrentRow[24]
    SET WorkDate TO CurrentRow[26]
    SET OverTime TO CurrentRow[87]
    IF OverTime = $'''0:00''' THEN
        NEXT LOOP
    END
    Web.InvokeWebService.InvokeWebServicePost Url: $'''https://script.google.com/macros/s/AKfycbw-1XljqJIMnbC2epfJ9l54uhBpx3HfjWCYb3BXQFGvzM_8cgswRScvykmqEEPS8W7r/exec''' Accept: $'''application/json''' ContentType: $'''application/json''' RequestBody: $'''{\"mode\":\"update\",\"employeeNo\":\"%EmployeeNo%\",\"workDate\":\"%WorkDate%\",\"overTime\":\"%OverTime%\"}''' ConnectionTimeout: 30 FollowRedirection: True ClearCookies: False FailOnErrorStatus: False EncodeRequestBody: False UserAgent: $'''Mozilla/5.0 (Windows; U; Windows NT 5.1; en-US; rv:1.8.1.21) Gecko/20100312 Firefox/3.6''' Encoding: Web.Encoding.AutoDetect AcceptUntrustedCertificates: False TrimRequestBody: True ResponseHeaders=> WebServiceResponseHeaders Response=> WebServiceResponse StatusCode=> StatusCode
    IF Contains(WebServiceResponse, $'''\"found\":false''', False) THEN
        Variables.AddItemToList Item: $'''%EmployeeNo%,%WorkDate%,%OverTime%''' List: UnappliedList
    END
END

---

---

プロトタイプVer.1完成

現時点で完成した機能は次のとおりです。

• Googleフォームによる時間外勤務申請
• 社員マスタとの自動照合
• 管理台帳への登録
• Power Automate Desktopによる勤務実績CSV取得
• Google Apps Scriptによる勤務実績との照合
• 未申請者一覧の自動作成

📷未申請一覧

ここまで完成したことで、

毎朝手作業で行っていた照合作業の大部分を自動化できる見通しが立ちました。

もちろん、まだ改善点は数多くあります。

しかし、「まず動くものを作る」という今回の目的は達成できたと思います。

---

ここからが本当のスタート

今回完成したのは、あくまでプロトタイプVer.1です。

このシステムを実際に使用するのは、私ではなく現場の後方担当者です。

そのため、次のステップとして実際に現場で使用してもらい、

• 分かりにくいところはないか
• 入力しづらい点はないか
• もっと欲しい機能はあるか

といった率直な意見を集めたいと思っています。

---

フィードバックいただいた内容

• EWさん（後方担当）

  • 従業員各個人個人がGoogleフォームで入力してくれるのはありがたいが、最初にそれを教えるのが大変そう。パソコンやスマホで入力といわれると、「難しい」「覚えられない」という拒絶反応が出る人もまだまだ多い。

  • 今まで紙に印刷して照らし合わせてチェックして、メモで修正依頼をしていたのがなくなれば大助かり。

  • 計画外の申請だけでなく、有給休暇の取得申請書も同じようにGoogleフォームや、LINEから申請して、パソコン上のデータで保管管理出来たらなお便利。

  • 会社の勤怠システムからデータを取るのはまだ人がやらなければならないので、そこまで自動化してくれたら、毎日のルーチンが30分から1時間削減できそう。

  
  

• KSさん（店舗総務）

  • 今まで3階の事務所に上がってきて計画外の申請書を書くのが大変だったので、これが実現したらみんな喜ぶと思う。
  • 年配の社員がまだまだ多いから、だれでも簡単な操作で処理ができるようにしてもらいたい。
  • 従業員の入れ替えがあった場合のメンテナンスはどうする？店長が異動してしまった後が不安。
  
  

• TSさん（副店長）

  • 事前や事後の申請があった際、その承認をするのは誰で、いつのタイミングか？　たとえば店長が休みや会議で不在のときはどうするのか？
  • 人事部や監査室との連携はとれるのか？紙でなくパソコン内のデータで保管してあれば会社としてＯＫとしてもらえると嬉しい。
  • 申請がタイムリーに承認者に届き、速やかにジャッジができるか？承認できない申請内容の場合はどうする？
  
  

• 気付いた改善点

  • システムはできても一般の従業員が使いにくかったらむしろ負担増。
  • 今日入社した人でもわかる簡単なインターフェイスで申請ができる仕組みが必要。
  • 日次の記録の残し方の検討・・・監査時などにわかりやすく統一された書式で出力できることが必要（PDF化）。
  • まだまだ運用上の課題は多い。

---

今後の改善予定

今回いただくフィードバックをもとに、Ver.2では次のような機能を追加したいと考えています。

• Gmailによる未申請通知
• 承認依頼メールの自動送信
• 管理者向けダッシュボード
• PDF出力
• AppSheetとの連携

現場の声を取り入れながら、少しずつ育てていく予定です。

---

まとめ

今回の課題を通して、私が一番学んだことがあります。

それは、

「完成してから使ってもらう」のではなく、まず動くものを作り、使ってもらい、改善していくことが大切だということです。

システム開発というと、完成形を目指して作り込むイメージがありました。

しかし実際には、現場で使ってもらうことで初めて気付くことがたくさんあります。

今回完成したプロトタイプVer.1も、まだスタートラインに立ったばかりです。

これから現場の皆さんの意見を取り入れながら、より使いやすいシステムへ育てていきたいと思います。

この記事を読んでくださった皆さんも、

「もっとこうしたら良いのでは？」

というアイデアがありましたら、ぜひコメントで教えていただけるとうれしいです。

最後までお読みいただき、ありがとうございました。