【10分で学べる】Googleフォームの回答を個別のシートに集計する方法

なおき | AI×時短術

2024年5月9日 21:00

「Googleフォームで回答がグラフになるけど生データが欲しいんだよな」
「Googleフォームの回答結果を自動で集計して分析したい」
「非エンジニアにもできる方法が知りたい！」

本記事ではこのような方を対象に、GAS(Google App Script)を使ってGoogleフォームの回答を個別のシートに集計する方法をご紹介します。

■本記事で分かること
GASを使ってGoogleフォームの回答を個別のシートに集計する方法

■本記事の信頼性
私は、本業でChatGPTなどのAIツールを活用して効率化を実現し、ほぼ毎日定時で帰宅。家族との充実した時間を確保している「なおき」といいます。

AIと全く無縁の体育系で文系出身（数学Ⅱで終わりました）の私でもできる時短術をわかりやすく説明できるよう、Xなどを活用して情報発信しています。

また、note開始３か月で総閲覧回数は約5000回
約5ケタ/月に至る記事の購入をいただきました！

これからも皆様のお役に立てる記事を配信していくのでよろしくお願いします！

この記事を読んでくださった方々が１分でも早く帰れることを願い、今回はGASを使ってGoogleフォームの回答を個別のシートに集計する方法をご紹介します！

1.Googleフォームの準備

まずは、Googleフォームで調査したいアンケート項目などを設定します。

ステップ１：Googleフォームの質問項目を設定

ご自身のアカウントでGoogleフォームの画面を表示してください。

フォームが表示できたら、フォームの質問などを作成していきます。

質問項目を追加したい場合はフォームの右側に表示される「⊕」をクリックして追加してください。

今回は簡単に「回答の自動集計」という名前のフォームを作成し、セミナー参加後のアンケートを取るようなものにしてみましょう！

回答者に入力してもらう項目として以下の内容を入れてみました。

【今回入れたアンケート内容の一例】

メールアドレス（記述式）必須質問
セミナーの満足度（5点スケールで評価）必須質問
最も役に立つと思う内容は？（選択式質問）必須質問
改善点や追加してほしい内容（自由回答の記述式）
またセミナーに参加したいですか？（はい/いいえ）

ステップ２：Googleフォームの回答をスプレッドシートで表示

次に、Googleフォームの「回答」タブをクリックし、回答結果をスプレッドシートで表示できるようにします。

「スプレッドシートで表示」をクリックすると
A列の左からタイムスタンプ、メールアドレスに続いて、先ほど設定したアンケート項目がスプレッドシートが表示されました。

とりあえず、ここまででGoogleフォームの設定はおしまいです。

2.GAS(Google App Script）の設定

それでは次にGASの設定に入っていきます。

ステップ１：Apps Scriptを開く

先ほど表示したGoogleスプレッドシートのメニューバーから
「拡張機能」→「Apps Script」を選択します。

ステップ２：コードの入力

Apps Scriptが開いたら、デフォルトで入力されている部分のコードを削除してください。

同じところに以下のコードをコピペします。

function autoSummarize() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("フォームの回答 1");
  var data = sheet.getDataRange().getValues();
  
  // 各質問の列番号（0から始まるインデックス）
  var questionIndices = {
    email: 1, // メールアドレス
    satisfaction: 2, // セミナーの満足度
    mostUsefulContent: 3, // 最も役に立つと思う内容
    improvement: 4, // 改善点や追加してほしい内容
    repeatAttendance: 5 // またセミナーに参加したいか
  };

  var questionDescriptions = {
    email: "メールアドレス",
    satisfaction: "セミナーの満足度 (1-5)",
    mostUsefulContent: "最も役に立った内容",
    improvement: "改善点や追加してほしい内容",
    repeatAttendance: "再参加希望 (はい/いいえ)"
  };

  // 質問ごとに異なるシートに出力
  var summaries = Object.keys(questionIndices).reduce(function(acc, key) {
    acc[key] = {};
    return acc;
  }, {});

  data.forEach(function(row, index) {
    if (index === 0) return; // ヘッダー行はスキップ
    for (var key in questionIndices) {
      var answer = row[questionIndices[key]];
      var summary = summaries[key];
      if (summary[answer]) {
        summary[answer]++;
      } else {
        summary[answer] = 1;
      }
    }
  });

  // 各質問の集計結果をそれぞれのシートに出力
  for (var key in summaries) {
    var outputSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(key) || 
                      SpreadsheetApp.getActiveSpreadsheet().insertSheet(key);
    outputSheet.clear(); // 既存のデータをクリア
    var summary = summaries[key];
    outputSheet.getRange("A1").setValue(questionDescriptions[key]);
    outputSheet.getRange("B1").setValue("回答数");
    var rowIndex = 2;
    for (var answer in summary) {
      outputSheet.getRange(rowIndex, 1).setValue(answer);
      outputSheet.getRange(rowIndex, 2).setValue(summary[answer]);
      rowIndex++;
    }
  }
}

// トリガー設定は元のスクリプトと同様
function createTrigger() {
  ScriptApp.newTrigger('autoSummarize')
    .forSpreadsheet(SpreadsheetApp.getActive())
    .onFormSubmit()
    .create();
}

このコードは、Googleフォームに回答があった場合、質問ごとにシートを作成し、回答結果を自動で集計してくれるコードです。

細かい機能は以下の通りです。

1.質問ごとのシート作成
各質問ごとに独立したシートで回答を集計し、シートが存在しない場合は新たに作成します。
この時、各シートの最初の行に質問内容と回答数というラベルをヘッダーに表示して、何の集計結果かが分かるように表示してくれます。

2.自動集計
各回答に対して数を数え、シートに質問内容、回答とその回数を記録します。

ちなみに、アンケート内容に応じてカスタマイズする場合は以下の部分を変更してください。

【質問内容とスプレッドシート内の列番号の位置を変えたい場合】
例えば「email: 1, // メールアドレス」は
「emailがスプレッドシートの2列目、ヘッダーにメールアドレスと記載がある状態」という感じになってます。

  // 各質問の列番号（0から始まるインデックス）
  var questionIndices = {
    email: 1, // メールアドレス
    satisfaction: 2, // セミナーの満足度
    mostUsefulContent: 3, // 最も役に立つと思う内容
    improvement: 4, // 改善点や追加してほしい内容
    repeatAttendance: 5 // またセミナーに参加したいか
  };

【集計結果を示すシート名とシート内の説明を変更する場合】
例えば、「satisfaction: "セミナーの満足度 (1-5)",」の部分は「satisfactionというシートを作って、ヘッダーにセミナーの満足度 (1-5)と入力」するようになっています。

  var questionDescriptions = {
    email: "メールアドレス",
    satisfaction: "セミナーの満足度 (1-5)",
    mostUsefulContent: "最も役に立った内容",
    improvement: "改善点や追加してほしい内容",
    repeatAttendance: "再参加希望 (はい/いいえ)"
  };