Google Apps Scriptでスプレッドシート上の全文検索をする【GAS】

Google Apps Scriptはちょいちょい新しいメソッドが知らない間に追加されていたりします。もちろん、事前に新機能として搭載されたものをスクリプトから操作できるように、後追いで追加されるケースもあります。

そんな中、Google Apps ScriptのRelase Noteを見ていた所、結構な量の新しいメソッドが追加されていました。その殆どはあまり興味がないものなのですが、2019年4月5日にSpreadsheet Serviceにいくつか追加と、またDataStudio Serviceという新しいものが増えて、おまけに今回登場したBigQueryとコネクトするサービスも追加されています。

今回はこのうちの1つである「TextFinder」というメソッドを見てみようと思います。これはセルを全文検索や置換をする為のメソッドです。

今回使用するスプレッドシート

スプレッドシート上の操作としての置換

現在、スプレッドシート上での文字の置換は、以下のような手順です。おなじみだと思います

  1. Ctrl + Fで検索窓を出す。
  2. 例えば年度で調べる場合、2014と入れる。
  3. 」をクリックする
  4. 色々な条件(正規表現や数式内まで走査など)がありますが、とりあえずはこれで検索をクリック
  5. 検索ボタンを押すたびに次のヒットしたものをスプレッドシート上から探し出して、ジャンプしてくれる。

非常にあっさり高速に動作するので、あっけないですが、問題はこれを行なうメソッドがなかったこと。配列でデータを取得後にどこに何があるというコードは自力で装備しなければなりませんでした。もちろん、書き込みに関しても。要望はだいぶ前からあったようですが、Queryに関してはQuery関数Visualization APIを使う手がありますが、テキストサーチはなかったんですねぇ。

図:詳細な検索窓

自力で全文検索するパターン

自力といっても非常にシンプルなリニアサーチ。配列でガッツリデータを取得したら、forループで2次元配列をくまなくサーチして、データがあったら、そのレコードを配列にぶちこむだけのものです。他にも色々な方法があるかと思いますが、これがシンプルでよく使ってます。

  • カウンタなどを用意しておけば、「どこのセル番地にあったよ」といったような事も普通に可能です。これが今までの全セル検索だったのです。ただしこの方法は対象のシートのみで有効です。
  • セルの値がkeywordを含むかどうかは、正規表現を使って調べます。searchで調べた結果、-1であれば含まれない、0以上であれば含むとわかるので、それを元に配列にレコードをpushしています。

FindTextメソッドを使ったパターン

FindTextを使って、データを探索してみましょう。自力で装備した場合に合わせて、見つかった行のデータをpushして表示するように調整しています。

  1. findtextの場合、該当のセルについてだけの情報が返ってくるので、nendo[i].getvalueとすると、2014という値が入ってるのを確認できます。
  2. また、getA1Notationで取得するとセル番地が返ってきます。つまり、アクティブセルについて指し示しているのと同じ状態なわけです。
  3. セル番地からタイトル行と配列は0からカウントする事を考え、-2するとちょうど、配列の位置と一致するのでこれを手がかりにarrayにpushさせています。
  4. また、今回は探索だけなので、textFinder.findAll()で検索だけをしましたが、これお textFinder.replaceAllWith('2020')とすると、2014を全て2020で置換してくれます

図:こんな具合に返ってきます。

関連リンク

コメントを残す

メールアドレスが公開されることはありません。 が付いている欄は必須項目です

日本語が含まれない投稿は無視されますのでご注意ください。(スパム対策)