Power Automate 関連のフォーラムでよく話題にあがるのが「テーブルじゃないExcelのデータを取り扱えないか」というものです。
Power Automate の Excel コネクターでは、原則テーブルが定義されているケースを対象としているので、非テーブル型なデータは取り扱えません。(表を取得とかができない)
これを回避するためには、一度 Office Script を利用してテーブル化してあげればよいのですが、よく見るサンプルだと
function main(workbook: ExcelScript.Workbook) { // Get the active worksheet. let sheet = workbook.getActiveWorksheet(); // Add a table that has headers using the data from B2:E5. sheet.addTable("B2:E5", true); }
こんな風にテーブルのレンジを指定しているんですよね。決まったデータ数(行数)ならこれでもいいのですが、不定な場合にどうしようかと調べてみましたので、結果をご紹介します。
対象とするのはこのような感じの表です。
Office Script を使ったテーブル作成
最初に結果を書いておきます。以下のコードをOffice Scriptのエディターにコピペしていただくと、1シート目にある表が、"table1"という名前のテーブルになります。
function main(workbook: ExcelScript.Workbook) { let selectedSheet = workbook.getWorksheets()[0]; let range = selectedSheet.getUsedRange(); console.log(range.getRowCount()) let table =selectedSheet.addTable(range.getAddress(),true); table.setName("table1"); table.setPredefinedTableStyle("TableStyleMedium2"); }
ポイントは getUserdRange()
で、これが使用しているセルの領域を返してくれます。例のファイルでは、B2:F9 の範囲ですね。
さらに、この結果 (range) にたいして getAddress()
を実行すると Sheet名: セルの範囲
の形式で見慣れた結果を返してくれます。
ここまでくれば最初にあげたサンプルと同じように、addTable()
すればテーブル化できます。
テーブル名を指定したい場合にはさらに setName("テーブル名")
を付け加えてください。
最後に動作確認です。
できた!非テーブルのデータに対してレンジを指定せずOffice Scriptでテーブル化できた! pic.twitter.com/XhSwvhfdF7
— Hiro (@mofumofu_dance) February 16, 2021
おまけ : Power Automate で利用する
ここはほんとにおまけ程度ですが、Power Automate で Office Scirptを実行してテーブルを作成し、そのテーブルのデータを取得したい場合には一定の時間Delayを入れてください。
APIでテーブルを認識できるまでに手元では5分程度時間を要していました。
ということで、よく話題になるテーブル化してデータ取得が簡単にできましたー!