In this post, I demonstrate how to take options on a Google Sheet and populate those options onto Google Forms Questions using Apps Script.
How to Video:
Video Notes:
- Further Information on OnOpen(e) Functions Click Here.
Code in Video:
function openForm(e)
{
populateQuestions();
}
function populateQuestions() {
var form = FormApp.getActiveForm();
var googleSheetsQuestions = getQuestionValues();
var itemsArray = form.getItems();
itemsArray.forEach(function(item){
googleSheetsQuestions[0].forEach(function(header_value, header_index) {
if(header_value == item.getTitle())
{
var choiceArray = [];
for(j = 1; j < googleSheetsQuestions.length; j++)
{
(googleSheetsQuestions[j][header_index] != '') ? choiceArray.push(googleSheetsQuestions[j][header_index]) : null;
}
item.asMultipleChoiceItem().setChoiceValues(choiceArray);
// If using Dropdown Questions use line below instead of line above.
//item.asListItem().setChoiceValues(choiceArray);
}
});
});
}
function getQuestionValues() {
var ss= SpreadsheetApp.openById('1GeFzNR-UoFl9xbla8E1Ditsdr49UnrCmyGRr-6m9wNw');
var questionSheet = ss.getSheetByName('Questions');
var returnData = questionSheet.getDataRange().getValues();
return returnData;
}
Related Posts
How to Create an Email Notification on Google Form Submit - In this post, I demonstrate how to create an email notification on Google Form Submit using Google Apps Script.
Google Form – Using Apps Script to Populate Google Sheet - In this post, I take a Google Form and populate a Google Sheet by using Google Apps Script. The Apps Script adds versatility in populating a Google Sheet.