GASで文字列を含むセルを検索する方法

GASでスプレッドシート内から文字列を含むセルを検索する方法

GASを使えば、スプレッドシート内で特定の文字列を含むセルを簡単に検索できます。それでは、基本的な方法を見てみましょう。

function searchText() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange("A1:D10");
  const values = range.getValues();
  const searchText = "猫";

  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      if (values[i][j].toString().indexOf(searchText) !== -1) {
        Logger.log("Found '" + searchText + "' in cell " + sheet.getRange(i + 1, j + 1).getA1Notation());
      }
    }
  }
}

スプレッドシートのgetActiveSpreadsheet()を用いて、アクティブなスプレッドシートを取得しています。

続いて、getActiveSheet()でアクティブシートを選択し、getRange(“A1:D10”)で指定範囲を得るのです。

範囲内の全てのデータはgetValues()によって配列として扱われます。

 

次に、関数の中で2重のループを用い、配列の各要素を調べます。

indexOf()を使って特定の文字列が存在するかチェックします。このメソッドでは、オブジェクトに引数が含まれない場合は-1を返し、含まれている場合は正の数のインデックスを返します。

それゆえ、-1ではない時、それ即ち、文字列を含んでいることを意味しています。

このスクリプトでは”猫”という文字列を探しています。

応用：他の方法でGASの文字列検索をおこなう方法

他の方法も見てみましょう。

フィルター機能を利用する

GASでフィルターを使い、より便利に文字列検索を行えます。

function filterAndSearch() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  sheet.getRange("A1:A10").createFilter();
  const criteria = SpreadsheetApp.newFilterCriteria().whenTextContains("猫").build();
  sheet.getRange("A1:A10").getFilter().setColumnFilterCriteria(1, criteria);
}

• getActiveSpreadsheet().getActiveSheet() … アクティブなシートを取得
• getRange("A1:A10").createFilter() … A1:A10の範囲にフィルターを作成
• SpreadsheetApp.newFilterCriteria() … フィルター条件を作るビルダー
• .whenTextContains("猫") … 「猫」を含むセルを残す条件
• setColumnFilterCriteria(1, criteria) … 1列目（A列）に条件を適用

フィルター機能を使うと、特定の文字列を含む行だけを抽出できます。この例ではColumn 1に”猫”の文字列が含まれる行を表示するようにしています。

簡単に言えば、「スプレッドシートのA列で『猫』を含む行だけを残すフィルターを自動でかけるスクリプト」。

 

例えば、こんな感じのシートで実行すると、

こうなります。

 

正規表現で高性能な検索

正規表現を用いると、条件を柔軟に設定して文字列を検索できます。

function regexSearch() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange("A1:D10");
  const values = range.getValues();
  const regex = /猫/;

  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      if (regex.test(values[i][j].toString())) {
        Logger.log("Regex matched in cell " + sheet.getRange(i + 1, j + 1).getA1Notation());
      }
    }
  }
}

• getValues() … 範囲を二次元配列として取得
• /猫/ … 「猫」を含むか判定する正規表現
• regex.test() … セルの内容が正規表現にマッチするか判定
• Logger.log() … マッチしたセルのアドレスをログに出力

正規表現では、test()を使って文字列とパターンの一致を確認します。

この方法を使うとより柔軟な条件での検索が可能です。

 

練習問題

“犬”という文字列が含まれるセルを検索し、そのセルの値を”わんわん”に置き換えるスクリプトを書いてみましょう。

 

 

練習問題の解答・解説

以下のスクリプトが解答です。

function replaceDogWithWoof() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  const range = sheet.getRange("A1:D10");
  const values = range.getValues();

  for (let i = 0; i < values.length; i++) {
    for (let j = 0; j < values[i].length; j++) {
      if (values[i][j].toString().indexOf("犬") !== -1) {
        sheet.getRange(i + 1, j + 1).setValue("わんわん");
        Logger.log("Replaced value in cell " + sheet.getRange(i + 1, j + 1).getA1Notation());
      }
    }
  }
}

このスクリプトでは文中で紹介した文字列検索方法を使い、文字列が存在するセルに対してsetValue(“わんわん”)で値を変更しています。

上記の方法で文字列を検出して目的の文字列に置き換えることができます。