Learn how to read the Google form question choices from a spreadsheet (if you have separate sections)

I want to read the Google form question choices (pull-down expressions) from the spreadsheet, so I create them in the script editor as follows:

The second question can be reflected, but the third question cannot be reflected.
The third question is in a different section, and I am at a loss because I do not know how to rewrite the code.Please let me know if anyone knows.

Thank you for your cooperation.

function updateForm(){
    // Obtain ID from URL of Google form
    // Something like this: "xxxxxxxxxx-xxxxxxxxxxxxx-xxxxxxx"
    varformId="xxxxxxxxx-xxxxxxxxx-xxxxxxx"
        constformFile=FormApp.openById(formId);
    variables = formFile.getItems();

    varsId="xxxxxxxxxx-xxxxxxxxxxxxxxxxxxx"; // Specify ID for spreadsheet
    varsName="category";// Specify sheet name for spreadsheet
    varsheet=SpreadsheetApp.openById(ssId).getSheetByName(ssName);
    // Get the bottom line number on the sheet
    var last_row =sheet.getLastRow();
    Logger.log(sheet);

    // Synchronize the first question with the contents of the spreadsheet
    varitemOfService=items[0];
    // 2nd row, 1st column, 1 cell retrieval
    var sitelistNames=dropNullItemFromArray(sheet.getRange(2,1,last_row,1).getValues());
    var sitelist=[];
    for (vari=0;i<sitelistNames.length;i++) {
        sitelist[i] = sitelistNames[i]
    }

    // Reflect the first question
    itemOfService.asListItem().setChoiceValues(sitelist).setRequired(true);

    // Synchronize the second question with the contents of the spreadsheet
    varitemOfService=items[1];
    var sitelistNames=dropNullItemFromArray(sheet.getRange(2,6, last_row,1).getValues());
    var sitelist=[];
    for (vari=0;i<sitelistNames.length;i++) {
        sitelist[i] = sitelistNames[i]
    }

    // Reflect the second question
    itemOfService.asListItem().setChoiceValues(sitelist).setRequired(true);

    // Synchronize the third question with the contents of the spreadsheet
    varitemOfService=items[2];
    var sitelistNames=dropNullItemFromArray(sheet.getRange(3,3, last_row,1).getValues());
    var sitelist=[];
    for (vari=0;i<sitelistNames.length;i++) {
        sitelist[i] = sitelistNames[i]
    }

    // Reflect the third question
    itemOfService.asListItem().setChoiceValues(sitelist).setRequired(true);

}

// A function that eliminates empty arrays in a good way
function dropNullItemFromArray(array){
    var new_array=[];
    array.forEach(function(value){
        if(value!=null&value!=""){
            new_array.push(value);
        }
    });
    return new_array;
}

google-apps-script

2022-09-30 20:11

1 Answers

From the Google Form you provided, I found out that you are using PAGE_BREAK.In this case, if you modify the script displayed, try modifying it as follows:

From:

varitemOfService=items[2];

To:

varitemOfService=items[3];

"PAGE_BREAK" is also an Item object, so itemOfService.asListItem().setChoiceValues(sitelist).setRequired(true); may have encountered an error.This fix will skip "PAGE_BREAK", which should avoid errors.

Proposal

I don't know if it will be helpful, but I thought that I might be able to modify the script as follows from the script shown and the Google Form and Google Spreadsheet you provided.I thought it would be useful to process Item objects in a loop.I hope it will be helpful.

function sample(){
  Const formId = "###"; // Set Form ID.
  Please set the constsId="###";// Spreadsheet ID.

  // Retrieve values from spreadsheet
  const sheet = SpreadsheetApp.openById(ssId).getSheetByName("sheet1");
  const[,...v] = sheet.getDataRange().getValues();
  
  // transposition
  const values=v[0].map(_,c)=>v.map(r=>r[c]));

  // Set the question to Google Form.At this time, PAGE_BREAK skips.
  constformFile=FormApp.openById(formId);
  letc = 0;
  formFile.getItems().forEach(e=>{
    if(e.getType()!=FormApp.ItemType.PAGE_BREAK) {//or if(e.getType()==FormApp.ItemType.LIST){
      e.asListItem().setChoiceValues(values[c++]).setRequired(true);
    }
  });
}

Reference

Enum ItemType

2022-09-30 20:11

If you have any answers or tips

Popular Tags

python x 4647

android x 1593

java x 1494

javascript x 1427

c x 927

c++ x 878

ruby-on-rails x 696

php x 692

python3 x 685

html x 656