One option I could suggest is to create a container-bound script in your Google Sheets to update the questions for multiple Google Forms using Apps Script.
Example Sheet:
We have a Google Sheet with 2 Sheets named "Questions" and "Forms" respectively.
Sheet:Questions contains the list of questions available in your Google Forms. To Update column can be used as a flag to determine which questions you want to update in the Google Forms.
Sheet:Forms contains the list of Google Forms Ids' where you want the questions to be updated.
Example Apps Script Code:
function updateFormsQuestions() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var formsSheet = ss.getSheetByName("Forms");
var questionsSheet = ss.getSheetByName("Questions");
//Get the list of forms to update
var formsList = formsSheet.getRange(2,1,formsSheet.getLastRow()-1).getValues();
Logger.log(formsList);
//Get the list of questions to update
var questionIndex = [];
var qCheckbox = questionsSheet.getRange(2,2,questionsSheet.getLastRow()-1,1).getValues();
Logger.log(qCheckbox);
qCheckbox.forEach(function(currentValue, index){
if(currentValue[0] == true)questionIndex.push(index);
});
Logger.log(questionIndex);
//Open Form and get all items
for( var i = 0; i < formsList.length; i++){
var forms = FormApp.openById(formsList[i][0]);
var items = forms.getItems();
//Update question
questionIndex.forEach(function(index){
Logger.log(index);
Logger.log(questionsSheet.getRange(index + 2, 1).getValue());
items[index].setTitle(questionsSheet.getRange(index + 2, 1).getValue());
});
}
}
What it Does?
- Get the list of Google Forms Ids' under
Sheet:Forms
- Get all checkbox values under
Sheet:Questions
starting from Cell B2
.
- Check each checkbox values if set to true, then push the current index to the
questionIndex
array. (Note that in javascript array index starts from zero).
- Open each forms listed in step 1, one-by-one using for-loop using FormApp.openById(). Then get all items of the current forms using .getItems(). It will give you an array of items available in your form (this may represent your questions in your form).
Forms items can be accessed as an array. Hence it should start from zero. (Question 1 = index 0, Question 2 = index 1 ... so on)
- Update the questions in the current item using .setTitle(). The new title will be based on the cell value under Sheet:Questions column A. You can get the value of the cell using .getValue() assuming you already selected the correct cell range using .getRange().
Notice that we increment the row index by 2 when getting the cell value questionsSheet.getRange(index + 2, 1).getValue()
. Cell rows and columns starts from 1 in sheets, if we want to access Cell A3, row = 3; col = 1
. The index that we saved earlier in Step 3 starts from zero (since we used an array). If you are curious why we increment 2 instead of 1. The reason is because we also adjusted the indexing when we start getting the checkbox values from row 2 instead of row 1 in Step 2.
Sheet:Questions
Column C will show you the indexing of the qCheckbox
array, while Column D will show you the indexing of sheets when accessing rows.
Output:
BEFORE:
AFTER:
Additional References:
Forms Service - allows scripts to create, access and modify Google Forms
Spreadsheet Service - allows scripts to create, access, modify Google Sheets
与恶龙缠斗过久,自身亦成为恶龙;凝视深渊过久,深渊将回以凝视…