Google Sheets Noughts And Crosses

Inspiration

I don't know why I thought this but I saw Google Sheets had a scripting environment and decided to make games in it. Noughts and Crosses sounded simple enough so I set to work making it in Google Sheets. I realised a bonus to this is it also now works multiplayer online.

This is written in Google Apps Script which is very similar to JavaScript.

Step 1: Creating the board

To start with I created a simple 3 by 3 board in a blank sheet by resizing the rows to make a simple grid in the cells A1:C3. I made it by storing the board state separately from the board so you cant just write over it.

So to store the board I use the bit below. In Row one are some letters to label it, A-C are row 1 of the board, D-F are row 2 of the board and G-I are row 3 of the board. In row 2 I store the state of each block, 1 being an X, 2 being a O and 0 being blank. In Row 3 I store whether there is anything in that block (whether you can play there or not). The db: 1 is just whether debug mode is on or not as it is possible hide these cells if it isn't. I also store whose turn it is (1 or 2 and whether someone has won or not.)

All of this information is used to run the game but not to play it so we can hide it later. In the bottom left I store a chart of player names and their symbol (X or O). It also shows here if they win.

This bit looks like this. I will show you how to do the colours later.

With all of this added your board should look something like this.

Step 2: Now to start Coding

So now we need to open the Google Apps Script Environment and begin coding. Open it up by clicking Tools, Script Editor. A blank window should open up.

To start with we will write a function to take the written down values and put them into the board. So write this bit of code.

function drawBoard() {
 mv = ["","X","O"] //Different possible pieces
 letters = SpreadsheetApp.getActiveSheet().getRange("E2:M2").getValues()[0]; /Get a list of cells E2 to M2 
 a = letters[0]; //Define each letter
 b = letters[1];
 c = letters[2];
 d = letters[3];
 e = letters[4];
 f = letters[5];
 g = letters[6];
 h = letters[7];
 i = letters[8];
 values = [[[mv[a]],[mv[b]],[mv[c]]],[[mv[d]],[mv[e]],[mv[f]]],[[mv[g]],[mv[h]],[mv[i]]]] //Make a 3x3 array of the grid.
 SpreadsheetApp.getActiveSheet().getRange("A1:C3").setValues(values); //Enter it into the board
}

We now want the board to be redrawn whenever the sheet is edited, to do this add this bit of code as a separate function.

function onEdit(e) {
 drawBoard();
}

How ever right now the board looks a bit boring so now we will setup conditional formatting to draw it better. To do this firstly select the board.

Now select Format, Conditional Formatting. Now click "Add new rule". Set the settings to be like shown below.

Then create another rule with these settings.

Now to test this, set one of the cells on the board to either X or O. The text will popup quickly with the colour but should disappear right after, don't worry this is just the code we wrote earlier redrawing the board. Now select one of the cells on the board and copy it to the cells below with O and X in it, this will add the rule to them as well.

Step 3: Adding Controls

To add controls to play the game we need to create a function to write. To do this add this code:

function turn() { //This is a function to check whos turn it is and change it
 turn = SpreadsheetApp.getActiveSheet().getRange("N2:N2").getValues()[0][0];
 if (turn==1) {
   SpreadsheetApp.getActiveSheet().getRange("N2").setValue(2);
 } else if (turn==2) {
   SpreadsheetApp.getActiveSheet().getRange("N2").setValue(1);
 }
 return(turn); //Returns the players turn
}
function write(sq) { //This function takes an for the cell that a player will play in it then checks if something is already in that cell, if its clear then it writes whoevers turn it is.
 col = sq[0]; //Finds the column
 ce = SpreadsheetApp.getActiveSheet().getRange((col+3)).getValue(); //Gets whether something is there
 if (ce == 0) {
   SpreadsheetApp.getActiveSheet().getRange(sq).setValue(turn()); //Writes the players turn from the turn function we created earlier.
   SpreadsheetApp.getActiveSheet().getRange((col+3)).setValue("1");
   onEdit(); //Calls onEdit() so that the board is redrawn
 }
}

Whilst now we have a function we still need a way to control it. There is a way to run the function in the sheet but not with a parameter (The cell) so next I created a separate function for each position as shown below. All these functions do is call the one we just made with different buttons. Copy the code shown here.

function tl() {
 write("E2");
}
function tm() {
 write("F2");
}
function tr() {
 write("G2");
}
function ml() {
 write("H2");
}
function mm() {
 write("I2");
}
function mr() {
 write("J2");
}
function bl() {
 write("K2");
}
function bm() {
 write("L2");
}
function br() {
 write("M2");
}

We will create one more function to reset the game. This is shown below.

function reset() {
 SpreadsheetApp.getActiveSheet().getRange("E2:M3").setValue("0"); //Clear the stored version of the board
 SpreadsheetApp.getActiveSheet().getRange("N2").setValue("1"); //Set it to player ones turn
 SpreadsheetApp.getActiveSheet().getRange("O2").setValue("0"); //Clear the win detection
 onEdit(); //Update board
}

Right now it doesn't detect wins but the code is in there anyway so later it will also clear.

Now we need to add buttons to the sheet.

To do this download these two images.

Then upload them to the sheet and position however you like, you will need to add the blue button 9 times. (You can copy and paste) Now to make these buttons trigger functions.

Right click on the reset button and press the 3 dots in the top write then select Assign Script... enter "reset" and press OK. When you click this image it should run the reset button.

Now arrange the buttons in a 3x3 grid and assign the scripts to each in this arrangement.

tl   tm   tr
ml mm mr
bl  bm  br

The game should now be playable, whoever turn it is will alternate and the buttons should work.

Step 4: Win Detection

Whilst the game is now playable, a nice feature would be the game telling you when someone wins. So here is some code to detect wins.

There are three types of win that this function looks for.

• Vertical

• Horizontal

• Diagonal

function checkWin() {
 b = SpreadsheetApp.getActiveSheet().getRange("E2:M2").getValues()[0]; //Gets the saved board
 for (var i = 1; i < b.length-1; i=i+3) { //Goes through board in 3s
   if (b[i]==b[i-1] && b[i]==b[i+1] && b[i] > 0) { //Checks if current row is all equal and greater
       SpreadsheetApp.getActiveSheet().getRange("O2").setValue(b[i]); //Outputs the winner     
       return b[i]; //Ends the function here
   }
 }
 for (var i = 0; i < 3; i++) {
   if (b[i]==b[i+3] && b[i]==b[i+6] && b[i] > 0) {
     SpreadsheetApp.getActiveSheet().getRange("O2").setValue(b[i]); //Outputs the winner
     return
   }
 }
 if (b[0]==b[4] && b[0] == b[8] && b[0]>0) {
   SpreadsheetApp.getActiveSheet().getRange("O2").setValue(b[0]);
   return b[0]; //Ends the function here
 }
 if (b[2]==b[4] && b[2] == b[6] && b[2]>0) {
    SpreadsheetApp.getActiveSheet().getRange("O2").setValue(b[2]);
   return b[2]; //Ends the function here
 }
SpreadsheetApp.getActiveSheet().getRange("O2").setValue(0);
return
}

We want this function to run every time the board is edited as this could mean someone has won to do this put this line of code in the onEdit function.

 checkWin();

The onEdit function should now look like this.

 function onEdit(e) {
     drawBoard();
     checkWin();
}

Now to show who wins put the formula shown below in cell C8.

 =if(O2=1,"Winner",if(O2=2,"Loser",""))

And the formula shown below in cell C9.

 =if(O2=2,"Winner",if(O2=1,"Loser",""))

If you want to make these look even better you can add some conditional formatting to make it turn green and red for winning and losing.

Step 5: Debug mode

I have one more function to add. All it does is turn debug mode on and off and hides the cells that don't need to be visible.

function debug() {
 db = SpreadsheetApp.getActiveSheet().getRange("F5").getValue(); //Read the cell
 if (db == 0) {
   SpreadsheetApp.getActiveSheet().hideColumns(5, 11); //Hide cells
   SpreadsheetApp.getActiveSheet().getRange("F5").setValue(1); 
 } else {
   SpreadsheetApp.getActiveSheet().unhideColumn(SpreadsheetApp.getActiveSheet().getRange("E1:O1")); //Unhide cells
   SpreadsheetApp.getActiveSheet().getRange("F5").setValue(0);
 }
}

If you want you can create a button for debug mode but I just left it, to enter debug mode you can go into Google Apps Script and run it at the top.

Complete

Before running this the script might ask for some permissions on your account so that it can access the sheet. If you allow these then you're done. Your game should now function. You can share the sheet with people and play against them although they might also have to have edit permissions and accept the permissions from the app.

Have fun playing.