Google Forms / Google Sheets Remove Options Script

Question

I have searched through a number of other answers but haven't quite been able to find a solution. I have a Google Form, which customers use to sign up for volunteer spots on a weekly basis. We fill out all the weeks for the year so customers can go through and sign up for the various weeks they are interested in. Looking for a script that could reference the answers in the linked Google sheet to change the questions on the form.

So if we have

• April 10th
• April 17th
• April 24th

in the list of options on google forms (and our cap is 6 people signed up), if April 10th has 4 people, April 17th has 6 people and April 24th has 1 person, the script would remove the April 17th question from Google forms. I have a count forumla on the Google sheets that sums the total people signed up for each day, so it is a simple range of single reference cells for each corresponding day in the list.

Answer 1

Script should look something like this:

Code:

var ssID = '1kmVW8fbHnRIwcJawPlTJ-hWHNnMuVovtqbsCinTKTKw';
var formID = '1z5kPkGAg9e7K7T5NMKBfa9J9mmx6hNVBBjL5hwPcOOo';
var wsData = SpreadsheetApp.openById(ssID).getSheetByName("data");
var form = FormApp.openById(formID);
var options = ["1 person", "2 person", "3 person", "4 person"];

function main() {
  // get list of responses directly from form instead of sheets
  var formResponses = form.getResponses();
  // get list of date questions
  var dates = wsData.getRange(2, 1, wsData.getLastRow(), 1).getValues().flat().filter(Boolean);
  var counts = {};
  for (var i = 0; i < formResponses.length; i++) {
    var formResponse = formResponses[i];
    var itemResponses = formResponse.getItemResponses();
    for (var j = 0; j < itemResponses.length; j++) {
      var itemResponse = itemResponses[j];
      var question = itemResponse.getItem().getTitle();
      // count only answers for date questions
      if (dates.includes(question)) {
        var answer = parseInt(itemResponse.getResponse().replace(' person',''));
        counts[question] = counts[question] ? counts[question] + answer : answer;
      }
    }
  }
  // get list of questions
  var items = form.getItems();
  var titles = items.map(function (item) {
    return item.getTitle();
  });

  dates = dates.forEach(function (date, index){
    // get position of each date question
    var pos = titles.indexOf(date)
    if (pos !== -1) {
      // get id
      var item = items[pos];
      var itemID = item.getId();
      var item = form.getItemById(itemID);
      // count remaining slots
      var slots = 6 - counts[date];
      // if all slots are already used, remove question
      if(slots == 0) {
        form.deleteItem(item);
      }
      else {
        // set new options up to remaining slots
        var newOptions = options.slice(0, slots);
        item.asListItem().setChoiceValues(newOptions);
      }
      // assign slots used in sheets
      wsData.getRange(index + 2, 2).setValue(counts[date]);
    }
  });
}

data sheet:

Form:

Note:

• Make sure to install onFormSubmit trigger for function main
• What this do is when the form is filled up and submitted, it triggers this function.
• Every form submission should be submitted once, avoid using "Submit another response" as it will use the non-updated form.
• This reads the responses using getResponses instead of reading response sheet
• This will remove the item if there are no remaining slots