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

Examples


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

menus

UI


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

Baz Roberts (Google+Flipboard / Twitter)


Advertisements

5 thoughts on “Apps Script Basics – if, prompt, menu, & onOpen trigger

  1. ternary operator. Yes
    (attendance < 0.8) ? cellB2.setBackground("red"):cellB2.setBackground("green");
    Could be:
    cellB2.setBackground((attendance < 0.8) ? "red":"green");

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s