Google関連

[Google Apps Script]スプレッドシートで検索と置換を行う

Google スプレッドシートには、検索と置換機能(Ctrl + F)が備わっています。

今年4月のApps Scriptのアップデートによって、TextFinderクラスが実装され、Apps Scriptからも簡単に検索と置換を行うことができるようになりました。

すべてのシートを対象に検索

「検索と置換」ダイアログの「検索」から検索対象を指定できるように、スクリプトからも検索対象を指定できます。

まずは、すべてのシートを対象に検索を行うコードです。

Spreadsheet.createTextFinderメソッドでTextFinderオブジェクトを取得し、findAllメソッドで検索を行うコードです。

function sample1() {
  Logger.log("--- sample1 ---");
  var targetSpreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var finder = targetSpreadsheet.createTextFinder("河").useRegularExpression(true); //[正規表現を使用した検索]有効
  var results = finder.findAll();
  results.forEach(function(rng) {
    Logger.log(rng.getSheet().getName() + ", " + rng.getA1Notation() + ", " + rng.getValue());
  });  
}

createTextFinderメソッドの引数で指定しているのは検索文字列で、useRegularExpressionメソッドの引数をtrueにすることで、「正規表現を使用した検索」オプションを有効にしています(今回の例では正規表現を使用していませんが・・・)。

結果は下図の通りで、すべてのシート上で検索が行われているのが確認できます。

なお、今回のスクリプトをテストするにあたって、データは「なんちゃって個人情報」で生成したものを利用しています。

指定したシートを対象に検索

次は、シートを指定して検索を行うコードです。
Sheet.createTextFinderメソッドを使用しています。

function sample2() {
  Logger.log("--- sample2 ---");
  var targetSheet = SpreadsheetApp.getActiveSheet();
  var finder = targetSheet.createTextFinder("河").useRegularExpression(true); //[正規表現を使用した検索]有効
  var results = finder.findAll();
  results.forEach(function(rng) {
    Logger.log(rng.getSheet().getName() + ", " + rng.getA1Notation() + ", " + rng.getValue());
  });
}

指定したセル範囲を対象に検索

次は、指定したセル範囲を対象に検索を行うコードです。
Range.createTextFinderメソッドを使用しています。

function sample3() {
  Logger.log("--- sample3 ---");
  var targetRange = SpreadsheetApp.getActiveSheet().getRange("A2:A20");
  targetRange.activate();
  var finder = targetRange.createTextFinder("河").useRegularExpression(true); //[正規表現を使用した検索]有効
  var results = finder.findAll();
  results.forEach(function(rng) {
    Logger.log(rng.getSheet().getName() + ", " + rng.getA1Notation() + ", " + rng.getValue());
  });
}

ヒットしたセルを順次処理

次は、findNextメソッドを使って、ヒットしたセルに対して順次処理を行うコードです。

function sample4() {
  var ui = SpreadsheetApp.getUi();
  var targetRange = SpreadsheetApp.getActiveSheet().getRange("A2:A20");
  var finder = targetRange.createTextFinder("河").useRegularExpression(true); //[正規表現を使用した検索]有効
  var res = finder.findNext();
  while(res !== null) {
    res.activate();
    if(ui.alert("セル[ " + res.getA1Notation() + " ]選択中\n処理を中止しますか?", ui.ButtonSet.YES_NO) == ui.Button.YES) {
      break;
    }
    res = finder.findNext();
  }
}

オプションを指定して検索

次は、「大文字と小文字の区別」「完全に一致するセルを検索」といったオプションを指定して検索を行うコードです。

検索オプションの指定には、ignoreDiacriticsメソッド(発音区別符号を無視するかどうか)、matchCaseメソッド(大文字と小文字の区別)、matchEntireCellメソッド(完全に一致するセルを検索)、matchFormulaTextメソッド(数式内も検索)、useRegularExpressionメソッド(正規表現を使用した検索)を使用します。

//オプションを指定して検索
function sample5() {
  Logger.log("--- sample5 ---");
  var targetSheet = SpreadsheetApp.getActiveSheet();
  var finder = targetSheet.createTextFinder("IKEUCHI_hiroshi@example.com")
    .ignoreDiacritics(true)  //発音区別符号を無視する
    .matchCase(true)         //[大文字と小文字の区別]有効
    .matchEntireCell(true)   //[完全に一致するセルを検索]有効
    .matchFormulaText(true); //[数式内も検索]有効
  var results = finder.findAll();
  results.forEach(function(rng) {
    Logger.log(rng.getSheet().getName() + ", " + rng.getA1Notation() + ", " + rng.getValue());
  });
}

ヒットした文字列を一括置換

次は、replaceAllWithメソッドを使って、ヒットした文字列を一括で置換するコードです。

function sample6() {
  var targetSheet = SpreadsheetApp.getActiveSheet();
  var finder = targetSheet.createTextFinder("@example.com");
  var cnt = finder.replaceAllWith("@exampleXYZ.com");
  SpreadsheetApp.getUi().alert(cnt + "件置換しました。");
}

確認しながら順次置換

最後は、findNextメソッドとreplaceWithメソッドを使って、順次確認しながら置換を行うコードです。

function sample7() {
  var ui = SpreadsheetApp.getUi();
  var targetSheet = SpreadsheetApp.getActiveSheet();
  var finder = targetSheet.createTextFinder("@example.com");
  var res = finder.findNext();
  while(res !== null) {
    switch(ui.alert("セル[ " + res.getA1Notation() + " ]の値[ " + res.getValue() + " ]を置換しますか?", ui.ButtonSet.YES_NO_CANCEL)) {
      case ui.Button.YES:
        finder.replaceWith("@exampleXYZ.com");
        break;
      case ui.Button.CANCEL:
      case ui.Button.CLOSE:
        return;
      default:
        break;
    }
    res = finder.findNext();
  }
}

以上のように、TextFinderを使うことで、簡単に文字列の検索や置換が行えます。
使い勝手も良く、使用する機会も多い処理ですので、覚えておくと良いでしょう。

10回目のMicrosoft MVP受賞とこれまでの振り返り前のページ

[Google Apps Script]箇条書きと番号付きリストを設定する次のページ

関連記事

  1. Google関連

    Gmail APIを使ってメール送信するC#コード

    「たまには.NET用のライブラリを使ってGoogle APIでも触って…

  2. Google関連

    [Google Apps Script]Google ドライブにあるファイルをファイル名順で出力する…

    以前書いた「Google ドライブにある画像をスライドに一括挿入するG…

  3. Google関連

    [Google Apps Script]郵便番号から住所を取得する自作関数と住所から地図画像のURL…

    郵便番号検索APIを使って郵便番号から住所を取得する自作関数と、Sta…

  4. Office関連

    Google Docs APIを使ってGoogle ドキュメントを操作するVBAマクロ

    前回の記事で新しく追加されたGoogle Docs APIを紹介しまし…

  5. Google関連

    [Google Apps Script]選択中のスライドに対してのみ処理を行う

    以前、Google スライドですべてのスライドに対して処理を行う方法に…

コメント

  • コメント (0)

  • トラックバックは利用できません。

  1. この記事へのコメントはありません。

Time limit is exhausted. Please reload CAPTCHA.

※本ページはプロモーションが含まれています。

Translate

最近の記事

アーカイブ

PAGE TOP