Google Sheets are powerful, and Apps Script makes them even more versatile and useful.
Yes, you can use them for finance dashboards, personal budgets, and project management (and we'll cover these as future topics). But in this article, I'll go through the basics of Apps Script by building a simple and playable Tic Tac Toe gameboard.
Here's the link to the spreadsheet we'll be making if you'd like to check it out while you follow along:
Let's go gif
Video Walkthrough Available
If you'd like to check out a video walkthrough of the Google Sheet, here you go:
Project Setup
Create a new Google Sheet by either going to your Google Drive and selecting NEW -> Google Sheet or by simply typing in sheets.new in the URL bar of your browser.
screenshot of Google Drive
Since this is a gameboard, we are going to give it a little bit of formatting to look good, add data validation and conditional formatting to add functionality to the game, and create useable buttons for our scorecard.
Here's what we'll end up with:
screenshot of Tic Tac Toe Google Sheet
Let's remove gridlines, add borders to the gameboard and the scorecard, and set an alternative font for the board.
To get rid of gridlines, select View -> Show -> Gridlines to uncheck this option.
screenshot of Google Sheets view options
To get a nice square grid with large X's and O's, I set the row height and column width of rows 2 - 4 and columns B - D by highlighting them, right clicking, and selecting the resize options.
screenshot of resizing columns in Google Sheets
I chose 150 pixels for the height and width. You'll need to do these separately – you cannot change both the row height and column width at the same time.
screenshot of resizing columns in Google Sheets
For the font size of the board, select 100, and for the Font, I am using Lexend. You can add additional Google Fonts from the toolbar dropdown:
screenshot of Fonts options in Google Sheets
Add a border to the board and the scorecard areas by highlighting the cells and then selecting the border options from the toolbar.
Click and drag over cells to select the whole range, and hold down the CTRL button to click and drag a second area. You can style these at the same time.
screenshot of border options in Google Sheets
Data Validation
Highlight the gameboard (B2:D4) and select Data -> Data validation from the menu.
This allows us to select Dropdown as the Criteria and add X and O as the two options to select.
Then click Advanced options and select Reject the input if the data is invalid, and plain text for the display style. This will keep the dropdown chips and handles from cluttering the gameboard.
screenshot of Data validation menu in Google Sheets
Conditional Formatting
We'll use conditional formatting for our gameboard also. We need to check for all the winning conditions, and if one of the players gets three in a row, we will highlight those cells.
Keeping the gameboard highlighted, select Format -> Conditional formatting.
screenshot of Format window in Google Sheets
There are eight conditions we'll check for three-in-a-row: going across x3, going down x3, and diagonal x2.
But we only need to write four formulas (two for the diagonals, one for across, and one for down) since we can use dollar ($) signs to drag the formula down and across for those.
For the down three-in-a-row:
//Apply to range B2:D2
=AND($B2=$C2,$B2=$D2,ISTEXT($B2))
For the across three-in-a-row:
//Apply to range B2:D2
=AND(B$2=B$3,B$2=B$4,ISTEXT(B$3))
For the diagonals we have to define them separately:
//Apply to range B2, C3, D4
=AND($B$2=$C$3,$B$2=$D$4,ISTEXT($B$2))
//Apply to range B4, C3, D2
=AND($B$4=$C$3,$B$4=$D$2,ISTEXT($B$4))
We test for equality of each cell and whether there is anything in the cell with the =ISTEXT()
function. By wrapping each item in an =AND()
function, we'll only apply the formatting if all conditions are met.
I selected a green background for the conditional formatting.
screenshot of conditional formatting window
Apps Script
Now for the scorecard logic. Let's open Apps Script by selecting Extensions -> Apps Script from the menu:
screenshot of Apps Script menu
We'll write four functions to handle our logic:
xScore()
will increment X's score in the scorecardoScore()
will increment O's score in the scorecardclearBoard()
will clear the board but keep the scoresreset()
will clear the board and set the scores back to zero
To make things more legible, let's set a few named ranges.
Highlight the gameboard again and select Data -> Named ranges. Give this range a name of Board. Do the same for cells G4 and H4 for xScore and oScore, respectively.
Now for the scores, we'll have the exact same function for each using only the two different ranges: xScore for X and oScore for O. Here's how those will look using the xScore as an example:
xScore()
& oScore()
:
function xScore() {
var sheet = SpreadsheetApp.getActive();
var xScore = sheet.getRangeByName('xScore').getValue();
sheet.getRangeByName('xScore').setValue(xScore+1); clearBoard();
}
- Line 1: This sets a variable (we'll do this in each function) for the active spreadsheet.
- Line 2: This sets a variable for xScore as the value in the named range xScore (cell G4)
- Line 3: This sets a new value for the xScore cell as whatever it was plus 1.
- Line 4: This runs the clearBoard() function which we'll write next...
clearBoard()
:
This will simply clear the gameboard but leave the score board untouched.
function clearBoard() {
let sheet = SpreadsheetApp.getActive();
let board = sheet.getRangeByName('Board');
board.clearContent();
}
- Line 1: Our sheet variable again.
- Line 2: Our board variable. This is grabbing the range B2:D4 which we named 'Board'
- Line3: This built-in
clearContent()
method simply clears everything in those cells. Pretty simple.
reset()
:
Now we need a function to set the score board and gameboard back to their original states.
function reset() {
let sheet = SpreadsheetApp.getActive();
sheet.getRangeByName('xScore').setValue(0);
sheet.getRangeByName('oScore').setValue(0); clearBoard();
}
- Line 1: our active sheet
- Line 2: we grab our xScore range and set its value to 0.
- Line 3: we do the same for our oScore
- Line 4: we run the reset function to handle the gameboard.
And that's it! Now we can run any of these functions from within the Apps Script editor and see that they work.
screenshot of running code within Apps Script editor
How to Create Buttons
It would be a lot nicer to have buttons in our actual spreadsheet to be able to run the functions.
To do this, we'll draw a button and then assign a script to it.
Select Insert -> Drawing from the menu.
Screenshot of Insert menu in Google Sheets
You can draw anything you'd like, but I chose the basic rounded rectangle.
Screenshot of shapes in Google Sheets drawings menu
Double click in the shape to add text, and resize, recolor, restyle as needed.
Screenshot of button in Google Sheets
Once you've created your button, click Save and Close. Then resize and position it where you'd like it in the Google Sheet. I've put mine right under the scorecard, and I made one for each score as well as a reset button.
Finally, to make the button work, click the three little dots at the top right of the button and select assign script. Then type in the name of the script (without the parentheses).
Screenshot of assigning a script to a button in Google Sheets
Now, all you've got to do is click one of the buttons and the assigned script will run 🔥.
Two notes:
- The first time you run a script, there will be a pop-up dialog box asking you to accept the security allowances. It's a safety net to make sure you know you're running the code that's written in Apps Script, and to examine it if you didn't write it. You'll need to click through those and accept the risk to allow it to run.
- If you need to move a button after assigning the script, you may get frustrated when clicking it doesn't bring up the three dots for the menu and only runs the script. To get around this and allow for movement and the three dot menu, right click the button.
Wrapping Up
I hope this has been helpful for you!
Please subscribe to my YouTube channel here for more content like this.
Have a great one!