Officeスクリプトの実行について

Web版ExcelのOfficeスクリプトの実行方法とその注意点について

OfficeスクリプトはWeb版のExcelで利用可能な機能です
またこれは組織アカウントなので個人アカウントだけでは利用できません
とはいえ、この機能を個人で利用したい場面は少ないので、問題はないと思います

それよりもタイトルにありますように、この機能のパッとしない点が実行方法が限定的である点です
何よりExcelを利用している時にこの機能で何かをしようというのは現時点では現実的では無いかもしれません

その最大の理由がイベント処理が無い点です

VBAをある程度触ってきた人には良くわかると思いますが、このイベント処理が無いというのは結構大変です
処理の開始は常に手動実行以外の方法が無いからです

しかもVBAと比べるのも土俵が違いすぎるかもしれませんが、処理速度が目で追えるレベルです
まだまだ発展途上の機能ではあるので、いずれはイベントも対応するかもしれません

が、現時点ではWeb版Excelとしては手動実行のみです
しかもこのスクリプトはブックとは別に存在しているので、ブックを渡せば処理が実行できるわけではありません
その処理ファイルが必要となります

これ自体は一長一短かとは思うので、VBAのイメージでいくと面倒に感じる部分もあるかもしれませんが、仕様と割り切って良いでしょう

あと気になるのが、所持してるスクリプトが全て表示されるので数が多くなってくるとちょっと管理が面倒です
これも何か対応を検討して欲しいな、と思ってます

自動実行(PowerAutomate)について

実行は手動のみと上記で記載しましたが、あくまでもWeb版のExcel単独でいうと
ということになります

と、いうのも
PowerAutomateから実行することが可能です

ちなみにDesktop版のPowerAutomateではスクリプトは実行できません
だってExcelがDesktop版の操作になるので

そして、このPowerAutomateからの実行では引数を渡して、返し値を受け取ることも可能です
これを応用すれば、PowerAutomate経由でPowerAppsでExcelデータを制限を受けずにデータの取得を行うことが出来ます

またPowerAutomateによりトリガーを設定することで、何かしらのイベントでスクリプトを実行することが出来ます
メールを受信したらその内容をExcelに転記したり、OneDriveにファイルが作成されたらファイル情報を転記したり等々

さらにこのスクリプトを併用することで、PowerAutomateではExcelのデータ入力はテーブル機能に対してしか行えなかったのが、任意のセルに入力することが出来ます
これが現状では最大の利用点かと思います

ただ、残念なことがあります
それはPowerAutomateでは2次元配列が作成出来ない点です
もちろん静的なものはコードの書き方で可能ですが、動的なものは作成できません

VBAでも同じですが、データの入力は配列で一括処理が早いので良く利用されます
これが引数として渡せないのです
もちろんスクリプト自体は多次元配列を引数に受け取ることも可能ですし、処理も可能です

そして、この処理時間に関して結構重要で最近ハマったのですが
PowerAutomateのアクションのタイムアウトの存在です
これがネックとなります

つまり、PowerAutomateでスクリプトを実行するアクションを実行した後に
その処理時間がタイムアウトの時間を超えてしまうと、PowerAutomate側でエラー終了となりスクリプト側でエラーは発生していないにも関わらずフローが失敗します

スクリプトをWeb版Excelで手動実行したら、エラー無く処理が完了するのに
PowerAutomateから実行するとエラーでフローが停止する

この現象に悩まされました

結果としてはタイムアウトが絡んでいることが分かったのですが
その設定を伸ばしても処理の引数が可変であった場合、どこまで保険を置くかは判断が難しいです

なので、タイムアウトの設定は変更せず
その時間内で処理が完了できるように調整する必要があります

例えば、全行の処理を実行したいのならば
スクリプト側で全行の処理にするのではなく、数百行ずつ実行する、といった処理の分割を行う必要があります
これが出来ない処理であれば、もはや現時点では仕様として対応出来ません

2000行ほどある処理を実行しようとした場合にタイムアウトとなっていたので
それを少しずつ減らして検証を行っていきました
結果として300行ほどがギリギリのラインであることが分かったので、処理は200行ずつ実行するようにしました
もちろんこれは処理内容によるので、ここに記載した数字には全く何の意味もありませんのであまり気には留めないで下さい
2000行だと毎回エラー終了するけど、200行ずつの分割実行にしたらエラー終了は無くなったという事が重要な点です

もう一つこの方法で重要なのが、返し値に処理が終端に達したことを知らせる必要があることです
その返し値になるまでPowerAutomateではループ処理を実行することになります

この辺はおいおい説明します

今回としてはWeb版Excelでは手動実行しかできない
PowerAutomateから自動実行は出来るがタイムアウトに注意する

ここを念頭において作成を進めてください

UNIQUE関数について

Office365とWeb版で利用可能なUNIQUE関数は重複除外リストが簡単に取得出来るやつです

Office365・Web版で利用可能な関数にUNIQUE関数というものがあります
この関数は指定した配列データの中で重複したデータを削除したデータを返す関数です
この関数を利用することで、表のデータから簡単に重複しないデータを取得できます

この重複しないデータの作成は、案外利用場面が多くあったのですが、標準機能ではデータタブ内にある重複データの削除という機能から行うことが出来ます
後述しますが、まだこちらの機能に完全に有利になってはいないので、この機能を知らない人はデータタブ内を確認してみてください

ただ、こちらは関数のためいちいちデータを転記して操作を行わなくても関数の再計算のタイミングで自動的に取得できるのは便利です(常に最新情報)

関数の書式

引数(太字は必須引数)
(配列, [列の比較], [回数指定])

「配列」は重複しないデータを作成する配列データです
連続していれば複数列を範囲指定することが出来ます

「[列の比較]」は、重複参照時に列方向を対象にするかどうかの指定です
列を対象にすると、列方向に重複しない場合はその列が取得されません
あまりExcelのデータ形式からすると使用しないかもしれません
省略時は行方向が指定されることになります

「[回数指定]」は、重複データの中でも1度しか出てこないデータだけを指定できます
1度しか出てこないデータは、配列データ内で完全に一意になっているデータという事になります
案外欲しい、この逆説のデータ取得は出来ません

実際の使用例

使用するデータベース

今回はこのデータを元に関数を使用してみます

UNIQUE(Sheet1!A2:A21)
関数で取得されたデータ

基本的な使い方として、指定データ範囲を配列データとして指定します
今回はA列のデータ範囲を指定しています

この中から重複したデータだけを削除したものが取得されています
データの総数は20個ですが、重複データが15個あるのがこれで分かりますね

=UNIQUE(Sheet1!A2:A21,,TRUE)
関数で取得されたデータ

次に完全な一意のデータのみを取得してみます
3つ目の引数に「TRUE」を指定します
指定配列内の中で、1度しか出てこなかったデータが取得されます

「kkk001」と「kkk003」が複数回出てきていたことが、前回の結果と比べると分かりますね
ここが前述の逆説のデータをとれないという点ですが、この複数回出てきているデータだけを取得する設定が無いんですね、なんでやろね

=UNIQUE(Sheet1!A2:B21)
関数で取得されたデータ

次に複数条件での重複除外データの取得を行ってみます
A列とB列の両方を配列データに指定することで、複合的な条件でリスト作成を行うことが出来ます

この場合ですと、A列とB列の両方が重複するものが除外される形になります

ただ少し残念なのが、連続した範囲でしかセル指定が行えない点です
A列とC列を参照したい場合に、そのままでは指定が行えないのですね
なので、C列のデータをB列に持ってくるような工夫が必要になります
ここが最初の方で記載した、既存の重複データの削除機能に優位になっていない点です
重複データの削除機能は飛び飛び列で条件指定を行うことが出来ます

この様に単独列の重複しないデータのリストが簡単に作成できる関数であることが分かってもらえると思います

XLOOKUP関数について

Office365とWeb版で利用可能なXLOOKUP関数はLOOKUP関数系の最終進化系です

XLOOKUP関数を利用していますでしょうか?
この関数はOffice365・Web版で利用可能な関数です

この関数は、ある程度関数を知っている人にはVLOOKUP関数等のLOOKUP関数系であることはイメージできると思います
VLOOKUPでもHLOOKUPでも無く、XLOOKUPが存在します

この関数は引数の配列内から検索して、その配列と同じ順番にあるデータを返す関数です
ただこれだけだと、あまり既存の関数と差があるとは思わないかもしれません

決定的に違うのは、前述の検索対象が「配列」であることです

これは意外に今までの関数ではあまり無い考え方になります
というのも、VLOOKUP関数等は引数に指定するデータは「セル範囲」であることが前提です
このデータの取得の行い方で全く動作と利便性が変化します

今までのLOOKUP関数は、セル範囲の中で検索を行う、見つかったセルと同じ行・列の指定した行数・列数を移動させたセルのデータを取得します
そのため、制限として「検索個所と取得個所が連続したセル範囲であること」「取得セルは検索セルより、左の列・上の行は指定できない」という大きな点がありました

そのため、LOOKUP関数を利用するために、本来別のシートやブックにあるデータを1つのシートにコピペしてデータを結合して検索可能な表を作成する必要がありました
これはセルという概念が前提であることが理由でした

しかし、XLOOKUP関数はセルではなく配列データから検索・取得を行うため
上記の制限が全くありません

つまり、「検索するデータ範囲と取得するデータ範囲が連続していなくていい」「取得するデータが検索データよりセルの位置が上でも左でもいい」ということになります
特に大きい利便性向上につながるのが、1点目の連続していない範囲でよくなった点です

要するに、同じシートである必要もなくなり、なんならブックを超えた検証を行うことも可能となりました
そのため、検証作業が非常に簡単になります

さらに地味にありがたいのが、見つからない場合の返し値を指定できる点です
LOOKUP関数系では見つからない場合エラーとなってしまうため、そのデータを参照した関数は連鎖的に全てエラー表示となってしまいます
そのため、LOOKUP関数をIFERROR関数でネストさせて見つからない場合は特定の値を返すように関数を組んでいることが多々あります

これが必要なくなりますので、作り込みによってはすさまじくネスト数を減らすことも可能となります

関数の書式

引数(太字は必須引数)
(検索値, 検索範囲, 戻り範囲, [見つからない場合], [一致モード], [検索モード])

「検索値」は検索対象のデータです
これはLOOKUP関数系と差は無いと思いますので、特に解説不要と思います

「検索範囲」は検索対象の配列データです
配列データですが、基本的にExcel上ではセル範囲で指定します

「戻り範囲」は取得対象の配列データです
検索対象で見つかった配列のデータ番号と同じ順番のデータが返されます
基本的に列を指定している場合は、同じ行数のデータになります

「[見つからない場合]」は検索配列に検索対象が見つからなかった場合に返す値の設定です
数値の0を指定したり「””」(空白)を設定したり、なんでも指定できます

「[一致モード]」は検証方法です、基本的には完全一致が条件です
ですが、なんとここでワイルドカードを指定することができる様に設定できます
ワイルドカードまで対応しているなんて、なんてことをしてくれるんでしょうか
ワイルドカードが分からない人にはピンと来ないかもしれませんが、本記事では割愛します
ワイルドカードが利用可能、という点が認知できていれば十分です

「[検索モード]」は検索方向の設定です
末尾から検索したいとき程度に利用しますが、あまり頻度は高くないと思います

実際の使用例

検証を行うデータテーブル
データ検証対象

こちらのテーブルデータをもとに検証を行う場面を想定してみます
実務となると検証対象が数百件以上となることもあると思います
今回はとりあえず20件で検索を行います

このデータの中で存在するデータと、そうではないデータを検証します

作成した関数
実際に作成した関数
=XLOOKUP(B2,Sheet1!A:A,B:B,"×")

上記のような関数を作成しました
この関数では、関数のセルの左にある入力値をSheet1のA列データから検索して、見つかったデータと同じ行数のSheet2のB列から取得を行います
見つからない場合は「×」を返します

そのため、取得が行えているデータの場合は条件セルと関数セルが同じ内容が表示されます
上記の画像でも同じものが2列に並んでいることが確認できます

検証結果の画像
検証を行った状態

このまま条件の行数分に関数をオートフィルで作成を行います
この画像の入力値は前提としてマスターデータと考えてください
登録すべきデータ自体は過不足なくここのリストにあります

最初に見せたデータテーブルに、このマスターデータの内容がちゃんと存在しているかを確認するための検証作業です
この画像の様にマスターデータを元に検証を行うことで、データの検証を行えます

実際画像を確認して分かるように、「kkk012」が入力テーブルには存在していないことが分かります
これでこのデータが入力漏れ等のミスが発生していることを発見することが出来ます

XLOOKUP関数は、別のシート、別のブックも可能なのが強みだと思います
また配列データなので、セルの行数が同じでなくてもセルの個数さえ一致していれば検証と取得を行えますので、非常に柔軟にセルデータに対して対応を行えます

VBAで検証処理を作成するのも良いのですが、やはり関数が利用しやすい場合は利用するべきですね
今後、この関数がExcelを席捲していくこと間違い無しです

Excel(Web版)のOfficeスクリプトについて

ExcelのWeb版のみ利用可能なOfficeスクリプトについて

ExcelのWeb版のみでかつ、365ユーザーのみが利用可能な機能として、記事タイトルのOfficeスクリプトがプレビュー機能ではありますが実装されています

まだまだ検証を始めたばかりなので、少しずつ記事に出来ればと考えています

まずもって、なにがこの機能の利点かと言いますと現在拡張されている将来性のある機能であることにつきます
VBAは進化をやめて久しい言語です
無くなることはあり得ないとは思いますが、衰退することも止む無いのかもしれません

その1番の理由が、共有時にVBAが利用できない点が大きいです
2020年はコロナという世の中の大きな変化を否応なしに求められました
そこで大きな変化となったのが、在宅勤務の存在です

出社せずに自宅で業務を遠隔にて行う
この形態においてExcelの最大の弱点が大きく露見する形となってしまいました

その最大の弱点こそ、共有・共同編集の弱さです

共有機能に関してはかなり昔からある機能ですが、なかなかクセがあり
うまく編集が保存されていなかったり、いろいろ不安定な部分がありました

しかし、現在はWebを介する形で共同編集が可能となります
Teamsにおいても共同編集(同時編集)が可能です
他の人が開いているので、読み取り専用で開く必要があったり
編集が完了したので保存したら他の人の編集データを上書きして消失させてしまったり…
保存のたびに使用しているであろう人に連絡を取って、閉じてもらったり、編集を中断してもらったり…
これらを一挙に解決してくれる共同同時編集は、今までのExcelにおいて考えても圧倒的に画期的です

ですが、ここで残念なお知らせです
共同編集環境ではVBAが使われへん!という死の宣告のようなものです

もちろんマクロブックを編集することも出来ますし、上書き保存したからといってマクロが無くなることもありません
ただただマクロが実行できないだけです

VBAのヘビーユーザーのだいごろもこれにはお手上げです
どうしようもないです「だって、仕様だもの」

この画期的な共同同時編集の便利さとVBAの便利さの間に挟まれ、まさにどちらに両足を付けるか思い悩む日々を悶々と過ごすことになります

ところがどっこいです
どちらに両足を付けるか悩んでいた、その間から割って入ってきたのがこのOfficeスクリプトです

これはWeb版のみ利用可能なので、Teamsでの作業時には利用できません

このOfficeスクリプトは、まさに自動処理を行う機能なのです
しかもVBAと同じくマクロの記録的にコードの自動作成も行える優れものです

もちろんまだまだ機能的にもVBAにはとても追いつきませんが、PowerAutomateと組み合わせることが可能なのです
自動実行は言わずもがなで、今後機能が追加されるであろうこのアプリとの連携を考えると居ても立っても居られない気持ちになります
どちらに跳ぼうか迷っていた時に、遥か天空から鋭く急降下して、いきなり背中を鷲掴みにされて大空高く連れ去られて行ってしまったような気持ちです

クラウド上の処理のため、処理速度は非常にゆったりしていますが、それだって今後のことを考えれば問題ないです

ちなみに、Officeスクリプト単体では正直あまり使わないかもしれません
なぜなら自動実行では無いので、手動実行するのならVBAの方が圧倒的に早くいろんなことが出来るからです

PowerAutomateとの連携時には引数の設定により、相互にデータのやりとりも行えます
これは非常に大きなことで、処理時間を度外視すれば
PowerAppsで委任がサポートされていないExcelですが、検索取得処理をOfficeスクリプトに任せて答えの数件をPowerAutomate経由で取得すれば2000件のアイテムを超えるデータを扱えることになります

Officeスクリプト単体では使い勝手は微妙ですが、これらのOfficeアプリとの連携を考えると非常に強力な処理が作成できそうです

VBAユーザーの皆様!これからはこのOfficeスクリプトにも注目をしましょう

ただ、Officeスクリプトは言語がVBAではなく、TypeScriptという別の言語ですけどね(ズコーっ!)