エクセルの複数条件に合致した値をPowerAutomateで抽出したい！SaaS版エクセルの新しいマクロOfficeスクリプトを使ってみる！

2023年11月13日 19:36

アイシーティーリンク株式会社の三好です( ^^)/
ここ数日で急に寒くなってきたね！いよいよスノボシーズン到来です！！
(￣▽￣)Ｖ　ブイ

マルチカムOps-CoreのバリヘルにECWCSとFILBEアサルトパック
ファティーグのトラウザーズという時代も所属もゴチャゴチャコーデ（笑）

実は私はミリオタ（ミリタリーオタク）なので衣類は軍放出品が多く、様々な年代の官給品を取り揃えておりまして（笑）
気温が下がってくるとM51やM65といった王道のジャケットを着る機会がやってくるのでテンション上がります（￣ー￣）
もちろんパッチは第一騎兵師団です（笑）

さて、今回はPowerAutomateでエクセルのマクロを動かそうと思ったけど出来ないぞ！という方のために、SaaS版Officeではマクロの代わりにOfficeスクリプトというのがあるんだよ！をご紹介しようと思います。

(￣Θ￣;)なんスカそれ！？って方も多いと思いますが
SaaS版エクセルの新しいマクロだと思ってください。
残念ながらマクロ（VBA）とは互換性がないため新しい言語を習得する必要があります(-"-;)
しかしマクロの「記録」と同じ機能もあるので、まずは使ってみるというのが第一歩ではないでしょうか。

エクセルの準備

下図のような表を準備しました。
「item_list」シートに表を記載
「export_data」シートに条件に一致した値を書き出しますが
PowerAutomateでシートの値を取り出すためには"テーブル"である必要がありますので、"テーブル名"も「export_data」として登録します。
まずは、Officeスクリプトを使って以下の条件に合致した品目を抽出するスクリプトを作成してみましょう。
◆条件
「種類」= フルーツ
「在庫」= 有
「送付履歴」= ない

"item_list"シート

エクセルの準備（テーブル作成）

今回は新規作成しますが、既存の表をテーブル化するのでもOKです。
「export_data」シートを選択し、タブの「挿入」>「テーブル」

今回は抽出データは1列なのでデータの範囲は「A1」
先頭行をテーブルの見出しとして使用するにチェックを入れて「OK」

下図のようにセルに色がつけばテーブルが完成です
見出し行に「列１」と記載されているので「品目」に変更します。

テーブルになるとタブに「テーブルデザイン」が出現しますのでそのタブを選択し、テーブル名を任意の名称に変更
今回は「export_table」とします。

Officeスクリプトの作成

SaaS版エクセルをブラウザ上で開き「自動化」>「新しいスクリプト」

画面右側にコードエディターが表示されます
下図のような画面が開いた場合は「スクリプトを書く」を選択

すると下図のような画面になり、コードを書く準備ができました

最初に記載されている3行を消して、以下の内容に書き換えます。

function main(workbook: ExcelScript.Workbook) {

    // 複数条件に一致した値を別シートに書き出す
    let item_list = workbook.getWorksheet("item_list");
    let export_data = workbook.getWorksheet("export_data");

    // export_dataシートの値を全て削除
    // null判定
    let cellValue = export_data.getRange("A2").getValue();
    // セルに値があるかどうかを確認
    if (cellValue !== "") {
        // セルに値がある場合の処理をここに書く
      export_data.getRange("A2").getExtendedRange(ExcelScript.KeyboardDirection.down).delete(ExcelScript.DeleteShiftDirection.up);
    } else {
        // セルに値がない場合、何もしない
    }
    
    // item_listシートの1行目でフィルターオン
    item_list.getAutoFilter().apply(item_list.getRange("1:1"));

    // 種別が「フルーツ」の場合
    item_list.getAutoFilter().apply(item_list.getAutoFilter().getRange(), 1, { filterOn: ExcelScript.FilterOn.values, values: ["フルーツ"] });
    // 在庫が「有」の場合
    item_list.getAutoFilter().apply(item_list.getAutoFilter().getRange(), 3, { filterOn: ExcelScript.FilterOn.values, values: ["有"] });
    // 送付履歴がない
    item_list.getAutoFilter().apply(item_list.getAutoFilter().getRange(), 4, { filterOn: ExcelScript.FilterOn.values, values: [""] });

    // 条件で絞った値をコピーしてペースト 
    export_data.getRange("A2").copyFrom(item_list.getRange("C2").getExtendedRange(ExcelScript.KeyboardDirection.down), ExcelScript.RangeCopyType.values, false, false);
    // フィルター解除
    item_list.getAutoFilter().clearCriteria();
    item_list.getAutoFilter().remove();
}

今回のスクリプトの流れを簡単に解説します
①"export_data"シートに値が入っている場合は削除します
②"item_list"シートで3つの条件でソートします
③ソートされたB列の値をコピーして"export_data"シートにペーストします
※コメントアウトは" // "です
※今回は絞込んだ時に何らかの値が抽出されることを前提としています
　絞込み後に値がない事が想定される場合は別途、エラー判定スクリプトを
　記載する必要があります

コードを貼り付けたら「実行」を押してみて下さい。

"export_data"シートに下図のように出力されれば成功です

これでエクセルとスクリプトは完成です。
あとはこれをPowerAutomateで指示してやるだけで動作します。
作成したスクリプトの名前は「スクリプト」となっており、そのままでも構いませんが、今回はとりあえず「export」に変更しておきます。
「スクリプト」と書かれている部分をクリックし「スクリプト名」欄に任意の名前を付けられます。

PowerAutomateでOfficeスクリプトを実行する

SaaS版PowerAutomateを開き、左側メニューの「＋作成」をクリック。

今回はトリガーは手動で行いますので
「インスタントクラウドフロー」を選択します

「フロー名」に任意の名称をつけ、「フローを手動でトリガーする」にチェックをいれて「作成」をクリックします
今回は「Officeスクリプトテスト」と名前を付けます

サインインが正常に行われると下図のような画面になります。
エクセルファイルを保存した場所に移動してファイルを指定します。
今回は、自分のOneDrive直下の「自分のファイル」内に「ブログ用サンプル」という名称で作成しています。
適切に指定できると、作成したスクリプト「export」がプルダウンで選択できるようになります。

この時点で、PowerAutomateを実行すればエクセルスクリプトを実行することが可能ですが、今回は実行した後出力された値をPowerAutomate上で取得することが目的なので、続いてアクションを追加します。

検索で「Excel」と入力し、「表内に存在する行を一覧表示」を探します。
一覧に出てこない場合は「さらに表示」を押します。

指定するファイルは先ほどと同じファイルを指定します。
今度はテーブルを指定する項目があるので、プルダウンメニューから先ほど作成したテーブル「export_table」を選択します。

これで完成です。
「保存」を押してテストしてみましょう

画面右上の「テスト」をクリック

手動にチェックし「テスト」をクリック

「続行」をクリック

「フローの実行」をクリック

「完了」をクリック

無事にすべてにグリーンのチェックがつけば正常終了です

「表内に存在する行を一覧表示」をクリックし
OUTPUTS > Show raw outputs をクリック

JSONで出力され、品目にエクセルで実行した時と同じ項目が表示されていれば成功です！

以上！いかがでしたでしょうか。
今回はOfficeスクリプトの使い方メインなので、PowerAutomateで出力した後の活用方法については特に記載しませんが、例えばソートして出力されたものがメールアドレスなどの場合、「メールの送信（V2）」アクションで宛先に動的コンテンツとして出力されたデータを入れてそれぞれに送信する！なんてことも可能です(^-^ )

Officeスクリプト難しい(-"-;)
と二の足踏んでいる方がいたら、この機会にぜひ挑戦してみてはいかがでしょうか！
それではまたの機会にお会いしましょう(^^)/