LANG SELRCT

Apps Script Reference  (Create: Create new Spreadsheet | Create new Apps Script

Saturday, September 14, 2019

Google Formで一度選択された値を次回フォームを開いた時の選択肢に表示しない


以下のようなケースを実現したくてフォーム側のスクリプトエディタに書いたコードです

  1. 特定の質問の選択肢をスプレッドシートから読み込む
  2. フォーム送信後、選択された値に一致する行のB列にメールアドレスを入れる
  3. 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トリガーはエディタに対して実行されるらしい

Latest post

Extracting data from Google Sheets with regular expressions

Introduction Regular expressions are a powerful tool that can be used to extract data from text.  In Google Sheets, regular expressions ca...