0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@YushiKato

スプレッドシートのQUERYでGASタイムアウトを秒で解決する：マスターデータ爆速検索の実践

0
Posted at

TL;DR

  • 大量行の全文検索をGASでループせず、Google SheetsのQUERY関数に任せるだけで秒レベルに高速化できる。
  • 実装は「一時シートにQUERYを流して結果上位だけ読む」。Apps Script側はlimitや列範囲を絞ってI/O最小化。
  • 改善余地があれば、正規化用の隠し列（例: A_norm）を追加して一致率と安定性を高める。

背景と課題

  • Web アプリ（doGet/doPost）は約30秒の応答制限、GASの単回実行も6分制限。
  • マスターデータが数万〜数十万行だと、getValues()で全件取得→ループでフィルタでは時間もメモリも厳しい。
  • クエリロジックそのものは単純な部分一致でも、ボトルネックは「取得と走査」。ここを除去するのが本質。

解決アプローチ（最小実装で最大効果）

  • フィルタリングはスプレッドシートのQUERYエンジンに任せ、Apps Scriptは「式を流す→結果の上位N件だけ読む」。
  • AND/OR・部分一致・列選択・上限件数など、必要十分な機能がQUERYで揃う。

シートでの即席テスト（手作業）

  • AND条件（両方含む）
=QUERY('DB_SEO実績'!A:D, "select A,B,C,D where A contains '美容室' and A contains '渋谷' limit 20", 1)
  • OR条件（どちらか含む）
=QUERY('DB_SEO実績'!A:D, "select A,B,C,D where A contains '美容室' or A contains '渋谷' limit 20", 1)
  • 複数列を対象（例: AまたはB列に含む）
=QUERY('DB_SEO実績'!A:D, "select A,B,C,D where A contains '美容室' or B contains '美容室' limit 20", 1)
  • ヒント
    • 遅い場合は対象範囲を固定上限で絞る（例: 'DB_SEO実績'!A1:D50000）、limitを小さく、列数も絞る。
    • キーワードにクォートが含まれる場合はエスケープ（'は\'、"は""など）。

Apps Scriptへの組み込み（実運用コード例）

  • ポイント
    • 一時シート（例: _TMP_SEARCH）にQUERY式を流し、SpreadsheetApp.flush()後に結果だけ読む。
    • 上位20件などに制限し、I/Oを極小化。
    • キーワードの簡易正規化（小文字化・全角→半角）のみでも効果。必要ならデータ側にA_norm隠し列を用意。
function searchSEODataViaQueryFormula(keywords) {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheetName = 'DB_SEO実績';
  const tempSheetName = '_TMP_SEARCH';

  const temp = ss.getSheetByName(tempSheetName) || ss.insertSheet(tempSheetName);
  temp.clear();

  // AND条件で部分一致検索
  const where = keywords
    .map(k => `A contains '${escapeQuery(normalizeJa(k))}'`)
    .join(' and ');

  const query = `select A,B,C,D where ${where} limit 20`;
  temp.getRange(1, 1).setFormula(`=QUERY('${srcSheetName}'!A:D, "${query}", 1)`);
  SpreadsheetApp.flush();

  const rows = temp.getLastRow();
  return rows > 1
    ? temp.getRange(2, 1, Math.min(20, rows - 1), 4).getDisplayValues()
    : [];
}

function escapeQuery(s) {
  return String(s)
    .replace(/\\/g, "\\\\")
    .replace(/"/g, '""')
    .replace(/'/g, "\\'");
}

function normalizeJa(s) {
  return String(s).toLowerCase()
    .replace(/[Ａ-Ｚａ-ｚ０-９]/g, c => String.fromCharCode(c.charCodeAt(0) - 0xFEE0))
    .trim();
}
  • 実装のコツ
    • 一時シート名をユーザー/セッション別に分けると併用でも安全（例: _TMP_SEARCH_${Session.getTemporaryActiveUserKey()}）。
    • シート作成/削除コストを避けたい場合は「再利用＆clear()」。
    • 値取得はgetDisplayValues()でもgetValues()でもOK（型の扱いに応じて選択）。

さらに効かせる実践Tips

  • 対象範囲の固定上限化
    • A:D → A1:D50000のように上限を切ると高速かつ安定。
  • 列数の削減
    • 必要列だけをselectする（例: select A,C）。
  • limitの調整
    • 実際に使う件数（例: 10〜20件）まで絞る。
  • 正規化用の隠し列（推奨）
    • 事前にA_norm列へ小文字化・全角→半角・不要記号除去を適用しておき、クエリはA_normに対して実行。
    • 例）Apps Scriptで夜間バッチ更新、または入力時トリガーで更新。
  • AND/ORの組み合わせ
    • 多キーワードでもQUERY文でシンプルに表現可能。必要に応じて括弧を使って優先順位を制御。

注意点と回避策

  • 文字種の揺れ
    • QUERYのcontainsは文字種の揺れに弱い場合があるため、データ正規化（A_norm）が有効。
  • クォートや特殊文字
    • escapeQuery()で最低限のエスケープを行う。必要に応じてさらに拡張。
  • 併用時の一時シート競合
    • ユーザー/セッション別のシート名にするか、LockServiceで排他制御。
  • フィルタ条件の複雑化
    • 複雑なN-gram/形態素を使った検索はQUERYでは限界。必要になったら段階的にインデックス手法へ。

いつインデックス方式に進むべきか

  • 条件
    • データが数十万行以上でQUERYでも数秒〜十数秒かかる場合
    • 日本語部分一致を高精度にしたい（N-gramや読みの正規化が必要）
  • 推奨
    • Driveにシャード分割したJSON（必要ならgzip）でインデックス保存＋CacheServiceでホット化
    • メタだけPropertiesService（バージョン番号など）
    • 取得は行番号だけ集めてgetRanges()で点読み

まとめ

  • まずは「QUERYに任せる」だけで、GAS側のループ走査をゼロにし、大規模データの検索を秒レベルにできる。
  • 実装コストが極小で、Web Appの30秒制限回避に即効性あり。
  • さらに必要なら、正規化列・範囲/列/limitの絞り込みで安定的に高速化。
  • それでも足りなければ、段階的にインデックス方式や外部検索基盤へ。

付録：よく使うQUERYスニペット

  • シンプルAND
=QUERY('DB_SEO実績'!A:D, "select A,B,C,D where A contains '美容室' and A contains '渋谷' limit 20", 1)
  • シンプルOR
=QUERY('DB_SEO実績'!A:D, "select A,B,C,D where A contains '美容室' or A contains '渋谷' limit 20", 1)
  • 複数列対象
=QUERY('DB_SEO実績'!A:D, "select A,B,C,D where A contains '美容室' or B contains '美容室' limit 20", 1)
  • 範囲＆列数の最適化例
=QUERY('DB_SEO実績'!A1:D50000, "select A,C where A contains '渋谷' limit 10", 1)
0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?