Googleスプレッドシート 検索と置換を使いこなそう！4（GAS TextFinderで 改行置換）

Googleスプレッドシートの 検索と置換シリーズ 4回目です。

前回までは3回にわたって GAS無しで出来る「検索と置換」の基本、Excelとの違い、正規表現を使った応用例などを紹介してきました。

今回はいよいよ GASを使った検索と置換、TextFinderクラスを使う方法に入っていきます。

GASを使わなくても正規表現で色々出来て十分凄いんですが、検索と置換をGAS(Google Apps Script)から操作できるようになると一気に世界が広がりますよ～。

前回は超応用例 として 先読み・後読みを活用した検索・置換について書きました。（「検索と置換」で出来ることのまとめも掲載）

GASを使おう！その前に

検索と置換では対応出来ない処理をやりたい！

そんな時コードがさくっと書ける人なら GASを使うのは良い選択肢ですが、不慣れな人はGAS以外の選択肢も考えた方がよいでしょう。

よくよく調べると意外と標準機能で対応できたり、シート関数を組み合わせれば対応出来たってケースも多かったりします。

今回紹介する拡張機能（アドオン）の追加も、選択肢の一つとして覚えておくと良いでしょう。

検索と置換の拡張機能 Advanced Find and Replace

検索と置換をパワーアップさせるアドオンの代表が Advanced Find and Replace です。

以前、困った表を集計する方法を書いた noteで紹介した、ExcelのPowerQuery（パワークエリ）的なことが出来る Googleスプレッドシートのアドオン Power Tools と同じと Ablebits の提供ですね。

こちらは有料アドオンですが、無料試用期間があるのでちょっと試してみるのも良いかもしれません。

mirは無料でとりあえず使ってみました。

このように検索はサイドバー表示となります。

検索においては Excelと同じような ワイルドカードが使えたり、Notes（メモ内の文字）も検索できたり、検索するシートを複数指定できたり、検索結果が 一覧表示されたりと、出来ることがかなり増えていますね。

正規表現を使える人はアドオンいらんだろって判断なんでしょうか？ あえてワイルドカードを採用したのは面白いです。

Excelの方が使い慣れている人や、便利ってのはわかるけど正規表現はちょっとハードルが高いんで、ワイルドカードがちょうどいいって人には良いかも。

メモ内も検索できるのは凄いです。残念ながらコメントの方は検索対象にすることは出来ません。コメントを扱えるGASはないんですよね。SheetsAPIだといけるっぽい。

このように 検索結果はちょっと横幅が狭いですがリスト表示され、選択するとそのセルがアクティブになります。

また「︙」 からオプションを開くことで、検索結果をエクスポートできたり、検索でヒットしたセルがある行全体を削除、エクスポートといったことも出来ます。

Ctrlを押しながらだと複数の結果を選択は出来るんですが、シートの方は複数セルがアクティブとならないのはちょっとだけ残念。

検索窓の 右の ▼ から過去の検索ワードの履歴が開けて、簡単に再検索できるのも便利です。

置換に関しては多機能というわけではありませんが、改行への置換が出来るのが魅力です。

置換後のワードを入力するボックスで Alt + Enter を押すと、[Line break] (改行)がセットできます。後は Replace all で改行への一括置換すればOK！簡単ですね。

Google公式じゃないアドオンを入れることに抵抗があったり、会社アカウントだと禁止されてたりもあるでしょうが、需要が合えば無料で試してみて導入を検討するのも良いんじゃないでしょうか。

でも、このくらいの機能はGoogleが提供して欲しいなーと思いますよね。。

GASの検索と置換 TextFinderクラスの基本

アドオンという選択肢を紹介しましたが、

• どうしてもアドオンは使いたくない！

• 無料お試しがあっても有料アドオンは不安！

• そもそもやりたいことはこのアドオンでは出来ない！

という場合は、GASでコードを書く必要があります。

GASの TextFinder で何が出来るか？を理解していきましょう。まずは基本の理解です。

TextFinderの基本を理解する

とりあえず押さえるべき基本、公式リファレンスを確認しておきましょう。

メソッド類は、ほぼ検索と置換の 機能 に対応したものなので、わかりやすいと思います。

このようにまとめてみました。

メソッドは、

このような流れで記述します。チェーンメソッド（.で繋いで記述）が可能です。

サンプルコード

const sheet = SpreadsheetApp.getActiveSheet();

function sample(){
  sheet.createTextFinder("りんご").matchFormulaText(true).replaceAllWith("ごりら");
}

こちらは、

今開いているシートを
"りんご" を含むセルを
数式内も含めて 検索して
全て"ごりら" に置換する

という処理を記述したコードです。

読みやすいですよね？

これをマクロにインポートして、ショートカットから実行してみましょう。

数式内含め"りんご" が "ごりら" に置換されました。
これが TextFinderの基本系です。

GASのTextFinderクラスには、以下の3つの特徴があります。

1. 検索と置換 機能で出来ること +α が可能になる

2. マクロの記録では 記録できない、生成AI は苦手っぽい

3. スピードや処理において getValues / setValues より良いケースがある

この3つの特徴も理解しておきましょう。

1. 検索と置換 機能で出来ること +α が可能になる

GASを使うことで 検索と置換 の標準機能では出来ないことが可能になります。

その一つが findAll( )　メソッドです。 

この返り値を見てみましょう。

findNext( ) では、検索にマッチした Range（オブジェクト）が、そして findAll( ) では、Range（オブジェクト）の配列が返ります。

Rangeオブジェクトが得られるということは、そのセルに対しての書式操作やA1表記でのセル位置取得、その行や列に対する削除や非表示といった操作、さらにoffsetを使って隣のセルを取得する、なんてことが出来ます。

夢が広がりますね。

また、findAll( ) で得られる Range（オブジェクト）の配列、

この配列の要素の個数　=　検索にマッチしたセルの件数

となります。つまり 全シート検索で マッチしたセル件数を取得したり、シート内で マッチした全てのセルをアクティブにする、なんてことも出来るわけです。

置換においては、前回は 無理やり数式をかまして それっぽい処理とした 改行への置換 も簡単に出来ちゃいます。

この辺りのプラスアルファで出来ることを、後ほど（もしくは次回）詳しく解説していきます！

2. マクロの記録では 記録できない、生成AIも苦手っぽい

GASのコードを 1から自分で書くのは厳しいけど、コピペで少しアレンジなら出来るって人は結構います。

そのような初心者よりちょっと上という人たちに有効なのが、マクロの記録と 生成AI です。

これらを上手に使うことで、ベースとなるコードを取得することが出来ます。

しかし、残念ながら マクロの記録は 「検索と置換」で行った操作は記録してくれません。

もう一つの頼みの綱、生成AIに聞いた場合はどうでしょうか？

「スプレッドシートで 開いているシートの りんご を ごりら に全て置換するGASのコードを教えて」

と質問をすると 

このように、どうしても メジャーな getValues と setValuesを使ったコード。この場合は、getValues で得た配列を  2重ループで配列内で "りんご"と「一致するセル」の値を "ごりら"に差し替えてから setValues というコードを返してくることが多いです。

Bardだけではなく、ChatGPTも同じようなコードを回答してきました。

「textFinderを使ったコードに変更できますか？」とリクエストしても

微妙にそうじゃないコードが返ってきました・・・。

Bardの方も同様に TextFinderを使ってとリクエストしてみましたが、こちらは完全にエラーになるコードです。

TextFinderクラスは情報が少ないのか、生成AIも苦手みたいです。

ネット上では

「 欲しいコードが一発で生成された、ChatGPT最高うぇーい！」

みたいな投稿が多いんですが、どうもmirは2，3回やっても欲しいコードが返ってきません。プロンプトが悪いのか、依頼する処理内容がよくないのか。。

とりあえず TextFinder を 使いこなす為には、ある程度は 自分で理解しておくことが重要 だと思いましょう。

3. スピードや処理において getValues / setValues より良いケースがある

検索と置換的な処理を GASで実行する場合、上で生成AIが回答したような getValuesして 配列内で置換処理してからsetValuesというコードでも 出来なくはないんです。

でも、データ量が多かったり、複数シート（またはブック全体 の全シート）のデータを置換したいといった場合は、どうしても処理時間が長くなってしまいます。

一方でTextFinderを使った際は、大量データへの検索と置換やブック全体への処理をする場合でも、短い簡潔なコードである上に処理速度も速い（ことが多い）というメリットがあります。

たとえば、上のBardの回答のコードを、ブック全体を対象にして一致するセルではなく、キーワード（りんご）を含むセル内の文字列を replace処理するコードに修正すると

const ss = SpreadsheetApp.getActiveSpreadsheet();

function replaceAll() {
  const sheets = ss.getSheets();
  
  sheets.forEach(sheet => {
    // セルの値を取得
    var values = sheet.getDataRange().getValues();

    // 置換処理
    for (var i = 0; i < values.length; i++) {
      for (var j = 0; j < values[i].length; j++) {
        // 「りんご」を「ごりら」に置換
        values[i][j] = values[i][j].toString().replace(/りんご/g,"ごりら");
      }
    }

    // セルの値を書き換え
    sheet.getDataRange().setValues(values);
  });
}

こんな感じになります。

これを10シート（検索ヒット100件程度） のスプレッドシートで実行した場合

14.5秒 と結構時間がかかる上に、数式が入っていたセルも setValuesで 全部値化されてしまいました。

3重ループ処理なんで 時間がかかるのは当然ですし、コードにおいても toString() で文字列化や replace処理の際の gフラグなど、初心者には注意点が幾つかあります。

これを 数式セルを考慮したコードに改良すると、さらに複雑化してきますし、処理時間もより長くなりそう・・・。と、結構大変だったりします。

しかし、同じ処理をTextFinder で記述すると

const ss = SpreadsheetApp.getActiveSpreadsheet();

function replaceAll2() {
  ss.createTextFinder("りんご").replaceAllWith("ごりら");
}

処理部分は 1行コードでサクッと書けちゃいます。しかも

速度は1.3秒程度と 圧倒的に早い！ 数式は自動で置換対象外となるんで考慮不要だし、超便利～。

検索して置換するような処理をGASでやる場合は、getValues setValuesではなく TextFinderを使った方が良さそう！ってなりますよね。

ちなみに 関数の実行時間測定が気になる方は、 「いつも隣にITのお仕事」さんの記事を参考にしてください。

数式への考慮以外にも、セル内の一部の文字だけ色がついていたり太字だったりする、リッチテキスト に対しても setValues()だと  完全に破壊してしまいますが、TextFinderだと 最小限の影響で置換ができます。これも 次回、具体例で検証したいと思います。

TextFinderを使うにあたっての基本の理解、そして便利さの実感は出来ましたでしょうか？

でも、この ブック全体（全シート）の "りんご"を"ごりら" に一括置換処理だったら、そもそもGAS使うまでもなく検索と置換で普通にやりゃいいって話です。

というわけで、GASの TextFinderを使うべき処理、3つの特徴の1つ目の 検索と置換 ＋α  （標準機能では対処できない 処理）の具体例を紹介していきましょう！

TextFinderを使って サクっと改行に置換する

検索と置換＋α で、もっともわかりやすい例が、改行への置換です。

標準機能では対応できず、苦肉の策で前回 「改行へ置換する式」に置換するという裏技を紹介しましたが、GAS の TextFinderを使えばサクッと実現できちゃいます。

↑ 前回の方法は無理やり感が強かった。

改行に置換するシンプルなGAS

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();

function replaceLf() {
  const SEARCH_WORD = "、";  //検索する文字
  const LINE_FEED = "\n";　//置換後（改行）
  let textFinder = sheet.createTextFinder(SEARCH_WORD);
  textFinder.replaceAllWith(LINE_FEED);
}

改行置換はGASだと非常にシンプルで、置換後の文字に "\n" を指定するだけ。

上のコードは開いてるアクティブシートを対象として  "、" を改行に置換する処理となっています。

sheet.createTextFinder(SEARCH_WORD)　の箇所を

ss.createTextFinder(SEARCH_WORD)

とすれば、全シート（ブック）を対象とする 一括改行置換へ変更も可能。

とりあえず上のコードを試してみましょう。

サクッと改行に置換できましたね。これが基本系です。

置換した件数を取得する

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();

function replaceLf() {
  const SEARCH_WORD = "、"; //検索する文字
  const LINE_FEED = "\n";　//置換後（改行）
  let textFinder = sheet.createTextFinder(SEARCH_WORD);
  textFinder = textFinder.replaceAllWith(LINE_FEED);
  console.log(textFinder);
}

上のコードを少しだけ修正して replaceAllWith(LINE_FEED) の返り値をログに出力してみましょう。

このように19という数値が返ってきました。これは 置換処理した件数（検索ワードである "、" の数）です。

検索にヒットしたセル数ではないので、その点は注意。

このように検索と置換で「すべて置換」をした際の件数と同じなのがわかりますね。

INPUTBOXで検索ワードを指定して改行に置換する

それではこれをベースに 検索ワードを実行時に指定して改行に置換する、もう少し汎用性の高いコードに修正してみましょう。

検索ワードを指定する 入力ボックスですが、今回は最近ちょっとデザイン変更があった一番シンプルな Browserクラスを使ってみましょう。

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();

function inputSearchWord(){
  const words = Browser.inputBox("改行に置換したい文字を入力");
  console.log(words);
}

実行してみると

こんな感じで見た目が 丸っこくなった inputBoxが表示されます。

以前の表示では右上に ✖ボタンがあったんですが、表示が変わったらなくなっちゃいましたね。

✖ボタンはありませんが、キャンセルしたい場合はダイアログ以外のシートの適当な部分をクリックすれば良いです。

キャンセルした場合は、console.log(words) で出力すると、このように cancel が返っているのがわかります。

入力ボックスに ”、” を入れて OKを押すと

words に 入力した  ”、” が格納されます。

これを 先ほど作成した 改行置換関数の　replaceLf に引数として渡してあげればよいですね。

合わせてcancelだった時は処理を終了するコードを追記しておきましょう。

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();

function inputSearchWord(){
  const words = Browser.inputBox("改行に置換したい文字を入力");
  if(words == "cancel") return; //キャンセルの時は処理終了
  replaceLf(words);
}

function replaceLf(words){
  const SEARCH_WORD = words;
  const LINE_FEED = "\n";
  let textFinder = sheet.createTextFinder(SEARCH_WORD);
  textFinder = textFinder.replaceAllWith(LINE_FEED);
  console.log(textFinder);
}

こんな感じになりました。

せっかくなんでもう少し欲張って、先ほど取得できることがわかった 置換した件数を 処理終了後にmsgBoxで表示させてみましょう。

INPUTBOXで入力したワードを改行に置換して、結果メッセージを表示する

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();

function inputSearchWord(){
  const words = Browser.inputBox("改行に置換したい文字を入力");
  if(words == "cancel") return; //キャンセルの時は処理終了
  const result = replaceLf(words);
  Browser.msgBox(`${words}と一致した${result} 件を 改行に置換しました`);
}

function replaceLf(words){
  const SEARCH_WORD = words;
  const LINE_FEED = "\n";
  let textFinder = sheet.createTextFinder(SEARCH_WORD);
  textFinder = textFinder.replaceAllWith(LINE_FEED);
  return textFinder;
}

完成形はこんな感じ。

処理の流れとしては

なんとなくわかりますよね？

テンプレートリテラルを使った結果表示の

Browser.msgBox(`${words}と一致した${result} 件を 改行に置換しました`)

↑ この部分は

Browser.msgBox( words + "と一致した" +result + "件を 改行に置換しました")

↑ このように + で文字列と変数を結合する記述でもよいです。

実際動かしてみましょう。

いい感じになりましたね。

最初の実行をカスタムメニューから実行できるようにする

ただ、これだとまだ スクリプトエディタから inputSearchWord を実行する必要があります。

ユーザーにエディタを触らせるのはちょっと嫌だなって思いますよね。

ボタンに関数をセットや、マクロにインポートしてショートカットで実行とする方法でもよいんですが、今回はカスタムメニューを追加して、そこからGASを実行する方法でいきましょう。

カスタムメニューの作成は Uiクラスを使います。

メニュー追加については、GASを使ったスピンボタンの回で触れてるんで、説明は割愛します。

というわけでメニュー追加も含めた 完全版コードは、

const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();

//起動時にカスタムメニュー追加
function onOpen() {
  const ui = SpreadsheetApp.getUi();         
  const menu = ui.createMenu('検索と置換＋α') // メニューを作成
    .addItem('改行置換', 'inputSearchWord')   // メニューにアイテムを追加
    .addToUi();
}

//ワード入力と結果表示
function inputSearchWord(){
  const words = Browser.inputBox("改行に置換したい文字を入力");
  if(words == "cancel") return; //キャンセルの時は処理終了
  const result = replaceLf(words);
  Browser.msgBox(`${words}と一致した${result} 件を 改行に置換しました`);
}

//改行への置換処理
function replaceLf(words){
  const SEARCH_WORD = words;
  const LINE_FEED = "\n";
  let textFinder = sheet.createTextFinder(SEARCH_WORD);
  textFinder = textFinder.replaceAllWith(LINE_FEED);
  return textFinder;
}

このようになりました。

動かしてみましょう。

メニューの「検索と置換＋α」から「改行置換」を選択することで、inputSearchWord が実行され、 指定した文字を改行に置換することができました～。

改行置換機能、完成です。

次回は GAS TextFinderを使った他の応用処理に挑戦

今回はこの「改行」への置換処理 までとします。（なんか短くない？って思ったかもしれませんが、ちょうどキリがいいからで、決して3連休に予定があるから執筆時間があまりとれないとかではないですｗ）

改行への置換コードを通じて、TextFinderで一括置換する基本の型は理解できたんじゃないでしょうか。下手に二次元配列を配列メソッドや二重ループで処理するよりも、だいぶわかりやすいコードだと思いませんか？

知ってるとちょっとお得な TextFinder。

次回は、TextFinderを使った他の応用例を一気出しして「検索と置換」シリーズを終了としたいと思います。