TL;DR
- PowerAutomate クラウドフローでExcel内のデータを扱う場合、扱うデータは「テーブル化」されている必要がある
- Excel方眼紙申請書からシートとセルを指定して値を抽出……とは出来ない。
- Excel方眼紙申請書を無理矢理テーブル化することは出来、基本的にはこの手法で申請書上に記載されたパラメータを取得する。
- 取得できないパラメータに関してはOfficeスクリプトを併用する。
やりたいこと
Excel方眼紙を使って作成された申請書に書かれた内容を、自動で値のチェックとかしたかったのです。
簡単なExcel方眼紙申請書を作成してみました。
E4セルからバージョンを取得して最新の申請書じゃなければ自動でリジェクトするとか、
C11セルから休む日を抽出してカレンダーの7/9のところに山田さんは休みですって自動で書いておくとか、
そういうことをしたいです。
Power Automate クラウドフローで、Excel方眼紙申請書のデータを扱う
直接値を抽出する（失敗）
Excelファイルから値を抽出してみましょう。
行の更新とか使うのかな？「抽出する」「セルを選択する」とか、そういうアクションが出てこなくて不安になります。
……？テーブルって、何ですか？方眼紙にはそんな概念ないですよね？
他のアクションを眺めてみても、テーブルという概念は必須フィールドとして存在するようです。
どうやら、手つかずのExcelデータをセル指定で値取得する方法は用意されていないようです。
申請書を自動でテーブルにしてから抽出する（大体有効・一部問題あり）
テーブルに変換
Excel方眼紙とかそういう事情を無視して、Excelをテーブルに変換することができます。
Excel Online (Business)コネクタから「テーブルの作成」アクションを選択し、パラメータを設定していきましょう。
こんな感じの見え方になります。
値を取得する
以下の手順を踏むことでパラメータの抽出が出来ます。
- Excel Online(Business)コネクタの「表内に存在する行を一覧表示」アクションを選択
- 事前に定義したアレイ変数に「表内に存在する行を一覧表示」のvalueを設定
- 以下の式を用いて、パラメータを抽出する
variables('アレイ変数')?[{行数（0スタート）}]?[{定義した列名}]
こんな感じです。
申請書を自動でテーブルにしてから抽出するときの問題点
この手順、2つくらい問題があります。見ていきましょう。
問題その1：見た目が変わる
ガラッと見た目が変わってしまいますね。
キモい見た目に違和感があるだけで済めばいいのですが、JTC内製のExcel方眼紙申請書は印刷される場合も想定して最適化されている場合があります。
典型的な申請書のフォーマットとはあまりにかけ離れた表のようなもの、内部ではともかく対外的にお出しするものには使えなさそうです。かといって、印刷用に調整するのも工数が掛かってしまいそうです。
こういうときは、せっかくPower Automateを使っているので、自動で複製しちゃいましょう。
「ファイルを複製する」的なそのものズバリなアクションはありませんが、下記の処理を組み合わせるとファイルを複製することができます。
SharePointコネクタから「パスによるファイル コンテンツの取得」および「ファイルの作成」アクションを組み合わせます。
- 提出された「印刷用ファイル」を複製して「作業用ファイル」を作成
- 「作業用ファイル」の表に対してテーブル化を実施
という段取りを踏めば、印刷用ファイルの見た目は変わらず、問題にならないかと思います。
問題その2：シート1行目の値は消える
テーブル化した場合、テーブル化で範囲指定した中で一番上の行にヘッダ行が自動で生成されます。
一番上の行に記載があったパラメータは、ヘッダ行にあたる箇所に記載されていた値が上書きされます。
これにより何が起こるかというと、
これが、
こうなります。
1行目に元々あった「有休申請書」という文字列と、版数が失われてしまいました。
対策としては以下のどちらかを行うことになります。
- Excel方眼紙の上部に余白行がある場合：テーブルの範囲を指定する際に余白行を含めてください。余白行にヘッダが設定されるため、申請書内のデータは上書きされません。
- Excel方眼紙の上部に余白行がない場合：Power Automate単独では解決できません。Office スクリプトを併用し、自動で空白用の一行目を追加させた上で処理を実行します。Ofiiceスクリプトについては次項で説明します。
これらの対策を行うと申請書1行目に抽出したい値がある場合でも、値の抽出が出来るようになります。
色んな問題を乗り越え、Excel申請書のパラメータを抽出できました。このパラメータを後続処理でどのように使うか、それはあなた次第です……。
おまけ: What's Office スクリプト？
長々解説はしませんが、簡単に言えばOfficeで使えるGASです。書式なんかはjavascriptに似ている気がします。
詳しくはこちらを見てください。
Power Automateからも起動できます。
スクリプトの記載・Power Automate中からのスクリプト操作方法
- 申請書のExcelを開く
- リボンから[自動化] > [新規スクリプト] を選択
- 右に出てきた[コードエディター]に行を追加するスクリプトとスクリプト名を付けて、保存
- PowerAutomateに戻り、Excel Online (Business)コネクタから「スクリプトの実行」アクションを選択し、パラメータを追記する。
- 行数追加スクリプトの実行タイミングは、ワークシートの取得前にしましょう。個人的にはファイル複製直後にやってしまった方が良いかと思います。
今回追加したコードの内容
function main(workbook: ExcelScript.Workbook) {
// 一番上に1行追加
workbook.getWorksheet("Sheet1").getRange("1:1").insert(ExcelScript.InsertShiftDirection.down);
}