Google Sheets Cheat Sheet

Google Sheets Minesweeper Game

Playing the built-in Minesweeper game on old PCs was great fun.

So much fun that the game deserves to exist as a Google Sheet:

shows preview of minesweeper google sheets game
Free Game

Google Sheets Minesweeper Game

The classic computer game Minesweeper brought to life in a Google Sheet!

Get Game

Feel free to make a copy for yourself and relive the Minesweeper experience.

How To Play

Select the level you'd like to play from the dropdown menu:

  • Beginner = 8x8 grid with 10 mines
  • Intermediate = 16x16 grid with 40 mines
  • Expert = 16x30 grid with 99 mines
shows how to change the level of play between beginner, intermediate, and expert using the level dropdown

To 'sweep' a square: type a space into the cell and hit Enter / Return.

To 'flag' (🚩) a square: type an 'f' into the cell and hit Enter / Return.

I find it best to use the arrow keys to quickly navigate around the board:

shows how to sweep and flag squares by typing in spaces or the f character

Sweep and flag squares to reveal their contents.

Numbers indicate how many mines are present in that square's adjacent squares.

Be careful not to sweep a mine:

shows what it looks like to lose by sweeping a mine. the entire board is revealed with the swept mine now an explosion

If you successfully flag or avoid all of the squares that hide mines you win:

shows what it looks like to win the game. all of the flagged or ignored mines change into smiley faces

Check the 'New Game ➜' checkbox to play again:

shows the board resetting when the new game checkbox is ticked

Have fun with it and read on if you're interested in learning a little about how the sheet was made.

hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

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

Google Sheets Cheat Sheet

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

How It Works

Formulas

Instead of using Apps Script (which I talk about below) I thought I'd signal wins and losses using formulas.

In cell Z2 you'll find this formula:

=IFERROR(IFS(COUNTIF(board,"💥")>0,"GAME OVER",COUNTIF(board,"😃")>0,"YOU WIN! 😃"))

Essentially: If the explosion emoji is present the game is over and if the smiley emoji is present the game is won.

Similarly, the counter that tells you how many flags you have left in cell W2 is based on this formula:

=IFERROR(SWITCH(M2,"Beginner",10,"Intermediate",40,"Expert",99)-COUNTIF(board,X2))

Which is simply the number of mines for this game less the number of flags (X2) already on the board.

Conditional Formatting

Again, instead of using Apps Script to format the numbers as they are revealed, I thought a more efficient way of doing things would be to use conditional formatting on the entire board.

That means the board has eight rules, one for each possible number:

shows the eight conditional formatting rules that apply to the numbers on the minesweeper board

The colors used are the actual colors from the original game.

Grouped Rows

The included instructions are 'hidden' using grouped rows which allows for easy revealing and hiding:

shows how to reveal and hide the game instructions using the row grouping toggle to the side of the sheet

You can create this effect yourself by selecting 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.

Google Apps Script

The Google Apps Script that runs in the background of the game is the real workhorse.

The code:

  • Randomly assigns the position of the mines
  • Creates the board
  • Handles sweeping and flagging
  • Deals with wins and losses

Just about everything that happens.

You can take a look at the code yourself by making a copy (using the link at the top of the post) and accessing the Apps Script IDE from the Google Sheets main menu:

Here are a few highlights:

Secret board

The position of the mines and the numbers within all of the squares needs to persist throughout the game.

This is possible within Apps Script using the CacheService or PropertiesService.

However, this would require authorization.

I like to avoid this in games so instead you can store the position of everything in the sheet rather than in memory.

Storing the board itself would give players quite the unfair advantage.

This is why before placing the board in the sheet it is obfuscated using the Utilities.base64Encode() method.

If people accidentally stumble across the stored board they find a string of characters that makes no sense:

shows an base64 encoded (obfuscated) minesweeper board. it's just a string of letters and numbers that doesn't make any sense

It's only when you decode this string that it becomes more useful:

Dark theme
Copy code
const placement = [
  ["", "", "", "", "", "", "", ""],
  ["", 1, 1, 1, "", "", "", ""],
  ["", 2, "💣", 2, "", 1, 2, 2],
  ["", 2, "💣", 2, "", 2, "💣", "💣"],
  ["", 2, 2, 2, 1, 3, "💣", "💣"],
  ["", 1, "💣", 1, 2, "💣", 4, 2],
  ["", 1, 1, 1, 2, "💣", 3, 1],
  ["", "", "", "", 1, 1, 2, "💣"]
]

Here's the code that makes this happen:

Dark theme
Copy code
//Stringify the underlying placement array
const jsonPlacement = JSON.stringify(placement);

//Encode and place the string in the sheet
const b64ePlacement = Utilities.base64Encode(jsonPlacement, Utilities.Charset.UTF_8);

sheet.getRange(secretA1)
  .setBackground(gray)
  .setFontColor(gray)
  .setValue(b64ePlacement);

onEdit(e) Function

Every time a change is made in the sheet (sweep, flag, level change, or new game) the in-built onEdit(e) function executes.

This function:

  1. Decodes the hidden board
  2. Checks if the edit happened in the board, level dropdown, or new game checkbox
  3. Responds based on the edit location

I usually try to get this function to stop running as quickly as possible using guard clauses.

But because the edits in this game can be so varied it's not as efficient as I'd like.

Oh well - that's the architecture of the system that we choose to build within.

Recursion

When a user clicks on a cell its underlying value (be it a mine, number or blank) needs to be revealed.

But in Minesweeper blank cells don't just reveal themselves - they reveal every nearby blank cell until they are bordered by numbers:

shows how sweeping one blank cell reveals all adjacent blank cells too

It would be a lot of work for the user to have to reveal all of the blank cells themselves.

You can solve this problem quite easily using recursion.

Recursion is when a function conditionally calls itself from within its own code.

In the Minesweeper case:

  • If a user sweeps a blank cell:
    • All of the adjacent cells should be checked:
      • If an adjacent cell contains a number:
        • Reveal it to the user
      • If an adjacent cell is blank:
        • All of the adjacent cells should be checked:
          • If an adjacent cell contains a number:
            • Reveal it to the user
          • If an adjacent cell is blank:
            • If an adjacent cell is blank:
              • All of the adjacent cells should be checked:
                • If an adjacent cell contains a number:
                  • Reveal it to the user
                • If an adjacent cell is blank:

You get the idea.

When you check one blank cell eventually all of the adjacent cells will be checked and revealed.

Here's what this looks like in Apps Script:

Dark theme
Copy code
function revealAdjacent(sheet, placement, column, row) {

  const maxColumn = placement[0].length - 1;
  const maxRow = placement.length - 1;

  //Reveal what's in the cell
  sheet.getRange(row + startRow, column + startCol).setValue(placement[row][column]);

  //Add this cell to the checked list
  checkedCells.push(`${column}|${row}`);

  //Nested for loops to check the cells around this cell
  for (let i = column - 1; i <= column + 1; i++) {

    const newColumn = i;

    for (let j = row - 1; j <= row + 1; j++) {

      const newRow = j;

      //If the cell is in bounds
      if (
        newColumn >= 0 &&
        newColumn <= maxColumn &&
        newRow >= 0 &&
        newRow <= maxRow
      ) {

        //and hasn't been checked before
        if (!checkedCells.includes(`${newColumn}|${newRow}`)) {

          //and is blank
          if (placement[newRow][newColumn] === "") {

            //Check and reveal
            revealAdjacent(sheet, placement, newColumn, newRow);

            //otherwise
          } else {

            //show the number value
            sheet.getRange(newRow + startRow, newColumn + startCol).setValue(placement[newRow][newColumn]);

          }

        }

      }

    }

  }

}

Have fun playing Minesweeper in Google Sheets!


hand pointing emoji hand pointing emoji

FREE RESOURCE

Google Sheets Cheat Sheet

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

Google Sheets 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.

Want Better-Looking Google Sheets?

Google Sheets Cheat Sheet

Get my 12-tip cheat sheet that will make your spreadsheets more user-friendly.

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

🗙