Microsoft Power Automate Advent Calendar 2023 12月22日(金)
この記事では、Power Automate と Office スクリプトを使用して Excel 帳票を自動出力する方法について解説しています。
この方法では、Power Apps で構築した JSON データを活用し、Excel の帳票テンプレートの固定文字列を Office スクリプトで自動的に置き換えます。これにより、柔軟で効率的な帳票作成が可能となりますが、JSON の構築とOfficeスクリプトの使用には少し技術的な敷居があります。このプロセスを一度構築すれば、様々な Excel 帳票に対応できる汎用性を持ちます。
記事投稿の経緯
この仕組みを思いついたきっかけは、クライアントからの強い要望です。
クライアントは Excel 帳票のレイアウトにこだわりがありました。
当初、Power Automate の標準コネクタ（Excel 内のテーブル更新）だけで実現しようとしましたが、Excel の特定のセルまたは結合セルに値を挿入することは不可能でした。
そこで、Office スクリプトを用いれば、Power Automate で受け取ったデータを基に、柔軟な帳票作成が可能になると考えました。
私と同じような悩みを持たれている方がいらっしゃると思いましたので、今回の記事を作成しました。
基本的な仕組み
全体構成は以下の通り。
Power Apps → Power Automate → Excel(Office Script) の順でデータが渡っていきます。
この仕組みのポイントをまとめると以下の通り
- Power Apps で帳票の JSON データを構築
- Power Automate から帳票の JSON データを Office スクリプト に渡す
- Office スクリプトは JSON データに従い、あらかじめ用意していた Excel 帳票テンプレートの固定文字列を置き換えていく
この仕組みによって、自由な Excel 帳票フォーマットにデータを書き込むことが可能となります。
Power Apps での JSON 構築
- ユーザは、画面上で帳票の種類や帳票出力が必要なデータを入力します。
- 帳票として出力したいデータを JSON 形式で構築します。
Power Apps の基本的な始め方について知りたい方は以下のリンクを参照してください。
Power Automate へのデータ受け渡し
Power Apps で構築した JSON データを Power Automate に引数として渡します。
- Power Automate で、SharePoint のドキュメントライブラリにあらかじめ準備しておいたテンプレートを配置します。
- このテンプレートファイルをコピーして、新しいファイルを作成します。
Office スクリプトの起動
- Power Automate は 引数に受け取った JSON データを指定して Excel 内の Office スクリプトを起動します。
- Office スクリプトは JSON データを受け取り、帳票の処理を開始します。
Excel 帳票の出力
- Office スクリプトは受け取った JSON データの内容を元に、テンプレートデータのセルの文字列を置き換えていきます。
- 置き換えが完了すれば帳票出力は完了します。
帳票出力の詳細
Excel 帳票テンプレートと JSON の役割
- Excel 帳票のテンプレートは、置き換えたいセルに、置き換え文字列をあらかじめ保持しています（例: ###金額###）
- JSON データには、これらの置き換え文字列に対応する実際のデータを持っています。
Power Apps で構築した JSON データ（サンプル）
{
"reports": [
{
"templateSheet": "OrderInfo",
"data": {
"###受注ID###": "20231222-01",
"###注文方法###": "Web",
"###注文日###": "2023年12月22日",
"###納品日###": "2023年12月25日",
"###顧客先名###": "Contoso",
"###品番コード1###": "00001",
"###受注単価1###": "30",
"###受注数1###": "100",
"###品番コード2###": "00002",
"###受注単価2###": "100",
"###受注数2###": "200",
"###品番コード3###": "00003",
"###受注単価3###": "200",
"###受注数3###": "1500",
"###品番コード4###": "00004",
"###受注単価4###": "150",
"###受注数4###": "4000",
"###品番コード5###": "00005",
"###受注単価5###": "10",
"###受注数5###": "3000"
}
},
{
"templateSheet": "OrderInfo",
"data": {
"###受注ID###": "20231222-02",
"###注文方法###": "Web",
"###注文日###": "2023年12月22日",
"###納品日###": "2023年12月29日",
"###顧客先名###": "Fabrikam",
"###品番コード1###": "00001",
"###受注単価1###": "30",
"###受注数1###": "100",
"###品番コード2###": "00002",
"###受注単価2###": "100",
"###受注数2###": "200",
"###品番コード3###": "00003",
"###受注単価3###": "200",
"###受注数3###": "1500",
"###品番コード4###": "00004",
"###受注単価4###": "150",
"###受注数4###": "4000",
"###品番コード5###": "00005",
"###受注単価5###": "10",
"###受注数5###": "3000"
}
}
]
}- reports: この配列は、生成したい各種帳票の設定を格納しています。
- templateSheet: ここでは、使用するExcelのテンプレートシートの名前を指定します。例えば「売上レポート」や「経費レポート」などです。
- data: このオブジェクトは、テンプレートシート内の特定の文字列（プレースホルダー）を、実際のデータで置き換えるための値を保持しています。たとえば、「###年度###」を「2023」に、「###金額###」を「¥1,000,000」に置き換えるなどです。
この JSON は、Office スクリプトによって解析され、指定されたテンプレートに基づいて、実際の帳票データが Exce lシートに反映されます。
Office スクリプトによるデータ処理
- スクリプトは JSON で指定されたテンプレートシートをコピーし、新しいシートを作成します。
- データとして使用するテンプレートのシート名を指定できるため、複数のレイアウトに対応可能です。
- シートをコピーし、指定された文字列を実際のデータで置き換えます。
- JSON データ次第で同じシートを複数作成することも可能です。
- 置き換えは「replaceAll」関数を利用して、シート全体に対して文字列を置き換えるようにしています。
参考スクリプト
// 各レポートのデータ構造を定義するインターフェース
interface Report {
templateSheet: string;
data: { [key: string]: string };
}
// 全体のJSON構造を定義するインターフェース
interface ReportsData {
reports: Report[];
}
// メイン関数：ExcelScript.Workbook オブジェクトと入力されたJSON文字列を受け取る
async function main(workbook: ExcelScript.Workbook, inputJson: string) {
// JSON文字列をオブジェクトに変換
let data: ReportsData = JSON.parse(inputJson);
// JSON内の各レポートに対して処理を実行
for (let report of data.reports) {
// シート名用タイムスタンプを設定
let timestamp = new Date().toISOString().replace(/[-:.TZ]/g, '').slice(0, -3);
// テンプレートシートを取得
let template = workbook.getWorksheet(report.templateSheet);
// シート名に日付・秒を追加
let newSheetName = `${report.templateSheet}_${timestamp}`;
// テンプレートをコピーして新しいシートを作成
let newSheet = template.copy(ExcelScript.WorksheetPositionType.end, template);
//コピーされたシートの名前を変更
newSheet.setName(newSheetName);
// JSONデータでテンプレートの文字列を置き換え
for (let key in report.data) {
let value = report.data[key];
await replaceInSheet(newSheet, key, value);
}
// テンプレートシートを削除
template.delete();
}
}
// シート内の特定の文字列を置き換える関数
async function replaceInSheet(sheet: ExcelScript.Worksheet, placeholder: string, value: string) {
//placeholder の値を value で全て置換する
//replaceAll関数では数値の置き換えはエラーとなるため、強制的に末尾に文字列 "numberReplace" を追加
sheet.replaceAll(placeholder, value.toString() + "numberReplace", {
completeMatch: false, //false：部分一致
matchCase: true //true:大文字小文字を区別する
});
//replaceAll関数で "numberReplace" を削除する
sheet.replaceAll("numberReplace", "", {
completeMatch: false, //false：部分一致
matchCase: true //true:大文字小文字を区別する
});
}「replaceAll」関数は、英数字が入っていない数値のみの値で置き換えようとするとエラーが発生します。このサンプルスクリプトは、置き換える値の末尾に強制的に文字列を追加しています。
そのあと、該当文字列を削除するために再度「replaceAll」関数で強制的に付与した文字列を削除しています。
処理完了後のクリーンアップ
- すべてのシートが生成データ項目が置き換えされた後、コピー元のテンプレートシートは削除されます。
- これにより、最終的な帳票はテンプレートシートが存在しない状態で出力されます。
Power Apps の処理詳細
シートとデータの設定
- ユーザーは Power Apps を使用して、出力したい帳票の種類を選択します。（今回のサンプル画面では帳票指定は固定です）
- 選択された帳票に対応するデータ（金額、日付など）は JSON 形式で設定されます。
今回の記事では Power Apps 上ので JSON 構築は割愛します🙏
JSON によるデータ管理
各帳票のシートとそれに対応するデータは、JSON 内で一緒に管理されます。
まとめ
この記事では、Power Automate と Office スクリプトを使用して Excel 帳票を自動出力する方法を説明しました。
Power Automate でも Excel 帳票は作成可能ですが、JSON の構築と Office スクリプトは少し敷居が高いです。Excel 帳票の要件毎に Power Apps、Power Automate、Office スクリプトを構築していると、少しローコードというにはほど遠い感じもします。
ただし、汎用的な JSON の構造と Office スクリプトを一度構築してしまえば様々な Excel 帳票に対応できるポテンシャルを秘めています。今回は比較的簡単なデータの設定だけの内容だけ紹介していますが、実際には図形オブジェクトの挿入や、写真データの挿入も Power Automate からのデータ連携で帳票を作成可能です。
時間はかかりそうですが、汎用的な仕組みについては別の記事で詳しく解説できればと思います。
コメント