Google Sheets Connect 4 Game

Kieran Dixon Updated:  January 11, 2022

I love Connect 4.

I love Google Sheets.

Here's a Connect 4 game built in Google Sheets:

Free Google Sheets Game

Google Sheets Connect 4 Game

This sheet is a two-player version of the classic board game Connect 4. All the classic fun and strategy in a spreadsheet!

Get Game

Instructions

To place tokens: tick the checkboxes above the game board when it's your turn.

Keeping placing tokens until there is a winner (or a draw):

The loser sees a slightly different result:

Then tick the 'New Game ➜' checkbox to start again:

That's how to play.

If you're interested in how it works, keep reading:

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

Building Connect 4 In Google Sheets

This game is a combination of:

• Simple design
• Nifty Google Sheets tricks
• A fair bit of Google Apps Script in the background

Simple Design

I like my sheets to be as easy-to-use as possible.

That means:

• As little wasted space as possible, and
• Limiting the ways for users to interact

This helps to avoid confusion and reduce the chance that things break.

The sheets' coloring is based on the token that each player uses.

Here's the game board for Player 1 (red):

In this sheet the player can:

1. Start a new game
2. Place their discs / checkers / tokens using the checkboxes above the board
3. Expand and read the instructions using the plus sign on the left

There's only three options for interaction and there's no extra space for players to assume that they should be typing in or clicking on anything else.

Google Sheets Tricks

Here's a couple of cool things the sheet uses:

Grouped Rows (Instructions)

The instructions for the game are hidden using grouped rows which allows for easy revealing and hiding:

You can do this by highlighting the rows (or columns) you want to show/reveal and then accessing the Group settings in the main menu:

Conditional Formatting (Win/Loss Highlight)

The winning streak is highlighted using conditional formatting:

The rules for the other player's sheet are similar but match the opposite emojis:

Google Apps Script

There is a lot of code running in the background of this sheet.

I'm not going to go over everything but instead will just hit the highlights.

Feel free to check out the code yourself by making a copy of the game and opening the IDE using the main menu:

onEdit(e)

The onEdit(e) simple trigger is my favorite way to allow users to interact with game-based sheets.

As long as the underlying script only interacts with itself, there's no need to seek the user's permission to approve the script (which understandably can cause some concern for people who can't read the code for themselves).

This trigger is going to run "when a user changes the value of any cell in a spreadsheet".

That's code for A LOT.

Therefore, when writing onEdit(e) triggers you should always aim to have it fail as quickly as possible.

This is the start of the onEdit(e) function in the Connect 4 sheet:

function onEdit(e) {

  //Make sure the edit is a checkbox tick
  if (e.value !== "TRUE" || e.oldValue !== "false") return;

  //Setup only variables required for next check (fail quickly)
  const range = e.range;
  const row = range.rowStart;

  //Continue only if the edit occurs in one of two rows
  if (row !== checkboxRow && row !== newGameRow) return;

The first few lines are desperately trying to make the function stop.

First if the edit was not the tick of a checkbox (comparison of the old value of the cell and the new value).

Then if the edit didn't occur in one of the two actionable rows.

While these checks are made only the necessary variables are initialized from one check to the next.

Placing A Token

Here's the function that places a token within the board:

function placeToken(board, chosenCol, playerToken) {
  
  //Isolate the chosen column
  const colArray = board.map(x=> x[chosenCol]);
  
  //Find the next blank row (by looping backwards)
  const rowToFill = colArray.lastIndexOf("");
  
  //Insert the player token into this slot 
  board[rowToFill][chosenCol] = playerToken;
  
  //And return the new board
  return board;
  
}

In real life tokens drop into the next available slot.

In the code the chosen column is isolated and then the underused lastIndexOf method is used to find the next available slot.

The player's token is then placed into the board and returned to check for winning streaks.

Looping Over Diagonals

When checking if the current player has won the game you need to check the rows and columns (the easy part) but also the left and right diagonals (the harder part).

To accomplish this you can use a while loop inside a for loop:

function rightDiagonals(board) {

  //Create an empty array for all of the diagonals
  const diagonals = [];

  //DIAGONALS FROM LEFT EDGE OF BOARD UP AND TO THE RIGHT
  //Loop over the rows of the board
  for (let i = 0; i < board.length; i++) {

    //Empty array to contain each diagonal's info
    const diagonal = [];

    //Col stays at zero as we're checking from left side of board up
    let row = i;
    let col = 0;

    //Loop through the diagonals (until the row number reaches 0 and can't go any further)
    while (row >= 0) {

      //Push three pieces of info in the diagonal array
      diagonal.push({ row, col, token: board[row][col] });

      //Increment the row and column to capture the diagonal
      row = row - 1;
      col = col + 1;

    }

    //Push the diagonal info into the main diagonals array
    diagonals.push(diagonal);

  }

  //DIAGONALS FROM BOTTOM EDGE OF BOARD (EXCLUDING COL 1) UP AND TO THE RIGHT
  //Loop over the columns of the board
  for (let i = 1; i < board[0].length; i++) {

    //Empty array to contain each diagonal's info
    const diagonal = [];

    //Row stays at bottom of board
    let row = board.length - 1;
    let col = i;

    //Loop through the diagonals (until the col number reaches the edge of the board and can't go any further)
    while (col < board[0].length) {

      //Push three pieces of info in the diagonal array
      diagonal.push({ row, col, token: board[row][col] });
      row = row - 1;
      col = col + 1;

    }

    diagonals.push(diagonal);

  }

  //Return on the diagonals that contain 4 or more spaces (as only these can contain wins)
  return diagonals.filter(diagonal => diagonal.length >= 4);

}

The for loop makes sure you begin in the required rows and columns and the while loop handles the traversing of the diagonal.

This wasn't as intuitive as I expected it to be and it was a great learning experience to have to sit back and think about a problem within the framework that Javascript provides.

I hope you enjoy playing Connect 4 in this sheet as much as I enjoyed making it.

FREE RESOURCE

Google Sheets Cheat Sheet

12 exclusive tips to make user-friendly sheets from today:

Get The Cheat Sheet ➜

You'll get updates from me with an easy-to-find "unsubscribe" link.

Kieran Dixon started using spreadsheets in 2010. He leveled-up his skills working for banks and running his own business. Now he makes Google Sheets and Apps Script more approachable for anyone looking to streamline their business and life.

You might like:

Google Sheets Battleship Game

Play the classic board game Battleship in a Google Sheet! All of the functionality of the original game in a spreadsheet.

Google Sheets Memory Game

Play the game of Memory in a Google Sheet! Click to reveal potential pairs (in your chosen difficulty level) and find all of the matches to win.