スプレッドシート×GAS×Cloud Run Jobsで入力検証つきデータ収集の仕組みを作ってみた

こんにちは、SCSKの齋藤です。
本記事では、スプレッドシートとGoogle Apps Script（GAS)、Cloud Run Jobsを組み合わせて「入力検証つきのデータ収集基盤」を検証環境で作ってみた話を紹介します。

はじめに

検証環境で、複数拠点の担当者が日々の点検結果を報告し、それをBigQueryに集約して分析するという仕組みを作る必要がありました。担当者はエンジニアではないため、専用のWebアプリを作って使ってもらうのはハードルが高そうだと感じていました。使い慣れたツールで、かつ変な値を入力してもすぐに気づける仕組みにしたいと考えていました。

ただ、スプレッドシートに直接データを入れてもらうだけだと、日付の重複や数値の入力ミスなどがそのままBigQueryに入ってしまいそうです。GASでセルの色を変えたりポップアップを出したりして、その場で気づいてもらえる仕組みにできないかと考えました。また、BigQueryへの書き込み処理自体は重くなりそうだったので、GAS単体で完結させず、Cloud Run Jobsに処理を渡す構成にしてみました。

この記事では、スプレッドシート×GAS×Cloud Run Jobsという構成を選んだ理由と、実際にどうやってバリデーションとデータ連携を実装したかを紹介します。

Google Apps Scriptとは

Google Apps Script（GAS）は、Google WorkspaceのアプリケーションをJavaScriptベースで拡張・自動化できるサーバーレスのスクリプト実行環境です。スプレッドシート、ドキュメント、Gmail、カレンダーなど各種Google Workspaceサービスに対して、コンテナバインドスクリプトという形でスクリプトを紐づけて実行できます。

GASの大きな特徴は、認証・実行環境の管理が不要な点です。スプレッドシートを開いているユーザーの権限で実行されるため、個別にサービスアカウントを用意しなくても、そのユーザーがアクセスできる範囲でGoogle Workspace内のデータを操作できます。さらにUrlFetchAppを使えば外部APIやGoogle CloudのAPIも呼び出せるため、ちょっとしたAPI連携のグルーコードとしても使えます。

一方で、1回の実行につき最大6分（無料アカウントでは6分、Google Workspaceアカウントでも同様の制限）という実行時間の制約があります。重い処理をGAS単体で完結させようとすると、この制限に引っかかる可能性があるため、重い処理は別のサービスに逃がす設計を考える必要があります。今回はこの制約が、後述するCloud Run Jobsとの役割分担を考えるきっかけになりました。

料金面では、GAS自体の実行に課金は発生しません（Google Workspaceのライセンス費用の範囲内で使えます）。ただし、外部API呼び出しには1日あたりの実行回数制限があるため、大量データを扱う場合はこの制限も意識しておいたほうがよさそうです。

なぜスプレッドシート×GASを選んだか

入力手段として一般的なGoogleフォームと比較し、それぞれのメリット・デメリットを整理しました。

この比較をしてみて、少なくとも今回のような「社内の限られたメンバーが、表形式のデータを定期的に入力する」というユースケースでは、スプレッドシート×GASのほうが実装コストも学習コストも低く抑えられそうだと感じました。GASであれば、入力されたセルの値をその場でチェックして、問題があれば赤く着色したりダイアログを出したりできるので、間違ったデータがBigQueryに入る前に気づいてもらえる可能性が上がるのも魅力でした。

やってみる

前提・準備

検証にあたって用意したものは以下の通りです。

• 入力用のGoogleスプレッドシート
• スプレッドシートにバインドするGASプロジェクト（コンテナバインドスクリプト）
• バリデーション通過後にBigQueryへの書き込みを行うCloud Run Job（Python）
• GASからCloud Run Jobsを起動するための権限設定（GASを実行するユーザー、もしくはGASプロジェクトに紐づくアカウントにCloud Run 起動元の権限を付与）

トリガー設計：軽量チェックと本検証を分ける

GASの実行時間制限を踏まえて、チェックを2段階に分けてみました。1つ目はonEditトリガーによる軽量なチェックです。セルが編集されるたびに発火し、明らかな入力ミス（数値のはずが文字列になっている、など）をその場で検知して該当セルを着色します。

function onEdit(e) {
  const range = e.range;
  const value = range.getValue();

  // 数値であるべき列に数値以外が入っていたら着色して知らせる
  if (isTargetColumn(range.getColumn()) && isNaN(value)) {
    range.setBackground('#ff2a00');
  } else {
    range.setBackground(null);
  }
}

2つ目は、担当者がすべての入力を終えたあとに押す「実行」ボタンからの本検証です。こちらはヘッダーの整合性、必須項目の空白チェック、数値の範囲チェック、日時の重複チェックなど、シート全体を対象にしたより重いチェックを行います。onEditの軽量チェックだけで完結させず、実行ボタン側でシート全体を俯瞰したチェックを入れることで、セル単位のチェックでは見つけられない不整合（他の行との重複など）も検知できるようにしています。

バリデーションの実装例

実行ボタンから走る本検証では、次のようなチェックを実装してみました。

このうち数値レンジチェックは、以下のように上限値・下限値を比較する形で実装しました。

function checkNumberRange(value, lowerBound, upperBound) {
  if (value < lowerBound || value > upperBound) {
    return false; // 範囲外は不正な値として扱う
  }
  return true;
}

チェックに引っかかった行はセルを着色したうえで、実行ボタン押下時にダイアログでエラー内容をまとめて表示し、そのままではCloud Run Jobsを起動しない、という流れにしています。すべてのチェックを通過して初めて次のステップに進む、というシンプルな構成です。

Cloud Run Jobsをキーレスで起動する

バリデーションを通過したら、GASからCloud Run Jobs Admin APIを呼び出してジョブを起動します。ここで工夫したのは、サービスアカウントキーを発行せずにScriptApp.getOAuthToken()で取得したOAuthトークンをそのまま使う点です。

function runCloudRunJob(jobPath) {
  const token = ScriptApp.getOAuthToken();
  const url = `https://run.googleapis.com/v2/${jobPath}:run`;

  const response = UrlFetchApp.fetch(url, {
    method: 'post',
    headers: { Authorization: `Bearer ${token}` },
    contentType: 'application/json',
    payload: JSON.stringify({ name: jobPath })
  });

  return response.getResponseCode();
}

ScriptApp.getOAuthToken()は、GASプロジェクトの実行コンテキストに紐づくOAuthトークンを取得する組み込み関数です。事前にappsscript.jsonのoauthScopesにCloud Run関連のスコープを追加しておく必要がありますが、鍵ファイルを作って管理する必要がない点はメリットに感じました。もちろん、GASプロジェクト側にCloud Run Jobsを起動できるだけの最小権限を付与しておくことは必要です。

Cloud Run Job側でBigQueryに格納する

Cloud Run Job（Python）側では、スプレッドシートを直接読み直し（GAS側の検証をすり抜けたケースに備えた最終防衛ラインとして）、BigQueryの実テーブルのスキーマを都度取得してから型変換を行い、格納する、という設計にしてみました。

def cast_to_schema(df, schema):
    for field in schema:
        if field.field_type == "INTEGER":
            df[field.name] = df[field.name].astype("Int64")
        elif field.field_type == "FLOAT":
            df[field.name] = df[field.name].astype("float64")
    return df

BigQueryのテーブルスキーマを都度取得してから変換することで、テーブル側にカラムを追加してもコード側の変換ロジックを大きく変えずに対応できそうだと感じました。決め打ちの型変換ではなく、スキーマ駆動で変換する設計にしたのは、今回の検証を通して得られた気づきの1つです。

工夫したところ・ハマったところ

「検証はGASだけで十分」ではなかった

最初はGAS側のチェックだけで十分だと思っていたのですが、検証を進めるうちに、GAS側のチェックをすり抜けるケース（例えば、複数人が同時に編集していてonEditのタイミングがずれるなど）がありそうだと気づきました。そのため、Cloud Run Job側でも最低限のバリデーション（重複チェックなど）を再度行う、という二段構えにしています。入力時の使い勝手のためのチェックと、データの正しさを担保するための最終チェックは役割が違うのだと実感しました。

実行中の編集をどう防ぐか

実行ボタンを押してからCloud Run Jobが処理を終えるまでの間に、別の担当者がシートを編集してしまうと、検証時点と実際に読み込まれるデータがずれてしまいます。これを防ぐため、実行中は対象範囲をRange.protect().setWarningOnly(true)で一時的に警告付き保護にし、処理が終わったら保護を解除する、という工夫を入れてみました。警告付き保護にすることで、誤操作を完全に防ぐわけではないものの、「今は処理中です」というシグナルを担当者に伝えられるのがよいと感じました。

GASの実行時間制限とどう付き合うか

GASは1回の実行が数分程度で打ち切られてしまうため、最初は「全部GASで完結させたい」と思っていたものの、データ量が増えたときに時間切れになるリスクを考えて、重い処理はCloud Run Jobsに任せる設計に落ち着きました。GASは「検証と起動のトリガー」に徹し、実際のデータ処理は別サービスに任せる、という役割分担にしたことで、GAS側の実装もシンプルに保てたように思います。

まとめ

• スプレッドシート×GASは、非エンジニアの担当者が使い慣れたツールのまま、入力時にすぐフィードバックを受けられる構成として検証してみて良さそうだと感じました
• onEditでの軽量チェックと、実行ボタンからの本検証を分けることで、GASの実行時間制限と付き合いながらチェックを実装できました
• ScriptApp.getOAuthToken()を使えば、サービスアカウントキーを発行せずにCloud Run Jobsを起動できます
• Cloud Run Job側でもBigQueryスキーマを都度取得してから型変換する設計にしたことで、テーブル変更に強くなりそうです
• GAS側の検証とジョブ側の検証は役割が違うもので、両方あって初めて安心できる構成になるのだと感じました