How to use a for loop to check answers against answer key?


I’m creating a spreadsheet for my pupils to practise their times table fluency. The pupils have five minutes to complete the 1-12 times tables. If they get full marks they have “busted” the level and graduate onto level 4 speedy table which have decimals and fractions as well as integers.

My primary aim is to write scrip to check the pupils answers from the ‘question’ sheet against the answer key on ‘answers’ sheet. Correct answers increment the score variable by one. I believe the way to achieve this is to use a for loop to scroll through the 12 columns then drop down to the next row until all the answers have been checked. However, my inadequate knowledge of for loops prevents me from completing the script.

My secondary aims are to add a ‘randomise’ button to change the order of the horizontal 1-12 and to add a button that counts down “3, 2, 1 go!” then starts a five minute countdown timer. After the countdown timer finishes it locks the sheet and returns the score.

I have left the script open to be edited via the link. I’d be really grateful for any help the community could offer me. Although I have opened the script up to be viewed by the public the script seems to be inaccessible so here’s a copy of my script.

function checkAnswers(){

var app = SpreadsheetApp;
var ss = app.getActiveSpreadsheet();
var activeSheet = ss.getActiveSheet();


var score = activeSheet.getRange(“Q11”).getValue();
var resultMessage = activeSheet.getRange(“Q12”).getValue();

if (score == 144) {

var answer = app.getActiveSheet(“questions”);
var answerKey = app.getActiveSheet(“answers”);
answerKey = app.getActiveRange(“C3”);
answer = app.getActiveRange(“C3”);

if (answer == answerKey){
(score + 1)

//for (var c = 0;c<11;c++){ } creating a loop tutorial