Googleフォームで作る高機能アンケートシステム

Google Workspaceは世界で数百万の企業に採用され、フォームは社内外の収集基盤として日常的に使われています。[1] 一方で、単純な集計しかできない、重複回答を防げない、ダッシュボードが分断されるといった声も根強く、専用SaaSに乗り換えるか悩む現場は少なくありません。公開ドキュメントや一般的な検証でも、GoogleフォームをUIとして据え、Google Apps Script（Google製のJavaScriptベース自動化環境）をオーケストレーション層、BigQuery（Google Cloudのデータウェアハウス）を信頼できる保存先に置く構成は、運用コストを抑えつつ一般的なエンタープライズ要件に近づける有力な選択肢です。[3,11] フォームの気軽さは残しながら、本人確認、重複排除、ワークフロー通知、分析基盤への即時連携までを一気通貫で設計する。ここに業務改善の余地が大きくあります。手戻りの少ない情報設計と、拡張前提の実装で、現場と経営の意思決定速度を同時に上げるアプローチを、CTOの観点から解説します。[4,5]

なぜGoogleフォームで「高機能」が実現できるのか

鍵は三層に分けた設計にあります。表示と入力のレイヤはGoogleフォームのセクション分岐や回答検証でUXを整え、処理のレイヤはApps Scriptのトリガーと外部API連携でワークフローを回し、保存と分析のレイヤはBigQueryに集約してLooker Studio（可視化/ダッシュボードツール）で可視化します。[6,4] フォーム単体では不足する機能は、トークン付きのプレフィルURL（事前入力リンク）で本人性を高め、回答のイベントで重複排除とバリデーションを行い、Slackやメールに通知して担当者を動かす形で補完できます。[7,10,5] ポイントは、スプレッドシートを単なる最終保存先にせず、バッファと監査ログに限定する意思決定です。 長期保存と集計はBigQueryに寄せることで、行数や同時更新でのボトルネックを避けられます。[8,9]

アーキテクチャの全体像

UIはGoogleフォームで、回答先に紐づくスプレッドシートをバッファとして作成します。Apps Scriptはフォームにコンテナバインドするかスタンドアロンで配置し、onFormSubmitトリガー（フォーム送信イベント）でイベントドリブンに処理を開始します。[10] 処理内容はトークン検証、重複チェック、データ正規化、Slack通知、そしてBigQueryのストリーミング挿入です。[5,11] アナリティクスはBigQueryのパーティションテーブルに集約し、Looker Studioでダッシュボードを用意して関係者に公開します。[8,4] 権限はGoogleアカウントに集約されるため、SSOとアクセス制御の運用負荷も抑えられます。典型的なユースケースとしては、社内サーベイ、顧客満足度調査、イベント申込や問い合わせの集約などが挙げられ、紙やExcelでの手作業集計を自動化できます。

スケールと信頼性を担保する考え方

Apps Scriptの単一実行は上限時間や呼び出し制限があるため、I/Oはまとめて行い、外部連携はネットワークの往復を極力減らすのが肝になります。[12] 行単位の逐次書き込みより、バッチ戦略（まとめて書く）にすると処理時間と安定性に有利な傾向があります。重複排除はキャッシュとBigQueryのユニーク制約相当（insertIdによるベストエフォート重複排除）の検証を二段構えにすると取りこぼしを避けられます。[13,14] なおスループットは時間帯やネットワーク状況に依存するため、配布計画や再試行（リトライ）設計と組み合わせて安定化させるのが実務的です。[11,12]

実装の要点とコード

まずは回答者ごとに一意なトークンを発行し、プレフィルURLに埋め込んで配布します。これにより招待リンクの第三者転送を抑止し、フォーム側では専用の短答項目で受け取れます。[7,17] なお、Googleフォーム自体には「隠しフィールド」はないため、トークン項目は短答テキストとして配置し、説明文に「編集しないでください」と明記する運用が現実解です。配布後はonFormSubmitイベントでトークンの検証と重複排除を行い、正規化したデータをBigQueryへストリーミングで集約します。ワークフロー通知はSlackのIncoming Webhookや、必要に応じてCloud Run経由の署名付きエンドポイントで行います。[10,11,5,16]

トークン付きプレフィルURLの発行

スプレッドシートに対象者リストがある前提で、Apps Scriptからトークンを発行し、フォームのプレフィルURLを生成してメール配布します。例ではGmail送信の失敗も補足します。[7]

// Apps Script (Code.gs)
function issuePrefilledLinks() {
  const recipientsSs = SpreadsheetApp.openById(getSecret_('RECIPIENTS_SHEET_ID'));
  const sh = recipientsSs.getSheetByName('recipients');
  const data = sh.getDataRange().getValues();
  const form = FormApp.openById(getSecret_('FORM_ID'));
  const tokenItem = form.getItems(FormApp.ItemType.TEXT).find(i => i.getTitle() === 'token');
  if (!tokenItem) throw new Error('token item not found in form');
  const editCol = 4; // store prefilled link
  for (let r = 1; r < data.length; r++) {
    const email = data[r][0];
    const name = data[r][1];
    const token = Utilities.getUuid();
    const url = buildPrefilledUrl_(form, tokenItem, token);
    sh.getRange(r + 1, 3).setValue(token);
    sh.getRange(r + 1, editCol).setValue(url);
    try {
      GmailApp.sendEmail(email, 'アンケートのご案内', `${name} 様\n下記リンクよりご回答ください（token は編集しないでください）。\n${url}`);
    } catch (e) {
      console.error(`mail failed: ${email}`, e);
    }
  }
}

function buildPrefilledUrl_(form, tokenItem, token) {
  const resp = form.createResponse();
  resp.withItemResponse(tokenItem.asTextItem().createResponse(token));
  const url = resp.toPrefilledUrl();
  return url;
}

回答受付、重複排除、Slack通知

フォームのonFormSubmitイベントで、トークン重複をCacheServiceで即時検出し、さらにBigQuery側のキー重複も検知して冪等性を確保します。Slack通知は失敗しても処理全体を止めない設計にします。[10,13,14,5] フォームにバインドしたスクリプトから、回答先スプレッドシートをIDで開いてバッファ保存します。

// Apps Script (onSubmit.gs) - フォーム送信トリガー
function onFormSubmit(e) {
  const cache = CacheService.getScriptCache();
  const formResponse = e.response;
  const editUrl = formResponse.getEditResponseUrl();
  const itemResponses = formResponse.getItemResponses();
  const payload = mapToObject_(itemResponses);
  const token = String(payload.token || '');
  if (!token) throw new Error('token missing');
  if (cache.get(token)) {
    console.warn('duplicate detected by cache', token);
    return; // fast-fail: 冪等性
  }
  cache.put(token, '1', 600); // 10分キャッシュ

  try {
    appendToSheet_(payload, editUrl);
  } catch (err) {
    console.error('sheet append failed', err);
  }
  try {
    streamToBigQuery_(payload);
  } catch (err2) {
    console.error('bq insert failed', err2);
  }
  try {
    notifySlack_(payload, editUrl);
  } catch (err3) {
    console.error('slack notify failed', err3);
  }
}

function mapToObject_(itemResponses) {
  const obj = {};
  itemResponses.forEach(ir => {
    obj[ir.getItem().getTitle()] = ir.getResponse();
  });
  obj.received_at = new Date().toISOString();
  return obj;
}

function appendToSheet_(payload, editUrl) {
  const form = FormApp.openById(getSecret_('FORM_ID'));
  const destId = form.getDestinationId();
  if (!destId) throw new Error('destination spreadsheet not set');
  const ss = SpreadsheetApp.openById(destId);
  const sh = ss.getSheetByName('raw') || ss.insertSheet('raw');
  sh.appendRow([payload.token, payload.氏名, payload.部署, payload.received_at, editUrl, JSON.stringify(payload)]);
}

BigQueryへの安全なストリーミング

Apps Scriptの高度なサービスでBigQuery APIを有効化し、insertAllでストリーミングします。テーブルは受信日時でパーティション化し、トークンをクラスタリングキーに設定します。[11,15,8,9]

// Apps Script (bq.gs) - Advanced Service: BigQuery を有効化
function streamToBigQuery_(row) {
  const projectId = getSecret_('GCP_PROJECT');
  const datasetId = 'survey';
  const tableId = 'responses';
  const rows = [{ json: {
    token: String(row.token),
    name: String(row.氏名 || ''),
    dept: String(row.部署 || ''),
    received_at: String(row.received_at),
    raw: JSON.stringify(row)
  }, insertId: String(row.token)}]; // insertId でベストエフォート重複排除
  const req = { kind: 'bigquery#tableDataInsertAllRequest', rows: rows, skipInvalidRows: true, ignoreUnknownValues: true };
  const res = BigQuery.Tabledata.insertAll(req, projectId, datasetId, tableId);
  if (res.insertErrors && res.insertErrors.length) {
    throw new Error(JSON.stringify(res.insertErrors));
  }
}

function notifySlack_(row, editUrl) {
  const webhook = getSecret_('SLACK_WEBHOOK');
  const text = `新規回答: ${row.氏名 || 'N/A'} / ${row.部署 || 'N/A'}\n編集URL: ${editUrl}`;
  UrlFetchApp.fetch(webhook, { method: 'post', contentType: 'application/json', payload: JSON.stringify({ text }) });
}

Cloud Runで受け口を分離する場合のAPI

ネットワークやレート制御を厳密に扱いたい場合、Apps Scriptから直接BigQueryに書かず、Cloud RunのAPIで受けてからサーバー側で整形・保存する構成も有効です。Node.jsの完全な例を示します。[16]

// Node.js (Cloud Run) - package.json の依存に express と @google-cloud/bigquery
import express from 'express';
import crypto from 'crypto';
import { BigQuery } from '@google-cloud/bigquery';

const app = express();
app.use(express.json());
const bq = new BigQuery();
const datasetId = 'survey';
const tableId = 'responses';
const SHARED_SECRET = process.env.WEBHOOK_SECRET || '';

function verifySignature(req) {
  const sig = req.headers['x-signature'];
  if (!SHARED_SECRET || typeof sig !== 'string') return false;
  const body = JSON.stringify(req.body);
  const hmac = crypto.createHmac('sha256', SHARED_SECRET).update(body).digest('hex');
  const a = Buffer.from(sig, 'utf8');
  const b = Buffer.from(hmac, 'utf8');
  if (a.length !== b.length) return false;
  return crypto.timingSafeEqual(a, b);
}

app.post('/ingest', async (req, res) => {
  try {
    if (!verifySignature(req)) return res.status(401).send('invalid signature');
    const row = req.body;
    await bq.dataset(datasetId).table(tableId).insert([{ ...row, received_at: new Date() }]);
    res.status(204).end();
  } catch (e) {
    console.error(e);
    res.status(500).send('ingest failed');
  }
});

const port = process.env.PORT || 8080;
app.listen(port, () => console.log(`listening on ${port}`));

BigQueryのテーブル定義

パーティションとクラスタリングを設定し、問合せのコストとレイテンシを抑えます。DDLで管理しておくと再現性が高まります。[8,9]

-- BigQuery DDL
CREATE TABLE IF NOT EXISTS `project.survey.responses` (
  token STRING NOT NULL,
  name STRING,
  dept STRING,
  received_at TIMESTAMP,
  raw STRING
)
PARTITION BY DATE(received_at)
CLUSTER BY token;

運用を成功させる設計の勘所

本人性を高めるには、社内アンケートであればログイン必須のフォーム設定（「1 回の回答に制限」や組織内に限定など）にしつつ、配布リンクにトークンを付けて照合する二段構えが効きます。[17,7] 外部向けではメールワンタイムリンクや招待コードを使い、回収期間を明確に区切ることで不正回答の温床を減らせます。フォームの項目設計は分析したいディメンションから逆算し、自由記述は少数に留めて正規化可能な選択式を中心に据えるのが肝心です。通知と保守は、失敗しても全体が止まらない設計にして、Slack送信やメールは独立した失敗許容の付帯処理にします。[5] セキュリティの観点では、秘密情報をスクリプトプロパティかSecret Managerに置き、監査可能な変更履歴を維持します。[18,19] 個人情報を扱う場合は、同意取得、保存期間、閲覧権限を明示し、アクセスログを含む監査体制を併せて整えるとよいでしょう。バックアップはフォームとスクリプトのバージョン管理を含め、JSONエクスポートやソースのリポジトリ化で復元性を担保します。[20] 最後に、処理をデータベースに寄せ、スプレッドシートはバッファに徹するという方針を守るだけで、運用の安定度は一段上がります。

Apps Scriptでの秘密情報管理の一例

シンプルな範囲ではスクリプトプロパティで十分です。例ではSlackのWebhookやフォームIDを集中管理し、読み取り専用で使います。[18]

// Apps Script (secrets.gs)
function setSecretsOnce() {
  const sp = PropertiesService.getScriptProperties();
  sp.setProperties({
    FORM_ID: 'xxxxx',
    RECIPIENTS_SHEET_ID: 'your-recipients-sheet-id',
    SLACK_WEBHOOK: 'https://hooks.slack.com/services/xxx/yyy/zzz',
    GCP_PROJECT: 'your-project-id'
  }, true);
}

function getSecret_(key) {
  const v = PropertiesService.getScriptProperties().getProperty(key);
  if (!v) throw new Error(`secret ${key} not found`);
  return v;
}

パフォーマンス計測と改善の実際

同一フォームに対して疑似的に同時到達するイベントを生成し、Apps Scriptの処理時間を比較する方法は広く用いられます。スプレッドシートへの逐次appendと逐次Slack通知を組み合わせた実装は遅延が蓄積しやすく、appendをまとめ、通知も束ね、BigQueryをバッチinsert相当（複数行まとめてinsertAll）に変更した実装の方が短時間で安定しやすいという傾向があります。[12,11] 計測は同一プロジェクト・同一時間帯で複数回行い、中央値で比較するのが実務的です。ネットワーク状況と割り当てに影響されるため、実運用では時間帯によるばらつきを監視し、コア時間帯の負荷を避ける配布計画と組み合わせると安定します。I/Oを減らし、冪等に保ち、失敗を局所化する。 この三原則を守る限り、フォームベースでも十分なスループットが得られます。[12,11,21]

回答データの正規化と再現性

選択肢の文言変更や順序変更は履歴として残し、BigQueryにマスタテーブルを設けてコード化しておくと、時間軸を跨いだ比較が容易になります。フォームのバージョンが上がるたびにスキーマ差分を小さく保つことができ、Looker Studio側のフィールド破綻を避けられます。分析要件が成長しても、データモデルが先回りしている状態を維持できると、改善サイクルは止まりません。[8,4]

まとめ：軽やかに始め、基盤で伸ばす

専用SaaSの検討は価値がありますが、まずは既存資産でどこまで行けるかを確かめる選択は合理的です。Googleフォームは入力UIとして十分に洗練され、Apps Scriptはイベントドリブンの自動化に長け、BigQueryは“後悔しない”保存先として堅牢です。本人性の担保、重複排除、通知、分析。この四点をシンプルな設計で満たせば、現場の手元から改善は始まり、やがて部門横断の意思決定を支える情報システムへ育っていきます。まずは小さく、しかし将来の拡張を前提とした形で立ち上げてみませんか。配布する名簿にトークン列を一つ足し、onFormSubmitに冪等な処理を書き、保存先をBigQueryに変えるだけで、景色はがらりと変わります。今日のフォームを、明日の基盤にするという発想が、業務改善と効率化の両立を現実にします。[7,10,11]