Google Apps Scriptでスプレッドシート内を検索して行番号を返す関数(高速版)


loupe

photo credit: giloudim #Printemps via photopin (license)

みなさん、こんにちは!
タカハシ(@ntakahashi0505)です。

Google Apps Scriptでスプレッドシートを操作しているときに、こんなことはありませんか?

スプレッドシート内に特定の値が存在しているかどうかを判定したい、または取り出したい。

けっこうありますよね。

  • 該当の値が存在していればそのレコードを上書き、さもなくばレコードを最終行の下に追加、みたいな処理はよくやりたくなります。
  • 該当の値が存在しているレコードの別の列の値を取り出したい、これはいわゆるVLOOKUP関数のような動きですね。よくやりたいです。

使用頻度が高いので、今回はそれを汎用的に使える関数として作っていきたいと思います。

Google Apps Scriptでスプレッドシートを検索して特定のデータがあるかどうかを判定する関数です。

ちなみに、本記事はGoogleスプレッドシート上に勤怠管理集計システムを作るシリーズの連載記事でもありまして、前回の記事はコチラ。

Google Apps Scriptでチャットワークからの打刻情報をシートの最終行に追加する方法
Googleスプレッドシート上に勤怠管理集計システムを作っています。今回はGoogle Apps Scriptでチャットワークからの打刻情報をappendRowでシートの最終行に追加する方法です。

ではよろしくお願いします。

後日追記:zip.applyメソッドとindexOfメソッドによる配列操作で検索する方法

本記事に書いてある方法は、Underscore for GASライブラリのzip.applyメソッドによる行列転置と、indexOfメソッドを使うことで、高速かつより簡潔に記述することができます。

ご興味があれば、こちらもご覧ください。

Google Apps Scriptで二次元配列の行と列を入れ替える方法とその革命的な効果
Google Apps Scriptでスプレッドシートのデータは二次元配列として操作できますが、列方向に検索したいときなどはUnderscoreのzipメソッドで行と列を入れ替えると革命的に便利です。
スポンサーリンク

スプレッドシートから特定の値を検索する関数

引数が何になるか考える

スプレッドシートの検索に必要なのは

  • シート自体
  • 検索する値
  • 検索対象の列

ですよね。シート全体の検索なら列の指定は不要ですが、今回は特定の列を検索対象にするという仕様にします。

以上、3つが引数になりますから

function findRow(sheet,val,col){
     //処理      
}

ということになります。

戻り値を決める

検索結果の行、列、もしくは両方、またはVLOOKUPのように検索したレコードのいずれかの値、などと色々とパターンが考えられますが、今回の戻り値は値を見つけた行数としたいと思います。

見つからなかった場合は0を返すという形です。

なお、この仕様だと0を戻した先でgetRangeなどに利用しようとすると当然ながらエラーとなりますので、エラー処理が必要になりますね。

アルゴリズムを考える

アルゴリズムとしては

  • シートの最初の行から最終行まで走査
    • 指定の列の値が検索する値と等しければ
      • その行数を返す
    • さもなくば
      • 0を返す

という処理をしれば良さそうですね。

これでスクリプトを組んでいくと

function findRow2(sheet,val,col){

  var lastRow=sheet.getDataRange().getLastRow(); //対象となるシートの最終行を取得

  for(var i=1;i<=lastRow;i++){
    if(sheet.getRange(i,col).getValue() === val){
      return i;
    }
  }
  return 0;
}

となります。

スプレッドシートの検索関数を高速化

これでも動くは動くのですが、for文の中にAPI呼び出しのgetRangeやgetValueが入ってしまっているのが気になりますね。

というのも、Google Apps Scriptの場合APIを叩く回数が多いと処理が遅くなってしまいます。

Google Apps Scriptのスプレッドシート読み書きを格段に高速化をする方法
Google Apps Scriptでスプレッドシートの操作をしていて実行速度が遅い!と感じたことがあると思います。今回はスプレッドシートを操作する場合に処理速度を格段に速くする方法をお伝えします。

従って、いったん検索対象とするシートを配列に格納して、配列に対して検索をするほうが格段に高速になります。

高速化したアルゴリズム

それを踏まえてアルゴリズムを考えると

  • シートの対象範囲を二次元配列に格納
  • 配列の最初から最後まで行方向に走査
    • [i][col-1]が与えられた値と等しければ
      • その行数(i+1)を返す
    • さもなくば
      • 0を返す

という処理にするのが望ましいということになります。

ここで注意点はスプレッドシートの行数列数は1からはじまる、配列番号は0からはじまる、というのを気を付けて下さいね。

これをスクリプトにすると

function findRow(sheet,val,col){

  var dat = sheet.getDataRange().getValues(); //受け取ったシートのデータを二次元配列に取得

  for(var i=1;i<dat.length;i++){
    if(dat[i][col-1] === val){
      return i+1;
    }
  }
  return 0;
}

となります。

for文の中にAPIがないので、気持ちが良いです。

まとめ

Google Apps Scriptでスプレッドシートを検索して特定のデータがあるかどうかを判定する関数を作成しました。

汎用的なので、けっこうアチコチで活躍してくれると思います。

このようによく使う関数やフレーズは、ストックをしておくと良いです。未来の自分がすごく助かります。

次回ですが、この関数を活用して、勤怠システムのデータ受取処理の続きを作っていきます。

Google Apps Scriptでチャットワークからの出勤・退勤の打刻データを様々な条件分岐を駆使して処理する
Googleスプレッドシート上に勤怠管理集計システムを作るのを目標に連載をしています。今回は、新たなデータを受け取ったときの処理の分岐について組み立てつつ、出勤・退勤打刻データの入力処理を一気に作っていきます。

どうぞお楽しみに!

連載目次:チャットワークのメッセージ送信で出勤・退勤の打刻をする

本シリーズはチャットワークの特定のグループチャットで出勤や退勤の打刻が可能になる勤怠システムをGoogle Apps Scriptとスプレッドシートを用いて作っていきます。これからはチャットというインターフェースがトレンドになってきますので、良い練習になると思いますよ!
  1. Google Apps Scriptでチャットワークでのチャット打刻システムを作る
  2. Google Apps ScriptでのDateオブジェクトの各生成方法、日付と時刻を分ける
  3. Google Apps Scriptでチャットワークからの打刻情報をシートの最終行に追加する方法
  4. Google Apps Scriptでスプレッドシート内を検索して行番号を返す関数(高速版)
  5. Google Apps Scriptでチャットワークからの出勤・退勤の打刻データを様々な条件分岐を駆使して処理する

タイトルとURLをコピーしました