【GASで便利に】キーワードを含むセル番地を一覧表示する

検索キーワードを全シートから探して、ヒットしたセル番地をまとめて表示するGoogle Apps Scriptです。標準の検索機能ではヒットしたセルが1個ずつ分かりますが、まとめて知りたくなったので作成しました。

こんな悩みを解決します

• ある文字列を含むセルを全て探したい
• 標準の検索機能ではセルの場所が1つずつ示されるが、まとめて教えてほしい

用意するもの

特にありません。今お使いのスプレッドシートに組み込めます。

このスクリプトで出来ること

• 入力した検索キーワードを、スプレッドシート内の全てのシートから探します。
• ヒットしたセル番地をポップアップで一覧表示することができます。

使い方

1.スクリプトの実行

ダミー個人情報生成サービスを利用して、3シートにダミーデータを用意しました。

上部メニューから「検索にヒットしたセル番地をリストアップ」を実行します。

2.検索キーワードの入力

入力ボックスが表示されますので、検索したいキーワードを入力します。
正規表現を使うことも可能ですが、テストはしていませんので不具合があってもご容赦ください。
日付型は内部で「YYYY/MM/DD HH:MM:SS」に変換してマッチングしますが、こちらもあまりテストしていません・・・。

3.検索結果の表示

キーワードにマッチしたセルの情報（シート名、セル番地、セルの中身）が一覧で表示されます。
セルの中身が長い場合は「先頭の30文字」にカットされますので、検索キーワードが見えない場合があります。

スクリプト

/**
 * このスクリプトの説明、使い方はこちら。
 * https://myfunc.jp/items/00034/index.html
 */

function onOpen() {
  // スプレッドシートを開いたときに実行される関数

  // UIの取得
  const ui = SpreadsheetApp.getUi()

  // メニューの表示名
  const menu = ui.createMenu('スクリプト実行');

  // メニューに追加するボタン
  menu.addItem('検索にヒットしたセル番地をリストアップ', 'search_and_cell_address_list');

  // メニューを画面に追加する
  menu.addToUi();
}


function search_and_cell_address_list() {

  // アラートを表示するためにUIを取得
  const ui = SpreadsheetApp.getUi();

  // アクティブなスプレッドシートを取得
  const ss = SpreadsheetApp.getActiveSpreadsheet();

  // 検索キーワードの入力ボックスを表示
  const response = ui.prompt('検索キーワードを入力してください', ui.ButtonSet.OK_CANCEL);

  // キャンセルが押されたら何もせず終了する
  if (response.getSelectedButton() == ui.Button.CANCEL) {
    return;
  }

  // 入力された検索キーワードを受け取る
  const keyword = response.getResponseText();

  // 検索キーワードをもとに正規表現を作成する。大文字小文字は無視しておく
  const regex = new RegExp(keyword, "i");

  // スプレッドシート内の全てのシートを取得する
  const sheets = ss.getSheets();

  // 結果を格納する配列を用意する
  let result = [];


  // ここから検索処理。シートの数だけループ
  for (let i = 0; i < sheets.length; i++) {

    // シート名を取得
    let sheetname = sheets[i].getSheetName();

    // 各シート内で使われている全ての範囲を指定
    let range = sheets[i].getRange(1, 1, sheets[i].getLastRow(), sheets[i].getLastColumn());

    // セル内の値を取得する
    let values = range.getValues();

    // R1C1方式をA1方式に変換するために、アルファベットの配列を用意する
    const alphabets = 'ABCDEFGHIJKLMNOPQRSTUVWXYZ'.split('');

    // シート内の行でループする
    for (let j = 0; j < values.length; j++) {

      // 1行に存在する列の数だけループする
      for (let k = 0; k < values[j].length; k++) {

        // セルのデータの型によって判定方法を変える
        // 日付型の場合はわかりやすい形式に変換してから比較する
        if (Object.prototype.toString.call(values[j][k]) == '[object Date]') {

          // yyyy/MM/dd 〜 形式に変換する
          let datetime = Utilities.formatDate(values[j][k], "JST", "yyyy/MM/dd HH:MM:ss");

          // 検索キーワードと比較し、マッチすれば結果格納用配列に追加する
          if (datetime.toString().match(regex)) {
            result.push([sheetname, alphabets[k] + (j + 1), datetime]);
          }

        } else {
          // 検索キーワードと比較し、マッチすれば結果格納用配列に追加する
          // 日付型以外はそのまま比較する。ただし数値型ではmatchが使えないのでtoStringで文字列型にして比較する
          if (values[j][k].toString().match(regex)) {
            result.push([sheetname, alphabets[k] + (j + 1), values[j][k].toString().substr(0, 30)]);
          }
        }
      }
    }
  }

  ui.alert('「' + keyword + '」に以下がマッチしました。\n\nシート名/セル番地/セル内文字列\n' + result.join('\n'));

}

関連するアプリ