MENU
カテゴリー
  1. ホーム
  2. サービス連携
  3. GASでスプレッドシート内の文字列を検索・置換｜ヒット数・セル番地など自由に表示

GASでスプレッドシート内の文字列を検索・置換｜ヒット数・セル番地など自由に表示

サービス連携
スプレッドシート連携

本記事では、GASでスプレッドシート内の文字列を検索する方法を解説しています。

特定のシート内で検索する方法や全てのシートから検索する方法など、4つのケースに合わせた検索方法をサンプルコードと合わせて紹介しているのでぜひチェックしてみてください。

また、検索した文字列を一括で置換するコードも掲載しています。ぜひご活用ください。

今回のサンプル動画

GASで検索・置換
目次

GASでスプレッドシート内の文字列を検索する方法

GASでスプレッドシート内の文字列を検索する場合、以下の4つのケースが想定されます。

  1. 開いているアクティブなシート内だけで検索
  2. 特定のシート内だけで検索
  3. 全てのシートで検索
  4. 特定の範囲内だけで検索

それぞれのパターン別に実際のコードを紹介していきますが、文字列を検索する部分のメソッドはどれも同じです。

アクティブなシート内で検索

まずは、現在開いているシート上で特定の文字列を検索するスクリプトです。

function string_search_in_sheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getActiveSheet();

  // 検索する文字列
  const searchString = "検索する文字列";

  const textObject = sheet.createTextFinder(searchString);
  const results = textObject.findAll();
  
  // 検索結果数を表示
  console.log("一致数：　" + results.length);

  // 検索結果のセル位置を表示
  for(let i = 0; i < results.length; i++) {
    console.log("セル：　" + results[i].getA1Notation())
  }
}

GASでスプレッドシートの文字列を検索するには、createTextFinderメソッドでオブジェクトを取得し、そのTextFinderオブジェクトに対してfindAllメソッドを使用します。（8〜9行目）

検索結果は配列で返ってくるので、lengthプロパティで個数を調べることも可能です。（12行目）

実行結果

実行結果

指定したシート内で検索

次に、シートを指定して検索するスクリプトです。

function string_search_in_sheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("検索シート②");

  // 検索する文字列
  const searchString = "検索する文字列";

  const textObject = sheet.createTextFinder(searchString);
  const results = textObject.findAll();
  
  // 検索結果数を表示
  console.log("一致数：　" + results.length);

  // 検索結果のセル位置を表示
  for(let i = 0; i < results.length; i++) {
    console.log("セル：　" + results[i].getA1Notation())
  }
}

8行目のcreateTextFinderメソッドで指定するシートオブジェクトが異なるだけで、基本的なコードはアクティブシート内で検索するものと同じです。

実行結果

実行結果

全てのシート内で検索

全てのシート内から検索するスクリプトは、これまでの2つと少し異なります。

function string_search_in_sheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();

  // 検索する文字列
  const searchString = "検索する文字列";

  for(let i = 0; i < sheets.length; i++) {
    let sheetName = sheets[i].getSheetName();
    let textObject = sheets[i].createTextFinder(searchString);
    let results = textObject.findAll();

    // シート名+検索結果数を表示
    console.log("【" + sheetName + "】" + "　一致数：　" + results.length);

    // 検索結果のセル位置を表示
    for (let j = 0; j < results.length; j++) {
      console.log("セル：　" + results[j].getA1Notation());
    }
  }
}

createTextFinderメソッドは1枚のシートが対象です。そのため、一度全てのシートオブジェクトをgetSheetsメソッドで取得し、取得したシートオブジェクト1つずつ検索する必要があります。

実行結果

実行結果

指定した範囲内で検索

最後に、指定した範囲内で検索するスクリプトです。今回はアクティブなシート内の範囲を指定する想定で作成しています。

function string_search_in_sheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("検索シート①");
  const searchRange = sheet.getRange("A1:A");

  // 検索する文字列
  const searchString = "検索する文字列";

  const textObject = searchRange.createTextFinder(searchString);
  const results = textObject.findAll();

  // 検索結果数を表示
  console.log("一致数：　" + results.length);

  // 検索結果のセル位置を表示
  for(let i = 0; i < results.length; i++) {
    console.log("セル：　" + results[i].getA1Notation())
  }
}

範囲内で検索をするには、Rangeオブジェクトに対してcreateTextFinderメソッドを使用します。（9行目）

実行結果

実行結果

検索方法を完全一致にする

上記のスクリプトは、デフォルトで部分一致（◯◯を含む）で検索するようになっています。

完全一致で検索するには、TextFinderオブジェクトにオプションを指定しなければなりません。

function string_search_in_sheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("完全一致"); // 検索するシート
  const searchRange = sheet.getRange("H1:H"); // 検索する範囲

  // 検索する文字列（完全一致）
  const searchString = "大阪府";

  const textObject = searchRange.createTextFinder(searchString).matchEntireCell(true);
  const results = textObject.findAll();

  // 検索結果のセル位置を表示
  for (let j = 0; j < results.length; j++) {
    console.log("行数：" + results[j].getRow() + "　セル：" + results[j].getA1Notation());
  }
}

9行目のmatchEntireCellメソッドが、完全一致に変更するオプションです。trueを指定することで完全一致で検索することができます。そのほかのオプションについては、公式リファレンスをご確認ください。

検索した文字列をまとめて置換する方法

続いて、検索した文字列を別の文字列にまとめて置換するスクリプトをご紹介します。

GASでシート上の文字列を置換するには、 replaceAllWithメソッドを利用します。

function string_search_in_sheets() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("置換");

  // 検索する文字列
  const searchString = "△";

  // 置換する文字列
  const replaceString = "✖️"

  // 置換
  sheet.createTextFinder(searchString).replaceAllWith(replaceString);
}

上記スクリプトを実行すると、以下のように置換されます。

実行結果

セルの背景色はスプレッドシートの条件付き書式で変更しているためGASは関係ありません

replaceAllWithメソッドの引数には置換したい文字列を指定しましょう。

GASでシート内の文字列を検索・置換する動画のコード全体像

最後に、記事冒頭のサンプル動画のコード全体像を掲載しておきます。

/**************************************
 * 検索・置換
 **************************************/

// 定数
const SS = SpreadsheetApp.getActiveSpreadsheet();
const ACTIVE_SHEET = SS.getActiveSheet();
const ALL_SHEETS = SS.getSheets();

/**
 * 検索
 */
// アクティブシート
function search_active_sheet() {
  let searchResults = ""; // 表示用
  const searchString = getSearchString_(); // 検索する文字列を取得
  if(searchString === "cancel") return; // キャンセルなら終了
  const matchFlag = getMatchFlag_(); // 完全一致検索フラグ（true：完全一致　false：部分一致）
  if(matchFlag === "cancel") return; // キャンセルなら終了

  const textObject = ACTIVE_SHEET.createTextFinder(searchString).matchEntireCell(matchFlag);
  const results = textObject.findAll();
  
  // 検索結果が0件の場合のメッセージ表示
  if(results.length === 0) {
    Browser.msgBox(`[${searchString}]にヒットしたものはありませんでした。`);
    return;
  }
  // 検索結果数を表示
  searchResults += `シート名：${ACTIVE_SHEET.getSheetName()}（ヒット数：${results.length}件）\\n`;

  // 検索結果のセル位置を表示
  for(let i = 0; i < results.length; i++) {
    searchResults += `セル　　：　${results[i].getA1Notation()}\\n`;
  }
  Browser.msgBox(searchResults);
}

// シート名指定
function search_sheet_name() {
  let searchResults = ""; // 表示用
  const inputSheetName = getSearchSheetName_(); // 検索するシート名を取得
  if(inputSheetName === "cancel") return; // キャンセルなら終了
  const sheetName = SS.getSheetByName(inputSheetName); // シートオブジェクト取得
  if(sheetName === null) {Browser.msgBox(`[${inputSheetName}]というシート名が存在しません。`);}

  const searchString = getSearchString_(); // 検索する文字列を取得
  if(searchString === "cancel") return; // キャンセルなら終了
  const matchFlag = getMatchFlag_(); // 完全一致検索フラグ（true：完全一致　false：部分一致）
  if(matchFlag === "cancel") return; // キャンセルなら終了

  const textObject = sheetName.createTextFinder(searchString).matchEntireCell(matchFlag);
  const results = textObject.findAll();

  // 検索結果が0件の場合のメッセージ表示
  if(results.length === 0) {
    Browser.msgBox(`[${searchString}]にヒットしたものはありませんでした。`);
    return;
  }
  
  // 検索結果数を表示
  searchResults += `シート名：${sheetName.getSheetName()}（ヒット数：${results.length}件）\\n`;

  // 検索結果のセル位置を表示
  for(let i = 0; i < results.length; i++) {
    searchResults += `セル　　：　${results[i].getA1Notation()}\\n`;
  }
  Browser.msgBox(searchResults);
}

// 全てのシート
function search_all_sheets() {
  let searchResults = ""; // 表示用
  let matchCounter = 0; // 一致したセルがあるシートカウント
  const searchString = getSearchString_(); // 検索する文字列を取得
  if(searchString === "cancel") return; // キャンセルなら終了
  const matchFlag = getMatchFlag_(); // 完全一致検索フラグ（true：完全一致　false：部分一致）
  if(matchFlag === "cancel") return; // キャンセルなら終了

  for(let i = 0; i < ALL_SHEETS.length; i++) {
    let sheetName = ALL_SHEETS[i].getSheetName();
    let textObject = ALL_SHEETS[i].createTextFinder(searchString).matchEntireCell(matchFlag);
    let results = textObject.findAll();

    // 検索結果が0件の場合のメッセージ表示
    if(results.length === 0) {
      if(matchCounter === SS.getNumSheets()) {
        Browser.msgBox(`[${searchString}]にヒットしたものはありませんでした。`);
        return;
      }
      matchCounter ++;
      continue;
    }

    // 検索結果数を表示
    searchResults += `シート名：${sheetName}（ヒット数：${results.length}件）\\n`;

    // 検索結果のセル位置を表示
    for (let j = 0; j < results.length; j++) {
      searchResults += `セル　　：　${results[i].getA1Notation()}\\n`;
    }
    searchResults += "\\n";
  }
  　Browser.msgBox(searchResults);
}

/**
 * 置換
 */
// アクティブシート
function replace_active_sheet() {
  let searchResults = ""; // 表示用
  const searchString = getSearchString_(); // 検索する文字列を取得
  if(searchString === "cancel") return; // キャンセルなら終了
  const matchFlag = getMatchFlag_(); // 完全一致検索フラグ（true：完全一致　false：部分一致）
  if(matchFlag === "cancel") return; // キャンセルなら終了
  const replaceString = getReplaceString_(); // 置換する文字列を取得
  if(replaceString === "cancel") return; // キャンセルなら終了

  const textObject = ACTIVE_SHEET.createTextFinder(searchString).matchEntireCell(matchFlag);
  const results = textObject.findAll();
  textObject.replaceAllWith(replaceString);
  
  // 検索結果が0件の場合のメッセージ表示
  if(results.length === 0) {
    Browser.msgBox(`[${searchString}]にヒットしたものはありませんでした。`);
    return;
  }
  // 検索結果数を表示
  searchResults += `[${searchString}]を[${replaceString}]に置換しました。\\n\\n`;
  searchResults += `シート名：${ACTIVE_SHEET.getSheetName()}（ヒット数：${results.length}件）\\n`;
  
  // 検索結果のセル位置を表示
  for(let i = 0; i < results.length; i++) {
    searchResults += `セル　　：　${results[i].getA1Notation()}\\n`;
  }
  Browser.msgBox(searchResults);
}

// シート名指定
function replace_sheet_name() {
  let searchResults = ""; // 表示用
  const inputSheetName = getSearchSheetName_(); // 検索するシート名を取得
  if(inputSheetName === "cancel") return; // キャンセルなら終了
  const sheetName = SS.getSheetByName(inputSheetName); // シートオブジェクト取得
  if(sheetName === null) {Browser.msgBox(`[${inputSheetName}]というシート名が存在しません。`);return;}
  const searchString = getSearchString_(); // 検索する文字列を取得
  if(searchString === "cancel") return; // キャンセルなら終了
  const matchFlag = getMatchFlag_(); // 完全一致検索フラグ（true：完全一致　false：部分一致）
  if(matchFlag === "cancel") return; // キャンセルなら終了
  const replaceString = getReplaceString_(); // 置換する文字列を取得
  if(replaceString === "cancel") return; // キャンセルなら終了

  const textObject = sheetName.createTextFinder(searchString).matchEntireCell(matchFlag);
  const results = textObject.findAll();
  textObject.replaceAllWith(replaceString);

  // 検索結果が0件の場合のメッセージ表示
  if(results.length === 0) {
    Browser.msgBox(`[${searchString}]にヒットしたものはありませんでした。`);
    return;
  }
  
  // 検索結果数を表示
  searchResults += `[${searchString}]を[${replaceString}]に置換しました。\\n\\n`;
  searchResults += `シート名：${sheetName.getSheetName()}（ヒット数：${results.length}件）\\n`;
  
  // 検索結果のセル位置を表示
  for(let i = 0; i < results.length; i++) {
    searchResults += `セル　　：　${results[i].getA1Notation()}\\n`;
  }
  Browser.msgBox(searchResults);
}

// 全てのシート
function replace_all_sheets() {
  let searchResults = ""; // 表示用
  let matchCounter = 0; // 一致したセルがあるシートカウント
  const searchString = getSearchString_(); // 検索する文字列を取得
  if(searchString === "cancel") return; // キャンセルなら終了
  const matchFlag = getMatchFlag_(); // 完全一致検索フラグ（true：完全一致　false：部分一致）
  if(matchFlag === "cancel") return; // キャンセルなら終了
  const replaceString = getReplaceString_(); // 置換する文字列を取得
  if(replaceString === "cancel") return; // キャンセルなら終了

  for(let i = 0; i < ALL_SHEETS.length; i++) {
    let sheetName = ALL_SHEETS[i].getSheetName();
    let textObject = ALL_SHEETS[i].createTextFinder(searchString).matchEntireCell(matchFlag);
    let results = textObject.findAll();
    textObject.replaceAllWith(replaceString);

    // 検索結果が0件の場合のメッセージ表示
    if(results.length === 0) {
      if(matchCounter === SS.getNumSheets() - 1) {
        Browser.msgBox(`[${searchString}]にヒットしたものはありませんでした。`);
        return;
      }
      matchCounter ++;
      continue;
    }

    // 検索結果数を表示
    searchResults += `シート名：${sheetName}（ヒット数：${results.length}件）\\n`;

    // 検索結果のセル位置を表示
    for (let j = 0; j < results.length; j++) {
      searchResults += `セル　　：　${results[i].getA1Notation()}\\n`;
    }
    searchResults += "\\n";
  }
  searchResults += `[${searchString}]を[${replaceString}]に置換しました。\\n`;
  Browser.msgBox(searchResults);
}

/**
 * 取得
 */
// 検索する文字列
function getSearchString_() {
  const searchString = Browser.inputBox("検索する文字列を入力してください。\\n",Browser.Buttons.OK_CANCEL);
  return searchString;
}
// 検索するシート名
function getSearchSheetName_() {
  const inputSheetName = Browser.inputBox("検索するシート名を入力してください。\\n",Browser.Buttons.OK_CANCEL);
  return inputSheetName;
}
// 置換する文字列
function getReplaceString_() {
  const replaceString = Browser.inputBox("置換したい文字列を入力してください。\\n",Browser.Buttons.OK_CANCEL);
  return replaceString;
}
// 完全一致で検索するか
function getMatchFlag_() {
  const matchFlag = Browser.msgBox("完全一致で検索しますか？\\n\\nはい　 → 完全一致\\nいいえ → 部分一致\\n",Browser.Buttons.YES_NO_CANCEL);
  if(matchFlag === "yes") {
    return true;
  } else if(matchFlag === "no") {
    return false;
  } else {
    return matchFlag;
  }
}

// 独自メニュー作成
function onOpen() {
  const ui = SpreadsheetApp.getUi();
  const menu = ui.createMenu("シート操作");

  menu.addItem("新規作成","m_gas_sheet_create");
  menu.addItem("コピー","m_gas_sheet_copy");
  menu.addSubMenu(
    ui.createMenu('削除')
    .addItem("特定のシートを削除","m_gas_sheet_delete_1")
    .addItem("特定のシート以外を削除","m_gas_sheet_delete_2")
    .addItem("特定の複数シートを削除","m_gas_sheet_delete_3")
    .addItem("特定の複数シート以外を削除","m_gas_sheet_delete_4")
     .addItem("全てのシートを削除","m_gas_sheet_delete_5")
  );
  menu.addItem("アクティブ化","m_gas_sheet_active");
  menu.addItem("アクティブ列の最終行を取得","m_gas_getlastrow");
  menu.addSeparator();
  menu.addItem("シートへ書き込み","m_writing_on_sheets");

  const menuSearch = ui.createMenu("検索・置換");
  menuSearch.addSubMenu(
    ui.createMenu("検索")
    .addItem("アクティブシート","search_active_sheet")
    .addItem("シート名指定","search_sheet_name")
    .addItem("全てのシート","search_all_sheets")
  )

  menu.addToUi();
  menuSearch.addToUi();
}

文字列の検索・置換はスプレッドシート上でもできますが、GASでのやり方を知っておくと非常に便利です。ぜひ、活用してみてください。

GASでスプレッドシートに新しいシートを作成する
GASでスプレッドシートのシートをコピーするスクリプト
GASでスプレッドシートのシートを削除｜パターン別コードあり
サービス連携
スプレッドシート連携
よかったらシェアしてね！
  • URLをコピーしました！

おすすめ記事

目次