Build a guessing game in Google Sheets

In this article, we will build a guessing game using Google Sheets and Apps Script.

Here's how the game will work. First, we will make Google Sheets pick a number. Then, the user will be asked to guess the number repeatedly until they guess correctly.

Here are the concepts that you will learn

• How to use the Math.random() function to make Google Sheets pick a random number.
• How to use UI prompts to get input from the user (in this case the input will be their guess).
• How to check if the user guessed correctly using IF statements.
• How to use the While loop to make the user guess repeatedly until they guess correctly.
• How to attach the script to an image in the sheet so the user can start the game by clicking on the image..

Getting set up

First we need to create a new Google Sheet and open the Script Editor. If you've never done that before, I've written an article that explains how to create your first apps script.

Rename the function in the script editor from myFunction to beginGame. So you should have the following code in the editor.

function beginGame() {

}

Making Google Sheets pick a number

We're going to write a function pickSecretNumber that picks a secret number between 0 and 10.

To do this, we will use the following code Math.round(10 * Math.random()). Let's see how this works:

• The function Math.random() returns a decimal number between 0 and 1 (see the first column in the table below).
• Then we multiply the value returned by Math.random() by 10. This results in a decimal number between 0 and 10 (see the second column in the table below).
• Finally, we use the function Math.round() to round the number to the nearest integer (see the third column for an example).

Let's write a function to pick the number.

function pickNumber() {
  return Math.round(10 * Math.random());
}

Modify the beginGame() function to call the pickNumber() function and store the secret number it returns in a variable.

function beginGame() {
  var secretNumber = pickNumber();
}

Ask the user to guess a number

Now we need to ask the user to guess a number between 0 and 10. We're going to use a prompt to do that.

Create a new function called guessNumber() that will ask the user to guess a number.

function guessNumber() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt("Guess a number between 0 and 10.");
  return result.getResponseText();
}

Modify the beginGame() function to call the pickNumber() function and store the secret number it returns in a variable.

function beginGame() {
  var secretNumber = pickNumber();
  var guess = guessNumber();
}

Check if the user guessed correctly and display a message to the user if they guessed correctly

We'll use an IF statement to check if the user guessed correctly. If they guessed correctly, we will display an alert with a message congratulating the user.

function beginGame() {
  var secretNumber = pickNumber();
  var guess = guessNumber();
  if (secretNumber === parseInt(guess)) {
    alert("Congrats! You guessed correctly.");
  }
}

The above code also calls a new function alert that we haven't written yet. Let's write that function next.

function alert(message) {
  var ui = SpreadsheetApp.getUi();
  ui.alert(message);
}

Try running the code by clicking the play icon.

You should see a prompt displayed in the Google sheet.

Enter a number and click [OK]. If you guess the correct number, you should see the congratulatory message.

Keep asking the user to guess until they guess correctly or they choose to stop

It is going to be hard to guess the correct number on the first try. So you want to keep asking the user to guess a number until they guess correctly. That said, you probably want to give them some way to say they'd like to give up and stop guessing.

Lets modify the function beginGame() to implement this functionality.

function beginGame() {
  var secretNumber = pickNumber();
  while(true) {
    var guess = guessNumber();
    if (guess === "stop") {
      break; 
    } else if (secretNumber === parseInt(guess)) {
      alert("Congrats! You guessed correctly.");
      break;
    }
  }
}

Let's walk through what this code does.

First, the while(true) loop is going to ask the user to keep guessing a number until we break out of the loop by using the break statement.

function beginGame() {
  var secretNumber = pickNumber();
  while(true) {
    var guess = guessNumber();
  }
}

We will break out of the loop if the user guesses correctly or if they enter "stop".

function beginGame() {
  var secretNumber = pickNumber();
  while(true) {
    var guess = guessNumber();
    if (guess === "stop") {
      break; //The user wants to stop.
    } else if (secretNumber === parseInt(guess)) {
      alert("Congrats! You guessed correctly.");
      break; //The user guessed correctly. So stop asking them to guess again.
    }
  }
}

You now have a working game! Awesome :).

Try playing it by clicking the play icon. Do you notice any bugs when you play the game? I quickly noticed one: clicking the [X] button on the prompt does not stop the game 😞.

Let's fix this bug.

Stop asking the user to guess if they close the prompt by clicking [X]

If the user closes the prompt, we should stop the game immediately. To implement this, we will write code to detect that the user clicked the [X] button and we will stop the game.

Since this is related to the prompt, we need to modify the code that handles displaying the prompt. This is the guessNumber() function.

function guessNumber() {
  var ui = SpreadsheetApp.getUi();
  var result = ui.prompt("Guess a number between 0 and 10.");
  if(result.getSelectedButton() === ui.Button.CLOSE) {
    return "stop";
  } else {
    return result.getResponseText();
  }
}

The method result.getSelectedButton() will return which button the user clicked. If it is the close (X) button, we return "stop" to stop the game. Otherwise, we return whatever value the user entered in the prompt. After you make the above changes, test and confirm that this fixes the bug.

Begin the game by clicking an image in your Google sheet

Now that we're done building the game, we probably want to play it several times. It can, however, be frustrating to begin the game by first opening the script editor, then clicking the play icon and then switching back to the Google sheet to make a guess.

You can make it so that the user can launch the game right from the Google sheet itself. A simple way to do this is by assigning the script to an image.

First, insert an image into the Google sheet using Insert →Image →Image over cells.

I inserted the image of a play icon (I guess I was so used to clicking play that I lost all of my creativity!).

Select the image and click the three-dot-menu. Then, select Assign script.

Enter beginGame and click [OK].

Now you can begin the game by clicking the image :). Isn't that so convenient!

Summary

In this article, you learned how to build a guessing game using Google Sheets and Apps Script. You also learned how to:

• Use the Math.random() function to generate random numbers.
• Use prompts to get user input.
• Use the while loop to keep asking the user to guess while giving them several ways to stop the game.
• Assign the script to an image in your Google sheet so you can run it by clicking the image.

Future ideas

Here are some ideas for you to try out on your own.

• Give the user feedback (did they guess too low or too high?)
• Track how many attempts the user took to guess correctly. Then, save this number in the spreadsheet so the user can track their performance over time.

Thanks for reading!