はじめに
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なので今見ると粗が目立ちますが、粗と感じることができるなら、それもまた成長なのかもしれません。
最後まで読んでいただきありがとうございました。