Apps Script Basics – SpreadsheetApp & for in loop

In this post, we’re going to look at the G Suite services, the Google documentation that’s available to help you, and then focus on one particular area, SpreadsheetApp to then create some spreadsheets. We’re also going to see the really useful for in loop in action. It’s important to have an understanding of how the different parts of Apps Script fit together. So, first let’s go to the Google documentation, which can be found at:

On the left, we have a list of the G Suite services available. As you can see below, we can work directly with most of the main apps within G Suite, from calendars to the spreadsheets. As your codes get more complex, you will start working with various services at the same time. So, for example, we could have data in a spreadsheet and create a Google Doc from it, or we could use that data to create a Google Form and to create an event on the calendar.

G Suite Services

7SpreadsheetApp - 28

Spreadsheet Service

In this post, we’re going to focus on the Spreadsheet service, which will provide an example as to how all this works together. As we can see from the description below, the Spreadsheet service allows us to work with Google Sheets.

7SpreadsheetApp - 33

Under the Service, we have the Classes. These are basically sub categories of the service. The first time you look at the list below, the number of different classes scares you a little and you wonder where to start! But when you start looking at the details, it’s not that difficult. For example, in the list below, half of the classes just refer to things you can do with charts.

7SpreadsheetApp - 347SpreadsheetApp - 35

The main four you will use time and time again are at the bottom of the list.

7SpreadsheetApp - 36

There is a hierarchy to these:

SpreadsheetApp > Spreadsheet > Sheet > Range

In general terms, if you want to edit some cells in a spreadsheet, you need to tell the code which spreadsheet you’re working with, which sheet you want to edit, and then which range.

Let’s focus on just the first of those classes, SpreadsheetApp. On the menu on the left, click on Spreadsheet (if not already open) and SpreadsheetApp.


7SpreadsheetApp - 29

This will open the documentation for the SpreadsheetApp. As we can see from the description below, this class is mainly for creating and opening Google Sheets.

7SpreadsheetApp - 30

Underneath classes we have methods. These are the specific instructions that tell the code to do a specific job. On the right of the page, we can see a list of the methods available under the SpreadsheetApp class. The names of them usually give a good indication as to what they do. If you want to jump to information about a particular method, just click on the method in this list and it will take you to the information for that method.

7SpreadsheetApp - 31

Here, I’ve clicked on the method create(name):

Sometimes, it gives you an example showing how to use it. Plus, the parameters it needs. So, in this case the name in the brackets needs to be a string. Finally, at the bottom it tells you what is returned. Here it’s a new spreadsheet.

7SpreadsheetApp - 37

OK, so now we have an overview of services, classes, and methods, let’s jump in and work our way through some examples, showing how the SpreadsheetApp works.

Creating menu – SpreadsheetApp.getUi

First, let’s create a menu to allow us to run all the examples from it without having to go back to the script editor every time. Conveniently, this will also show one of the methods linked to the SpreadsheetApp class.

7SpreadsheetApp - 1b

Line 2: Use the name onOpen() to trigger it to run when the user opens the spreadsheet.

Line 3: Here, we use the SpreadsheetApp class and the getUi() method as we’re editing the user interface of the spreadsheet.

Line 4: We create a menu using createMenu() and give it a name.

Lines 5-11: Then, we add the items in the menu using additem(). In the brackets add, the name of the items and the function names they correspond with.

Lines 12-13: Finally, we add all this to the Ui using addToUi() and end it with a semi-colon and close the function with a curly bracket.

As I’ve mentioned in an earlier post, it’s useful to store this chunk of code in a separate script file, as you’ll probably be using it for various projects, so you’ll want to just copy and paste it in and then edit it to save you time.

Global variables

In some of the following examples we’re going to be using the same spreadsheet and the same sheet, so instead of repeating the code in various examples, we can write them outside the functions, so that the same code can be used in different functions. These are called global variables and this just means that the variables are available to all the functions in this piece of code, not just within a particular function as is often the case. So, we’re going to set up two variables, one for the active spreadsheet and one for the active sheet.

7SpreadsheetApp - 2

Line 16: We get the active spreadsheet using the SpreadsheetApp class and the getActiveSpreadsheet() method amd store it in the variable ss.

Line 17: We get the active sheet from the variable we just set up, ss, along with getActiveSheet() and store it in the variable sheet.

Example 1 – Creating a new spreadsheet

Let’s start with an easy example. We’re going to create a new spreadsheet and call it “New Spreadsheet1”.

7SpreadsheetApp - 24

7SpreadsheetApp - 3

Line 20: We set up the function and call it example1.

Lines 21-22: Start with SpreadsheetApp then use the create() method to make a new spreadsheet. In the brackets, add the name you want to give the file. Then close the function.

Run the function example1 and we can see in our My Drive, the newly created spreadsheet:

7SpreadsheetApp - 13

Example 2 – Creating a spreadsheet with limited rows & columns

We’re going to create another spreadsheet but this time we’re going to add some extra information in the brackets to set it up with a fixed number of rows and columns.

7SpreadsheetApp - 25

7SpreadsheetApp - 4

Line 25: Set up the function called example2.

Line 26: We again use the create method, but this time there are 3 pieces of information within the brackets: spreadsheet name, number of rows, number of columns

As we can see it created a spreadsheet as before.

7SpreadsheetApp - 14

And we have set it up with a fixed number of rows and columns in it.

7SpreadsheetApp - 38

Example 3 – Creating a spreadsheet with a name from the sheet

Quite often the name we want to give the newly created spreadsheet is taken from some source of data. Here, we’re going to get the name of the spreadsheet from one of the cells in the original spreadsheet.

7SpreadsheetApp - 5

Line 30: Set up the function example3.

Line 31: First, we need to get the name from the cell on the sheet. We get the sheet (from the global variable sheet we set up earlier), get cell A1 using getRange() and get its value using getValue(). Then we store it in the variable name.

Line 32: Then we use the create() method we used before, but this time use the variable name in the brackets.

As we can see, it has named the new spreadsheet with the name from cell A1.

7SpreadsheetApp - 39

7SpreadsheetApp - 12

Example 4 – Creating multiple spreadsheets with different names

This time let’s take it a step further and create 3 different spreadsheets using names from the original sheet. We’ll use a for in loop to make the different spreadsheets.

7SpreadsheetApp - 6

7SpreadsheetApp - 42

Line 37: First, we get the names of the spreadsheets from the sheet. These are stored in cells B1 to B3. As there is more than 1 name, we use getValues() to store the names in an array and we’ll call that array names. Here’s what the content of that array looks like:

7SpreadsheetApp - 40

Now, we start the for in loop. The beauty of the for in loop is that it will automatically work out how many items are in your array and loop that many times around. So, in our names array we have 3 names, so it will go around the loop 3 times, and do whatever we put in between the curly brackets, 3 times. I.e. in this case, it will create 3 different spreadsheets.

The syntax for a for in loop is:

for (counter variable in array name) {

Do something x the number of items in the array


7SpreadsheetApp - 6

Line 38: Here, we’ll use i as the counter and we add names as it’s the array we want to loop through.

Line 39: Each loop we want to create a spreadsheet, so we use the SpreadsheetApp and create() as before. This time in the brackets, we refer to the information in the array name and we need to move position within that array each time we go around. So, the first name will be at position 0 (name[0]), then position 1 (name[1]), and so on. The counter i automatically starts at 0.

Lines 40-41: Then we close the loop and the function.

As we can see, it’s created the 3 spreadsheets and given each one a different name, which has been taken from the cells B1 to B3 in our original sheet. All with very few lines of code!

7SpreadsheetApp - 15

Example 5 – Getting data from one spreadsheet and adding it to another (URL)

Here, we’re going to see how easy it is to get data from one spreadsheet and to add it to another spreadsheet.

7SpreadsheetApp - 27

7SpreadsheetApp - 11

7SpreadsheetApp - 41

Line 45: First, we need to get the text in cell C1 in the first spreadsheet. Use getRange() and getValue() to get that, using the global variable sheet again. Then store it in the variable text.

7SpreadsheetApp - 7

Line 46: Now, we need to get the other spreadsheet. We do that by ‘opening’ it. Note, this doesn’t open it for the user but it does on the server-side and tells the code that we are now working with this new spreadsheet. Here, we’re going to open it by its URL. So, use the openByUrl() method and add the complete URL in the brackets between quote marks. Store the spreadsheet in newSS.

Line 47: We then use newSS to tell the code that’s the spreadsheet we want to use, then we get the active sheet and add the text using getRange() and setValue(). This will add the text in cell A1 in our new spreadsheet.

7SpreadsheetApp - 16

7SpreadsheetApp - 17

Example 6 – Getting data from one spreadsheet and adding it to another (ID)

This is very similar to the previous example but this time we’re going to use the ID of the new spreadsheet and not the URL. The ID is the part with random looking letters and numbers after the /d/ and before the /edit parts.

7SpreadsheetApp - 26

7SpreadsheetApp - 8

Personally, I always try to always work with the ID and not the URL as the format is cleaner to work with.

As we can see, in cell A2 it’s added the text from the original spreadsheet. The text in cell A1 is from the previous exmaple.

7SpreadsheetApp - 18

Example 7 – Creating multiple spreadsheets with different names and different pieces of text

Finally, let’s create 3 different spreadsheets each with its own name, which we will get from our original sheet. Plus, we will add a different piece of text in each one. This is just an expansion of what we’ve seen so far and you’ll see it’s really easy to do.

7SpreadsheetApp - 19

7SpreadsheetApp - 9

Line 59: First, let’s get the list of spreadsheet names from cells D1 to D3 and store it in the array ssNames.

Line 60: Then, let’s get the list of texts we’re going to add into the spreadsheets and store them in the array texts.

7SpreadsheetApp - 10

Line 62: We’re going to loop down the both lists so let’s set up a for in loop. As both our lists are the same length, we can just use one of them to count the number of times we go around the loop. Here, we’re using the ssNames array.

Line 63: First, we want to create the spreadsheets and give it a name from the array ssNames. As before, we control which name we’re using by the counter i. So, for example, spreadsheet 1 will use the first name in the array. We store the spreadsheet in the variable spreadsheet.

Line 64: Then, we call that spreadsheet and get its active sheet and cell A1, and add the text from the text array at the current position. So, for example, in spreadsheet 1, we’ll use the first piece of text.

As we can see, it’s created the 3 different spreadsheets, given them individual names, and added a different piece of text in each one.

7SpreadsheetApp - 20

7SpreadsheetApp - 21

7SpreadsheetApp - 22

7SpreadsheetApp - 23

The code can be found here on GitHub.

You can make a copy of the file here.

Link to the google reference page on SpreadsheetApp

More info on for in loops

eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Google+Flipboard / Twitter)

Apps Script Basics – if, prompt, menu, & onOpen trigger

In this post, we’re going to look at how we can get the computer to react to data in a spreadsheet and to user input. We’re going to look at the following:

  • If, else if, and else statements – to allow the program to make decisions
  • Create your own menu in the spreadsheet to run your programs from
  • Automatically set up the menu using the onOpen trigger
  • Creating a dialogue box to allow the user to enter data using ui.prompt

Creating a menu

Running scripts from the Script Editor is fine when you’re writing them but you don’t want your user to have to open the Script Editor every time they want to run your code. One of the best and easiest ways is to create a new menu. Here, we’re going to add all the examples to a single menu within our spreadsheet.

Open the Script Editor from the Tools menu.

First Scripts - 2

Delete the code already in the editor and rename the project. See my earlier post on how to do this.

First Scripts - 5

6If - 31

Line 1: Start with the function line and call it onOpen(). It has to be called this as this will tell the computer that you want this function to run every time the spreadsheet is opened. This is called a trigger.

Line 2: We need to get the spreadsheet’s user-interface, so we use first the SpreadsheetApp class and then the method getUi(). Note, we don’t add a semi-colon as most of these lines are all connected to each other.

Line 3: Now, we need to create the menu and give it a name. Use the createMenu() method and in the brackets add the name of the menu you want in quote marks.

Lines 4-13: Now, we add the items in our menu. So, logically we use the addItem() method for each one. In the brackets you need to add the name of the item (i.e. what the user can see) and the name of the function that will be run when the user clicks on that name. There’s one line per item.

Lines 14-15: Finally, we add it all to the user interface by using addToUi(). Note, as it’s the last line we need to add the semi-colon. Then we close the function with a curly bracket.

Refresh the spreadsheet page and the new menu will appear. At the moment, clicking on the options won’t do anything as we haven’t written the functions for each one yet.

6If - 38

Note, when you refresh the page the script editor closes, so you’ll have to reopen it to work on it further.

Example 1 – Set the background to red if the attendance is < 80%

Here, we’ll start with a simple example to show the use of the if statement. We have the attendance figure for a student and we want to fill the cell red if the attendance is less than the required 80% minimum.

6If - 32

6If - 1

Line 2: Start with the function line.

Line 3: We need to get the current active sheet. We can do this directly from the SpreadsheetApp class by using getActiveSheet(). We’ll then store that in the variable ss for later use.

Line 4: Now, let’s get the attendance figure. We do that by getting the range using getRange(), in this case just one cell, B3, and then get its value using getValue(). Then we’ll store it in the variable attendance.

Now we want to check if the attendance figure is less than 80% and if so, we’ll fill the cell in red. We need an if statement for this and if you’ve used the IF function in spreadsheets before, you’ll be familiar with this concept. If not, I’ll explain it here.

If statements have the following structure:

If (a condition) {

Do something if the condition is true


6If - 2

Line 6: First we start with the keyword if then in the brackets we put the condition we want to check for. In this case, it’s if the figure in the attendance variable is less than 80% (or 0.8). Then we open the curly brackets.

Line 7: If this is true, we want to change the background colour to red. so, we get the range we want using getRange() and set its background colour to red, using setBackground().

Line 8: Close the if statement with a closed curly bracket.

Line 9: Close the function with another curly bracket.

Back on the spreadsheet, open the New menu and select Example 1. This will run the code and if the figure is less than 80% it will change the background to red.

6If - 39

And sure enough, it has. The problem with this code is that it will only do something if the condition is true. So, if we changed the figure to 90% and ran it again, it would do anything as the condition is now false and we’ve not told it to do anything if the attendance is more than 80%, so the cell would remain red.

6If - 40

Example 2 – Set the background to red if the attendance is < 80% or otherwise green

This time let’s adjust our code to do something if the condition isn’t true.

6If - 3

Lines 13-15: Same as above, except call the function example2.

6If - 4

Line 17-19: The if statement is the same as above.

6If - 5

This time we’re going to add an else statement. This runs if the above if statement doesn’t. It’s like saying if the condition is not true, run this one.

Line 21: Here, we just need the else keyword, then open the curly brackets.

Line 22: Then we state what we want it to do. In this case, we want it to change the background to green, if the attendance figure isn’t less than 80% (i.e. is 80% or more).

Lines 23-24: Close the else statement with the curly bracket, and the function too.

6If - 41

6If - 39

Example 3 – Set the background to red if the attendance is < 70%, yellow 70-80%, green 80% or more

So, far we’ve only had 2 options but we can set up as many alternative options as we like by using the else if statement. In this example, let’s have 3 attendance bands. Less than 70% it’s red, 70% to 80% it’s yellow, 80% or more it’s green.

6If - 6

Lines 29-31: As before.

6If - 7

Line 33: First, let’s check if the attendance figure is less than 70%.

Lines 34-35: If so, it changes the background to red. Then close the if statement.

6If - 8

If the above isn’t true then we want to check to see if it’s less than 80%. Note, as we’ve already checked to see if it’s less than 70% and it’s not, really want we’re doing here is checking that it’s between 70% and 80%.

Line 37: As this is an alternative option we use else if. This works in the same way as the if statement.

Line 38: This time we change it to use if it’s true.

6If - 9

Lines 41-44: Finally, if none of the above are true then we use the else function to run a default action, i.e. change it to green, as we now it has to be 80% or more.

6If - 42

Example 4 – Set the background to red if the attendance is < 80% OR the exam mark is < 70%

Now, we have the situation where the student has to have attended more than 80% of classes and to get more than 70% in the exam to pass the course. So, we can think of it in another way, if he has less than 80% in attendance OR gets less than 70% in the exam, he won’t pass and we need to highlight this in red.

6If - 33

6If - 10

Lines 48-51: Similar to the examples before, except this time we need the attendance figure from cell E3 and the exam mark from cell F3.

6If - 11

Lines 53-55: This time we’re going to check for 2 different conditions and check if one is true or the other is true. We start with the attendance check then follow this by the 2 pipes || then the exam check. This checks to see if the attendance is less than 80% OR if the exam mark is less than 70%. If either or both are true, it colours the student’s name in cell D3 red.

6If - 12

Lines 57-60: As before, if the above isn’t true, then we’ll colour it green.

6If - 43

6If - 44

6If - 45

Example 5 – Set the background to green if the attendance is 80% or more AND the exam mark is 70% or more

We could look at the previous example from a different way and change the background to green, if both the attendance figure is 80% or more AND the exam mark is 70% or more.

6If - 33

6If - 13

Lines 64-67: As before.

6If - 14

Lines 69-71: Again we will check for two conditions, but this time we want to know if both are true. We start with the attendance check (80% or more) then follow it by the 2 ampersands &&, which mean AND. Then we add the exam check. If both of these are true we change the cell colour to green.

6If - 15

Lines 73-76: If at least one of them isn’t true then it runs the else statement and fills the colour red.

6If - 45

Example 6 – Get the student’s data and add it to their individual sheet

Now we’ve looked at the basics of if, else if, and else, let’s look at how we can use the input from a user and get the program to act accordingly based on that input. Here, we have a sheet with the attendance and exam marks for 4 students. What I’d like the program to do is when I type in the student’s name in cell B5 and I run the program, it will get their data and write it on their individual sheet.

6If - 34

6If - 35

This time as we’ll be using multiple sheets, let’s be specific about which sheets we’re using. Using just getActiveSheet() may cause problems if we haven’t got the right sheet open.

6If - 46

Line 80: First let’s get the active spreadsheet and add it to the variable ss.

Line 81: Then, let’s get the sheet called Sheet1 by its name by using getSheetByName().

Line 82: Then we get the student’s name on sheet1 from cell B5 and store it in the variable name.

6If - 17

We’re going to check the name the user’s entered in cell B5 is equal to one of the 4 students we have in our table. Then it will get the corresponding data for that student.

Line 84: First, we check if the name is the same as John. Note, the use of the triple equals sign to mean equals to. One common mistake is to write a single one here, which is want is used to assign a value, for example to assign it to a variable.

Line 85: If it is John, then we get John’s data from the range A7 to E9 and store it in the figures variable.

6If - 18

We do the same for each of the students but here we use else if as these are alternatives to the first if statement. For each student we just change the range. Whatever name is chosen the appropriate figures are added to the figures variable.

Now, we have the student’s name (stored in name) and the data (stored in figures), we need to add it to their individual sheet.

6If - 19

Line 100: First, we need to get the sheet we want. We do this by getting the sheet by its name. The name will depend on the one which the user typed in, which is stored in the variable name. I’ve added the active() method so that it opens that sheet.

Line 101: Finally, we add the data from Sheet1 to the student’s sheet. We get the student’s sheet, get the range we want (note the size of the range must match the size of the original range) and then we use setValues() to add the values to the student’s sheet.

Run the code and you will see the student’s sheet open and the following data added. As you can see it’s not formatted. It’s important to remember that when we use getValues() we are only getting the values, and not any of the formatting. We would have to set the formatting to make it look a bit prettier. Check out my post on formatting sheets.

6If - 37

Example 6b – Return back to first sheet

Having a code which takes us to a specific sheet is great, but what happens when we want to go back to the original sheet? This little piece of code does just that.

6If - 20

Line 107: The key line here is to get the sheet called Sheet1 and to open it. In the menu I’ve called this “return to sheet 1”.

Example 7 – Ask for the student’s name, then open their individual sheet

In all the examples above, we’ve used data in the spreadsheet as the input. Now, let’s make this a little bit more professional. Let’s open a dialogue box where the user will enter the student’s name they want and to keep it simple, let’s just open that student’s sheet. You could of course, adapt the code from the previous example, to add the data to the sheet.

6If - 22

Line 113: As with the creating the menu, we first need to get the user interface, using the getUi() method, then store it in the variable ui.

Line 114: Then we use the prompt() method to display a dialogue box which will require the user to enter something. In the brackets we add the text we want to show in the box. The prompt we’ll store in the variable response.

6If - 47

The user has 2 options, either they type something in and click OK or they could just close the dialogue box without entering anything.

6If - 23

Line 116: First, we check to see if they have clicked the OK button. We get the prompt which is stored in response and get the button that was selected by using getSelectedButton(). Then, we check to see if this is the same as the OK button by getting the OK button in the user interface by using ui.Button.OK.

Line 117: If it is the OK button, then we get the name that was added in the box by the user. We do this by getting the response text using getResponseText() and we store this in the variable name.

Line 118: Then we open the sheet with the name stored in the variable name, by using getSheetbyName() and activate().

Line 119: If the user does something else, i.e. doesn’t click OK but closes the dialogue by clicking on the X, then we have an alternative option. Here, I don’t want it to do anything, so I use else and leave it blank in between the curly brackets, meaning it won’t run anything if the OK button wasn’t pressed.

Lines 120-121: Close the else and if statements with the curly brackets.

Running the code, we will see the dialogue box open prompting us for a name.

6If - 47

Enter a name and click OK and this will take us to that student’s sheet.

6If - 48

One problem with this code is that if the user enters a name that doesn’t match one of the 4 students, it will throw an error. In a future post we’ll look at dealing with errors.

Example 8 – Set the background to red if the attendance is < 80% or otherwise green (ternary operator)

Referring back to example 2, we could simplify the code a little by using an alternative way to deal with a condition that is either true or false. It’s called the ternary operator and instead of writing 6 lines of code we can do it in just 1.

We want to check if John’s attendance is less than 80% and if so, change the background to red and if not change it to green.

6If - 32

6If - 24b

Line 125: We get the active sheet (make sure you have sheet1 open when running this).

Line 126: We get the cell B3 and store it in the variable cellB3. I’ve done this as we will be using the range later on.

Line 127: Then, get the attendance figure in that cell.

6If - 25

This has the following format:

(condition to check) ? action to take if it’s true:action to take if it’s false;

Line 128: We check if the attendance figure is less than 80%, then there’s the question mark. Then we state we want to change the background to red if it’s true. Then a colon, then we want to change the background to green if it’s false.

As we can see it does exactly the same as example 2 but with less code:

6If - 39

To simplify the line even further, you could also write this with the following syntax: cellB2.setBackground((attendance < 0.8) ? “red”:”green”)) *Thanks to Michael O’Neal for that.

Example 9 – Set the background colour for all students’ attendance figures

Writing code for just one student is not really a good use of Apps Script and using Conditional Formatting would be far better. The next could also be done using conditional formatting, but we’re here to learn Apps Script right? And here we’re going to combine a for loop with the if statements, so consolidating the learning from my previous post on loops.

Here we have a table of students’ attendance figures and we’re going to add red, yellow, or green depending on the figure for each.

6If - 36

6If - 49

Line 134: First, get the sheet called Sheet1.

Line 135: Then get the range I2 to I5. As there is more than one value, we use getValues(). Then we store it in the variable attendanceFigures.

6If - 27

In the array attendanceFigures, in index 0, we have the value from cell I2; in index 1 from value from I3, and so on.

Line 137: Set up the for loop and we want to go from 0 (the first value in the array).

We want the loop to continue until the end of the range, i.e. I5 (index 3 in the array). We could state that it continues while i<4, but the code can easily work out how many figures are in the array, by getting its length, using the length property. So, attendanceFigures.length = 4.

Then we want to move down the rows one at a time, so we increase i by 1 each time using i++.

6If - 50

Line 139: We’re going to need the row number, and we can get it by linking it to the array index. So, array index 0 is row 3 (I3), so let’s make the variable r 3 more than the variable i, whihch is the array index.

6If - 51

Then within the curly brackets, we carry out the if checks.

Line 141: We check if the figure in attendanceFigures is less than 70%. The first time around the loop it will check if the attendance figure in cell I2 is <70%, as i = 0, and so on.

Line 142: If it is <70%, then it gets the range and changes the background to red. The range (row, column) will be (r, 9), as for example, we are starting in row 3 (I3) and we are in column 9, as it’s column I.

6If - 52

We then check if the attendance is less than 80%, using the else if statement.

6If - 53

Lines 149-150: Finally, if it’s neither less than 70% or 80% it must be 80% or more, so we change the background to green.

Lines 151-153: We close the else statement, for loop, and function with the curly brackets.

6If - 54

When you run the code for the first time, you’ll be asked to authorise it. Click ‘Review Permissions’.

6IF 100

Click the your account.

6IF 101

Then click ‘Allow’.

6IF 102

Here’s the link to make a copy of the file, which contains the code.

You can find the code here at GitHub:

onOpen menu


If you want to learn more check out these links on the W3schools site:

Comparison and logical operators

if, else if , else, ternary operator

Plus, more information on menus and the user interface (dialogue boxes, etc):



eBooks available on Drive, Forms, Sheets, Docs, Slides, and Sheet Functions:

Baz Roberts (Google+Flipboard / Twitter)