2022/01/13に更新

【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'));

}

関連するアプリ


【GASで便利に】2つのセルの値を入れ替える

選択した2つのセル(範囲)の値を、入れ替えるGoogle Apps Scriptです。行や列の入れ替えもできます。

【GASで便利に】セルの順番を逆にする(行)

選択した行範囲の順番を逆にするGoogle Apps Scriptです。ソートではなく、単純に順番を逆にします。関数の組み合わせでも実現できますが、GASを登録してしまったほうが早いかもしれません。

【GASで便利に】セルの順番を逆にする(列)

選択した列範囲の順番を逆にするGoogle Apps Scriptです。行バージョンを作ったので、列バージョンも。ニーズはあるのでしょうか。

【GASで便利に】セルの順番を逆にする(行と列)

選択した行および列範囲の順番を逆にするGoogle Apps Scriptです。これは本当に使い所があるのかわかりませんが、作ってみたので公開します。

【GASで便利に】指定した文字数でセルに分割する(行方向)

1つのセルに入力されている文字列を、指定した文字数で分割するGoogle Apps Scriptです。区切りに使用する文字が無くても大丈夫です。行方向に分割します。

【GASで便利に】指定した文字数でセルに分割する(列方向)

1つのセルに入力されている文字列を、指定した文字数で分割するGoogle Apps Scriptです。区切りに使用する文字が無くても大丈夫です。列方向に分割します。

【GASで時短】結合されたセルを検索&解除する

選択範囲に存在する結合されたセルを検索し、解除することもできるGoogle Apps Scriptです。予想外の結合によって困ったことがある方は多いと思います。このアプリが転ばぬ先の杖になれば幸いです。結合を解除するかどうかはダイアログで選択できます。

【GASで時短】データ・文字が入っていない行を削除する

シート内の「何も書き込まれていない行」を削除してくれるGoogle Apps Scriptです。スプレッドシートをメモ帳代わりにしていると、特に意味のない空白行がたくさんあったりして、消すのが面倒だったので作成してみました。すべての列に何も文字が入っていない行を消し去ります。

【GASで時短】1行ごとに空白行を挿入する

1行ごとに任意の空白行を挿入してくれるGoogle Apps Scriptです。データを整理中に「今ある行の間に1行ずつ足したい・・・」ということが稀によくあるので作成しました。追加する行数はプロンプトで指定が可能です。

【GASで便利に】シート内の全てのリンクを抽出する

シート内に存在するハイパーリンク(URL)を取り出すGoogle Apps Scriptです。リンクを全て抜き出したくなったので作成してみました。

【GASで時短】全シートに条件付き書式をセットする

すべてのシートに条件付き書式を適用してくれるGoogle Apps Scriptです。条件付き書式は便利ですが手動で設定するのは面倒なんですよね。

【GASで時短】全シートの条件付き書式を削除する

すべてのシートのすべての条件付き書式を削除するGoogle Apps Scriptです。条件付き書式はシート単位で操作する必要があり、面倒なので作成しました。

【GASで時短】全てのシートのデータを1シートにまとめる

スプレッドシートの全てのシートのデータを、1つのシートに集約するGoogle Apps Scriptです。各シートで列が揃っていないと使いにくいですが、思いつきで作ってみました。

タグ一覧

最新のアプリ

人気のアプリ

myfunc.jp