Google Sheets Cheat Sheet

Google Sheets Connect 4 Game

I love Connect 4.

I love Google Sheets.

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

shows preview of connect 4 google sheets game
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.

shows how to take a turn by ticking a checkbox above the game board

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

shows smiling emojis appear when a player wins a game

The loser sees a slightly different result:

shows the surprised emojis that appears when a player loses a game

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

shows what happens when a new game is started

That's how to play.

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

FREE RESOURCE

Google Sheets Cheat Sheet

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

Building Connect 4 In Google Sheets

This game is a combination of:

Simple Design

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

That means:

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):

shows the connect 4 game board within the google sheet

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:

shows how to reveal and hide the instructions using a google sheets row group

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:

These options can also be accessed by right clicking your selection and going to the View more row/column actions submenu.

Conditional Formatting (Win/Loss Highlight)

The winning streak is highlighted using conditional formatting:

shows the conditional formatting rules applied to player 1's board

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

shows the conditional formatting rules applied to player 2's board

Conditional formatting can be accessed using the main menu:

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:

Dark theme
Copy code
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:

Dark theme
Copy code
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:

Dark theme
Copy code
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

Learn 12 tips and tactics that will make your sheets more beautiful and user-friendly from today.

Google Sheets Cheat Sheet

By entering your email address you agree to get email updates from me. I'll respect your privacy and you can unsubscribe at any time.

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.