はじめに

2024年秋ごろの初心者奮闘記。
このころはまだ（自分の置かれた環境では）生成AIにコードを書いてもらうことが難しく、四苦八苦していた思い出があります。

前提

対象となる帳票を日付等でフィルターし、期日を過ぎた対象案件だけを抽出するスクリプトを作成しました。
PowerAutomateと組み合わせ、対象案件の担当者にはアラートメールを送る業務改善に使用します。

Excelブックの概要

[申請]シート　　　　…　承認を貰いたい案件について各担当者が記入するシート
[社員マスタ]シート　…　社員の社員コードや氏名等のマスタ
[申請フロー]シート　…　担当者がどの上長に承認を求めるか？が記載されたシート

改修前のスクリプトと課題

最初に作ったOffice Scriptsでは、フィルター後のデータを新しいSheet（Sheet1）に出力していました。

let sheet1 = workbook.addWorksheet();
sheet1.getRangeByIndexes(...).setValues(...);

この方法、動くには動くのですが…

他の人が先に Sheet1 を作ってしまうとエラー
PowerAutomateのフロー内で Sheet1 を削除する別スクリプトが必要
その別スクリプトも（なんらかの理由で） Sheet1 が無いとエラー

上記の理由から、運用が不安定という問題がありました。

改修の方針

そこで、「Sheet1を使わず、JSON形式で返す」という方針に変更しました。

目的

PowerAutomateで後続処理がしやすくなる
エラーが減る
スクリプトの実行が2回→1回になる

実装で苦労した点

今回の改修で一番苦労したのは、他シートの情報を紐づけてJSONにまとめる処理でした。特に以下の4つのポイントでつまずきました。

1. Map の使い方がわからない
[社員マスタ]シートの情報を社員コードで引けるようにしたかったのですが、Map の使い方が最初は全然ピンときませんでした。

const memberMap = new Map(memberData.map(row => [String(row[0]), row]));

この書き方で、row[0] をキーにして、row 全体を値として格納しているのですが、
row[0] が何列目の何の情報なのか？
row[1] や row[5] を取り出すときに、どの列が何を意味しているのか？
というのが、Excelの列構成と照らし合わせながらでないと理解できず、何度も console.log() で中身を確認しました。

2. undefined に悩まされる
Map.get() で値を取り出すとき、キーが存在しないと undefined が返ってきます。

let name = memberMap.get(original社員コード) ? String(memberMap.get(original社員コード)[2]) : "";

このように三項演算子で undefined を避けるようにしましたが、最初は TypeError: Cannot read properties of undefined が頻発していました。

Map に正しくデータが入っていない
キーが文字列になっていない（String() を忘れていた）
row[2] が空だった

など、原因が複数あり、デバッグに時間がかかりました。

3. 複数の Map を使うと混乱する
[承認フロー]シートから「苗字 → 社員コード」「苗字 → 社員コード → メールアドレス」といった紐づけをするために、listMap1 と listMap2 を作りました。

const listMap1 = new Map(listData.map(row => [String(row[0]), String(row[1])]));
const listMap2 = new Map(listData.map(row => [String(row[2]), String(row[3])]));

この2つの Map を使って、承認者やリーダーのアドレスを引く処理を書いたのですが、

どっちの Map がどの紐づけなのか？
row[0]〜row[3] が何の列なのか？
が混乱して、何度もExcelの元データを見返しました。

4. 日付の変換も地味に難しい
Excelの日付はシリアル値で格納されているため、JavaScriptの Date オブジェクトに変換する必要があります。

let date = new Date((Number(values7[i][0]) - 25569) * 86400 * 1000);
納入期日 = date.toISOString().split('T')[0];

この「25569を引いて、86400×1000を掛ける」という変換式も、最初は意味がわからず、調べながら書きました。

改修後のスクリプトのポイント

最終的には、以下のような形でJSONを返すようにしました。

jsonData.push({
  社員コード: 社員コード,
  担当者氏名: 担当者氏名,
  申請No: String(values3[i][0]),
  件名: String(values4[i][0]),
  担当者アドレス: 担当者アドレス,
  承認者アドレス: 承認者アドレス,
  リーダーアドレス: リーダーアドレス,
  納入期日: 納入期日,
});
return jsonData;

これで、PowerAutomate側でJSONを受け取って処理できるようになり、Sheet1を使わない運用が実現できました。

おわりに

約1年前に書いたOfficescriptsなので今見ると粗が目立ちますが、粗と感じることができるなら、それもまた成長なのかもしれません。
最後まで読んでいただきありがとうございました。