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.

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

Turning lego into an AR experience

Wind them up and let them go

I have a bit of a reputation around school as the VR and AR guy (I don't know why?) so it was no surprise when a grade 10 student came to me asking for help with his MYP Personal Project. He was in a bit of a panic and sent me this email

He had created several Lego models of buildings in Kuala Lumpur in the Lego Digital Designer program. He has examples of old style buildings and new buildings. The problem was that the Personal Project Exhibition was fast approaching and he didn't have time to order the bricks and build his model. He was hoping he could turn his model into an AR experience.

When I met him I checked out his models (he had put a lot of work into them) then explained that I didn't have a clue how to turn these models into AR. 

We sat together and started searching online for possible solutions (it was a strong example of me being able to model my searching techniques and show me being a learner) after a rather tedious and long search, we came across this post AR augmented reality Lego. There were some sketchy details of how to do it.

We played and finally worked it out

• we had to open the .lxf (lego digital designer) file in the Mecabricks.com website
• download the file from Mecabricks as a .stl file
• on a computer download the edrawingsviewer app and open the .stl file
• save the edrawings version as an .eprt file
• download the edrawings app on an iPhone
• transfer the file to the iPhone (the files can be rather large) open the file in app
• using the downloadable mat from the website bring your model to AR reality

The effect was pretty cool, you could walk around it, zoom in and out, it was AR, while it took a while we did it. The only real issue was that is wasn't a colour version and we needed the mat. The student went away pretty happy. As the AR guy I was feeling pretty chuffed with myself, as per usual when you start getting over confident a dose of humility soon follows.

The best bit

A couple of days later I got this email from the student.

Hi Mr. Derry,

I have been playing around with the different formatting when exporting the files as I had found out that the .stl format takes all the colours out (which was the main problem). After looking through a few ways - I have successfully transferred both my buildings from the Lego Digital Designer, into AR, with colour (it doesn't even need a QR code!). I found another website which supports 3D modelling called Sketchfab, and uploaded the files through a .dae format from Mecabricks, and got Sketchfab on my phone as well, which allows it to be seen in AR and VR. I have attached all the pictures of how they turned out. 

I am very excited to show you everything in school soon (perhaps in innovation tomorrow right after break time), and would like to thank you for all the help you have given me. I wouldn't have managed to pull this out without it! 

Have a great day,

Jordan. 

On his own, he found another way to do it, a better way with colour and no need for a mat or a QR code. You could even go close up and inside the AR model and check out the interior, something you couldn't even do in real life. This is what I love about empowering students with technology, as a teacher I showed him a thing or two and showed him how to search and what is potentially possible. He then took this knowledge and skill and built on it.

I like to call this "wind them up and let them go" get them enthused and excited and watch what they create.

I love doing this with programs like Scratch and GarageBand, just the other day after showing all the grade 5 students live loops,  I had a grade 5 student say to me "Mr D I love live loops in garage band on my iPad. I can't stop playing with it, my parents don't believe I am making such cool music".

We are also doing this in a big way at school through our innovation time, which is 2 blocks per week (the same as every other subject) of passion project time in secondary school, we certainly are winding them up and letting them go. That is a whole other post and we have some great examples to share.

Long may schools and teachers empower students with knowledge, skills and the time to explore their passions.