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

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

Google関連

2019.07.3

[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();
  }
}