Learn Google Apps Script Basics by Building a Tic Tac Toe Game

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:

No alt text provided for this image — 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.

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:

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.

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.

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.

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:

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.

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.

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.

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.

Apps Script

Now for the scorecard logic. Let's open Apps Script by selecting Extensions -> Apps Script from the menu:

We'll write four functions to handle our logic:

xScore() will increment X's score in the scorecard
oScore() will increment O's score in the scorecard
clearBoard() will clear the board but keep the scores
reset() 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.

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.

You can draw anything you'd like, but I chose the basic rounded rectangle.

Double click in the shape to add text, and resize, recolor, restyle as needed.

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

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!