目的
元々私が検討していた内容として以下の内容がありました。
- OfficeScriptsで複数項目の集計をするとき、可読性やスピードをどうやって維持するか
これはどのプログラムも共通の課題でして…SQLとして扱うところの
SELECT SEX, PREF, SUM(HOLDBALL) FROM A GROUP BY SEX, PREF
をコードで記載するのってかなり難しんですよ。要は複数項目でグルーピングしつつ、集計値を出すと言う実装になります。ゴリ押しすると可読性崩壊、スピード大幅減少は免れません。なので、ここをなんとかと言う形で検証していきます。
前提
このコードはあくまで検証用のサンプルとなります。そのため、テスト等は行なっておりません。ご利用の際は、予めその旨ご理解の上で、ご利用してください。
また、書いている私は人間です。ChatGPTに書かしているわけではありませんし、普通に間違えます。間違えがあったとしても「ああ、間違えてるわ〜」程度で流して頂けると幸いです。
本題の前に
今回の件の他にSQLのJOINを再現するのも結構曲者な感じがします。ただ、OfficeScriptsの場合最大のパートナーであるPowerAutomateがそれを対処してくれる↓(リンク先参照：外部リンク)ので今回は検討外とします。
https://mofumofupower.hatenablog.com/entry/json_dictionary
初めてみた時、感動しました。これなら万件単位の結合も秒で処理出来ますからね。
課題について
単純に考えてみる
これだけを聞くと
- OfficeScripts で for や if 等でゴリ押せば良くね？
- PowerAutomateにやらせれば良いじゃん
って、なりそうですが…間違いなく可読性崩壊＆スピードが出ません。
データを処理すると検討した場合相手のデータは数万件あると常に頭に入れておいてください。
forとかPowerAutomateのコントロールはデータ件数に比例して時間が延びます。
一順でも地獄なのにネスト構造なんかやったら崩壊です。
もうちょっと考えてみる
まあ、鉄板なのがMapクラス(巷ではDictionaryクラス)を使ってキーに項目、値に集計値を
入れるという方法です。確かにこれならいけそうなのですが…ここでOfficeScriptsの落とし穴
キー値には数字や文字列しか入れることが出来ないと言う課題に直面します。
Javaの場合にはgroupingByを使ってキーにオブジェクトを定義することで
複数キーを持ったオブジェクトをキーにすることが出来ます(equalsやhashCodeは
オーバーライド必須)。なので、集計後にentrySet()とかを使えば、キーで利用した値と
その集計結果全部を一度に取得出来ます。
しかし、OfficeScriptsではキー値には数字や文字列しか入れることが出来ないため、
キーを強引に(JSON.stringifyとか使って)文字列にしてキーになんてしたら
キーで利用した複数の項目データが死んでしまい復元出来ない→地獄を見るのです。
え？オブジェクトをキーにしてもコンパイルエラーが起きないし使えるのでは？って？
…基本データ型と参照型の違いを一度勉強してからこちらに戻ってきてください。
余談ですが、OfficeScripts系列にequalsやhashCodeなんてものはありません。
→こうして見ると、JavaのequalsやhashCodeのような比較仕組みの重要さがよく分かります。
Mapクラスを改造してみた
じゃあ、どうするか…？そうだ、オブジェクトをキーとして定義出来るMapクラスを別途作れば良いじゃない！が結論になりました。…ってか、利便性あるに決まってるから実装してくれれば良いのに…ってことで作成。
→そして作っているうちにあ、これどんなに頑張っても汎用性無理だわって分かったので後で解説
CustomMapクラスを作ってみた
キーにオブジェクトを入れても正常作動するCustomMapはこちら。結構良いもの出来た(使い方も既存のMapと酷似)と勝手に思ってます。
/**
* CustomMap クラス
*
* このクラスは、キーとしてオブジェクトを使用し、キーオブジェクトの内容を比較するために、
* 参照管理と内容管理を同時に行います。`keyRef` によって、オブジェクトの参照をキーとして管理し、
* 内容が同一であれば同じキーとして扱います。
*
* 【利用シーン】
* - オブジェクト内容でキーを比較したい場合
* - 参照による比較が必要な場合
*
* @template K - キーの型
* @template V - 値の型
*/
class CustomMap<K, V> {
private map: Map<string, V>;
private keyRef: Map<string, K>; // 参照管理用
constructor() {
this.map = new Map<string, V>();
this.keyRef = new Map<string, K>();
}
/**
* キーを文字列に変換するためのメソッド(Object.values(key).join(“|”)と検討)
*
* @param key オブジェクトのキー
* @returns キーを文字列に変換した結果
*/
private keyToString(key: K): string {
return JSON.stringify(key); // キーを文字列に変換
}
/**
* オブジェクトの内容をキーとして使用し、値をセットするメソッド
*
* @param key オブジェクトのキー
* @param value セットする値
*/
set(key: K, value: V): void {
const keyStr = this.keyToString(key); // keyToString を使って文字列化
this.keyRef.set(keyStr, key); // keyRefに参照を保持
this.map.set(keyStr, value);
}
/**
* キーに対応する値を取得するメソッド
*
* @param key 取得するキー
* @returns キーに対応する値
*/
get(key: K): V | undefined {
const keyStr = this.keyToString(key); // keyToString を使って文字列化
return this.map.get(keyStr);
}
/**
* キーが存在するかをチェックするメソッド
*
* @param key チェックするキー
* @returns キーが存在すれば true, それ以外は false
*/
has(key: K): boolean {
const keyStr = this.keyToString(key); // keyToString を使って文字列化
return this.map.has(keyStr);
}
/**
* マップの全てのキーを取得するメソッド
*
* @returns 全てのキーの配列
*/
keys(): K[] {
return Array.from(this.keyRef.values()); // キーをオブジェクトのまま取得
}
/**
* マップの全ての値を取得するメソッド
*
* @returns 全ての値の配列
*/
values(): V[] {
return Array.from(this.map.values());
}
/**
* マップの全てのエントリー（キーと値）を取得するメソッド
*
* @returns 全てのエントリーの配列
*/
entries(): { key: K; value: V }[] {
return Array.from(this.map.entries()).map(([keyStr, value]) => ({
key: this.keyRef.get(keyStr)!,
value,
}));
}
/**
* マップのサイズ（エントリー数）を取得するメソッド
*
* @returns マップのサイズ
*/
size(): number {
return this.map.size;
}
/**
* マップをクリアするメソッド
*
* @returns なし
*/
clear(): void {
this.map.clear();
this.keyRef.clear();
}
}
汎用化する上での課題(と今後の為の考察)
いやー。これ使えば神や〜とか思っていたのですが…以下の部分がボトルネックなんですよ。うーん、汎用性あるものって作るの難しいですねぇ。
/**
* キーを文字列に変換するためのメソッド(Object.values(key).join(“|”)と検討)
*
* @param key オブジェクトのキー
* @returns キーを文字列に変換した結果
*/
private keyToString(key: K): string {
return JSON.stringify(key); // キーを文字列に変換
}
ちょっと考察してみますか。
JSON.stringify(key)の場合
一番汎用性があるのはこの書き方です。まあ特殊な使い方をするとこれすらも壊せますが、それはさておきやっぱり処理速度問題が生じてしまいます。
というのも、この書き方だとキーとなる文字列が非常に長くなってしまいます。詳しい内容は自分でログを取って見ていただきたいのですが…イメージとしては
{"sex":"man","pref":"北海道"}
みたいな感じです(実際はエスケープ文字とかも入ったりするのでもっと長くなる場合もある)。今回は2項目だけですが…10項目とかあったら大惨事です。
そもそも{}とか"sex""pref"とかは固定な文字列なので要らないじゃないですか。これが入ってきてしまうのでキーが長くなりスピード遅延という理論です。他にも特殊な使い方(普通に使っている分には問題なし)をすると壊せてしまうという課題もあったりします。
Object.values(key).join(“|”)の場合
上記を解決する手段がこの書き方です。これを使えば
"man|北海道"
みたいなキーになるので、非常にシンプルになります。じゃあこれで良いじゃん！と思いそうですが甘い。仮に、キーに入れるオブジェクトの中に更に参照型があったら詰むのです。逆に先ほどのJSON.stringifyだと、参照型がオブジェクトの中の変数にあっても問題ありません。…うーん。
じゃあ他にないの？
他にもtoString()をオーバーライドするという手段もあるのですが…それをすると、全部のキーにtoString()を手動で記載しないといけない面倒臭さがありまして、Javaじゃないので絶対皆んな無視するじゃないですか？ってなってくると汎用性には適さないと言う結論になっています。
結論は？
結局何使えば良いのさ？って言われそうなので一応まとめときます。正しいか否かの最終判断は自己責任でよろしくお願いします。
| 利用場面 | JSON.stringify | Object.values | toString |
|---|---|---|---|
| キー内に参照型あり | ◯ | × | △ |
| キー内に参照型なし | △ | ◯ | △ |
CustomMapクラスを使って集計してみよう
まあ、課題はさておき(細かい部分なので使い方さえ誤らなければ正しく動きます)…どうやって使うのかわからないと意味ないので使い方例を紹介。今回は使い方紹介なので変な文字が入ったらーみたいなthrow部分は適当です。
Power Automate から渡されるデータの想定
PowerAutomate側からはJSON形式で以下のような配列が送られる想定です。このデータは少なすぎるがゆえ、集計はされませんが(キーが1通りしかないため)、実際は1万データ以上似たようなデータがあるとお考えください。
[
{
"sex" : "man",
"pref" : "北海道",
"holdBall" : 10
},
{
"sex" : "wowam",
"pref" : "青森",
"holdBall" : 20
}
]
メイン関数
実際に動かす際のメイン関数内部はこちら
// InputDataJsonインターフェースは後ほど記載
// InputDataクラスは後ほど記載
// InputDataKeyクラスは後ほど記載
// OutputDataクラスは後ほど記載
// CustomMapクラスは先ほど定義したものを記載
/**
* main関数
*
* PowerAutomateから受け取ったデータを集計し、結果を返すメインの処理を行う。
*
* @param workbook ExcelScript.Workbookオブジェクト
* @param inputDataJson 集計対象となるユーザーデータの配列
* @returns 集計結果と処理ステータス
*/
function main(workbook: ExcelScript.Workbook, inputDataJson: Array<InputDataJson>) {
try {
// CustomMapクラスを呼び出し
const customMap = new CustomMap<InputDataKey, number>();
// JSONデータをクラスに格納
const inputData = inputDataJson.map(i =>
new InputData(i.sex, i.pref, i.holdBall)
);
// データをマップに格納し集計
// ここでは集計だが、応用すればカウントやキーに紐づくデータを集約することも出来る
inputData.forEach(item => {
const key = item.getKeys();
const existingValue = customMap.get(key) || 0;
customMap.set(key, existingValue + item.getHoldBall()); // ボールの数を加算
});
// 集計結果を生成
const outputDataList = customMap.entries().map(({ key, value }) => new OutputData(key, value));
// 成功時のレスポンス
return {
status: "success",
result: outputDataList
};
} catch (error) {
// エラー発生時のレスポンス
return {
status: "error",
detail: {
message: error instanceof Error ? error.message : "不明なエラー",
stack: error instanceof Error ? error.stack : null // エラーのスタック情報を含める
}
};
}
}
クラス
上記のメイン関数を動かすために予め指定するクラスです。main関数の上に記載します。
CustomMapクラスについては既に記載済みのため省略。
/**
* PowerAutomateから受け取るデータの型を定義
* 性別、都道府県、保有ボールの数を持っています。
* @interface InputDataJson
*/
interface InputDataJson {
/** 性別 */
sex: string;
/** 出身都道府県 */
pref: string;
/** 保有しているボールの数(PowerAutomate→OfficeScriptは常に文字列でしか受け取れない) */
holdBall: string;
}
/**
* InputDataクラス
*
* ユーザーの情報を格納するためのクラスです。性別、都道府県、保有ボールの数を持っています。
*
* @param sex 性別（"man" または "woman"）
* @param pref 出身都道府県
* @param holdBall 保有しているボールの数（文字列で受け取る）
*/
class InputData {
private sex: string;
private pref: string;
private holdBall: number;
constructor(sex: string, pref: string, holdBall: string) {
if (!sex || !pref || !holdBall)
throw new Error("不正な入力データがあります。全てのフィールドを正しく入力してください。");
this.sex = sex;
this.pref = pref;
this.holdBall = Number(holdBall); // 文字列のボール数を数値に変換
if (isNaN(this.holdBall)) {
throw new Error(`Invalid holdBall value: ${holdBall}`);
}
}
/**
* キーとなるオブジェクトを取得する
*
* @returns InputDataKeyオブジェクト
*/
getKeys(): InputDataKey {
return new InputDataKey(this.sex, this.pref);
}
/**
* 保有ボールの数を取得する
*
* @returns 数値型の保有ボールの数
*/
getHoldBall(): number {
return this.holdBall;
}
}
/**
* InputDataKeyクラス
*
* InputDataのキーとなる情報（性別と都道府県）を格納するクラスです。
*/
class InputDataKey {
private sex: string;
private pref: string;
constructor(sex: string, pref: string) {
this.sex = sex;
this.pref = pref;
}
/**
* 性別を取得
*/
getSex(): string {
return this.sex;
}
/**
* 都道府県を取得
*/
getPref(): string {
return this.pref;
}
/**
* キーが等しいかどうかを比較するメソッド
*
* これを実装していなくてもCustomMapは正常に動きますが…
* キーの比較ということもあり念の為記載しています。
*/
isEqual(other: InputDataKey): boolean {
return this.sex === other.sex && this.pref === other.pref;
}
}
/**
* OutputDataクラス
*
* 集計結果を格納するためのクラスです。
*/
class OutputData {
private sex: string;
private pref: string;
private holdBall: number;
constructor(key: InputDataKey, holdBall: number) {
this.sex = key.getSex();
this.pref = key.getPref();
this.holdBall = holdBall;
}
}
まあ、setterやconstructorの制約を…とか言われそうですが、今回はそれが主題ではないので見なかったことにしてください。
集計はどこでやってるの？
以下の部分で実施しております。
// データをマップに格納し集計
// ここでは集計だが、応用すればカウントやキーに紐づくデータを集約することも出来る
inputData.forEach(item => {
const key = item.getKeys();
const existingValue = customMap.get(key) || 0;
customMap.set(key, existingValue + item.getHoldBall()); // ボールの数を加算
});
オブジェクトキーを生成させた上でそれに紐づく既存の集計結果値をCostomMapクラスから取得(なければ初期値定義)。そして、集計結果の値を同じキーとして上書き保存します。まあ、一順は免れませんが…forよりforEachの方が信頼ありますし、可読性も維持出来ていると思います。
集計以外にも応用出来ない？
ふむ…思いつく限りではカウントや集約でしょうかねぇ。
カウントしてみる
inputData.forEach(item => {
const key = item.getKeys();
const existingCount = customMap.get(key) || 0;
customMap.set(key, existingCount + 1); //++existingCountでも良い？existingCount++はNG。
});
同一キーで集約してみる
inputData.forEach(item => {
const key = item.getKeys();
const existingList = customMap.get(key) || new Array();
existingList.push(item); // リストに追加
customMap.set(key, existingList); // リストごと保存。追加→保存で分割しないと配列の長さ(push()の戻り値)がinputされるらしいので注意
});
まあ、こんな感じで応用も効くので適宜検証してみてください。
まとめ
今回、OfficeScriptsで複数項目の集計をするときどうするか？というテーマで、CustomMapを使った手法を試してみました。いや、中々都合よく汎用性のあるものは出来ませんねぇ。何か「こうすると良いんじゃない？」みたいな案があれば、コメントよろしくお願いします。
最後に
うん。私が動く前にOfficeScriptでのこれぐらいの使い方情報はネットに出回っていて欲しかったなぁ。まずどこにもPowerAutomateからの受け取りやPowerAutomateへの受け渡し方…あとこのような日常で使いそうな集計絡みが落ちていないのがおかしいのよ。私も研究気質があるので、〇〇出来ないかなぁ…と調査したい気持ちはわからなくないですが…まずは最低ラインでの実務で利用出来る水準の情報は流して欲しいです。よろしくお願いします。