How to fetch data from Google Sheets and show it on Google Forms?

Question

I am making a Google Form and I want to add some questions to it, but those questions should be picked randomly from a pool of questions which will be in a Google Sheet. What needs to be done for this? For Example: I want to show 5 random questions out the 20 questions pool from the Google Sheets

P.s : I did try already mentioned solution on Stackoverflow but it didn't help for my purpose.

Answer 1

To create a script that answers your issue, I created a sample sheet that looks like this:

Where A is the question, and succeeding columns are the options. Note: You can opt to remove this from the sheet if you only have to generate questions without any options. I included options as that would be the worst case in your issue.

So to start, we need to have the IDs for both form and spreadsheet:

var formID = <FORM_ID>;
var ssID = <SPREADSHEET_ID>;
var fData = FormApp.openById(formID);
var wsData = SpreadsheetApp.openById(ssID).getSheetByName("Sheet1");

Next step is to try and generate questions from spreadsheet:

function populateForm() {
  // get all values from spreadsheet
  var ssValues = wsData.getDataRange().getValues();

  // traverse all values
  for(var row = 0; row < ssValues.length; row++){
    var newItem;
    var options = [];
    for(var col = 0; col < ssValues[row].length; col++){
      var cellValue = ssValues[row][col];
      switch(col) {
        case 0:
          // if question, add as item
          newItem = fData.addListItem().setTitle(cellValue).getId(); 
          break;
        default:
          // if not question, add as choice
          options.push(cellValue);
      }
    }
    // add accumulated options as choices for the recently added item
    fData.getItemById(newItem).asListItem().setChoiceValues(options);
  }
}

After this step, you should be able to get all the questions added to the form. Now, since we only need to generate 5 randomized questions, I've added the function below for us to use.

function getFiveRandomQuestions(array){
  // randomly remove questions until 5 remains
  for(var i = array.length - 1; i >= 5; i--){
    array.splice(Math.floor(Math.random() * array.length), 1);
  }
  return array;
}

The function above accepts an array (the result of getValues()) and then remove items from it randomly until only 5 items remain.

Below is the code after integrating all the functions above. Note: I added clearForm() to remove existing items so the form resets whenever I run populateForm(). This was for testing just in case you'll need it to. Feel modify the code as much as you need to.

// Randomize Form from Sheets
var formID = <FORM_ID>;
var ssID = <SPREADSHEET_ID>;
var fData = FormApp.openById(formID);
var wsData = SpreadsheetApp.openById(ssID).getSheetByName("Sheet1");

function clearForm(){
  // clears all items
  var items = fData.getItems();
  while(items.length > 0){
    fData.deleteItem(items.pop());
  }
}

function getFiveRandomQuestions(array){
  // randomly remove questions until 5 remains
  for(var i = array.length - 1; i >= 5; i--){
    array.splice(Math.floor(Math.random() * array.length), 1);
  }
  return array;
}

function populateForm() {
  // call clearForm to prevent appending newly randomized questions
  clearForm();

  var ssValues = wsData.getDataRange().getValues();

  // remove random questions until 5 are remaining
  var formItems = getFiveRandomQuestions(ssValues);

  for(var row = 0; row < formItems.length; row++){
    var newItem;
    var options = [];
    for(var col = 0; col < formItems[row].length; col++){
      var cellValue = formItems[row][col];
      switch(col) {
        case 0:
          // if question, add as item
          newItem = fData.addListItem().setTitle(cellValue).getId(); 
          break;
        default:
          // if not question, add as choice
          options.push(cellValue);
      }
    }
    // add accumulated options as choices for the recently added item
    fData.getItemById(newItem).asListItem().setChoiceValues(options);
  }
}

Here is the sample output:

Note that this answer is a straightforward one and it can still be optimized based on your test case. Feel free to ask questions if anything is unclear to you.

EDIT:

If you want to add hard-coded questions, you need to add it before the loop.

// remove random questions until 5 are remaining
var formItems = getFiveRandomQuestions(ssValues);
    
// start of hardcoded questions
fData.addListItem().setTitle('Name').getId(); 
fData.addListItem().setTitle('Email').getId(); 
// end of hardcoded questions    

for(var row = 0; row < formItems.length; row++){

Also, if you like to have only five questions and already have 2 via hardcode, we need to reduce the randomizer function to 3 instead of 5.

// note that I renamed the function to getRandomQuestions so bear in mind to update the function call too
function getRandomQuestions(array){
  for(var i = array.length - 1; i >= 3; i--){
    array.splice(Math.floor(Math.random() * array.length), 1);
  }
  return array;
}

Sample output: