Monday, 11 February 2019

Make your own Random Google Sheets worksheets

I finally found a use for Macros

A couple of months ago I started playing with Google Sheets IF Formula and RANDBETWEEN to create interactive google sheet Maths sheet, this was essentially a drill and practice activity. I wanted my students know how to do this and create their own drill and practise activities. The idea was that the students could create maths worksheets that have a random selection of the numbers in the equations.

It was easy enough to set up a sheet using predetermined number and the IF statement to check if the students had the correct answer or not. I added some colour and extra formatting using conditional formatting. I then used COUNTIF to count the number of correct answers. It was all starting to look pretty and come together. Then I introduced RANDBETWEEN to make the numbers random to gamify and increase the longevity of the sheet.

The only problem is that when you use RANDBETWEEN anytime a change is made to the worksheet a new random number is generated. that means when an answer is written the numbers that make up the equation change.
Bitmoji Image

I searched online, checked out add ons, tried pasting values, but couldn't find a way to make the random numbers stick, I started playing with Macros and was almost there when I got distracted by some other bright shiny thing. Someone showed me a few websites that do this automagically. Which was OK but not as good as the students creating their own.

I had put this in the back of my mind until yesterday someone in my PLN on twitter asked for some free drill and practice websites that he could give to the teachers in his school, I suggested getting the students to create their own using Google Sheets, the IF formula and RANDBETWEEN. He reminded me of the issues of the constant changing random numbers. I got back to work.

I created a series of ten random numbers in ten cells (H3 to H12),
I then recorded a Macro that copied the ten cells and pasted the values only into the cell next door. A Macro allows you to record a series of steps or activity that you want to do over and over again on a Google Sheet, it then turns those steps into a script, which you can then run and it will do them for you.

Often to run a script you need to use the tools menu.

But you can also add a script to a Google drawing, so I created a button to randomise the numbers when a user clicks the button the script runs. 

Essentially when the button is pressed the script runs, ten random numbers are copied and their values are pasted into the cells next to the random numbers. Because I used paste values only, it doesn't matter how many time the original cells change those numbers in cells I3 to I12 wont change until the button is pressed again.

I then use those values (I3 to I12) to populate my tables test. The Random numbers stick. I hid the columns with all the data so all the user sees is the equations and a button to change the numbers.

I can the use COUNTIF to calculate the number correct and put that as a fraction on the page.

My next step is to work out a way to time how long it takes to complete the equations and add a super cool pop up message when the user gets 10/10.

For your very own copy of the Google sheet to see all the formulas and play with click here


  1. I am not sure I get every step yet, but this is VERY cool.

    1. and...thanks for sharing your idea and all the steps. Vicki Heupel @33heupel on twitter.