【GASの始め方】TextFinderで行と列を特定しよう

みなさんこんにちは！GASおじさんです。

「GASでスプレッドシートを自由自在に操るためのスキル習得講座」の第10回です。

前回の記事はこちら。

前回はfor文とif文でデータ抽出して配列を生成する方法について解説しました。

今回はTextFinderを使って「行」や「列」を特定する方法について解説していきます。

応用問題レベル4

解説にあたって応用問題レベル4を用意しました。

まずは以下のスプレッドシート「GASをはじめよう！応用問題レベル4」を開いてコピーを作成してください。

GASをはじめよう！応用問題レベル4（コピー用）

レベル4では集計シートのC列に「平均」という項目が追加されました。

A社〜E社の各シートを確認すると、最終行に「平均」が追加されており、「合計」は最後から2番目の行となっています。

ということで問題です。

売上合計額と売上平均額を集計シートにまとめるGASプログラムを作成してください。

前回作成したコードをカスタマイズ

前回作成したコード

まずは前回のレベル3で作成したコードを確認しましょう。

function setTotalSales() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('集計');
  const companies = getCompanies();
  const values = [];
  for(const company of companies){
    values.push([company, getTotalSales(company)]);
  }
  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const companies = [];
  for(const sheet of sheets){
    const sheetName = sheet.getName();
    if(sheetName != '集計'){
      companies.push(sheetName);
    }
  }
  return companies;
}

function getTotalSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  return sheet.getRange(lastRow, 2).getValue();
}

こちらは合計売上のみを集計するプログラムとなっていますね。これをカスタマイズして、合計売上に加えて、平均売上も集計するプログラムに書き換えていきましょう。

合計売上を取得する関数を修正

まず、合計売上を取得するgetTotalSales関数に関して、現状はA社〜E社の各シートの最終行の値を取得しています。

しかし、今回レベル4では最終行に「平均」が追加されたため、「合計」は最後から2番目の行にあります。

ということでまずはgetTotalSales関数を以下のように修正しましょう。

function getTotalSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  return sheet.getRange(lastRow-1, 2).getValue();
}

getRangeの「行」をlastRowからlastRow-1に変更しました。こうすることで、最後から2番目の行、つまり合計売上を取得できますね。

平均売上を取得する関数を作成

次に、getTotalSales関数をコピーして、平均売上を取得する関数を作成しましょう。

平均売上を取得するので、関数名はgetAverageSalesとしましょうか。

function getAverageSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  return sheet.getRange(lastRow, 2).getValue();
}

こちらは最終行を取得すればいいので、getRangeの行はlastRowとしましょう。

getAverageSales関数を作成したら、setTotalSales関数の中で呼び出しましょう。

function setTotalSales() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('集計');
  const companies = getCompanies();
  const values = [];
  for(const company of companies){
    values.push([company, getTotalSales(company), getAverageSales(company)]);
  }
  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

7行目の二次元配列を生成する部分で呼び出しています。

こうすれば、

集計シートのA列に会社名(company)
集計シートのB列に合計売上(getTotalSales(company))
集計シートのC列に平均売上(getAverageSales(company))

をsetValuesできますね。

適切な関数名を考える

なお、この場合、合計売上に加えて平均売上も集計することになるので、「setTotalSales」という関数名は考え直した方がいいでしょう。

さて、どのような関数名にするのが適切だと思いますか？

そうですね、合計売上と平均売上を入力するので、「setTotalAndAverageSales」などとしたくなりますが、これはちょっと安直すぎますね。

もしかすると、今後もまた「合計」「平均」以外の項目が増えて、「最大売上」とか「最小売上」を集計する、みたいなことになるかもしれません。そうなったときはまた関数名を考え直さなければいけません。

関数名の命名は、わかりやすくするために具体性を持たせることも大事ですが、具体的すぎると他の処理を含める余裕がなくなってしまうので、ちょうどいい抽象度にすることも大切です。

ここは「売上情報を入力する」というふうに抽象化して、「setSalesInfo」などとするのがいいのではないでしょうか。

あるいは「売上データを入力する」で「setSalesData」などでもいいでしょうか。

いや、そもそも今やっていることは「会社の売上を集計する」ということをやっているので、「aggregateCompanySales」とか「summarizeCompanySales」とするのがいいかもしれません。

このように、適切な関数名（あるいは変数名）を考えることは、プログラミングにおいてはとても大事なプロセスです。

適切な命名は、コードの保守性や可読性を高めることにつながるので、「本当にこの名前でいいのか？」と考え続ける癖をつけましょう。

今回は「aggregateCompanySales」を採用しますね。

function aggregateCompanySales() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('集計');
  const companies = getCompanies();
  const values = [];
  for(const company of companies){
    values.push([company, getTotalSales(company), getAverageSales(company)]);
  }
  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const companies = [];
  for(const sheet of sheets){
    const sheetName = sheet.getName();
    if(sheetName != '集計'){
      companies.push(sheetName);
    }
  }
  return companies;
}

function getTotalSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  return sheet.getRange(lastRow-1, 2).getValue();
}

function getAverageSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  return sheet.getRange(lastRow, 2).getValue();
}

では、いざこちらのコードを実行してみます。

集計シートを確認すると…

無事、合計売上と平均売上を集計することができました！

現状の課題

さて、無事集計することはできましたが、現状のコードは、とある問題を抱えています。

具体的には以下の2つの関数の「lastRow」に関する問題です。

function getTotalSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  return sheet.getRange(lastRow-1, 2).getValue();
}

function getAverageSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const lastRow = sheet.getLastRow();
  return sheet.getRange(lastRow, 2).getValue();
}

getTotalSales関数は最後から2番目の行lastRow-1の値を取得する
getAverageSales関数は最終行lastRowの値を取得する

という内容になっていますが、もしまた最終行に以下のような項目が追加されたらどうなるでしょうか？

最終行に「最大」と「最小」が追加されました。

こうなると、また項目の位置がずれてしまうので、

合計: lastRow - 3
平均: lastRow - 2
最大: lastRow - 1
最小: lastRow

というように、取得する範囲を定義しなおさなければなりません。

毎度このように定義し直すのは大変ですよね。

そこで、TextFinderの出番です。

TextFinderで行と列を特定する方法

TextFinderとは

TextFinderとは、ズバリこれのことです。

Windowsの場合はCtrl + F、Macの場合はCommand + Fで出てくるこの検索窓のことです（ちなみにFはFindのF）。

この検索窓に「合計」と打ち込むと、「合計」と入力されたセルを検索してくれますよね。

探したいテキストがどこにあるのかを教えてくれる、非常に便利な機能なので、知ってる人も多いと思います。

これを使って、「合計」や「平均」の行がどこなのかを特定していくプログラムを書いていきます！

createTextFinderメソッド

それではまず、TextFinderを生成するためのメソッド、createTextFinderについて説明していきます。

以下はcreateTextFinderの使用例です。

function textFind() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('A社');
  const finder = sheet.createTextFinder('合計');
  const targetRow = finder.findNext().getRow();
  console.log(targetRow);
}

こちらは、「A社」シートの中から、「合計」という文字がどこにあるのかを探し、そのセルの行数をログ出力する関数となっています。

こちらを実際に実行してみると、targetRowは「5」と返ってきます。

これは、A社シートの中の「合計」が現在A5セルにあるため、このような結果となります。

このようにcreateTextFinderメソッドを使うことで、TextFinderオブジェクトを生成し、それに対してfindNext().getRow()とするとそのセルの行数を取得することができるのです。

とても便利ですね。

しかし、これを使う際は注意が必要です。

注意点

もし以下のように、C1セルに「合計」と入力されている場合、どうなるでしょうか。

この状態でもう一度プログラムを実行すると、今度は以下のような結果となります。

targetRowが「1」になってしまいました。

これは、スプレッドシート上で検索してみたらわかるように、「合計」と検索したときにヒットする最初のセルがC1セルだからですね。

2件目にヒットするセルを取得するには、TextFinderオブジェクトfinderに対して、再度findNext()しなければなりません。

function textFind() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('A社');
  const finder = sheet.createTextFinder('合計');
  const targetRow1 = finder.findNext().getRow();
  const targetRow2 = finder.findNext().getRow();
  console.log(targetRow1);
  console.log(targetRow2);
}

この場合2回目のfindNextで欲しい行を特定できていますが、もしまたどこか他のセルに「合計」という文字が増えてしまったら困りますよね。

検索範囲を絞る

そこで、検索範囲を絞ってプログラムを実行していきましょう。

コードを以下のように書き換えてみます。

function textFind() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('A社');
  const finder = sheet.getRange('A:A').createTextFinder('合計');
  const targetRow = finder.findNext().getRow();
  console.log(targetRow);
}

4行目を書き換えました。sheetの後に.getRange('A:A')を追加しています。

こうすることで、「A列の中から“合計”という文字を探してね」という指示を与えることができます。

これで再度実行すると、無事「5」という数字が返ってきました。

ちなみに、この「検索範囲を絞る」というオプションは、スプレッドシート上で検索するときも使うことができますね。

検索窓の右側にある「︙」をクリックすると、

以下の「検索と置換」ウィンドウが表示されます。

この画面で検索範囲を「特定の範囲」にして、'A社'!A:Aとして検索すると、A社シートのA列の中から「合計」というテキストを探してくれますね。

この動きをGASで実装したというイメージですね。

完全一致検索でもっと安全にする

検索範囲を絞ることで少し安全になりましたが、まだまだ油断はできません。

もし、A列が以下のようになってたらどうでしょうか？

「合計」の1個上に「先月合計」という項目が追加されました（ちょっとありえないシチュエーションですが…）。

この場合、A列に検索範囲を絞ったとしても、「先月合計」の方が先に検索にヒットしてしまうため、欲しい行数を取得することができません。

そこで、「完全一致検索」をします。

function textFind() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('A社');
  const finder = sheet.getRange('A:A').createTextFinder('合計').matchEntireCell(true);
  const targetRow = finder.findNext().getRow();
  console.log(targetRow);
}

4行目のcreateTextFinderメソッドの後に、.matchEntireCell(true)を加えました。

こうすることで完全一致検索ができるので、プログラムを実行すると欲しい値が返ってきます。

ちなみにこれもスプレッドシート上の検索オプションにありますね。

この動きをGASで実装したというわけです。

列も特定できる

さて、これで「行」を特定できました。

せっかくなので、ついでに「列」を特定する方法も説明しておきます。

先ほどは「A列の中から”合計”というテキストを探す」という動きを実装しましたが、

続けて「1行目の中から”売上”というテキストを探す」という動きも実装してみます。

function textFind() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('A社');
  const finder = sheet.getRange('A:A').createTextFinder('合計').matchEntireCell(true);
  const targetRow = finder.findNext().getRow();
  const finder2 = sheet.getRange('1:1').createTextFinder('売上').matchEntireCell(true);
  const targetCol = finder2.findNext().getColumn();
  console.log(targetRow);
  console.log(targetCol);
}

6〜7行目で列を特定する動きを実装しました。

まず6行目、getRangeの範囲を('1:1')とすることで「1行目の中から探す」というふうに検索範囲を絞ってますね。

また、検索する文字列は「売上」なので、createTextFinderの引数を'売上'としています。

7行目はgetRow()の代わりに、getColumn()としています。こうすることで、列数を特定できます。

プログラムを実行してみると、targetColは「2」となり、「売上」という文字がある「列」を特定することができました。

1行で書いてもいい

なお、説明のために、TextFinderオブジェクトをfinderやfinder2という変数に格納しておりましたが、実際は以下のように1行で書いても構いません。

function textFind() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('A社');
  const targetRow = sheet.getRange('A:A').createTextFinder('合計').matchEntireCell(true).findNext().getRow();
  const targetCol = sheet.getRange('1:1').createTextFinder('売上').matchEntireCell(true).findNext().getColumn();
  console.log(targetRow);
  console.log(targetCol);
}

1行で書いてもいいですが、横に長くなりすぎるのもあまり良くないので、以下のように途中で改行するのもいいでしょう。

function textFind() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('A社');
  const targetRow = sheet.getRange('A:A').createTextFinder('合計')
                    .matchEntireCell(true).findNext().getRow();
  const targetCol = sheet.getRange('1:1').createTextFinder('売上')
                    .matchEntireCell(true).findNext().getColumn();
  console.log(targetRow);
  console.log(targetCol);
}

こうすると読みやすいですね。

いざ実装

行を特定する

ではここまでを踏まえて、いざ実装してみたいと思います。

問題のgetTotalSales関数とgetAverageSales関数を書き換えていきます。

function getTotalSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const targetRow = sheet.getRange('A:A').createTextFinder('合計')
                    .matchEntireCell(true).findNext().getRow();
  return sheet.getRange(targetRow, 2).getValue();
}

function getAverageSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const targetRow = sheet.getRange('A:A').createTextFinder('平均')
                    .matchEntireCell(true).findNext().getRow();
  return sheet.getRange(targetRow, 2).getValue();
}

それぞれの関数の中でtargetRowを定義しました。

「合計」や「平均」というテキストをA列から探して、標的となる「行(row)」を特定しています。

これにより、最終行への項目の追加に左右されなくなったため、より汎用性の高い、安全なコードにすることができました。

列を特定する

また、getRange(targetRow, 2)ということで、列が現在「2」となっていますが、これも変数化したほうがいいですね。

もしかしたら、以下のように売上の列が変わることもあるかもしれませんからね。

ちなみに、(targetRow, 2)の「2」のように、コード内に直接書かれた具体的な数値のことを「マジックナンバー」といいます。マジックナンバーはできるだけ変数化するのがプログラミングにおける定石です。

ということで以下のように書き換えていきましょう。

function getTotalSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const targetRow = sheet.getRange('A:A').createTextFinder('合計')
                    .matchEntireCell(true).findNext().getRow();
  const targetCol = sheet.getRange('1:1').createTextFinder('売上')
                    .matchEntireCell(true).findNext().getColumn();
  return sheet.getRange(targetRow, targetCol).getValue();
}

function getAverageSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const targetRow = sheet.getRange('A:A').createTextFinder('平均')
                    .matchEntireCell(true).findNext().getRow();
  const targetCol = sheet.getRange('1:1').createTextFinder('売上')
                    .matchEntireCell(true).findNext().getColumn();
  return sheet.getRange(targetRow, targetCol).getValue();
}

それぞれの関数の中でtargetColを定義しました。

「売上」というテキストを1行目の中から探して、標的となる「列(column)」を特定しています。

これで、マジックナンバーを変数化することができました！

いざ実行

それでは、修正後の全体のコードを確認します。

function aggregateCompanySales() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName('集計');
  const companies = getCompanies();
  const values = [];
  for(const company of companies){
    values.push([company, getTotalSales(company), getAverageSales(company)]);
  }
  sheet.getRange(2, 1, values.length, values[0].length).setValues(values);
}

function getCompanies() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheets = ss.getSheets();
  const companies = [];
  for(const sheet of sheets){
    const sheetName = sheet.getName();
    if(sheetName != '集計'){
      companies.push(sheetName);
    }
  }
  return companies;
}

function getTotalSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const targetRow = sheet.getRange('A:A').createTextFinder('合計')
                    .matchEntireCell(true).findNext().getRow();
  const targetCol = sheet.getRange('1:1').createTextFinder('売上')
                    .matchEntireCell(true).findNext().getColumn();
  return sheet.getRange(targetRow, targetCol).getValue();
}

function getAverageSales(sheetName) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);
  const targetRow = sheet.getRange('A:A').createTextFinder('平均')
                    .matchEntireCell(true).findNext().getRow();
  const targetCol = sheet.getRange('1:1').createTextFinder('売上')
                    .matchEntireCell(true).findNext().getColumn();
  return sheet.getRange(targetRow, targetCol).getValue();
}

aggregateCompanySalesを実行して、スプレッドシートを確認すると…

無事、集計することができました！

まとめ

以上、TextFinderで行と列を特定する方法について解説しました。

スプレッドシートを使っていれば、行や列の位置が変わるということは頻繁に起きる事象だと思います。

そのようなときでも柔軟に対応できるコードを書くために、今回の内容をしっかり習得しておきましょう。

「マジックナンバーはなるべく回避して変数化する」というプログラミングの基本に則り、より汎用性の高いプログラム作成を目指していきましょう。

次回はこれまで何気なく使っていた「オブジェクト」と「メソッド」について解説していきます！