以下のようなケースを実現したくてフォーム側のスクリプトエディタに書いたコードです
- 特定の質問の選択肢をスプレッドシートから読み込む
- フォーム送信後、選択された値に一致する行のB列にメールアドレスを入れる
- B列に値が入っていないA列の値のみ取得してフォームの選択肢を書き換える
itemId と ss_url を設定して
afterSubmit
をフォーム送信時のトリガーに設定する
コード.gs
var itemId = フォームの質問のID; var ss_url = "https://docs.google.com/spreadsheets/d/SPREADSHEET_ID/edit#gid=0"; var sheet = returnSheet(ss_url); var email = Session.getActiveUser().getEmail(); /************************************ フォーム送信時のトリガーに設定しておく ************************************/ function afterSubmit(e) { var obj = getItemAnswers(e); var value = getTargetAnswer(obj) var sheet = returnSheet(ss_url); var email = e.response.getRespondentEmail(); setValueToSheet(value, email); getUpdatedValuesFromSheet(sheet); } /************************************ シートを返す ************************************/ function returnSheet(ss_url) { var ss = SpreadsheetApp.openByUrl(ss_url); var sh_id = ss_url.split("gid=")[1]; var shs = ss.getSheets(); for (var i = 0; i < shs.length; i++) { if (shs[i].getSheetId() == sh_id) { break; } } return ss.getSheets()[i]; } /************************************ フォームで回答された値をシートに入力する ************************************/ function setValueToSheet(value, email) { var row = findRow(value); Logger.log(row) if(value !== "none") { sheet.getRange(row, 2).setValue(email); } } /************************************ 渡されたvalue, colから対象の行を見つける ************************************/ function findRow(value){ var array = createArray(); var row = array.indexOf(value) + 1; return row; } /************************************ 1行目、1列目からlast_row行、2列の範囲の値を取得して返す ************************************/ function createArray() { var last_row = sheet.getLastRow(); var range = sheet.getRange(1, 1, last_row, 2); var values = range.getValues(); var array = []; for(var i = 0; i < values.length; i++){ array.push(values[i][0]); } return array; } /************************************ 更新されたシートで2列目に値が入っていない1列目の値を配列で返す ************************************/ function getUpdatedValuesFromSheet() { var lastRow = sheet.getLastRow(); var range = sheet.getRange(2, 1, lastRow-1, 2); var values = range.getValues(); var choices = []; for(var i = 0; i < values.length; i++) { if(values[i][1] === "") { choices.push(values[i][0]); } } setChoiceValues(itemId, choices); } /************************************ フォームのitemIdの質問の選択肢を書き換える ************************************/ function setChoiceValues(itemId, values) { var form = FormApp.getActiveForm(); var item = form.getItemById(itemId); if(values.length < 1) { values = ["none"]; } var choices = item.asMultipleChoiceItem().setChoiceValues(values); } /************************************ 送信時の回答を取得して返す ************************************/ function getItemAnswers(e) { FormApp.getActiveForm(); var itemResponses = e.response.getItemResponses(); var obj = {}; for (var i = 0; i < itemResponses.length; i++) { var itemResponse = itemResponses[i]; var item_id = itemResponse.getItem().getId(); var question = itemResponse.getItem().getTitle(); var answer = itemResponse.getResponse(); obj[item_id] = answer; } return obj; } /************************************ itemIdの回答を返す ************************************/ function getTargetAnswer(obj) { var targetAnswer = obj[itemId]; return targetAnswer; } |
補足
残る課題
- 複数人が同時にフォームを開いた場合は、後から開いた方の回答が反映されない
- シートへのリンクをフォームに貼ってシートを直接編集したほうが確実かも
- シートが変更されたときに実行するトリガーでできたらその方が良いかも
関連記事
Google Formの質問項目を取得する
Google Formで質問の選択肢の値を取得したい
Google Formの質問の選択肢をコードで書き換えたい
Google Formの質問項目をスプレッドシートから読み込みたい
Google FormのonOpenトリガーはエディタに対して実行されるらしい