Office ScriptsでPower Automateの機能を補完する

現在Preview機能として提供されている、Office Scriptsを利用すると、Power Automateで提供されていない機能を補うことができるかもという内容です。

Power Automateでは、配列のソートや、特定のプロパティに関する合計、平均などの機能/関数は、2020/5/11時点で提供されていません。(熱く期待されているものの・・・)

一方のOffice Scriptsは、JavaScriptを利用して、Excel上の操作を自動化するもので、大体のJavaScriptの処理ができます。 (外部ライブラリの利用は不可)

この2つを組み合わせることで、Power Automateにない機能性を、Office Scriptsで補完できそうだったので、ご紹介します。

対象とする操作

前述の2つ、ソートと合計(平均)をとる処理をPower Automateで実現してみます。

これまで単純なソートであれば、一度Excelのテーブルにデータ登録をして、ソートするようにクエリを書いたうえでアイテムの取得を行う方法が考えられていました。 またはループ処理したり、Azure functionsを利用したり。

ただ、Azure Functionsは、当然ですが、Azure上にリソースを作成できないと使えません。企業によっては、そのあたりがネックになることもあります。ということで、可能な限り標準コネクターでやってみるべく、Office Scriptsを利用します。

ソート処理

オブジェクト配列の、特定のプロパティでのソートを考えます。 Office Scripts内だけでこれを実施する場合には、以下のスクリプトでこれを実現できます。

async function main(context: Excel.RequestContext) {

  function sortByProperty(property: string, asc: boolean) {
    return function (a, b) {
      if (a[property] > b[property])
        return asc ? 1 : -1;
      else if (a[property] < b[property])
        return asc ? -1 : 1;

      return 0;
    }
  }
  let arr =
    [
      { "名前": "AAA", "身長": 156, "体重": 43 },
      { "名前": "BBB", "身長": 175, "体重": 70 },
      { "名前": "CCC", "身長": 192, "体重": 110 },
      { "名前": "DDD", "身長": 166, "体重": 55 }
    ];
  console.log(arr.sort(sortByProperty("身長", false)));

}

この処理の結果、ログには

    [
      { "名前": "CCC", "身長": 192, "体重": 110 }, 
      { "名前": "BBB", "身長": 175, "体重": 70 },
      { "名前": "DDD", "身長": 166, "体重": 55 },    
      { "名前": "AAA", "身長": 156, "体重": 43 }
    ]

身長で降順にソートされた配列が表示されます。

Power Automateで入力を動的にする場合には、入力する配列 (arr)と、ソートするプロパティ、昇順フラグを設定します。

結果、期待通り、特定のプロパティでソートした配列が得られました。

合計処理

合計についても、Power Automate部分は同じなので、割愛します。 実行するスクリプトは、以下のようなものです。

async function main(context: Excel.RequestContext) {

  var sum = function (arr) {
    var sum = 0;
    arr.forEach(function (elm) {
      sum += elm;
    });
    return sum;
  };
  let arr=
    [
      { "名前": "AAA", "身長": 156, "体重": 43 },
      { "名前": "BBB", "身長": 175, "体重": 70 },
      { "名前": "CCC", "身長": 192, "体重": 110 },
      { "名前": "DDD", "身長": 166, "体重": 55 }
    ];
  let total = {
    "tHeight": sum(arr.map(({ 身長 }) => 身長)),
    "tWeight": sum(arr.map(({ 体重 }) => 体重)),
    "count":arr.length
  };
  console.log(total);
  //returns {"tHeight":689,"tWeight":278,"count":4}
}

繰り返しですが、Power Automateで処理する場合には、配列をほかの結果に置き換えて、合計を取るプロパティ (ここでは身長と体重)を書き換えてください。

Office Scriptsの結果を後続処理で利用するには

Office ScriptsをPower Automateで実行した結果を、後続処理で利用するには、少し加工が必要です。

単純に、Run Scriptアクションの結果を表示すると、console.logの出力内容が取れますが、実行時間がついてきます。

実際には、タイムスタンプの後ろ側が欲しいので、substring関数を利用します。

json(
    substring(
        first(outputs('Run_script')?['body']?['logs']),
        28,
        sub(length(first(outputs('Run_script')?['body']?['logs'])),28)
    )
)

タイムスタンプ分、ずれた位置から、残りの文字数分を取得しています。一番外側の"json"は、文字列からjsonオブジェクトに戻す関数です。

このような処理によって、Office Scriptsの実行結果が、Power Automateの後続処理で利用可能になります。

おわり

お気づきだと思いますが、今回はOffice Scripts使っているくせに、一回もセルにデータを読み書きしていません。 純粋に、Power Automateの機能補完目的で使ってしまってごめんなさいという気持ちですが、何か標準の方法で対応が難しい場合には、短いスクリプトで処理できるかもしれない という内容でした。