Google Sheets Cheat Sheet

Google Sheets Battleship Game

When I was a kid I loved the Battleship board game.

It's fun and can be thrilling in its final stages as both players come close to sinking all of the other's ships.

What better way to celebrate such an iconic board game than by recreating it in a spreadsheet?

screenshot from the google sheets battleship game to entice you to make a copy for yourself
Free Google Sheets Game

Google Sheets Battleship Game

This sheet is a two-player version of the classic board game Battleship. Sink your friend's battleship in a spreadsheet!

Get Game

Now I'm going to break down some of the cool spreadsheet techniques that I've used to recreate the game in Google Sheets. I won't cover everything, just the best bits.

Defence Board

This is where you place your ships and your opponent attacks you (the right-hand side of the sheet):

screenshot of the battleship defence board within the google sheet. it shows a coordinate board and ship data table.

Positioning ships

Conditional formatting

Before the game begins, you use the table on the right to position your ships on the defence board using the direction and coordinates options.

As you change the values in the table, the underlying conditional formatting will show you where your ship is about to be placed:

demonstration of a player placing their frigate by changing coordinates in the ship data table of the defence board

The conditional formatting I'm using to show the possible position as you change the values in the table is quite complex:

=if($AK$6="Horizontal",and(row(W6)-(row($W$6)-1)=match($AL$6,$V$6:$V$15,0),column(W6)-(column($W$6)-1)>=$AM$6,column(W6)-(column($W$6)-1)<=$AM$6+switch($AI$6,"Minesweeper",2,"Submarine",3,"Frigate",3,"Battleship",4,"Aircraft Carrier",5)-1),and(row(W6)-(row($W$6)-1)>=match($AL$6,$V$6:$V$15,0),row(W6)-(row($W$6)-1)<=match($AL$6,$V$6:$V$15,0)+switch($AI$6,"Minesweeper",2,"Submarine",3,"Frigate",3,"Battleship",4,"Aircraft Carrier",5)-1,column(W6)-(column($W$6)-1)=$AM$6))

Put simply:

If the ship is horizontal: If the current row is the chosen row coordinate AND the column number is within the bounds of the chosen column coordinate plus the length of the ship, return TRUE. Else return FALSE.

Else (if the ship is vertical): If the current row number is within the bounds of the chosen row coordinate plus the length of the ship AND the current column is the chosen column coordinate, return TRUE. Else return FALSE.

Returning TRUE triggers the relevant ship's colour to appear. FALSE leaves the cell with the standard board background.

There is one of these rules applied to the board per ship (and therefore color), five in total:

conditional formatting rules that help players to place their five ships before the game begins
Limited dropdown options

The coordinates available in the ship positioning table are limited based on the ship's direction:

demonstration of how the available ship coordinates change when a player changes the ship direction

These are populated from from a table in a hidden sheet called 'Dropdowns':

dependent dropdown tables for each player in the hidden dropdowns sheet

The reduced number of coordinates help to limit your ability to position ships out of bounds.

If a ship is horizontal: All row coordinates and limited column coordinates are available.

If a ship is vertical: All column coordinates and limited row coordinates are available.

example of how the coordinates available to a player vary based on the direction of their ships

The row coordinates are creating using the following formula:

=ArrayFormula(if(D5="Horizontal",{"A","B","C","D","E","F","G","H","I","J"},vlookup("A",{"A","B","C","D","E","F","G","H","I","J"},sequence(1,11-C5,1,1),0)))

Put simply:

If the ship is horizontal: Show all letters A through to J.

Else (if the ship is vertical): Show a limited sequence of letters, shortened based on the length of the ship so that the ship won't be positioned out of bounds.

This formula uses a nifty feature of VLOOKUP that allows you to return multiple values if the index you submit is an array of multiple column numbers.

The column coordinates are created using the following formula:

=if(D5="Horizontal",sequence(1,11-C5,1,1),sequence(1,10,1,1))

Put simply:

If the ship is horizontal: Show a limited sequence of numbers, shortened based on the length of the ship so that the ship won't be positioned out of bounds.

Else (if the ship is vertical): Show all the numbers 1 through to 10.

The relevant coordinates for each player and ship are then fed into the dropdowns using range-based data validations.

This doesn't solve the problem completely though as a ship's possible coordinates can still place it out of bounds if you position a ship and then change its direction. When this happens, the table itself alerts you to the error:

demonstration of a player placing a ship out of bounds and the corresponding error that appears

The conditional formatting formula for this effect is:

=IF($AK6="Horizontal",$AM6+switch($AI6,"Minesweeper",2,"Submarine",3,"Frigate",3,"Battleship",4,"Aircraft Carrier",5)>11,match($AL6,$V$6:$V$15,0)+switch($AI6,"Minesweeper",2,"Submarine",3,"Frigate",3,"Battleship",4,"Aircraft Carrier",5)>11)

Put simply:

If the ship is horizontal: If the current column plus the ship's length is greater than 11, return TRUE. Else return FALSE.

Else (if the ship is vertical): If the current row number plus the ship's length is greater than 11, return TRUE. Else return FALSE.

Returning TRUE triggers the formatting to highlight the out of bounds ship in red.

Attack Board

This is where you attack your opponent and monitor your progress (the left-hand side of the sheet):

screenshot of the attack board players use to fire at their opponent to sink their battleships

Crosshair conditional formatting

As you change the attack coordinates, the 'crosshair' on the board adjusts to show you exactly where you're aiming:

shows how the conditional formatting functions as a set of crosshairs on the attack board as a player changes their targeted coordinates

This is done using two conditional formatting rules.

The formula for the red centre of the crosshair is:

=and(column(D6)-column($C$6)=$Q$14,row(D6)-row($D$5)=match($P$14,$C$6:$C$15,0))

Put simply:

If the coordinate details match both the row AND column number of the cell, make its background red.

The formula for the lighter red row and column highlighting is similar with one important difference:

=or(column(D6)-column($C$6)=$Q$14,row(D6)-row($D$5)=match($P$14,$C$6:$C$15,0))

Put simply:

If the coordinate details match either the row OR column number of the cell, make its background light red.

Changing the outer function from AND to OR makes the rule highlight as required.

Status table

As you hit your opponent's ships, you don't know which ship you're hitting.

You only know when you've sunk a specific ship.

In the original board game this was done by announcing, 'You sunk my battleship'.

This table replaces that announcement by showing you the status of your opponents ships as the game progresses:

example of attack board status so a player knows which of the opponent ships have been sunk

Instructions

The instructions are hidden by default. If a player is unsure how to proceed, they can click on the plus sign to the left of the sheet to reveal a short set of instructions:

how to access the game instructions placed within a row grouping

To achieve this effect, the rows below the main playing board are placed in what's called a 'group'. The user can then toggle between hiding and unhiding this group of cells using the plus/minus button that appears to the left of the row numbers.

You can also create groups of columns.

Gameplay

The white boxes at the top of the sheet tell players what to do next and whether it's their turn:

all of the various game status updates a player will see throughout a game about the attack and defence boards

In response to these instructions, there are three possible actions you can take:

  1. Place Ships
  2. Fire
  3. New Game

Place ships

This is the first action of every game.

When a player is happy with the positioning of their ships based on the coordinates table and the corresponding conditional formatting, checking the PLACE SHIPS tick box will place emojis in the highlighted cells on the defence board.

shows a players ships being placed by the underlying script triggered by the place ships check box

Whoever places their ships first gets to attack first.

Fire

This allows you to attack your opponent at the chosen coordinate (when it's your turn).

You'll see the result on your attack board:

shows a player hitting an opponent ship in the attack board

Your opponent will see the result on their defence board:

shows a player being hit by an opponent attack in the defence board

This action goes back and forth until all of one player's ships are sunk and a winner is declared:

shows a player delivering the final hit and winning the game

New game

This resets the boards of both players so the game can restart.

shows how the underlying script resets the game boards when triggered by the NEW GAME check box

How the game works

A number of Google Apps Script functions are attached to the sheet and, when triggered by your input, run and allow the game to progress.

Stepping through every detail of every function is a lot.

Instead, here are some of the more important/awesome features:

Tick boxes and onEdit(e)

Checking tick boxes and the onEdit(e) function attached to the spreadsheet makes the game work.

Tick boxes are the best thing to pair with onEdit(e) because an edit happens with a single click, as opposed to most edits that require typing.

The combination of tick boxes and onEdit is appealing because:

  1. Users don't need to authorise the sheet
  2. Triggering scripts using buttons (drawings) or menu items doesn't work on mobile (for now)
1. Authorisation

As soon as the Spreadsheet Service is run in a script, you need to step through an authorisation process that involves the following:

the authorization popup users see when a script is runthe authorization details users see when a script that requires spreadsheet service permissions runs

So to play a fun game of Battleship I need users to agree that the game can:

  1. Access their data on Google
  2. See, edit, create and delete their spreadsheets in Google Drive

If the user can't read the code I've written, their immediate reaction is a hard 'No' to both of those requests. And that's totally understandable!

I'm not saying the messaging from Google is inappropriate, because I think it's important to protect users.

Instead, I'm saying that when the code simply interacts with the spreadsheet to which it is attached, a workaround for this is needed that doesn't have the potential to scare people into not using the sheet.

Luckily, such a workaround exists!

onEdit(e) is designated as a Simple Trigger.

onEdit(e) cannot access services that require authorization but can modify the file they are bound to.

As long as the script only edits the attached spreadsheet and doesn't try to access another service, everything can be triggered using onEdit(e) without the requirement for scary authorisation messages.

2. Scripts on mobile

At the moment, scripts triggered using menu items or buttons (created using drawings) are not accessible on mobile.

Scripts triggered using onEdit(e) are available on mobile.

So when it seems like a user might want to access the sheet on mobile (e.g. for a game), onEdit(e) triggers are the only option.

If you're interested in reading the actual code of the onEdit(e) function, here it is:

Dark theme
Copy code
function onEdit(e) {

  //Setup sheet
  let playerSheet = ss.getActiveSheet();
  let playerSheetName = playerSheet.getName();

  //Ignore Dropdown
  if (playerSheetName !== "Dropdowns") {

    //Get edited cell
    let cell = e.range;
    let val = cell.getValue();

    //Confirm tick box
    if (val) {

      //Get row and col data
      let row = cell.getRow();
      let col = cell.getColumn();
      let opponentSheetName;
      let opponentSheet;

      //Figure out opponent vs player sheets
      if (playerSheetName === 'Player 1') {
        opponentSheetName = 'Player 2';
      } else {
        opponentSheetName = 'Player 1';
      }

      opponentSheet = ss.getSheetByName(opponentSheetName);

      //If the fire checkbox is ticked: attack, check win and untick box
      if (row === 15 && col === 17) {

        attack(playerSheet, opponentSheet);
        checkWin(playerSheet, opponentSheet);
        cell.setValue(false);

      } else if (col === 37) {

        //If the fire checkbox is ticked: place ships and untick box
        if (row === 12) {

          placeShips(playerSheet, opponentSheet);
          cell.setValue(false);

          //If the new game checkbox is ticked: reset game and untick box
        } else if (row === 13) {

          newGame();
          cell.setValue(false);

        }

      }

    }

  }

}

This function is heavily dependent on the formatting of the document and the relevant tick box cells being in specific locations. This isn't ideal, but it makes it easier than having to reference two sets of named ranges - one for each player.

Warning messages

To keep the game running smoothly and functioning correctly there are a number of in-built error messages that prevent users from doing things when they shouldn't.


Preventing users from overlapping their ships:

error message shown when the placement of ships overlaps

Preventing users from replacing their ships during a game:

error message shown if a user tries to place their ships while a game is in progress

Preventing a user from firing when it's not their turn:

error message shown when a user tries to attack when it is not their turn

Preventing users from wasting a turn by firing on the same location:

error message shown when a user tries to attack a coordinate that has already been hit

Preventing firing when the game is over:

error message shown when a user tries to attack when the game is already complete

Wrap up

That's it! Make a copy and have some fun 😃


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.

🗙