組織運営を行う上では、会議や行事開催の出欠調査は重要業務で大変な事務作業でした。
昔は、手紙やはがきで行っていました。返信封筒や返信ハガキを用意して文章印刷と宛名印刷と郵便局へ持ち込み、配達と返信を待って、登録記載するという手間と時間と労力がかかりました。

それを大幅に軽減してくれるのが、Google　や　Teams　のフォームという機能。
Web上で会員へ通知して、出欠回答を求め、必要項目を記載した内容を自動的にスプレッドシートで報告してくれる。

無料で使える　Google　Form　は大変便利で、会議や行事開催で多くの人が利用しています。

一方で、その出欠回答にマスター情報を追加したらより管理データとしてより使いやすくなるが、
セキュリティ維持と、行事運営の協力者に必要な出欠状況の提供を、どうすれば両立できるか？が、Google Spreadsheetの機能を利用して簡潔に構築できる。

要求するインプット項目も減らすことができた。

基本的な使いかたとセキュリティ維持の考え方は、下記を参照推奨

  • 詳しい公開手順を確認したい場合は、formrunの解説が役立ちます。
  • セキュリティ対策のポイントは、Tayoriの記事で確認できます。

 

その場合の方法をGeminiに教えてもらえたので、情報を共有します。

その前に、セキュリティ維持のための「設定」をご紹介

　特に重要なのが、「結果の概要を表示」を「Off」にすること。
　これをOnにすると、回答者全員の個人情報が外部に漏洩してしまう大事故になります。

事前知識と作業の流れ

・Google Formの回答は、Google Spread Sheetで提供される。

・Google Spread Sheet　ファイルは、エクセル同様にシートを追加できる。

・回答データに影響させることなく、管理用資料を作りたい。

・出欠回答を動的に（追加の都度）、そのままコピーし表示するシートを作る（関数利用）

・マスタデータシートのキー項目は表の左端（A列）に置く。

・キー項目を利用して、マスターの複数項目を一括してデータに追加できる
・これらを利用して管理用シートを作成。ただ、このファイルには機密シートもあり非公開とする。

・公開用のスプレッドシート（ファイル）を別に作成し、公開シートだけ表示させ、シートのURLを共有。

　出欠変更など、最新の内容を抽出したシートを追加し、さらに参加・不参加数を集計する新しいシートを作るのもGoogle Spreadsheetの機能を使うと簡単だ。
　方法は、Geminiが教えてくれた。
　提案されたプログラムを左上の対象セル１つに記入するだけで、全部を展開してくれる。
　その簡明さは、Excel　よりはるかに解りやすく、短く表現できる。

回答元シートの編集権限やアクセス権を非関係者に与えることなく、セキュリティ（データの改ざん防止・アクセス制御）の観点から、公開用別シートに公開用内容を読み込み、管理上必要情報を付加できる。

読み込む方法： IMPORTRANGE 関数（一番手軽・推奨）

Google スプレッドシート標準の IMPORTRANGE 関数を使えば、別のスプレッドシートファイルのデータをリアルタイムに参照できます。

手順

  1. 新しいスプレッドシートを作成 Google Drive上で閲覧関係者用に共有するための「別ファイル」を新規作成。
  2. アクセス許可（初回のみ） 新シートのどこかのセル（例: A1 ）に、一時的に以下の数式を入力して Enter を押します。 
    =IMPORTRANGE("フォーム回答元シートのURL", "'フォームの回答 1'!A1:H")
    • 入力するとセルに #REF! というエラーが表示されます。
    • セルにカーソルを合わせると「アクセスを許可」という青いボタンが表示されるので、それをクリックします。（これで2つのファイル間でのデータ連携が許可されます）
  3. 先ほどの「完成版数式」に組み込む アクセス許可が完了したら、新シートの A1セル の数式を以下のように書き換えます。 （元の raw や header_raw の部分を IMPORTRANGE に置き換えたものです）

【セキュリティ上のメリット】

  • 共有用シートの閲覧権を持つ関係者は、 IMPORTRANGE 経由での回答結果しか見ることができません。
  • 元の「フォーム回答シート」を開く権限がないため、元データを直接上書き・削除されたり、フォームの作成・編集画面にアクセスされたりするリスクを100%遮断できます。

全体構成と事前準備

前提条件

  1. 回答シート： Google Formsの直接結果である'フォームの回答 1' （A列：タイムスタンプ 〜 H列、メールアドレスは B列）
  2. マスターシート： 'マスター' （A列：メールアドレス、B列〜K列：回答に追加したい会員管理情報）
  3. まとめシート： 新しく作成したシートの A1セル に数式を入力だけで、全ての回答を取り込み再構成し、所定のソート項目で並べ替える

完成版プログラム（A1セルに入力）　わずか、これだけ！

=LET(
form_url, "フォーム回答元シートのURL",
header_raw, IMPORTRANGE(form_url, "'フォームの回答 1'!A1:H1"),
header_master, IMPORTRANGE(form_url, "マスター!B1:K1"),
raw, FILTER(
IMPORTRANGE(form_url, "'フォームの回答 1'!A2:H"), 
INDEX(IMPORTRANGE(form_url, "'フォームの回答 1'!A2:A"),,1) <> ""
),
sorted, SORT(raw, 1, FALSE),
latest, SORTN(sorted, 9999, 2, 2, FALSE),
combined, HSTACK(
latest,
MAP(INDEX(latest,,2), LAMBDA(email, XLOOKUP(
email, 
IMPORTRANGE(form_url, "マスター!A:A"), 
IMPORTRANGE(form_url, "マスター!B:K"), 
"未登録"
)))
),
data_sorted, SORT(combined, 18, TRUE),
VSTACK(
HSTACK(header_raw, header_master),
data_sorted
)
)

プログラム（数式）の逐次解説

この数式は LET 関数を使い、処理をステップごとに変数へ代入しながら順番に実行 しています。

【処理フロー】
[1. ヘッダーと元のデータの定義]
↓
[2. タイムスタンプ順（新しい順）に並べ替え]
↓
[3. メールアドレスの重複除去（最新の1件を保持）]
↓
[4. XLOOKUPでマスター情報（B~K列）を横に連結]
↓
[5. マスターのK列（18列目）を基準に昇順ソート]
↓
[6. ヘッダー行とデータ本体を縦に合体して出力]

ステップ別の詳細解説

1. ヘッダー行とデータの取得（定義）

  • header_raw, 'フォームの回答 1'!A1:H1, 回答シートの1行目（A〜H列の見出し）をそのまま取得します。
  • header_master, マスター!B1:K1, マスターシートの1行目（B〜K列の見出し）を取得します。
  • raw, FILTER('フォームの回答 1'!A2:H, 'フォームの回答 1'!A2:A <> ""), 回答データの本体（A2〜H列）を取り出します。 FILTER(..., A2:A <> "") を挟むことで、タイムスタンプが空の無効な「空行」を除外しています。

2. 日時が新しい順にソート

  • sorted, SORT(raw, 1, FALSE), データ本体 raw を、1列目（タイムスタンプ）を基準にして 降順（ FALSE ＝新しい順） に並べ替えます。

3. メールアドレスによる最新回答の絞り込み

  • latest, SORTN(sorted, 9999, 2, 2, FALSE), SORTN 関数を使い、重複を除外したデータを作成します。
    • 2 （第3引数）: 重複行を排除するモード
    • 2 （第4引数）: 2列目（メールアドレス）をキーにして重複チェック
    • すでに降順ソートされているため、一番上にある「最も新しい回答」だけが残ります。

4. マスター情報の引き当てと横結合

  • combined, HSTACK( latest, MAP(INDEX(latest,,2), LAMBDA(email, XLOOKUP(email, マスター!A:A, マスター!B:K, "未登録"))) ),
    • INDEX(latest,,2) で最新回答のメールアドレス列だけを取り出します。
    • MAP と LAMBDA を使い、メールアドレスを1件ずつ XLOOKUP に渡します。
    • XLOOKUP でマスターの A列（メール）を検索し、一致した行の B列〜K列（10列分）を一括取得 します（見つからない場合は "未登録" ）。
    • HSTACK （Horizontal Stack）で、回答データ（8列）の右側にマスター情報（10列）を横方向に連結します（合計18列の表になります）。

5. 指定列（マスターK列）での昇順ソート

  • data_sorted, SORT(combined, 18, TRUE), 作成した18列の表 combined 全体を、18列目（＝マスターのK列） をキーにして 昇順（ TRUE ＝あいうえお順/昇順） に並べ替えます。

6. ヘッダーとデータの縦結合・完成出力

  • VSTACK( HSTACK(header_raw, header_master), data_sorted )
    • HSTACK(header_raw, header_master) で作成した「回答の見出し ＋ マスターの見出し」のヘッダー行を作成します。
    • VSTACK （Vertical Stack）を使い、ヘッダー行の下にソート済みデータ data_sorted を縦方向に合体させて画面に出力します。

このプログラム（数式）の素晴らしいポイント

  1. 完全自動＆リアルタイム更新 Google Formsに新しい回答が入ったり、既存の人が回答を変更したりしても、手動操作・スクリプト実行なしで一瞬で最新状態に再計算されます。
  2. 列名の動的変更に対応 フォームの設問名やマスターの項目名が変わっても、セル指定（ A1:H1 など）から直接読み込んでいるため、数式を書き直す必要がありません。
  3. エラー・無駄のカット FILTER で空行を除外し、 XLOOKUP の非検出時に "未登録" を出しているため、末尾に変なデータが残ったりエラー落ちしたりしません。
  4. プログラム管理不要（ノーコード/ノースクリプト） PythonやGoogle Apps Script (GAS) のようにサーバー設定やトリガー設定、権限承認などが一切不要で、スプレッドシートの標準機能だけで完結しています。

共有相手（関係者）には、このシートを「閲覧専用」として提供するだけで、常に最新の整列済み出欠表を確認してもらうことができます。

ぜひご活用ください！

 

つくってみた感想

目的とやりたいことをAIのGeminiに伝え、何度かやり取りしたら、紹介した短いプログラムを作ってくれた。
それを、左上隅のセル１か所に記載するだけで、目的の表ができ、回答が増えると自動再計算してWebで提供。
これをエクセルのマクロ（Visualbasic）やPythonで作ったら膨大な行数になるだろう。
行っていることは、転記・マスタとの結合・選択・並べ替え・Webに表示し提供するという、データベースとネット機能フル出動だ。

Google スプレッドシートの LET や FILTER 、 SORTN 、 MAP / LAMBDA といったモダンな関数の組み合わせは、もはや「単なる表計算」を超えて 「Web上でリアルタイムに動く超軽量なデータベース＆プログラム」 そのものでした。

今回の仕組みが特に強力な理由を振り返ってみると、Web上のスプレッドシートならではの大きな強みが際立っています。

  1. PythonやAccessに対するアドバンテージ
    • 環境構築・サーバーが不要： ブラウザひとつで完結し、PCの電源が落ちていてもGoogleのクラウド上で24時間動く。
    • Web表示・共有が圧倒的に楽： プログラムの実行結果をWeb画面（UI）としてそのまま関係者に安全に共有できる。
  2. Excelに対するアドバンテージ
    • Google Formsとのネイティブ連携： 回答が届いた瞬間にトリガー不要で即座に自動再計算される。
    • IMPORTRANGE による強固なセキュリティ： ファイルレベルでアクセス権限を分離できるため、元データの保護が容易。

「プログラミングコードを書くよりも簡潔で、保守（メンテナンス）もしやすい」