Google Form random questions from a bank, different every time Form is opened

Question

I have a list of 14 questions in a Google Sheet that I would like to use 5 of at random to populate a Google Form. I have managed to do this using the FormCreator add-on to some degree but I would like the questions to be randomly generated every time the form is opened. At the moment the form populates with 5 random questions but will always stick to the original 5, regardless if the selection changes.

Formula I have used to select the random 5 from my 14 (in Sheets):

=ArrayFormula(Array_Constrain(vlookup(Query({ROW($A$2:$A$16),randbetween(row($A$2:$A$16)^0,9^9)},"Select Col1 order by Col2 Asc"),{row($A$2:$A$16),$A$2:$A$16},2,FALSE),5,1))

I am trying to create one form that will be different every time my students open it, rather than painstakingly creating 100+ 'random' forms manually to be used.

Solution 1

There is currently no way to randomly change the form each time it is opened. Google doesn't provide any mechanisms to dynamically change the form in response to user behaviour, so there is now way of updating or triggering logic when the form is opened.

There are a couple of options that might work.

You could set up a script on a trigger, to run every X minutes, that randomly updates the list of questions.

This approach requires some custom scripting, and would lead to some odd behaviour in the responses sheet as the answer columns won't get re-used the way you might expect.

Another option is to create a bunch of sections with random sets of questions. Say you make 10 or 15 of these sections, each with 5 randomly selected quesdtions. Then, for a first section, you create a select box with a list of randomized numbers, with the question labelled "Choose any number", and set each number option to take the user to a different 5 question section. Then, select the option to "shuffle option order" on that initial question.

This approach doesn't require any scripting, and gives you a kind of pseudo-random process within a single form. It might be suitable depending on your needs.

Solution 2

as an aside, can you alter the formula so that duplicates aren't used? Meaning that all five in the random range are unique?

this way the 5 generated questions would always be unique/non-duplicated

=ARRAYFORMULA(ARRAY_CONSTRAIN(VLOOKUP(QUERY({ROW(A2:A16), 
 RANDBETWEEN(ROW(A2:A16)^0, 9^9)},
 "select Col1 order by Col2"), {ROW(A2:A16), A2:A16}, 2, 0), 5, 1))

but the formula could be shorter:

=ARRAYFORMULA(QUERY({RANDBETWEEN(ROW(A2:A16), 999^99), A2:A16}, 
 "select Col2 order by Col1 limit 5", 0))

Comments

[player0]

well the formula is written correctly so this is more likely a scripting/addon question

[Mr L Fenner]

@player0 as an aside, can you alter the formula so that duplicates aren't used? Meaning that all five in the random range are unique?

[Kessy]

How do you fill the form with the questions with the add-on, or using apps script?

[Mr L Fenner]

@kessy the add-on creates the form

[Mr L Fenner]

@player0 can the random selection formula be altered to present going across the columns horizontally rather than downwards?

[player0]

@MrLFenner sure, just wrap it into TRANSPOSE