Teacher Tech blog with Alice Keeler

Paperless Is Not a Pedagogy

Alice Keeler

Google Sheets: Randomize Google Form Results

Google Sheets: Randomize Google Form Results

Randomize results (1)

Sometimes I have need to randomize the results from a Google Form. For example, I had students submit links to their digital portfolios. I want the other students to view the portfolios. However, if the results are not randomized the first few in the list will be viewed many times. The students at the bottom of the list will not be viewed at all.

Sorting by a dynamic random number causes the results to continuously sort randomly.

Google Sheets

The results of a Google Form go to a Google Sheets spreadsheet. Locate the spreadsheet.

Insert a Random Column

Insert a column into the Form results on the spreadsheet. Insert the column next to the column you wish to randomly sort.
Name the column “Random.”
In the cells type the formula =rand().
Each row of data will need to have a random number assigned.
rand

Insert a Sheet

Click the plus icon in the bottom left-hand corner to insert a sheet.
Plus Icon

Header Columns

In row 1 type the header columns for the data you wish to sort and display. Include the random column. Freeze the first row to lock in place.
column headers

Sort Function

Use the =sort() function to sort the data from the Form responses tab. In cell A2 of the new sheet, type the formula:

=sort(‘Form Responses 1’!B:C,2,true)

=sort()

=sort(‘Tab Name’!Range, sort column, true)
The sort formula first lists the name of the tab where the data is being pulled from. The tab name is in single quotations. An exclamation mark follows the tab name. The range of data to be sorted comes next. Note that the range B:C says to sort everything in columns B to C. After the comma is the column you wish to sort by. In my data column B has the data and column C has the random number. I want to sort by random number. Column B is the 1st column in the range and column C is the 2nd column in the range. Thus, 2 is the sort column. True sorts ascending, False sorts descending.

Hide Form Results

On the Form Responses tab, click on the arrow to “Hide sheet.”
Hide Sheet

This leaves only the tab with the sorted information. Click here for a sample.

© 2024 All Rights Reserved.

💥 FREE OTIS WORKSHOP

Join Alice Keeler, Thursday Mar 28th or register to gain access to the recording.
Create a free OTIS account.

Join Alice Keeler for this session for a way to create dynamic and interactive digital lessons. The Desmos platform is completely free and allows for any topic to be created or customized.

Exit this pop up by pressing escape or clicking anywhere off the pop up.