google-apps-scriptgoogle-formsgoogle-forms-api

How to set the ability to go to section based on answer for Google Forms using script?


I already have the below code working, but can't seem to figure out how to set all but the last answer to "Go to next section" and the last answer to "Submit form".

function updateFormChoices() {
  try {
    // Spreadsheet info
    var spreadsheetId = "Spreadsheet ID here";
    var sheetName = "Sheet1";
    var columnBValues = getSpreadsheetValues(spreadsheetId, sheetName, "B");

    // Form info
    var formId = "Form ID here";
    var questionTitle = "Which of the following streams do you prefer over the other?";
    
    // Get the form
    var form = FormApp.openById(formId);

    // Get the question item
    var items = form.getItems(FormApp.ItemType.LIST);
    
    // Find the question by title
    for (var i = 0; i < items.length; i++) {
      if (items[i].getTitle() === questionTitle) {
        var item = items[i].asListItem();
        break;
      }
    }

    // Log information
    console.log("Column B Values: " + columnBValues.join(", "));
    
    // Update choices
    item.setChoiceValues(columnBValues);
  } catch (error) {
    console.error("Error: " + error.toString());
  }
}

function getSpreadsheetValues(spreadsheetId, sheetName, column) {
  var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
  var values = sheet.getRange(column + "1:" + column + sheet.getLastRow()).getValues();
  return values.flat().filter(String); // Flatten the array and remove empty values
}

I was using ChatGPT to come up with it as I really don't know much about this stuff, but it gave me the following code and I was hoping it would give me the go to section based on answer options, continuing to next section for all answers other than the last, and submitting form for the last.

    // Create choices with page navigation settings
    var choices = [];
    for (var j = 0; j < columnBValues.length; j++) {
      var choice = item.createChoice(columnBValues[j]);
      if (j < columnBValues.length - 1) {
        // Continue to next section for all choices except the last one
        choice.getPageNavigationItem().setGoToPage(FormApp.PageNavigationType.CONTINUE);
      } else {
        // Submit form for the last choice
        choice.getPageNavigationItem().setGoToPage(FormApp.PageNavigationType.SUBMIT);
      }
      choices.push(choice);
    }

    // Set the choices
    item.setChoices(choices);

Solution

  • I found a way to do what I wanted, I used item.setChoices(choices) with choices being item.createChoice(<choices directed from spreadsheet>, navigationType) and navigationType being automatically set to FormApp.PageNavigationType.SUBMIT for the last one (the -1) and FormApp.PageNavigationType.CONTINUE for the rest.

    function updateFormChoices() {
      try {
        // Spreadsheet info
        var spreadsheetId = "<Your spreadsheet ID here>";
        var sheetName = "Sheet1";
        var columnBValues = getSpreadsheetValues(spreadsheetId, sheetName, "B");
    
        // Form info
        var formId = "<Your form ID here>";
        var questionTitle = "Which of the following streams do you prefer over the other?";
        
        // Get the form
        var form = FormApp.openById(formId);
    
        // Get the question item
        var items = form.getItems(FormApp.ItemType.LIST);
        
        // Find the question by title
        var item;
        for (var i = 0; i < items.length; i++) {
          if (items[i].getTitle() === questionTitle) {
            item = items[i].asListItem();
            break;
          }
        }
    
        // Create choices with navigation types
        var choices = [];
        for (var j = 0; j < columnBValues.length; j++) {
          var navigationType = j === columnBValues.length - 1 ?
            FormApp.PageNavigationType.SUBMIT :
            FormApp.PageNavigationType.CONTINUE;
    
          choices.push(item.createChoice(columnBValues[j], navigationType));
        }
    
        // Set choices
        item.setChoices(choices);
    
      } catch (error) {
        console.error("Error: " + error.toString());
      }
    }
    
    function getSpreadsheetValues(spreadsheetId, sheetName, column) {
      var sheet = SpreadsheetApp.openById(spreadsheetId).getSheetByName(sheetName);
      var values = sheet.getRange(column + "1:" + column + sheet.getLastRow()).getValues();
      return values.flat().filter(String); // Flatten the array and remove empty values
    }