CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsspreadsheet
User9123
User9123

Reputation: 27

Google Apps Script - Making a list of values that fit the criteria

Im new to google apps script and need some help So I have a list of questions of multiple types and I want to pick a specific number of questions of a certain type. My spreadsheet formatting is like this.

A1 = questionID (Q1, Q2 ,Q3)

B1 = questionType (1, 2, 3)

D1 = number of questions required

E1 = type of question required

I can already shuffle the questions to make it randomized, but that requires 2 steps. Is it possible to picking random questions of a type at the same time?

Upvotes: 1

Views: 65

Answers (1)

JSDBroughton
JSDBroughton

Reputation: 4034

This is possible with Sheet formulas and no scripting. 

=query(

    sort(Source!$A$2:$B, arrayFormula(randbetween(sign(row(Source!$A$2:$B)), 1000000)), true),

    "select Col1, Col2 

    where Col1!='' AND Col2="&$E$1&"

    limit "&$E$2&"", 0)

The 3 lines within the Query are:

  1. Seed the query with a randomised sort of all the questions.
  2. Return Column A and Column B (because the query was fed the results of a sort, the alpha column addresses are lost)
  3. Limit to non blank rows where the question type is specified in E1 and limit the returned responses to the number in E2

I have amended your example workbook with a sheet that demonstrates this.

Upvotes: 1

Related Questions