※本記事はプロモーションを含みます
目次
Google Apps Script（GAS）でスプレッドシートのデータを操作する際、よく発生する処理の一つが、特定のキーとなる値がどの行に存在するかを調べる作業です。顧客IDや注文番号、メールアドレスなどをキーにして該当する行を特定し、その行の別セルを更新したりステータスを書き換える実装は日常的に記述します。ただし実装のやり方によっては処理時間が大幅に変わり、なので最悪の場合は実行時間上限エラーで落とされる原因にもなります。この記事では、スプレッドシートで効率的に値がある行番号を探すコードの実装パターンと、開発でよく直面する行数のズレや型不一致の回避策について整理します。
1行ずつgetValueを呼ぶループ処理から配列の一括検索へ切り替える
GASで値を検索して行番号を特定しようとする際、初心者が書きがちなのが、1行目から順番にgetValue()を呼び出して値を確認するforループです。たとえば1000行あるデータに対して1行ずつgetValue()を判定すると、Googleのサーバーとスプレッドシートの間で1000回の通信が発生します。GASにおけるSpreadsheetAppサービスの呼び出しはオーバーヘッドが大きいので、ループ内でセル単位の操作を行うと、数千行のデータで簡単に数十秒から数分の実行時間がかかってしまいます。
これに対し、getRange().getValues() を使って目的の列全体を一度に二次元配列として取得し、メモリ上で検索を行う手法に切りげると、通信回数はわずか1回で済みます。なので処理時間は数百ミリ秒程度まで劇的に短縮されます。スプレッドシートで値がある行番号を探す処理を書く場合は、範囲を一括で配列化してからJavaScriptの標準メソッドで探すアプローチを採用しましょう。
以下は、指定したシート、列番号、検索したい値を渡すことで、最初に合致したスプレッドシート上の行番号を返す実用的な関数です。検索処理においてGASの値の検索で行番号を取得したい場面で、そのまま利用できます。
function findRow(sheet, col, value) {
const values = sheet.getRange(2, col, sheet.getLastRow() - 1, 1).getValues().flat();
const idx = values.indexOf(value);
return idx === -1 ? null : idx + 2;
}この関数は、1行目にヘッダー項目が存在し、2行目以降にデータが入っている一般的なテーブル構造を前提としています。この関数を使うタイミングというのは、フォームからの自動入力結果に対して既存レコードの更新を行う際や、IDをキーにしてステータス列を書き換える事前準備として、対象行を確定させたいときです。
ただしこのコードを使う際には、取得されるインデックスと実際のシート行番号がずれる点に強い注意が必要です。配列のインデックス（idx）は0から始まりますが、getRangeで指定しているのは2行目からの範囲です。なので配列の0番目の要素はシートの2行目に相当します。ここで計算式を誤ると、目的の行の1行上や1行下を書き換えてしまう重大なバグにつながります。
このズレを回避するためには、コード内にある「+ 2」というオフセット計算の根拠を正確に理解しておく必要があります。開始行が2行目の場合、インデックス0に2を足すことでシート行番号の「2」と一致します。ただし、もし3行目からデータが始まるシート構造であれば「+ 3」に変更しなければなりません。なので開始行とオフセットの加算数を、常にセットで管理することが重要です。
1次元配列への平坦化とインデックス計算で実シートの行番号を特定する
上記で紹介したコードの内部で何が起きているのかを、詳しく紐解きます。getValues() メソッドが返す値は、常に2次元配列という構造をとります。というのは、たとえ1列分しか範囲を指定していなくても、GASの仕様上、行と列を表現するためのネストされた配列として取得されるからです。
JavaScript標準の indexOf() メソッドは、1次元の配列に対して要素を比較し検索する関数です。なので2次元配列のまま indexOf(value) を実行しても、内部の配列オブジェクト自体と引数が比較されてしまい、常に -1（見つからない）が返されてしまいます。そこで利用しているのが flat() メソッドです。flat() を適用することで、2次元配列という構造が1次元配列に変換（平坦化）され、indexOf() による一発検索が可能になります。
検索の結果、一致する値が存在した場合は 0 以上の整数が idx に代入されます。ただし値が見つからなかった場合は -1 が返されるので、三項演算子を使って見つからない場合は null を返し、見つかった場合は「idx + 2」として実際のシート行番号に変換して戻り値としています。
ここで実装上のハマりポイントとなるのが、データが1行も存在しない場合や、ヘッダー行しか存在しない場合の挙動です。sheet.getLastRow() が 1 のとき（＝データ行が存在せずヘッダーのみの時）、sheet.getLastRow() – 1 の計算結果は 0 になります。getRange(2, col, 0, 1) という範囲指定は、取得する行数が0行となるので、GASのエラー（例外）を発生させてプログラムが停止します。
実務で運用するスクリプトでは、このような例外で処理が落ちないよう、関数の冒頭でデータの有無をチェックするガード節を挟むのが堅牢な設計です。たとえば以下のように、データ領域が存在するかを事前判定するロジックを付け加えます。
getLastRow() が 2 未満の場合は検索するまでもなくデータ行が存在しないので、直ちに null を返すように構成します。これにより、シートが空の状態でスクリプトを実行してもエラーで停止することなく、呼び出し側で対象データなしとして安全に処理を継続できます。
型の一致や空セルで意図通りに行番号が取れない現象の回避策
コードのロジックが正しくても、実際のスプレッドシート運用において、確実に存在するはずの値なのに indexOf が -1 を返してしまう不具合がよく発生します。というのは、この原因の多くがスプレッドシートのセルの表示形式と、JavaScriptにおける型の一致（厳密等価比較）の問題に起因しているからです。
indexOf() メソッド内部では、比較演算子の === （厳密等価比較）が使用されます。というのは、これが値だけでなくデータ型も一致しているかを判定するものだからです。スプレッドシート側で数値として入力されている「1001」と、GAS側で文字列として扱っている「”1001″」を比較した場合、見た目は同じであっても型が異なるので不一致と判定されます。APIから受け取ったIDが文字列型で、シート上のセルが自動判別で数値型になっている場合によくこの不具合が起きます。
これを回避するためには、検索を行う前に型を統一するか、indexOf の代わりに findIndex() メソッドを使用して型変換を行いながら判定する手法をとります。たとえば以下のように記述することで、数値と文字列の違いを許容して検索することが可能です。
values.findIndex(val => String(val) === String(value))
こうすることで、両者を一時的に文字列型に揃えてから比較するので、シート側の表示形式が数値であれ文字列であれ、意図した通りの行番号を取得できます。
ただし、日付（Dateオブジェクト）を検索対象とする場合も注意が必要です。スプレッドシート内のセルが日付形式の場合、getValues() で取得される値はJavaScriptの Date オブジェクトになります。Date オブジェクト同士を === や indexOf() で比較しても、参照が異なるので常に false になります。日付で行を探したい場合は、Utilities.formatDate() を用いて一度特定のフォーマット（例: “YYYY/MM/dd”）の文字列に変換してから比較を行うか、getTime() メソッドでミリ秒の数値に変換して比較しましょう。
さらに、検索対象の範囲内に空セルが含まれている場合、getValues() は空文字 “” を返します。検索したい値自体が null や undefined、空文字である場合、意図しない空行の行番号を取得してしまうリスクがあるので、呼び出し側で検索値が空でないことを確認しましょう。
大量データ検索で実行速度を落とさないための実践的なチューニング
データ件数が数千行から数万行に及ぶ大規模なスプレッドシートで、この「値から行番号を探す」処理をループ内で何度も呼び出す場合、パフォーマンス上の新たな課題が浮上します。たとえば、別システムからエクスポートした1000件の更新リストがあり、それぞれの件数について findRow() を1000回呼び出すようなケースです。
findRow() 自体は高速ですが、1回呼び出すごとに getRange().getValues() を実行しているので、1000回の呼び出しが発生すると合計1000回のAPI通信が行われます。いくらメモリ上での検索が高速であっても、スプレッドシートからのデータ取得が1000回発生すれば、全体の処理時間は再び数十秒以上に増大し、GASの6分制限（一律実行時間制限）に抵触します。
このようなケースでの回避策は、シート全体のデータを最初に1回だけ取得し、メモリ上で連想配列（Mapオブジェクト）のキャッシュ構造を作るアプローチです。
具体的には、処理の開始時に目的の列をすべて取得し、キー（検索したい値）と値（行番号）のペアを JavaScript の Map オブジェクトに保存しておきます。Map への登録時に「行番号 = 配列インデックス + 開始行」として計算して格納しておけば、その後の1000回の検索はすべて Map.get(key) を呼び出すだけで完了します。Map の参照コストは O(1) なので、1000回の行番号特定処理が数ミリ秒で完了するようになります。
また、別の選択肢として、GAS固有の機能である TextFinder API（sheet.createTextFinder()）を活用するツール的な手法もあります。TextFinder はスプレッドシートの標準検索機能（Ctrl+F）をプログラムから利用するようなイメージで動作し、getValues() で巨大な配列をメモリに読み込むことなく、Googleのサーバー側で直接検索を行わせることができます。
特定の1件だけをピンポイントで検索したい場合や、ワイルドカード検索・大文字小文字を区別しない曖昧検索を行いたい場合は TextFinder が適しています。ただし、同じ列に対して何度も連続して検索を行う場合や、取得したデータに対して細かな型変換やビジネスロジックを挟みたい場合は、今回解説した getValues() + flat() + indexOf/findIndex を使ったメモリ内処理の方が柔軟であり、コードの可読性も高く保てます。
業務自動化の現場では、単に動くコードを書くだけではなく、データ件数が増えたときに耐えられるかや、表示形式が変わったときに壊れないかという視点を持つことが重要です。まずは基礎となる行番号の計算ロジックとオフセットの扱いをマスターし、用途に応じて適切なキャッシュ化や型変換のガードを組み込んで行きましょう。
