Excel セルデータを Office Scripts でフローに渡す
今回は、Excel の任意のセルに入っているデータを Power Automate のフローに引き渡す方法についてご紹介します。
テーブルにしていれば、フローから直接データを取得しにいけますが、テーブルではない場合は今回紹介する Office Scripts を利用すると便利です。
完成イメージの確認
今回作成する フローの完成イメージです。今回は、SharePoint サイトのライブラリに入館証明書 貸与申請 (Excel ファイル) が保存されると、ファイルからデータを抜き出しリストに転記します。
事前準備
1.Excel ファイルの準備
申請フォーマットなど抜き出したいデータが含まれている Excel ファイルを用意します。
2.SharePoint サイトにライブラリとリストを用意
任意の名前でライブラリを用意します。特別な設定入りません。
Excel ファイル (入館証明書 貸与申請) の内容が登録されていくリストを用意します。今回は以下の通りに用意しました。
実装
Office Script と フローの実装を行います。
1.Office Script の実装
Excel の [自動化] タブより [新しいスクリプト] をクリックし以下の通り記述し、任意の名前を付けて保存します。
function main(workbook: ExcelScript.Workbook) {
let selectedSheet = workbook.getActiveWorksheet();
// DateTimeFormatOptionsを指定して日付を文字列に変換
let options: Intl.DateTimeFormatOptions = { year: 'numeric', month: '2-digit', day: '2-digit' };
const obj = {
"申請日": new Date((selectedSheet.getCell(3, 5).getValue() as number - 25569) * 86400 * 1000 ).toLocaleDateString('ja-jp', options),//行列番号による指定(日付⇒文字列 yyyy/MM/dd)
"申請者所属": selectedSheet.getRange('B6').getValue(),//アドレスによる指定
"申請者名": selectedSheet.getRange('申請者名').getValue(),//範囲名による指定
"利用者会社名": selectedSheet.getCell(8, 1).getValue(),//行列番号による指定 文字列
"利用者会氏名": selectedSheet.getCell(9, 1).getValue(),
"利用期間自": formatDateToYYYYMMDD(new Date((selectedSheet.getCell(11, 2).getValue() as number - 25569) * 86400 * 1000)),//シリアル値から文字列に変換
"利用期間至": selectedSheet.getCell(11, 5).getValue(),//Excel 版シリアル値のまま
"入館理由": selectedSheet.getRange('入館理由').getValue()
}
console.log(obj);
return obj;
}
const formatDateToYYYYMMDD = (date:Date) => {
const year = date.getFullYear();
const month = (date.getMonth() + 1).toString().padStart(2, '0');
const day = date.getDate().toString().padStart(2, '0');
return `${year}-${month}-${day}`;
}
※ 行列番号を指定する場合、1 行目が 0 で A 列が 0 となります。
2.フローの実装
以下の通りフローを作成します。
Office Script で 属性名を指定してオブジェクトを作成したので、動的なコンテンツから簡単に指定が可能です。
申請日:
Office Script 内で yyyy/MM/dd となるよう文字列にしているため、そのまま日付列にセット可能
利用期間(自):
Office Script 内で yyyy-MM-dd となるよう文字列にしているため、そのまま日付列にセット可能
利用期間(至):
Office Script 内で Excel のシリアル値のまま フローに返しました。
Excel のシリアル値は、1900/01/01 からはじまり、1 日で 1 であるため、式で変換します。但し、Excel は 1900 年をうるう年として認識しており、1900/02/29 という存在しない日にちをカウントしてしまいます。その為、関数は以下の様に設定します。
addDays('1899-12-30', int(outputs('スクリプトの実行')?['body/result/利用期間至']), 'yyyy-MM-dd')
動作確認
各入力項目に値を入れた状態でライブラリにアップロードします。”ファイルが作成されたとき” トリガーを利用しているため、自動でフローが動作しリストにデータが転記されます。
まとめ
テーブルにはなっていない Excel ファイルからデータを抽出しなければならないケースはまだまだ多々あると思うので、参考になれば幸いです♪
また、今回のサンプルには出てきませんが、日付を利用する時は以下に気を付けて利用しましょう。
- スクリプト内で new Date() などを用いて日付を生成した場合
シリアル値: 1 ミリセカンド= 1 - Excel セル内のデータの場合
シリアル値: 1 日= 1
日付は シリアル値の違いもあるので、Office Script 内で文字列に変換するのかフロー側で変換するのか検討が必要になりそうですね。
関連コース
- CI628-H Power Automate クラウド フロー 基礎
クラウド フローの基本的な概念、構築手順を習得いただけます。日常的な業務に沿った Excel 操作やメール操作を中心としたシナリオを実習の題材とし、クラウド フローの基本操作を習得し、自部門の業務を効率化するためのフローを自力で構築できるようになることをめざします。 - CI618-H Power Automate クラウド フロー 実践
Power Automate の基本操作を習得した方を対象に、関数を用いた数式記述、繰り返し処理、JSON データ処理、さらに安定したフローを構築するためのエラーハンドリング手法など、Power Automate の応用力を高めるために必要なスキル習得をめざします。 - CI627-H Power Automate for desktop による自動化
Power Automate for desktop の利用方法について、基本的な UI 操作の自動化、条件分岐やループ処理などの制御構造、ファイル操作、Excel 連携など、業務現場で頻出するシナリオを題材に、ローカル環境での反復作業を自動化するフローを、自ら設計・構築できるスキルの習得をめざします。 - CI626-H Microsoft 365 – Office スクリプトと Excel によるデータ活用
Power Query を利用したデータの取り込みや加工、Power Pivot による高度なデータ モデルの構築と分析手法を学びます。また Office スクリプトを利用することで、Excel 内で行うルーチン業務をスクリプト化して自動化し、業務の効率化と標準化を実現する方法も解説します。