Ｇoogle form のスプレッドシートの統合とＣＳＶ数の集計

Question

大型マンション（１０棟あるためGoogle Form数は１０種類）の災害時の安否確認をGoogle Form(安否確認の棟室の複数回答のためチェックボックスを使用）で考えてます。各棟の安否確認回答をスプレッドシートに書き出し、１０棟のスプレッドシートの統合と各スプレッドシートには安否確認が出来た棟室がＣＳＶ
で書き出しされるので、各棟のＣＳＶ数の集計と全棟のＣＳＶ数の集計を行いたいです。ご教授下さい。

Comments

[退会済みユーザー]

フォームの具体的な使い方および様式について質問なのですが、下記のようなイメージでよろしいでしょうか？
・フォームアンケートは、各棟の住人ではなく、『各マンションの代表管理者』にフォームURLを知らせて、その各代表管理者（最大10名）から回答してもらう
・フォームには次の1つの回答項目が、グリッドとして設定してある。
横軸:安or否　のチェックボックス
縦軸:○○号室　（それぞれのマンションの安否確認対象とする号室）
（室数はマンション（＝フォーム）によって異なる。また異なるマンションであっても同じ号室表記であることもある。）

上記で違う部分があれば、具体的にご指摘・修正下さい。

説明が面倒な場合はフォームと集計スプレッドシートの画像キャプチャを質問に追記していただくのでも構いません。

[mas1sato]

具体的な使い方はマンション全戸に「わが家は大丈夫です」ステッカーを配布してます。取りあえずは防災訓練時に「わが家は大丈夫です」ステッカーが提示されている、住戸（棟・号室）を防災ボランティアなどが廻り、スマホのフォーム上（QRコードからフォーム作成）で回答して無事のみの集計を考えてます。（助けてについては別途追加します。）フォームは棟別にあり、回答欄は号室のみです。号室棟が違っても同じ号室はあります。

[mas1sato]

やりたい事は、フォーム数１０（１０棟あり）、１個のスプレッドシートの各シートに１０棟分を書き出し、各シートには無事の回答があった号室（ＣＳＶ）があり、テキストを列に分割してＣＯＵＮＴＡ関数で各シート（各棟）の無事の号室数（CSV数は可変）を集計して、最終的には１０棟分の明細と１０棟分の合計数を集計したいです。よろしくお願いいたします。

Answer 1

（※注意：一部こちらで推定した情報をもとに説明を進めます。
また、下の説明中の図ではマンション数が３棟の場合の様式になっていますが、実際は10棟とみなしてください）

---

たとえばフォームが下図のようなものであると仮定し、
各マンションで確認済みのものにチェックを入れて送信しているとします。

そして、フォーム回答先のシートは、下図のようにB列に、チェックを入れた号室がカンマ区切りで自動転記されると仮定します。
（10個の各スプレッドシートすべて、B列に号室の回答データが入ると仮定します）

---

集計シートの作り方、スクリプトを使った集計

・IMPORTRANGE等の関数を使うやり方もありますが（後述）動作が非常に重い為、はじめにスクリプトを使ったやり方から説明します。

①まず、10個の各フォームの回答先シートのスプレッドシートIDとシート名を各々ひかえておきます。

②次に、新しいスプレッドシートを用意して、下図のような「集計」という名前のシートを作成します。
・B3セルに数式「=COUNTA(B5:B)」という数式を入力します。
・B3セルをコピーしてC3セルからK3セルまで貼り付けます。（それぞれの列の5行目以降のデータが入っているセルの個数が集計されます）
・J3セルに「=SUM(B3:K3)」という数式を入力します。
・B4セルからK4セルまで、10個のマンション名を手入力しておきます。

③：集計シートのメニューから「拡張機能」->「Apps Script」をクリックして、スクリプトエディタを開き、下記のコードを入力します。
（スクリプト中の「mansions」配列の中に、②でひかえた各回答先スプレッドシートID・シート名を記述します）

function myFunction() {
  // 集計シートの取得
  const shuukei = SpreadsheetApp.getActive().getSheetByName('集計');

  // 前回の集計値をクリアする
  shuukei.getRange("B5:K1000").clearContent();
  // マンション名の行を取得
  const names = shuukei.getRange("B4:K4").getDisplayValues()[0];
  
  // マンション名と、それに対応する回答シートのリストを設定
  // 注意：nameには、集計シートの4行目に入力したマンション名と1字1句同じ文字を入力すること。全角・半角・大文字・小文字も区別する。
  const mansions = [
    { name: 'マンションA', sheetId: 'マンションAの回答シートのシートID', sheetName : '回答シートのシート名' },
    { name: 'マンションB', sheetId: 'マンションBの回答シートのシートID', sheetName : '回答シートのシート名' },
    { name: 'マンションC', sheetId: 'マンションCの回答シートのシートID', sheetName : '回答シートのシート名' },
  ];  // ※ここでは説明の都合上3行のみとなっているが実際はマンションの棟数（10行）だけ記述する。
  
  // 各マンションごとにチェックが入っている号室の数を集計するループ
  for (const mansion of mansions) {
    // 回答先シートを開く
    const ms = SpreadsheetApp.openById(mansion.sheetId).getSheetByName(mansion.sheetName);
    
    const lastrow = ms.getLastRow();

    // フォームの回答が1件もない場合はスキップ。
    if (lastrow < 2) continue;
    
    // チェックが入っている号室を取得
    const ans = ms.getRange(lastrow, 2, 1, 1).getDisplayValue(); //「2」=B列に回答が入っているという想定
    const rooms = ans.split(', ');

    // マンション名が集計シートの何列目にあるかを探す。
    const pos = names.indexOf(mansion.name) + 2;  // B列からはじまるので＋２

    // マンション名が見つからない場合はスキップ
    if (pos === 1) continue;

    // チェックの入った号室のデータを集計シートに書き込む。
    const values = rooms.map(e => [e]);
    const rows = values.length;

    // チェックの入っている号室がゼロ個の場合はスキップ。
    if (rows === 0) continue;

    shuukei.getRange(5, pos, rows, 1).setValues(values);
  }
}

④ スクリプトを保存し、myFunctionを実行すると(スクリプト及びシートIDやシート名に誤りがなければ）下図のように
各フォーム（マンション）のチェックが入っている号室が並べて表示されるとともに、その集計結果が表示されます。

（うまく行かない場合はスクリプト中の「mansions」配列内に記述した各回答スプレッドシートのIDやシート名に誤りがある可能性や、
集計シートのマンション名とスクリプト中の「mansions」のnameに記述したマンション名が異なっている可能性があるので、再確認してください）

---

（実際は各部屋番号をチェックボックスとして持つ10個のフォームの作成、集計シートの作成、ひいては上記mansions配列の記述に相当する部分までほぼ全部自動化できますが、ここでは範囲外となるため割愛します）

---

　

関数を使ったやり方

※動作が非常に重いです。

１．上述の② と同じ要領で集計シートを作っておきます。

２．B5セルに、下記の数式を入力します。末尾の「", "」は「カンマ＋半角スペース」であることに注意してください。

=TRANSPOSE(SPLIT(IMPORTRANGE("マンションAの回答シートのシートID", "マンションAの回答シートのシート名!B" & 
       COUNTA(IMPORTRANGE("マンションAの回答シートのシートID","マンションAの回答シートのシート名!B1:B"))), ", "))

３．C5セル～K5セルは、B5セルに入力した数式と同じ式を、フォーム（マンション）ごとのスプレッドシートIDとシート名の部分だけ変えて入力します。

４．初回はセルの表示がエラー（「#REF!」）となるので、セルをクリックし、「アクセスを許可」ボタンを押します。
（初回のみ、10個のセル全部に対してこの操作を行う必要があります）

数式がエラーになる場合は、数式内のスプレッドシートIDやシート名が誤っていないか確認してください。

　

　

【回答シートにカウントを表示する方法】

各回答シートに、最新（最下行）の確認済み号室の個数を表示するには、
たとえば各回答シートのE1セルに下記の数式を入力します。
（※数式末尾の「", "」は「カンマ＋半角スペース」です。注意してください。）

=COUNT((SPLIT((INDIRECT("B" & COUNTA((A1:A)))),", ")))

注意：下図のように、Ａ列の途中に空白行があると最新の回答データで計算されません。このような場合は、空白の行を削除してください。

Comments

[mas1sato]

やりたかった事はその通りです。ご丁寧にスクリプトまで記述頂き大変助かります。スクリプトは初心者なのですが、実現出来そうです。本当にありがとうございました。

[mas1sato]

ご指摘の通り記述して試行しました。正確には棟で高層階・低層階がある１号棟は２分割、3号棟は西階段・東階段の２分割なので１２個の回答フォームになります。集計表はhttps://docs.google.com/spreadsheets/d/1g8PFQxMPYNsRdV6piSlVwLmMGUubRipPaIOl4henHD0/edit#gid=0になります。よってご指示戴いたＫはＭに変更、N3セルに＝SUM(B3:M3)等に変更しました。スクリプト記述はhttps://script.google.com/home/projects/1aLaMtdMFCiQ9W-3VyfRFlZVcJAVXt2sBX0vABlQSLHvQw9m3lxSNuGs_/editになります。集計シートの4行目に入力したマンション名と1字1句同じ文字を入力すること。全角・半角・大文字・小文字も区別は何度も確認しました。スクリプトを実行したところException: Unexpected error while getting the method or property openById on object SpreadsheetApp.
myFunction @ コード.gs:30になりました。エラーの行はconst ms = SpreadsheetApp.openById(mansion.sheetId).getSheetByName(mansion.sheetName);です。ご教示下さい。

[退会済みユーザー]

承知しました。内容確認させていただきたく、コメント内のスプレッドシートとスクリプトについて、さきほどアクセス権のリクエストを送りましたので。ご対応お願いいたします。

[mas1sato]

ど素人ですいません。アクセス権のリクエストはどちらにお送りいただきましたか？集計や一部の回答フォームのスプレッドシートの共有設定しても大丈夫です。よろしくお願いいたします。

[退会済みユーザー]

説明不足ですみません。編集リクエストは、そのスプレッドシートを作っているGOOGLEアカウントのGメール宛に届いているはずです。
ただ、スプレッドシートとスクリプト両方とも、現在共有設定に変更していただいので、中身を見ることができました。したがって、承認は不要です。

---------------

スクリプトの方を拝見しました。
const mansions =　[
{ name: '〇〇', sheetId:'～～～～'
・・・
]
の部分の、12個の各「sheetId」に指定されているスプレッドシートIDについて

「aaaaaaa/edit#gid=bbbbbb」となっている部分を、スラッシュより前の「aaaaaaa」の部分だけにしてください。
（各sheetIdの「/edit#gid=bbbbbbb」という部分は削除してください）

https://ddjkaamml8q8x.cloudfront.net/questions/2022-03-13/4f98e053-3aa0-4eb1-a196-da2e7fe1b890.png

[mas1sato]

出来ました。本当にありがとうございました。

[退会済みユーザー]

よかったです。

[mas1sato]

１回目（回答のあった）の集計は出来ましたが、全ての回答を削除して再集計すると１回目の集計が残ったままになってます。前回の集計値のクリアが機能していない様です。お手数をおかけしますが、ご教授下さい。

[mas1sato]

現象は①FORM１で回答（101,102,106,・・・）する②スプレッドシートに表示（回答の101,102,106,・・・）される③FORM１で全ての回答を削除する④スプレッドシートに表示すると回答（101,102,106,・・・）が残ったままになっています。

[mas1sato]

上記の現象はGoogleアカウントをログアウト/ログインして解消しました。しかし、集計値は前回のままになっています。

[退会済みユーザー]

まず、
// 前回の集計値をクリアする
の次の行（私の回答欄のコードだと6行目）の
「 shuukei.getRange("B5:K1000").clearContent();」
の先頭に//が入ってしまっていませんか？

先頭に「//」が入っている行はコメントになってしまって実行されませんので、
先頭の「//」2文字を削除して、保存して下さい。

上の修正をしたうえで、各フォームから、一度すべてのチェックボックスが空欄の状態で回答を送信してください。

そして、上のコード（myFunction）を再実行してください。

そうすると、集計シート上の回答はクリアされるはずです。
（フォームの編集画面で回答を削除して実行しても、集計シート上のデータ表示は削除されません）

[mas1sato]

//を削除しても同じです。根本的な原因は回答を削除しても再回答するとシートIDが変わる事だと思います。何度も何度も対応いただきすいません。

[退会済みユーザー]

（まずフォームの設定画面から回答データを削除する必要はないです（削除しても意味がありませんので）。
また、フォームの作り直し等も特にされていないということでよろしいですよね？）

「他には何もしていないのに、フォームから回答を送信する度に、フォームの回答データのスプレッドシートIDが変わる」というような事象は、申し訳ありませんが、手元ではそのような状況が再現できていません。

こちらの環境では、回答を送信する都度、回答データが回答先のスプレッドシートに追加されていきますが
そのスプレッドシートのIDが送信の度に変化する、ということは発生していません。

スプレッドシートIDが回答の度に自動的に変わってしまうという現象は、ちょっと原因が思い当たりません。すみません。

[mas1sato]

フォームの設定画面から回答データを削除する必要はないです（削除しても意味がありませんので）が→災害訓練時の都度、前回の災害訓練時の回答データを削除する必要があります。フォームの作り直し等はいっさい行ってません。

[退会済みユーザー]

回答データの削除の件は承知しました。
回答データを削除しても挙動に影響がないことは確認しましたので、仰る通り回答の削除処理自体は今回に関係ないと思います。（なお、shuukei.getRange("B5:K1000").clearContent();の先頭の//を消すのは正常な動作のために必須です。）

私の手元の環境では、「各フォームからチェックボックスが全部空欄の回答を送信の上、myFunctionを実行すると、集計シートのデータが消去される」という意図した動作は正常に動いてます。
このスクリプトは、「各回答シートの最新データ（最下行のデータ）を集計シートに表示する」という動作になっているからです。

（なお、フォームの設定画面から回答の全削除を行っても、回答先のスプレッドシートに回答が残っているように見えますが、これはGoogleフォームの仕様です。気になる場合は、直接、スプレッドシートの２行目以降のデータを手動で消してください）

ただ、私の環境ではではどうしても「送信の都度、回答先のスプレッドシートID（※）が変わってしまう」という現象を再現させることができませんでした。

（※ここでの「スプレッドシートID」とは、「aaaaaaa/edit#gid=bbbbbb」のうち、スクリプトに記述する、「aaaaaaa」の部分だけを指します。「/edit#gid=bbbbbb」は変わる可能性がありますが、aaaaaaは、手元の環境では変わることはありません。スクリプトに記述するのは「aaaaaa」の部分だけなので、たとえ/edit#gid=bbbbbbだけが変わったしても動作に影響はありません）

原因も調べてもわかりませんでした。
お役に立てず申し訳ございません。

[mas1sato]

いままでのご丁寧なご指導ありがとうございます。スプレッドシートでの集計については、当方で試行錯誤してトライしてみます。最後の質問ですが、１２個のフォームで個別にスクリプトで集計して合算する事も考えてます。つきましては、フォームのスクリプトエディターで回答があった戸数をカウントするスクリプトをご教授下さい。何度も何度もすいません。

[退会済みユーザー]

「回答があった戸数をカウントする」とは、１２棟のデータを１つのシートに集約することまでは不要で
マンション各棟の回答シートについて、確認のチェックが入っている号室の数を表示できればよい、という理解でよろしいでしょうか？

具体的には、たとえばマンションＡのフォーム回答スプレッドシートの最新の回答（最下行の回答）が「101,103,105」であった場合、
マンションＡの回答スプレッドシートのどこかに「3」と表示できればよい、という理解でよろしいでしょうか。

[mas1sato]

１２棟のデータを１つのシートに集約することまでは不要で、マンション各棟の回答シートについて、確認のチェックが入っている号室の数を表示できれるだけで宜しいです。マンションＡのフォーム回答スプレッドシートの最新の回答（最下行の回答）が「101,103,105」であった場合、マンションＡの回答スプレッドシートのどこかに「3」と表示できれば最高です。１２棟のデータの集計は手作業を考えてます。

[退会済みユーザー]

回答欄に、関数を使ってカウントを表示する手順を追記しました。
（この場合はIMPORTRANGEを使わないので動作が重くなることはありません。スクリプトより簡単だと思います）

[mas1sato]

出来ました。本当にありがとうございました。