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.

Students Prepare to Present

Help Your Students Prepare to Present

Maximize student success in presentations with ‘Speaker Notes by AliceKeeler,’ the ideal Google Slides add-on. Enhance how students prepare to present with easy transfer of speaker notes to Google Docs, promoting effective communication skills. Dive into the world of engaging, technology-aided presentations and empower students to shine in their academic endeavors. Discover the key to transforming student presentations into interactive, skill-building experiences.

Read More »

Infographic 7 Basic Steps for a Google Form

New to using Google Forms? This tool is essential for teachers to not only save time but to be adaptive to student needs. Use Forms to survey students, play games, personalize instruction, and assessment. This infographic on the 7 basic steps for a Google Form will help you get started.

Read More »

Online Workshop: Get Your Add-on Published

How can you make Google Workspace (Docs/Sheets/Slides) even better? You can create custom Add-ons with Google Apps Script. Wondering How to Get Your Google Workspace Add-on Published? Join Alice Keeler, Google Developer Expert, to learn the steps to get your Add-on officially published.

Read More »

Leave a Reply

This site uses Akismet to reduce spam. Learn how your comment data is processed.

%d