Google

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

2019年4月17日 / 最終更新日時 : 2022年7月12日 officeの杜 Google

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

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

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

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

1 今回使用するスプレッドシート
2 スプレッドシート上の操作としての置換
3 自力で全文検索するパターン
4 FindTextメソッドを使ったパターン
5 関連リンク

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

全文検索するメソッドを使うスプレッドシート

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

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

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

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

図：詳細な検索窓

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

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

var keyword = "2014"
//二次元配列を回して全文検索するコード
function fulltextsearch() {
  //探索結果を格納する配列
  var array = [];
  
  //スプレッドシートを取得する
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("database").getRange("A2:D").getValues();
  var length = sheet.length;   //行数を取得
  var col = sheet[0].length;   //列数を取得
  var str;
  var pattern = new RegExp(keyword);  //正規表現パターン

  //配列を回して探索
  for(var i = 0;i<length;i++){
    for(var j = 0;j<col;j++){
      //配列の値を取り出す
      str = String(sheet[i][j]);
      
      //正規表現でチェック
      var result = str.search(pattern);

      //行内をくまなく検索
      if(result >= 0){
        //データが見つかったら配列にレコードをpushする
        array.push(sheet[i])
        
        //ループを抜ける
        break;
      }
    }
  }
  
  //ログに配列データを書き出す
  Logger.log(array);
  Logger.log(array.length + "件ヒットしたよ");
}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

34

35

36

37

38

var keyword = "2014"

//二次元配列を回して全文検索するコード

function fulltextsearch() {

//探索結果を格納する配列

var array = [];

//スプレッドシートを取得する

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("database").getRange("A2:D").getValues();

var length = sheet.length; //行数を取得

var col = sheet[0].length; //列数を取得

var str;

var pattern = new RegExp(keyword); //正規表現パターン

//配列を回して探索

for(var i = 0;i<length;i++){

for(var j = 0;j<col;j++){

//配列の値を取り出す

str = String(sheet[i][j]);

//正規表現でチェック

var result = str.search(pattern);

//行内をくまなく検索

if(result >= 0){

//データが見つかったら配列にレコードをpushする

array.push(sheet[i])

//ループを抜ける

break;

}

//ログに配列データを書き出す

Logger.log(array);

Logger.log(array.length + "件ヒットしたよ");

}

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

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

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

//findtextを使った全文検索をするコード
function findtextsearch(){
  //探索結果を格納する配列
  var array = [];
  
  //スプレッドシートを取得する
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("database")
  var data = sheet.getRange("A2:D").getValues();
  
  //findtextサーチ
  var textFinder = sheet.createTextFinder(keyword);
  var nendo = textFinder.findAll();
  
  //findtext結果を配列に打ち込む
  for(var i = 0;i<nendo.length;i++){
    //セル番地を取得する（見つかったセルだけが取得されてる）
    var cellman = nendo[i].getA1Notation()

    //セル番地から数字のみ取り出し
    //タイトル行から始まるのと、0からカウントするので、-2を引いた値が配列での該当の行番号になる
    var address = cellman.replace(/[^0-9]/g, '') - 2;
    
    //番地データに+1してレコードを配列からpush
    array.push(data[address]);
    
  }

  //ログにデータを書き出す
  Logger.log(array);
  Logger.log(nendo.length + "件ヒットしたみたいだぜ");

}

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

24

25

26

27

28

29

30

31

32

33

//findtextを使った全文検索をするコード

function findtextsearch(){

//探索結果を格納する配列

var array = [];

//スプレッドシートを取得する

var ss = SpreadsheetApp.getActiveSpreadsheet();

var sheet = ss.getSheetByName("database")

var data = sheet.getRange("A2:D").getValues();

//findtextサーチ

var textFinder = sheet.createTextFinder(keyword);

var nendo = textFinder.findAll();

//findtext結果を配列に打ち込む

for(var i = 0;i<nendo.length;i++){

//セル番地を取得する（見つかったセルだけが取得されてる）

var cellman = nendo[i].getA1Notation()

//セル番地から数字のみ取り出し

//タイトル行から始まるのと、0からカウントするので、-2を引いた値が配列での該当の行番号になる

var address = cellman.replace(/[^0-9]/g, '') - 2;

//番地データに+1してレコードを配列からpush

array.push(data[address]);

}

//ログにデータを書き出す

Logger.log(array);

Logger.log(nendo.length + "件ヒットしたみたいだぜ");

}

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

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

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

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

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

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

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

関連リンク

コメントを残すコメントをキャンセル

Google Apps Scriptでスプレッドシートに画像を貼り付ける【GAS】

Google Apps ScriptでXMLをよしなに扱う方法【GAS】

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

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

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

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

関連リンク

コメントを残す コメントをキャンセル

コメントを残すコメントをキャンセル