photo credit: giloudim #Printemps via photopin (license)
みなさん、こんにちは！
タカハシ(@ntakahashi0505)です。
Google Apps Scriptでスプレッドシートを操作しているときに、こんなことはありませんか？
スプレッドシート内に特定の値が存在しているかどうかを判定したい、または取り出したい。
けっこうありますよね。
- 該当の値が存在していればそのレコードを上書き、さもなくばレコードを最終行の下に追加、みたいな処理はよくやりたくなります。
- 該当の値が存在しているレコードの別の列の値を取り出したい、これはいわゆるVLOOKUP関数のような動きですね。よくやりたいです。
使用頻度が高いので、今回はそれを汎用的に使える関数として作っていきたいと思います。
Google Apps Scriptでスプレッドシートを検索して特定のデータがあるかどうかを判定する関数です。
ちなみに、本記事はGoogleスプレッドシート上に勤怠管理集計システムを作るシリーズの連載記事でもありまして、前回の記事はコチラ。
ではよろしくお願いします。
後日追記：zip.applyメソッドとindexOfメソッドによる配列操作で検索する方法
本記事に書いてある方法は、Underscore for GASライブラリのzip.applyメソッドによる行列転置と、indexOfメソッドを使うことで、高速かつより簡潔に記述することができます。
ご興味があれば、こちらもご覧ください。
スプレッドシートから特定の値を検索する関数
引数が何になるか考える
スプレッドシートの検索に必要なのは
- シート自体
- 検索する値
- 検索対象の列
ですよね。シート全体の検索なら列の指定は不要ですが、今回は特定の列を検索対象にするという仕様にします。
以上、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を叩く回数が多いと処理が遅くなってしまいます。
従って、いったん検索対象とするシートを配列に格納して、配列に対して検索をするほうが格段に高速になります。
高速化したアルゴリズム
それを踏まえてアルゴリズムを考えると
- シートの対象範囲を二次元配列に格納
- 配列の最初から最後まで行方向に走査
- [i][col-1]が与えられた値と等しければ
- その行数(i+1)を返す
- さもなくば
- 0を返す
- [i][col-1]が与えられた値と等しければ
という処理にするのが望ましいということになります。
ここで注意点はスプレッドシートの行数列数は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でスプレッドシートを検索して特定のデータがあるかどうかを判定する関数を作成しました。
汎用的なので、けっこうアチコチで活躍してくれると思います。
このようによく使う関数やフレーズは、ストックをしておくと良いです。未来の自分がすごく助かります。
次回ですが、この関数を活用して、勤怠システムのデータ受取処理の続きを作っていきます。
どうぞお楽しみに！
