Google Apps Script is basically JavaScript

It uses JavaScript 1.6 mostly, with some additions from 1.7, 1.8 (source).    Realistically, as long as you stay clear of ECMAScript 5 & 6, you should be fine.  The first step to writing Apps scripts is to learn JavaScript, at least the basics such as:

Here’s a great starting point: https://www.codecademy.com/learn/learn-javascript

The 4 main methods of running a script

If you’re just starting out, you’re likely to copy and paste a script someone else has written into your Spreadsheet.  The first obstacle you’re likely to run into is “how do I run this apps script?”

There are a number of ways to run a script, but we’re going to focus on 4 of the simplest ways excluding Add-ons and web apps. Before we begin, we’re going to be running a simple guessing game.

Please go to https://github.com/dsottimano/guessing-game-gdocs/blob/master/index.js#L1 and copy the code from the index.js file to your clipboard.

1) Running through the Script editor

In the top UI, go to Tools > Script Editor and paste in the code from the Github guessing game repository

Then select guessingGame from the script menu selector and hit run. Afterwards, you should get prompted for authorization, just go ahead and authorize and re-run the script.

Go back to your Spreadsheet and you should see the first inputbox for the game:

2) Running the script by attaching a function to the spreadsheet UI

This is going to require a little bit of extra code. Go back to your script editor and paste in the following function at the top of the code block:


function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Guessing Game')
.addItem('Play!', 'guessingGame')
.addToUi();
}

Like so:

Run the onOpen script and go back to your Spreadsheet – you should now see your custom menu.  Sidenote: what is onOpen? It’s a standard trigger that will run each time you open your spreadsheet, that means you’ll never have to run it again as it will always recreate the top menu for you.

3) Running the script by attaching a function to an image

Google Spreadsheets allows you to add images/drawings or other “objects” directly into the Spreadsheet. We can also assign a script to them. Go to insert > drawing:

Create a button:

Click on the button menu when the button appears in the spreadsheet and choose assign script:

Assign the script by entering: guessingGame

That’s it, click on the button and the game will start.

4) Running the script by calling it directly from a spreadsheet cell

These are called custom functions or custom formulas, and our current code isn’t going to work this way.  The Google Scripts that will be featured on this site are largely going to be custom functions, so you’ll need to understand the reasons why you can’t call some custom functions directly from the spreadsheet.

For the purpose of demonstrating, go ahead and type in =guessingGame() into any cell:

You’ll get a permission error. Why? We can’t manipulate other cells from a custom function called in a spreadsheet and we cannot manipulate the UI either. Our guessingGame script won’t work because we’re calling UI elements such as msgbox and inputbox.

However, for the sake of this tutorial, I also included a primitive guessGame() function that will work as a custom function, it simply takes in a number from the user and if the number guessed is the same as the random number generated by the computer, you win, else you lose.

Try adding this formula in any cell, pick any number from 1 to 5 and add it as the argument: =guessGame(2)

If you’re a bit lost, play with the spreadsheet

Make a copy of https://docs.google.com/spreadsheets/d/1qDXnfoRbV8vEv9xMKML5yz4GfMeUzdum9WH5QGajn0c/edit#gid=0 and you should have all the scripts pre-loaded. Poke around, break stuff, learn.

Next, move onto working with the script editor.