やりたいこと

前回記事でGoogleフォームの解答内容の結果をメール送信する仕組みをGASで作りました。
ただ前回のままでだと変更が発生したときに毎回GASを書き換える必要があるため、よく設定しそうな項目はスプレッドシートで設定できるようにします。

正解の選択肢をスプレッドシートで管理

設定を記載するスプレッドシートを作成します。
今回はGoogleフォームから作成できるスプレッドシートを利用しようと思います。（別のスプレッドシートを用意しても良いです。）

スプレッドシートに選択肢の正解を記載

作られたスプレッドシートにシートを1枚追加します。ここでは 回答 というシート名にしました。
そこに質問と全ての選択肢を記載し、正解の選択肢のセル背景色を白以外で塗ります。

それではGASを書いていきましょう。
とその前に前回はシンプルにコードを書くようにしましたが、今回はフォームの質問が増えても対応できるようforループでフォームの回答を取得するようにします。
それに伴い前回は q1 q2 q3 変数にフォームの回答を格納していましたが、今回は answers 配列に全て格納します。

コード.js

function getFormData(e) {
  // フォームからメールアドレスを取得
  const email = e.response.getItemResponses()[0].getResponse();

  // フォームから回答を取得
  // 回答を格納する配列
  let answers = [];
  // 質問の数だけループ
  for(let i = 1; i < e.response.getItemResponses().length; i ++) {
    // フォームの回答を配列に格納
    answers.push(e.response.getItemResponses()[i].getResponse());
  }

  // メール件名
  const mailTitle = '結果通知';

  // 判定してメール本文を取得
  const message = judgement(answers)

  // 結果をメールで送信
  GmailApp.sendEmail(email, mailTitle, message);
}

judgement(answers) で引数の渡し方が変数から配列に変わったのでjudgement関数側も変更が必要なのですが、今回は判定のための正解の選択肢を直接GASには書かずスプレッドシートの値を使うためjudement関数は最初から書き直します。

フォームの回答を取得

まずjudgement関数でanswer配列の中身を正しく取得できるようにしましょう。

コード.js

function judgement(answers) {
  for(let i = 0; i < answers.length; i++) {
    console.log(answers[i]);
  }
}

フォーム送信でログに正しく選択肢が出ていますね？

スプレッドシートから正解の選択肢を取得

次に正解が書いてあるスプレッドシートを取得します。このGASはスプレッドシートのGASではないため、スプレッドシートの場所を教えてあげる必要があります。
SpreadsheetApp.openByUrl(スプレッドシートのURL) でスプレッドシートを指定できます。
またシートが複数あるのでシートも指定する必要があります。
シートは スプレッドシートオブジェクト.getSheetByName(シート名) で指定出来ます。

さてセルの内容が正しく取得できること試してみましょう！
・・・の前に、テストのためにいちいちフォーム送信するのが面倒なので、一旦先ほど書いた answers 配列の取得する部分はコメントアウトして実行しないようにします。
これでApps Scriptエディタから実行してもエラーなしにスプレッドシート取得のテストができます。
またスプレッドシートへの初回アクセスになるためアクセス許可が求められます。

コード.js

function judgement(answers) {
  const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/..（省略）');
  const sh = ss.getSheetByName('回答');
  const values = sh.getRange(1, 2, 3, 2).getValues();
  Logger.log(values);

  // ここをコメントアウトしないと「TypeError: Cannot read property 'length' of undefined」エラーになってしまう
  //for(let i = 0; i < answers.length; i++) {
  //  console.log(answers[i]);
  //}
}

GAS実行で、実行ログに [[選択肢 1, 選択肢 2], [選択肢 1, 選択肢 2], [選択肢 1, 選択肢 2]] と表示されスプレッドシートの内容が取得できているはずです。
では次にスプレッドシートのセル背景色を取得しましょう。
取得は シートオブジェクト.getRange(セル範囲).getBackground() で出来ます。

コード.js

function judgement(answers) {
  const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/..（省略）');
  const sh = ss.getSheetByName('回答');
  // 選択肢セル全ての背景色を取得
  Logger.log(sh.getRange(1, 2).getBackground());
  Logger.log(sh.getRange(1, 3).getBackground());
  Logger.log(sh.getRange(2, 2).getBackground());
  Logger.log(sh.getRange(2, 3).getBackground());
  Logger.log(sh.getRange(3, 2).getBackground());
  Logger.log(sh.getRange(3, 3).getBackground());

  // 確認のためにフォーム送信しなくて良いように一旦コメントアウト
  //for(let i = 0; i < answers.length; i++) {
  //  console.log(answers[i]);
  //}
}

B列の選択肢 1のセルは #fff2cc C列の選択肢 2のセルは #ffffff とログ出力されるはずです。
今度は色つきセルの選択肢だけを集めて表示してみましょう。

コード.js

function judgement(answers) {
  const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/..（省略）');
  const sh = ss.getSheetByName('回答');

  // 正解の選択肢格納用配列
  let corrects = [];
  // セルに色がついているかループで判定
  for(let i = 1; i < answers.length + 1; i++) {
    for(let j = 2; j < 4; j++) {
      // セルの背景色が白（#ffffff）以外なら下を実行
      if('#ffffff' != sh.getRange(i, j).getBackground()) {
        // セルに色がついていたら配列に格納
        corrects.push(sh.getRange(i, j).getValue());
      }
    }
  }
  console.log(corrects);

  // 確認のためにフォーム送信しなくて良いように一旦コメントアウト
  //for(let i = 0; i < answers.length; i++) {
  //  console.log(answers[i]);
  //}
}

今回のGASでは answers.length を使っていて、フォーム送信をしないとanswerが取得できないためフォーム送信をしてテストをします。
その際、先ほどスプレッドシートのアクセス許可をしましたが、フォーム送信時トリガーに対してもスプレッドシートのアクセス許可が必要になります
フォーム送信時トリガーを編集で開き、何もしないまま保存してアクセス許可を通しておきましょう。

フォームの回答と正解の選択肢に比較

色つきセルの選択肢（＝正解の選択肢）をうまく集められたらanswers配列と比較してみましょう。
どちらの配列も質問順に格納されているので、シンプルに比較するだけでOKです。

コード.js

function judgement(answers) {
  const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/..（省略）');
  const sh = ss.getSheetByName('回答');

  // 正解の選択肢格納用配列
  let corrects = [];
  // セルに色がついているかループで判定
  for(let i = 1; i < answers.length + 1; i++) {
    for(let j = 2; j < 4; j++) {
      if('#ffffff' != sh.getRange(i, j).getBackground()) {
        // セルに色がついていたら配列に格納
        corrects.push(sh.getRange(i, j).getValue());
      }
    }
  }

  // 正解数
  let score = 0;
  // フォーム回答と色つきセルの選択肢を比較
  for(let i = 0; i < answers.length; i++) {
    if(answers[i] == corrects[i]) {
      // フォーム回答と色つきセルの選択肢が一致したら正解数+1
      score += 1;
    }
  }
  console.log(score)
}

これで判定ができ正解数が数えられました！

スプレッドシートでメール文章を管理

前回はこの正解数に対してメッセージをGASで書いていましたが、メッセージを変えたいこともあると思うのでスプレッドシートでメール文章を管理するようにしましょう。
新しくシートを作成し、今回はメールというシート名にしました。
作成したメールシートは以下の内容にしました。

B1セルが送信するメールの件名、A4セルとB4セルがメール文章です。

GASでメール情報を取得

ではGASでメールシートの値を取得します。
今まで書いたGASの関数に書くとテストが面倒なので、今回は別の関数で書きます。

コード.js

function mailData() {
  const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/..（省略）');
  const sh = ss.getSheetByName('メール');

  // メール件名のB1セルの値を取得
  const mailTitle = sh.getRange('B1').getValue();
  // メール文章のA4セルとB4セルの値をまとめて取得
  const mailText = sh.getRange(4, 1, 1, 2).getValues();
  Logger.log([mailTitle, mailText]);
}

はい。GASを実行してみると実行ログに [結果通知, [[合格です！, 不合格です..]]] と出てますね。
あ、そうだ！
2問以上正解で合格 という合格基準も変わるかもしれないので、ここもスプレッドシートでいじれるようにしましょう。
スプレッドシートのA3セルの 2問以上正解 の入力値を 2 だけにして、スプレッドシートの表示形式の詳細設定で表示の仕方を変えましょう。

カスタム数式形式に 0問以上正解 と入力し適用を押します。
これで見た目は先ほどと同様で、実態はただの2になりました。
B4セルも同じようにしておきましょう。

それではGASに戻って、先ほどのメール情報取得にA3セルの合格基準も取得するようにしましょう。
ついでに最後に結果をreturnで返すようにしておきましょう。

コード.js

function mailData() {
  const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/..（省略）');
  const sh = ss.getSheetByName('メール');

  // 合格基準のA3セルの値を取得
  const border = sh.getRange('A3').getValue();
  // メール件名のB1セルの値を取得
  const mailTitle = sh.getRange('B1').getValue();
  // メール文章のA4セルとB4セルの値をまとめて取得
  const mailText = sh.getRange(4, 1, 1, 2).getValues();

  return [border, mailTitle, mailText];
}

正解数からメール内容を決定

ではこれを judgement(answers)関数の中で呼んで、scoreと比較して合否を決定しましょう。

コード.js

function judgement(answers) {

  （省略）

  // 正解数
  let score = 0;
  // フォーム回答と色つきセルの選択肢を比較
  for(let i = 0; i < answers.length; i++) {
    if(answers[i] == corrects[i]) {
      // フォーム回答と色つきセルの選択肢が一致したら正解数+1
      score += 1;
    }
  }

  // スプレッドシートのメールシートの内容を取得
  const border = mailData()[0];
  const mailTitle = mailData()[1];
  const mailText = mailData()[2];

  // 正解数とスプレッドシートの合格基準を比較
  if(score >= border) {
    console.log('合格です！');
  } else {
    console.log('不合格です..');
  }
}

ひとまず正解数で生成する結果メッセージはGASに埋め込みました。
うまくいくか試してみましょう。

次にスプレッドシートのメール文章を結果メッセージにしましょう。

コード.js

function judgement(answers) {

  （省略）

  // 正解数
  let score = 0;
  // フォーム回答と色つきセルの選択肢を比較
  for(let i = 0; i < answers.length; i++) {
    if(answers[i] == corrects[i]) {
      // フォーム回答と色つきセルの選択肢が一致したら正解数+1
      score += 1;
    }
  }

  // スプレッドシートのメールシートの内容を取得
  const border = mailData()[0];
  const mailTitle = mailData()[1];
  const mailOkText = mailData()[2][0][0];
  const mailNgText = mailData()[2][0][1];

  let mailText = '';
  // 正解数とスプレッドシートの合格基準を比較
  if(score >= border) {
    mailText += mailOkText;
  } else {
    mailText += mailNgText;
  }

  console.log(mailText);
}

フォームを送信した結果、正しい結果メッセージが出ましたか？
問題なければjudgement(answers)関数としてメール件名とメール本文を返したいので、最後の　console.log(mailText); を return [mailTitle, mailText]; に変えておきましょう。

結果をメールで送信

ついにメール送信に必要なデータが揃いました！
judgement(answers)関数で生成したメール件名とメール文章をgetFormData(e)関数に取り込みメールを送信しましょう。
getFormData(e)関数で固定のメール件名を書いていましたが不要なので消します。

コード.js

function getFormData(e) {
  // フォームからメールアドレスを取得
  const email = e.response.getItemResponses()[0].getResponse();

  // フォームから回答を取得
  // 回答を格納する配列
  let answers = [];
  // 質問の数だけループ
  for(let i = 1; i < e.response.getItemResponses().length; i ++) {
    // フォームの回答を配列に格納
    answers.push(e.response.getItemResponses()[i].getResponse());
  }

  // メール件名
  const mailTitle = judgement(answers)[0];
  // 判定してメール本文を取得
  const message = judgement(answers)[1];
  // 結果をメールで送信
  GmailApp.sendEmail(email, mailTitle, message);
}

完成です！

まとめ

コードを全て書くと

コード.js

function getFormData(e) {
  // フォームからメールアドレスを取得
  const email = e.response.getItemResponses()[0].getResponse();

  // フォームから回答を取得
  // 回答を格納する配列
  let answers = [];
  // 質問の数だけループ
  for(let i = 1; i < e.response.getItemResponses().length; i ++) {
    // フォームの回答を配列に格納
    answers.push(e.response.getItemResponses()[i].getResponse());
  }

  // メール件名
  const mailTitle = judgement(answers)[0];
  // 判定してメール本文を取得
  const message = judgement(answers)[1];
  // 結果をメールで送信
  GmailApp.sendEmail(email, mailTitle, message);
}

function judgement(answers) {
  const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1akDoqYXVHWvw4_ZbPGEBawcMW3Sx0Dr3kTQHSehEAdk/edit#gid=2076483290');
  const sh = ss.getSheetByName('回答');

  // 正解の選択肢格納用配列
  let corrects = [];
  // セルに色がついているかループで判定
  for(let i = 1; i < answers.length + 1; i++) {
    for(let j = 2; j < 4; j++) {
      if('#ffffff' != sh.getRange(i, j).getBackground()) {
        // セルに色がついていたら配列に格納
        corrects.push(sh.getRange(i, j).getValue());
      }
    }
  }

  // 正解数
  let score = 0;
  // フォーム回答と色つきセルの選択肢を比較
  for(let i = 0; i < answers.length; i++) {
    if(answers[i] == corrects[i]) {
      // フォーム回答と色つきセルの選択肢が一致したら正解数+1
      score += 1;
    }
  }

  // スプレッドシートのメールシートの内容を取得
  const border = mailData()[0];
  const mailTitle = mailData()[1];
  const mailOkText = mailData()[2][0][0];
  const mailNgText = mailData()[2][0][1];

  let mailText = '';
  // 正解数とスプレッドシートの合格基準を比較
  if(score >= border) {
    mailText += mailOkText;
  } else {
    mailText += mailNgText;
  }

  return [mailTitle, mailText];
}

function mailData() {
  const ss = SpreadsheetApp.openByUrl('https://docs.google.com/spreadsheets/d/1akDoqYXVHWvw4_ZbPGEBawcMW3Sx0Dr3kTQHSehEAdk/edit#gid=2076483290');
  const sh = ss.getSheetByName('メール');

  // 合格基準のA3セルの値を取得
  const border = sh.getRange('A3').getValue();
  // メール件名のB1セルの値を取得
  const mailTitle = sh.getRange('B1').getValue();
  // メール文章のA4セルとB4セルの値をまとめて取得
  const mailText = sh.getRange(4, 1, 1, 2).getValues();

  return [border, mailTitle, mailText];
}

前回記事で作ったフォームの結果をメールで送る仕組みの設定をスプレッドシートで行うようにできました！
これはなかなか使えるのではないでしょうか。と自分では思っています自己満足大事
それではお疲れ様でした