Tic Tac Toe in Google Sheets

In this tutorial, I will show you how to build the Tic Tac Toe game in Google Sheets using Apps Script. I had a lot of fun building this and I hope you'll enjoy building it too!

Here is a screencast of the game in action:

Prerequisites

This tutorial assumes you're familiar with:

• The basics of coding in Google Sheets using Apps Script.

• Reading from and writing to a range in Google Sheets using Apps Script.

• The forEach() loop in Apps Script.

• Creating clickable buttons in Google Sheets.

7 steps to build a Tic Tac Toe game in Google Sheets using Apps Script

• Step 1 — Create a Google Sheets spreadsheet and the Tic Tac Toe board

• Step 2 — Create a box to display messages to the players

• Step 3 — Set up an onSelectionChange simple trigger to detect selections and display the user's selection in the message box

• Step 4 — Create a function to check if their selection is valid

• Step 5 — If their selection is valid, process their selection and notify the other player that it is now their turn

• Step 6 — Determine whether a user has won or if the game is a draw

• Step 7 — Create the ability to reset the game

Step 1 — Create a Google Sheets spreadsheet and the Tic Tac Toe board

Create a new Google Sheets spreadsheet and create a sheet called "Board" in it. This is the sheet that will contain the Tic Tac Toe board.

A Tic Tac Toe board consists of 9 squares (3 rows X 3 columns) and looks like this:

You can create this in your spreadsheet by following these steps:

• Create a sheet called "Board".

• Resize rows and columns by selecting all the cells in the spreadsheet and then dragging the row and column separators until each cell looks like a square.

• Select the range B2:D4 and add a black border such that every cell has a border.

• Delete all rows after row 6 and all columns after column L.

• Turn off gridlines from the View menu.

Here is a video demonstrating how to perform the above steps:

When you are done, you should have a sheet like this:

The next step is to format the Tic Tac Toe board:

• Select the cells in the board and fill them with a light gray color.

• Then enter some test values. Select all the cells in the grid once again and center align their contents vertically and horizontally and also set the font size to 16.

This screencast below demonstrates how to perform the above steps:

The final step is to create a named range for the Tic Tac Toe board. Select the 9 cells in the board, right click and select Define named range.

Give the range Board!B2:D4 the name "Board". When you are done, you should see this named range listed under the Named ranges sidebar menu.

You're done creating the Tic Tac Toe board!

Step 2 — Create a box to display messages to the players

During the game, we will need to notify the players in the following situations:

• After each player's turn to notify the other player that it is their turn.

• When the game is over and no one won.

• When a player wins the game.

Create the space to display this notification by:

• Selecting the range G2:J2 (four cells in row 2) and merging them.

• Fill the merged range with a light gray color.

• Set the text wrapping to wrap.

• Set vertical alignment to middle.

Give this range the name "Message". When you are done, you should see two named ranges in your spreadsheet: Board and Message.

Step 3 — Set up an onSelectionChange simple trigger to detect selections and display the user's selection in the message box

Open the Apps Script code editor by selecting Extensions —> Apps Script. Then replace the default code in the editor with the code below.

function onSelectionChange(e) {
 var rowStart = e.range.rowStart;
 var rowEnd = e.range.rowEnd;
 var columnStart = e.range.columnStart;
 var columnEnd = e.range.columnEnd;

 var message = `rowStart: ${rowStart} rowEnd: ${rowEnd} columnStart: ${columnStart} columnEnd: ${columnEnd}`;

 SpreadsheetApp.getActive().getRangeByName("Message").setValue(message);
}

Then in your spreadsheet, try selecting various cells and you'll see the message box updating with the row and column coordinates of the range you're selecting.

Step 4 — Create a function to check if the user's selection is valid

Now that your script is automatically detecting selections in your spreadsheet, the next step is to check if the user's selection is valid.

The user's selection is valid if:

• This range they select is within the Tic Tac Toe board.

• They select a single cell.

• The cell does not already have a value.

We'll create three new functions:

• checkSelection(): Takes the coordinates of the selected range and determines if the selection is valid.

• displayMessage(): Displays a message in the message box.

• error(): Displays an error message in the message box.

We will also modify the onSelectionChange() function to call the checkSelection() function instead of displaying the selected coordinates in the message box.

function onSelectionChange(e) {
  //Reset the message box.
  displayMessage("");
  checkSelection(e.range);
}

function checkSelection(range) {
  //Check if the click was outside the board or not
  if(range.rowStart < 2 || range.rowEnd > 4 || range.columnStart < 2 || range.columnEnd > 4) {
    error("Select a cell inside the board.")
    return false;
  }

  //Check if a single cell was selected
  if(range.rowStart != range.rowEnd || range.columnStart != range.columnEnd) {
    error("Select a single cell.")
    return false;
  }

  //Check if the selected cell already contains a value
  var cell = SpreadsheetApp
    .getActive()
    .getSheetByName("Board")
    .getRange(range.rowStart, range.columnStart);
  var cellValue = cell.getValue();

  if(cellValue != "") {
    error("Select an empty cell.")
    return false;
  }

  return true;
}

function displayMessage(message) {
  SpreadsheetApp.getActive().getRangeByName("Message").setValue(message);
}

function error(message) {
  displayMessage("⚠️ " + message);
}

You can test if your code is working correctly by selecting various cells in your spreadsheet. I entered an "X" in a cell within the board to test the scenario where the user selects a cell that isn't empty.

Step 5 — If the user's selection is valid, process their play and notify the other player that it is now their turn

We'll create a new function play() to process the user's play. This function will check whose turn it is and enter that player's character in the cell that they selected. Then the function will display a message informing the other player that it is now their turn to play.

function play(range) {
  var whoseTurnIsit = whoseTurnIsIt();
  
  //Enter the player's character in the cell they selected.
  SpreadsheetApp
    .getActive()
    .getSheetByName("Board")
    .getRange(range.rowStart, range.columnStart)
    .setValue(whoseTurnIsit == "1" ? "X" : "O");

  displayMessage("Player " + (whoseTurnIsit == "1" ? "2's " : "1's ") + "turn.");
}

function whoseTurnIsIt() {
  var board = SpreadsheetApp.getActive().getRangeByName("Board").getValues();
  var numChars = 0;
  board.forEach(function(row) {
    row.forEach(function(col) {
      if(col == "X" || col == "O")
        numChars++;
    });
  });
  return numChars % 2 == 0? 1 : 2;
}

The whoseTurnIsIt() function checks how many squares on the Tic Tac Toe board are taken (i.e., filled). If this number is even, it means it is player 1's turn. Otherwise it is player 2's turn.

We also need to modify the onSelectionChange() function to call the play() function if the user's selection is valid.

function onSelectionChange(e) {
  //Reset the message box
  displayMessage("");
  var isSelectionValid = checkSelection(e.range);
  if(isSelectionValid)
    play(e.range);
}

When you're done with this step, you should be able to play the game by selecting squares within the Tic Tac Toe board.

So far the game play seems to work correctly with turns alternating between the two players. However, the game will never stop since we haven't implemented checking for the two ways the game can end:

• A player wins the game.

• The game comes to a draw and no player wins.

We will implement this next.

Step 6 — Determine whether a user has won or if the game is a draw

To determine whether the current play led to the player winning the game, we will create a function called hasPlayerWonGame(). This function checks if any row or column or diagonal has the same characters in them. It also checks to ensure that the character isn't blank (without this check, the function will return true even for an empty board).

function hasPlayerWonGame() {
  var board = SpreadsheetApp.getActive().getRangeByName("Board").getValues();
  if(
      (board[0][0] === board[0][1] && board[0][1] === board[0][2] && board[0][0] != "") ||
      (board[1][0] === board[1][1] && board[1][1] === board[1][2] && board[1][0] != "") ||
      (board[2][0] === board[2][1] && board[2][1] === board[2][2] && board[2][0] != "") ||
      (board[0][0] === board[1][0] && board[1][0] === board[2][0] && board[0][0] != "") ||
      (board[0][1] === board[1][1] && board[1][1] === board[2][1] && board[0][1] != "") ||
      (board[0][2] === board[1][2] && board[1][2] === board[2][2] && board[0][2] != "") ||
      (board[0][0] === board[1][1] && board[1][1] === board[2][2] && board[0][0] != "") ||
      (board[2][0] === board[1][1] && board[1][1] === board[0][2] && board[2][0] != "")
  ) {
    return true;
  }
}

We need to modify the play() function to check if the current player won based on the square they just selected.

function play(range) {
  var player = whoseTurnIsIt();
  
  //Enter the player's character in the cell they selected.
  SpreadsheetApp
    .getActive()
    .getSheetByName("Board")
    .getRange(range.rowStart, range.columnStart)
    .setValue(player == "1" ? "X" : "O");

  if(hasPlayerWonGame()) {
    displayMessage("Player " + player + " has won!");
  } else {
    displayMessage("Player " + (player == "1" ? "2's " : "1's ") + "turn.");
  }
}

To check if the game is a draw, we will check if all nine squares have been filled with a character and that no player has won. To implement this check, we will do something similar to what we did in the whoseTurnIsIt() function. We'll count the squares that are taken and if this number is 9 and if no player has won, it means the game is a draw.

Also, since both functions rely on counting the number of squares that are taken (i.e., are not blank), we will create a separate function called numSquaresTaken() that returns the number of non blank squares. Then we will modify the whoseTurnIsIt() function to use this new function and we'll also create a function called areAllSquaresTaken() to check if all nine squares are taken.

function whoseTurnIsIt() {
  return numSquaresTaken() % 2 == 0 ? 1 : 2;
}

function areAllSquaresTaken() {
  return numSquaresTaken() === 9;
}

function numSquaresTaken() {
  var board = SpreadsheetApp.getActive().getRangeByName("Board").getValues();
  var numChars = 0;
  board.forEach(function(row) {
    row.forEach(function(col) {
      if(col == "X" || col == "O")
        numChars++;
    });
  });
  return numChars;
}

function play(range) {
  var player = whoseTurnIsIt();
  
  //Enter the player's character in the cell they selected.
  SpreadsheetApp
    .getActive()
    .getSheetByName("Board")
    .getRange(range.rowStart, range.columnStart)
    .setValue(player == "1" ? "X" : "O");

  if(hasPlayerWonGame()) {
    displayMessage("Player " + player + " has won!");
  } else if(areAllSquaresTaken()) {
    displayMessage("Game is a draw!");
  } else { 
    displayMessage("Player " + (player == "1" ? "2's " : "1's ") + "turn.");
  }
}

Now, we finally have a working Tic Tac Toe game in Google Sheets!

Here is an example of the game in action where Player 2 wins the game:

Here is an example where the game comes to a draw:

Step 7 — Create the ability to reset the game

The final step is to create an easy way to reset the game. To do this, we will create a clickable button in Google Sheets. When the button is clicked, the game will be reset.

First create a button using a drawing. I created a blue button but you can use any color of your choice.

Next, create a function called resetGame() that resets the game. This function:

• Clears the board by setting each square to a blank value.

• Tells player 1 that it is their turn to play.

• Sets the active cell outside the board. This is to ensure that the first click within the board will run the onSelectionChange() trigger.

function resetGame() {
  var spreadsheet = SpreadsheetApp.getActive();
  //Clear board
  spreadsheet.getRangeByName("Board").setValue("");
  //Notify that it is player 1's turn
  displayMessage("Player 1's turn");
  //Set the active cell outside the board
  spreadsheet.getSheetByName("Board").getRange(1,1).activate();
}

Finally, assign this function to the button by selecting the button, selecting the "three-dots" menu on it and assigning the resetGame() function to it.

That's it! You're done building Tic Tac Toe in Google Sheets :).

Conclusion

In this tutorial I showed you how to build a Tic Tac Toe game in Google Sheets using Apps Script.

Now it is finally time to have fun playing Tic Tac Toe! I really enjoyed building this game in Google Sheets and have had lots of fun playing it with my family.

Hope you found this tutorial helpful and thank you for reading!