Google Sheets Battleship Game
Updated: May 25, 2021 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?
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 GameNow 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):
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:
The conditional formatting I'm using to show the possible position as you change the values in the table is quite complex:
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:
Limited dropdown options
The coordinates available in the ship positioning table are limited based on the ship's direction:
These are populated from from a table in a hidden sheet called 'Dropdowns':
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.
The row coordinates are creating using the following formula:
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:
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:
The conditional formatting formula for this effect is:
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):
Crosshair conditional formatting
As you change the attack coordinates, the 'crosshair' on the board adjusts to show you exactly where you're aiming:
This is done using two conditional formatting rules.
The formula for the red centre of the crosshair is:
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:
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:
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:
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:
In response to these instructions, there are three possible actions you can take:
- Place Ships
- Fire
- 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.
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:
Your opponent will see the result on their defence board:
This action goes back and forth until all of one player's ships are sunk and a winner is declared:
New game
This resets the boards of both players so the game can restart.
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:
- Users don't need to authorise the sheet
- 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:
So to play a fun game of Battleship I need users to agree that the game can:
- Access their data on Google
- 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:
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:
Preventing users from replacing their ships during a game:
Preventing a user from firing when it's not their turn:
Preventing users from wasting a turn by firing on the same location:
Preventing firing when the game is over:
Wrap up
That's it! Make a copy and have some fun 😃
FREE RESOURCE
Google Sheets Cheat Sheet
12 exclusive tips to make user-friendly sheets from today:
You'll get updates from me with an easy-to-find "unsubscribe" link.
