【Office Script】順列に応じた並び替え処理

VBAはいろいろできるものの、Power AutomateだとかOffice Scriptだとかで肩身の狭い思いがします。
今は、Teams上の組織のデータを集約する処理をPower Automateで作っていますが、Power AutomateでデータをExcelに追加すると、Excelのくせに一番最下行にしか追加してくれません。

そのため、データの追加のあとOffice Scriptで並び替えをするコードをつくってみました。しかし会社毎に並び替える際に、やはり順序というものがありますので、会社名テーブルに会社の順列でテーブルを作り、それに応じて並び替えをするというものになります。

ちなみにこれを実行する際に、単純に実行してしまうとExcelのデータ追加が終わってないのに実行されたりしてしまいます。ですので、Excelのデータ追加の完了を確認するフローを実行後に、並び替えスクリプトを実行という具合にしないとうまくいかないかと思います。

とりあえず順列での並び替えです。正直VBAばかりやっていたので、スクリプトすべてを理解できていないですが、とりあえずChatGPTの力を借りて動くものができました。しかし、ChatGPTにそのまま頼むと、何やら非同期での処理を記述する古いOfiice Scriptというのがあるらしく、「ばかやろう、最新の同期スタイルで記述しやがれ」としかってあげないと出力されないので要注意です。

あとは＋ブックに追加してTeamsファイルにUpしないとだめですね。

function main(workbook: ExcelScript.Workbook) {
const companyNameSheet = workbook.getWorksheet("会社名");
const destinationListSheet = workbook.getWorksheet("宛先一覧");
// 「会社名」シートのA2セルから最終行までのデータを取得
const lastRowCompany = companyNameSheet.getUsedRange().getRowCount();
const companyNamesRange = companyNameSheet.getRange("A2:A" + lastRowCompany);
const companyNamesValues = companyNamesRange.getValues();
const companyNames = companyNamesValues.map(row => row[0]);
// 「宛先一覧」シートのA列からI列までのデータを取得
const lastRowDestination = destinationListSheet.getUsedRange().getRowCount();
const destinationRange = destinationListSheet.getRange("A1:G" + lastRowDestination);
const destinationValues = destinationRange.getValues();
const sortedDestinations: number[][] = []; // 型をany[][]に変更
// 会社名に基づいて宛先一覧をソート
companyNames.forEach(companyName => {
destinationValues.forEach((row, index) => {
if (row[0] === companyName) {
// 同じ会社名が見つかった場合、既に追加されていないかチェック
if (!sortedDestinations.some(sortedRow => JSON.stringify(destinationValues[index]) === JSON.stringify(sortedRow))) {
// 新しいリストに追加
sortedDestinations.push(destinationValues[index]); // rowではなく、destinationValues[index]を使用
}
}
});
});
// 修正: ソートされた宛先一覧を新しいシートに書き込む際の処理
let sortedSheet = workbook.getWorksheet("宛先一覧"); // "宛先一覧"という名前が既に使用されているため、新しいシート名を変更
if (!sortedSheet) {
sortedSheet = workbook.addWorksheet("宛先一覧");
}
const sortedRange = sortedSheet.getRange("A2:G" + (sortedDestinations.length + 1)); // 行数に1を足す
sortedRange.setValues(sortedDestinations);
}

なんだかインデントがうまくいかないですねぇ