GAS使ってGoogleFormの内容を動的に書き換える | クズリーマンのカス備忘録

GAS使ってGoogleFormの内容を動的に書き換える

スポンサーリンク

Qiitaの記事で基本的にできた!(ありがとうございます!)

マスターのデータが読み込まれない!?

マスターのデータを上記のQiita記事に従い、スプシに作成していたのだが、読み込まれない、と思って、AIに聞きながら色々調べたら、スプシのアクセス権を設定してなかったことが原因だった。

→画面右上から共有にする。編集権限は不要で、読み取り権限だけあればよい。

もしかしたらそれだけでも動いていたかもしれない。

コードを修正

コードも少し不備があったようで、AIにキキながら修正した。

最終的に動いたコード:

自動削除したい項目があるコントロールが「プルダウンメニュー」の場合

function myFunction() {
  //https://docs.google.com/spreadsheets/d/xxx/editのxxxの部分
  //マスター
  var masterSpreadSheetID = 'あいでぃーをいれる';
  //回答用
  var answerSpreadSheetID = 'あいでぃーをいれる';

  // シート名
  var masterSheetName = 'マスター';
  var answerSheetName = 'フォームの回答';

  // 選択肢を動的にするプルダウンのタイトル
  var questionName = '日程候補';

  // スプレッドシートIDでシートを取得
  var masterSheets = SpreadsheetApp.openById(masterSpreadSheetID);
  var masterSheet = masterSheets.getSheetByName(masterSheetName);

  var answerSheets = SpreadsheetApp.openById(answerSpreadSheetID);
  var answerSheet = answerSheets.getSheetByName(answerSheetName);

  // マスターシートのA行の2行目から下の値を配列で取得
  var sheetLastRow = masterSheet.getLastRow();
  if (sheetLastRow > 1) {
    var candidate = masterSheet.getRange(2, 1, sheetLastRow - 1, 2).getValues();
  } else {
    return;
  }

  // 回答シートの状況も取得(A行の2行目から)
  var answerSheetLastRow = answerSheet.getLastRow();
  if (answerSheetLastRow > 1) {
    var questionNames = answerSheet.getRange(1, 1, 1, answerSheet.getLastColumn()).getValues()[0];
    var colCount = -1;
    for (var i = 0; i < questionNames.length; i++) {
      if (questionNames[i] === questionName) {
        colCount = i;
        break;
      }
    }
    if (colCount >= 0) {
      var answerData = answerSheet.getRange(2, colCount + 1, answerSheetLastRow - 1, 1).getValues();
    } else {
      // questionNameが見つからなかった
      return;
    }
  } 

  // フォーム取得→項目も取得
  var form = FormApp.getActiveForm();
  var items = form.getItems();

  // 動的に選択肢を作成
  items.forEach(function(item){
    // 名前が"日程候補"のプルダウンの項目を
    if(item.getTitle() === questionName){
      var listItemQuestion = item.asListItem();
      var choices = [];

      candidate.forEach(function(nameAndCapacity){        
        if(nameAndCapacity[0] != ""){
          // 定員が0(null) or 無回答 の場合は選択肢を表示
          if (answerData == null || nameAndCapacity[1] == 0 || nameAndCapacity[1] == ""){
            choices.push(listItemQuestion.createChoice(nameAndCapacity[0]));
          } else {
            var counter = 0;
            // 何人埋まってるか確認
            for(var i = 0; i < answerData.length; i++){
              if (nameAndCapacity[0] == answerData[i][0]){
                counter++;
              }
            }
            // 埋まってなければ選択肢を表示
            if (counter < nameAndCapacity[1]){
              choices.push(listItemQuestion.createChoice(nameAndCapacity[0]));
            }
          }
        }
      });

      //選択肢の残数によってFormを受け付けるかどうか判断
      if (choices.length > 0) {
        // フォームの回答を受け付ける
        form.setAcceptingResponses(true);
        listItemQuestion.setChoices(choices);
      } else {
        // 回答受付終了
        form.setAcceptingResponses(false);        
      }
      return;
    }
  });
}

自動削除したい項目があるコントロールが「ラジオボタン」の場合

function myFunction() {
  //https://docs.google.com/spreadsheets/d/xxx/editのxxxの部分
  //マスター
  var masterSpreadSheetID = 'あいでぃーをいれる';
  //回答用
  var answerSpreadSheetID = 'あいでぃーをいれる';

  // シート名
  var masterSheetName = 'マスター';
  var answerSheetName = 'フォームの回答';

  // 選択肢を動的にするプルダウンのタイトル
  var questionName = '日程候補';

  // スプレッドシートIDでシートを取得
  var masterSheets = SpreadsheetApp.openById(masterSpreadSheetID);
  var masterSheet = masterSheets.getSheetByName(masterSheetName);

  var answerSheets = SpreadsheetApp.openById(answerSpreadSheetID);
  var answerSheet = answerSheets.getSheetByName(answerSheetName);

  // マスターシートのA行の2行目から下の値を配列で取得
  var sheetLastRow = masterSheet.getLastRow();
  if (sheetLastRow > 1) {
    var candidate = masterSheet.getRange(2, 1, sheetLastRow - 1, 2).getValues();
  } else {
    return;
  }

  // 回答シートの状況も取得(A行の2行目から)
  var answerSheetLastRow = answerSheet.getLastRow();
  if (answerSheetLastRow > 1) {
    var questionNames = answerSheet.getRange(1, 1, 1, answerSheet.getLastColumn()).getValues()[0];
    var colCount = -1;
    for (var i = 0; i < questionNames.length; i++) {
      if (questionNames[i] === questionName) {
        colCount = i;
        break;
      }
    }
    if (colCount >= 0) {
      var answerData = answerSheet.getRange(2, colCount + 1, answerSheetLastRow - 1, 1).getValues();
    } else {
      // questionNameが見つからなかった
      return;
    }
  } 

  // フォーム取得→項目も取得
  var form = FormApp.getActiveForm();
  var items = form.getItems();

  // 動的に選択肢を作成
  items.forEach(function(item){
    // 名前が"日程候補"のラジオボタンの項目を
    if(item.getTitle() === questionName){
      var radioItemQuestion = item.asMultipleChoiceItem(); // ここを変更
      var choices = [];

      candidate.forEach(function(nameAndCapacity){        
        if(nameAndCapacity[0] != ""){
          // 定員が0(null) or 無回答 の場合は選択肢を表示
          if (answerData == null || nameAndCapacity[1] == 0 || nameAndCapacity[1] == ""){
            choices.push(radioItemQuestion.createChoice(nameAndCapacity[0])); // ここを変更
          } else {
            var counter = 0;
            // 何人埋まってるか確認
            for(var i = 0; i < answerData.length; i++){
              if (nameAndCapacity[0] == answerData[i][0]){
                counter++;
              }
            }
            // 埋まってなければ選択肢を表示
            if (counter < nameAndCapacity[1]){
              choices.push(radioItemQuestion.createChoice(nameAndCapacity[0])); // ここを変更
            }
          }
        }
      });

      //選択肢の残数によってFormを受け付けるかどうか判断
      if (choices.length > 0) {
        // フォームの回答を受け付ける
        form.setAcceptingResponses(true);
        radioItemQuestion.setChoices(choices); // ここを変更
      } else {
        // 回答受付終了
        form.setAcceptingResponses(false);        
      }
      return;
    }
  });
}

定員オーバーしても選択肢から消えない!?→全角スペースのせいだった。

定員オーバーしても選択肢から消えなかった。

結論から言うと、選択肢の文字列に全角スペースがあったのが原因だった。

フォーム送信されたときにアンケート結果を溜めるスプシ「フォームの回答」シートを見てみると、全角スペースのハズの選択肢が、半角スペースに変換されて登録されてた。

対策としては、選択肢から全角スペースをなくすか、プログラムの比較時に全角スペースを半角スペースに変換する。

トリガー > イベントの種類が「フォーム送信時」

上記の記事だと、イベントのタイミングは「フォーム送信時」となっているが、それだと、フォームやスプシのマスターデータの変更を行ってから、変更の内容が即時にフォーム側に反映されず、ユーザーがフォームで送信を行ってから反映されることになる。

自分でフォーム送信すればいい話だが、仕事でやっている場合、お客さん先にメールが行くことになり、事前の連絡が必要。

即座に反映させたい場合、「起動時」の方を選択したい。
ちなみに「起動時」については以下の注意点があるので要注意。

Googleフォームには「起動時」のトリガーがあるが、これは、フォーム回答者がフォームを開いた時ではなく 、フォーム作成者がフォーム編集画面を開いたときに発火するトリガーなので注意。

https://qiita.com/ynaito/items/c21f16c9c612449defc8#%E3%83%95%E3%82%A9%E3%83%BC%E3%83%A0%E8%B5%B7%E5%8B%95%E6%99%82%E3%81%AE%E3%83%88%E3%83%AA%E3%82%AC%E3%83%BC%E3%81%AF%E3%83%95%E3%82%A9%E3%83%BC%E3%83%A0%E7%B7%A8%E9%9B%86%E7%94%BB%E9%9D%A2%E8%B5%B7%E5%8B%95%E6%99%82%E3%81%AA%E3%81%AE%E3%81%A7%E6%B3%A8%E6%84%8F

しかし、「起動時」を選択すると、上記のコードをそのまま使うと、今度は上限のカウントを行わなくなる。そのため、即座に反映させたいが、送信時にGASが動いてくれないので、上限に達したかなどの処理は別に切り分ける必要が出てくる。

GASのトリガーを、以下の2つにすればいい。

  • フォーム送信時
  • 起動時

GASの処理内容が、(スプシの内容を基に)フォーム内容を更新する、というものだから、編集画面起動時およびフォーム送信時両方で発火しても問題ない。
むしろリアルタイムに発火しまくっても良いぐらい。

コメント

タイトルとURLをコピーしました