目的
いや、ネットに書いてあるOffice Script情報があまりに可読性悪すぎてモヤモヤするので勢いで書くことにしました。ちゃんと他者が見て読めるものを書きましょう。
事例
簡潔にこんなので良いでしょ。これだけで、大体の書き方は分かると思います。
- Power Automate から複数データを持ってきてください
- Excel には何かしらのテーブル1, 2があります(テーブル名は Power Automate で定義する)
- テーブル1にはPower Automateから持ってきた値を入れます
- テーブル2から値を取得します
- Power Automateにテーブル2のデータを持っていきます
可哀想だから、日付の処理も書いてあげるか。
前提
私の経験でコードを記載しています。実際の環境でテスト等はしていません。なので動かなかったとしても責任は取りません。ご了承ください。
また、書いている私は人間です。ChatGPTに書かしているわけではありませんし、普通に間違えます。間違えがあったとしても「ああ、間違えてるわ〜」程度で流して頂けると幸いです。
Power Automate から渡されるデータの想定
PowerAutomate側からはJSON形式で以下のような配列が送られる想定です。逆にPowerAutomateへ送る場合も同じような配列を想定しています。
[
{
"name" : "田中太郎",
"age" : 17,
"birth" : "2008-5-1"
},
{
"name" : "佐藤次郎",
"age" : 25,
"birth" : "1999-1-20"
}
]
サンプルコード
メイン関数
まずはメイン関数。実際の処理からです。
// InputDataインターフェースは後ほど記載
// InputPersonDataクラスは後ほど記載
// OutputPersonDataクラスは後ほど記載
// excelDateToJSDate関数(メインでは使ってないけど)は後ほど記載
/**
* メイン処理を行います。Power Automateから受け取ったデータをExcelに書き込みます。
*
* @param workbook ExcelScript.Workbookオブジェクト
* @param table1Name テーブル1の名前
* @param table2Name テーブル2の名前
* @param inputData Power Automateからの入力データ
* @returns 結果のステータスとデータ
*/
function main(workbook: ExcelScript.Workbook, table1Name: string, table2Name: string, inputData: Array<InputData>) {
try {
// テーブル定義
const table1 = workbook.getTable(table1Name);
const table2 = workbook.getTable(table2Name);
// 取得データをクラスへ再格納
const inputPersonDataList = inputData.map(i =>
new InputPersonData(i.name, i.age, i.birth)
);
// テーブル1に書き込む（生年月日は文字列に変換）
table1.addRows(-1, inputPersonDataList.map(i =>
[i.getName(), i.getAge(), i.getBirth().toLocaleDateString()]
));
// テーブル2からデータを取得
const outputPersonDataList = table2.getRangeBetweenHeaderAndTotal().getValues()
.map(i =>
new OutputPersonData(i[0] as string, i[1] as number, i[2] as number)
);
// 結果を返却
let result = {
status: "success",
dataList: outputPersonDataList
};
return result;
} catch (error) {
// エラーが発生した場合にその旨をPower Automateを通知する
let result = {
status: "error",
message: error.message || "不明なエラーが発生しました。",
stack: error.stack || "スタックトレースは取得できませんでした。"
};
return result;
}
}
簡単に解説すれば
- Excel(外部)へのデータのやり取りをするならtry-catchで囲んでおく
→finallyは不要(開け閉めをOfficeScriptで実施しないため)
→正常終了か異常終了かでstatusを作っておく - テーブル設定→テーブル1への書き込み→テーブル2からの読み込み→結果返却
→主線以外の情報を書かない
でしょうか。データの返却はクラスとしてリストに格納して持っていくことを推奨です。
むしろそうしないと、PowerAutomateでのデータ処理がややこしくなります。
データのインプットはインターフェースが個人的には推奨です。詳細の考察は後ほど記載します。
クラス／関数
上記のメイン関数を動かすために予め指定するクラス／関数等が以下になります。OfficeScript(だけじゃなさそう？)では、先に色々定義した上で最後にメイン関数を記載するのが処理的にも主流らしいので先ほどのmain関数の上に記載します。
/**
* PowerAutomateから受け取るデータの型を定義
* テーブルのデータを処理するために必要な情報を格納します
* @interface InputData
*/
interface InputData {
/** 名前 */
name: string;
/** 年齢(PowerAutomate→OfficeScriptは常に文字列でしか受け取れない) */
age: string;
/** 誕生日(PowerAutomate→OfficeScriptは常に文字列でしか受け取れない) */
birth: string;
}
/**
* 受け取ったデータを処理するためのクラス
* クラス内で名前、年齢、誕生日を管理し、getterを提供します
* @class InputPersonData
*/
class InputPersonData {
private name: string;
private age: number;
private birth: Date;
/**
* クラスのインスタンスを初期化
* @param name 名前
* @param age 年齢(文字列から数値に変換)
* @param birth 誕生日(文字列からDate型に変換)
* @throws {Error} 入力データに不正な値が含まれている場合にエラーを投げます
*/
constructor(name: string, age: string, birth: string) {
if (!name || !age || !birth)
throw new Error("不正な入力データがあります。全てのフィールドを正しく入力してください。");
this.name = name;
this.age = Number(age); // 年齢を数字として扱えるように変換
this.birth = new Date(birth); // 誕生日を日付として扱えるように変換
// もし年齢や誕生日が不正な場合にもエラーを投げる
if (isNaN(this.age) || isNaN(this.birth.getTime()))
throw new Error("不正な年齢または誕生日です。");
}
/** 名前を取得 */
public getName(): string {
return this.name;
}
/** 年齢を取得 */
public getAge(): number {
return this.age;
}
/** 誕生日を取得 */
public getBirth(): Date {
return this.birth;
}
}
/**
* Excelからデータを処理するためのクラス
* テーブル2から取得したデータをオブジェクトとして保持します
* @class OutputPersonData
*/
class OutputPersonData {
private name: string;
private age: number;
private birth: Date;
/**
* コンストラクタでデータを受け取り、Date型に変換して保存
* @param name 名前（空文字不可）
* @param age 年齢（0以下・NaN不可）
* @param birth Excelのシリアル値から変換した誕生日（0以下・NaN不可）
* @throws {Error} 不正なExcelデータが渡された場合
*/
constructor(name: string, age: number, birth: number) {
// データの有無＋型・数値チェックを厳密に行う
if (!name || isNaN(age) || age <= 0 || isNaN(birth) || birth <= 0)
throw new Error("不正なExcelデータです。");
this.name = name;
this.age = age;
this.birth = excelDateToJSDate(birth);
// 上のthrowで不正データは基本弾けるが、将来的に関数が変わっても
// 壊れにくくするための保険
if (isNaN(this.birth.getTime())) throw new Error("不正な誕生日データです。");
}
}
/**
* Excelのシリアル値をJavaScriptのDateオブジェクトに変換します。
*
* @param excelSerial Excelで使用されるシリアル値
* @returns {Date} 変換されたJavaScriptのDateオブジェクト
*/
function excelDateToJSDate(excelSerial: number): Date {
const excelBaseDate = 25569; // ExcelとJSの基準日差 (1970-01-01)
const msInDay = 86400000; // 1日のミリ秒
return new Date((excelSerial - excelBaseDate) * msInDay);
}
個人的ですが、可読性崩壊を回避するために
- throwのような強制エラーはsetterかconstructorの中にのみ書く
というルールを設けております。書かなきゃ良くね？と思われそうですが、書いておかないと次世代の開発者が追加で何か開発するときに変な値を入れてきてそのまま実装されてしまい「運用中にエラーorなんか変な処理される」と言うような事故物件は避けたいと言うのがあります。
まあ、メイン関数であえて「InputDataインターフェース」→「InputPersonDataクラス」にしているのも「変な値が入ったらエラーにして処理を強制終了させるために入れています。
実際だと、「InputPersonDataクラス」へ到達する前に変な値が入っていないかチェックして入っていたら却下通知とか飛ばすんでしょうけど…今回は全データ正常な値が入っている前提での処理になっているので気にしない。
なぜclassではなくinterfaceでPowerAutomateのデータを受け取るのか？
ここら辺私の直感なんですよ。だけど、直感って書くと文句を言われそうなので…ChatGPTとの協力のもと以下のようにまとめてみました。
- PowerAutomateはJSONとしてデータを保持している
- TypeScriptのclassをそのまま型として指定するとnewされないため、プロトタイプの
初期化が出来ずコンパイルエラーや予期せぬ挙動の原因になる
実際、私が実装してた頃は永遠と意味不明なコンパイルエラーでしたし。もうちょっと上記を分かりやすくするなら…
- interface は「ただの形の定義（構造チェック）」
→JSONデータを直に受け取っても問題ない - class は「実体＋構造＋関数」←でもJSONデータから直接生成することが出来ない
→JSONデータは直接受け取ることが出来ない
でしょうかね。ここはまだブラックボックスです。
工夫点？
いや、私はもう記載することなくね？あとは勝手に。って思っていたのですが…某ChatGPTから以下のような内容を書けって言われまして…。私日本語苦手なので適当に簡略化してペタって貼っておきますね。別に強調することじゃないと思うんだけどなぁ。
as number の処理における落とし穴とその打開について
メイン関数内にある↓と
new OutputPersonData(i[0] as string, i[1] as number, i[2] as number)
OutputPersonDataクラスのコンストラクタにある↓について
constructor(name: string, age: number, birth: number) {
// データの有無＋型・数値チェックを厳密に行う
if (!name || isNaN(age) || age <= 0 || isNaN(birth) || birth <= 0)
throw new Error("不正なExcelデータです。");
this.name = name;
this.age = age;
this.birth = excelDateToJSDate(birth);
// 上のthrowで不正データは基本弾けるが、将来的に関数が変わっても
// 壊れにくくするための保険
if (isNaN(this.birth.getTime())) throw new Error("不正な誕生日データです。");
}
例えば、age = "twenty"とか入ってきたときにisNaN(age)を入れておかないと、不正値って気づけないんですよねぇ。"twenty" as numberはエラーではなくNaNを返却するだけで正常に通過してしまいます。型変換ミスはエラー処理じゃないの？ってJavaの世界で生きていた私には意外な感覚でした。
でChatGPTではこう自慢しておけって言われました。
この問題に気づけたのは、自分でも意外だったのですが、
「変なデータが入ってくる可能性」を常に疑う習慣があったからだと思います。
業務ではこういうチェックが多すぎて「遅い」と言われがちでしたが、
Qiitaのように読み手がバグの予防策を求めている場所では、むしろ強みになると感じました。
絶対言い訳乙って上司にケチつけられるんだよなぁ。開発も時折やってますがQCDとか言っておきながら世の中皆んな「スピード重視だ！」「あと金だ！」ですよ？Q？知らんな？って。「お、君早くシステム作れるんだね。残業もしてくれるのか。うん良い子だ。次も頼むよ。」「あ？要件定義だ？設計書だ？テストだ？定時退社？遅い。君使えない。」これが現実。で、圧かけておいてトラブル起きてから「誰だこんな問題だらけのものを作ったのは！」ってなるのが日常茶飯事。「急かすお前が悪い」「残業まみれで上司怖くて注意力散漫なんだが…？」っていつも突っ込みたい(大分今はマシになりましたが、昔は酷かった)。
isNaN(this.birth.getTime())のチェックって必要？不要？
さっきのコンストラクタ
constructor(name: string, age: number, birth: number) {
// データの有無＋型・数値チェックを厳密に行う
if (!name || isNaN(age) || age <= 0 || isNaN(birth) || birth <= 0)
throw new Error("不正なExcelデータです。");
this.name = name;
this.age = age;
this.birth = excelDateToJSDate(birth);
// 上のthrowで不正データは基本弾けるが、将来的に関数が変わっても
// 壊れにくくするための保険
if (isNaN(this.birth.getTime())) throw new Error("不正な誕生日データです。");
}
って、よくよく見るとisNaN(this.birth.getTime())のチェックは絶対TRUEになることがないとわかります。まあ、とは言っても今後誰かがexcelDateToJSDateの中身とか改造するかも知れませんし、まあフォーマットとか今後のためとか、そんな感じで常に書くように努めようと思っています。
最後に
そういえばQiitaに転がっているOfficeScriptのコードって集計関数絡みの可読性皆んな悪いなぁ…って思っているこの頃です。SQLで言うところのGROUP BYとかJavaで言うところのgroupingByが、OfficeScriptだとなんか微妙なんですよねぇ。OfficeScriptというよりTypeScript全般的に。
可読性悪い = 処理スピード出ない(万件数分なんて論外よ？)という持論を持っている(水泳と同じ)ので、ここもなんとかしたいですねぇ。