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);
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
}