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 Apps Script editor from the Extensions menu.

Creating new script file

A script project can have more than one script file. As a general rule, for longer programs, it’s a good idea to separate them into parts, as it’s easier to find the part you want.

Let’s create a new script file to put the menu code in.  Click on the plus icon next to Files.

Then select Script. It will prompt you to name the script file.

Type in a name. Here, I’ve called it “onOpen” just because the function will be called that, but you can call it whatever you like.

Now you have two script files, the original default one called “Code” and the one we’ve just made “onOpen”. “.gs” shows that they are script files.

Rename the project, for example, “5menu”.

Create a new spreadsheet menu

Click on the “Code” script file. Delete the code already in the editor.

1.	function onOpen() {
2.	  SpreadsheetApp.getUi()
3.	                .createMenu('New menu')
4.	                .addItem('Example 1', 'example1')
5.	                .addItem('Example 2', 'example2')
6.	                .addItem('Example 3', 'example3')
7.	                .addItem('Example 4', 'example4')
8.	                .addItem('Example 5', 'example5')
9.	                .addItem('Example 6', 'example6')
10.	                .addItem('Return to Sheet1', 'example6b')
11.	                .addItem('Example 7', 'example7')
12.	                .addItem('Example 8', 'example8')
13.	                .addItem('Example 9', 'example9')
14.	                .addToUi();
15.	}

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 the following 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. Note, quote marks can be single or double ones but they must match.

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.

Run the function and you will see it’s added a menu to the spreadsheet. Your very own menu!

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.

1.	//Set background to red if attendance is less than 80%
2.	function example1() {
3.	  const ss = SpreadsheetApp.getActiveSheet();
4.	  const cellB3 = ss.getRange("B3");
5.	  const attendance = cellB3.getValue();

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.

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. We store this in the variable cellB3, as we will use this again later.

Line 5: Then we get the value of cell B3 using getValue(). 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

}

7.	  if (attendance < 0.8) {
8.	    cellB3.setBackground("red");
9.	  }
10.	}

Line 7: 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 8: If this is true, we want to change the background colour to red. So, we use the range stored in the variable cellB3 and set its background colour to red, using setBackground().

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

Line 10: Close the function with another curly bracket.

Back on the spreadsheet, open the new menu and select Example 1. You’ll have to authorize the script the first time. This will run the code and if the figure is less than 80% it will change the background to red.

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 wouldn’t 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.

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.

12.	//Set background to red if attendance is less than 80%
13.	//Otherwise set it to green
14.	function example2() {
15.	  const ss = SpreadsheetApp.getActiveSheet();
16.	  const cellB3 = ss.getRange("B3");
17.	  const attendance = cellB3.getValue();

Lines 14-17: Same as above, except call the function example2.

19.	  if (attendance < 0.8) {
20.	    cellB3.setBackground("red");
21.	  }

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

23.	  else {
24.	    cellB3.setBackground("green"); 
25.	  }
26.	}

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 23: Here, we just need the else keyword, then open the curly brackets.

Line 24: 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 25-26: Close the else statement with the curly bracket, and the function too.

Run the script and if you have a figure in B3 that’s less than 80% the background will be red and if the figure is 80% or more it’ll be green.

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.

28.	//Set background to red if attendance is less than 70%
29.	//Set background to yellow if attendance is 70-80%
30.	//Set background to green if attendance is 80% or more
31.	function example3() {
32.	  const ss = SpreadsheetApp.getActiveSheet();
33.	  const cellB3 = ss.getRange("B3");
34.	  const attendance = cellB3.getValue();

Lines 31-34: As before.

36.	  if (attendance < 0.7) {
37.	    cellB3.setBackground("red");
38.	  }

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

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

40.	  else if (attendance < 0.8) {
41.	    cellB3.setBackground("yellow");
42.	  }

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 40: As this is an alternative option we use else if. This works in the same way as the if statement.

Line 41: This time we change it to yellow if it’s true.

44.	  else {
45.	    cellB3.setBackground("green"); 
46.	  }
47.	}

Lines 44-47: 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 know it has to be 80% or more.

Adding the figure 75% and running the code, we can see it’s changed the background to yellow.

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 name in red.

49.	//Set background of name to red if
50.	//attendance is less than 80% OR exam is less than 70%
51.	function example4() {
52.	  const ss = SpreadsheetApp.getActiveSheet();
53.	  const cellD3 = ss.getRange(“D3”);
54.	  const attendance = ss.getRange("E3").getValue();
55.	  const exam = ss.getRange("F3").getValue();

Lines 51-55: Similar to the examples before, we get cell D3, ready to add the appropriate colour, except this time we need the attendance figure from cell E3 and the exam mark from cell F3.

57.	  if (attendance < 0.8 || exam < 0.7) {
58.	    cellD3.setBackground("red");
59.	  }

Lines 53-55: This time we’re going to check for 2 different conditions Lines 57-59: 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 || (normally found on the 1 key) 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.

61.	  else {
62.	    cellD3.setBackground("green"); 
63.	  }
64.	}

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

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.

66.	//Set background of name to green if
67.	//attendance is 80% or more AND exam is 70% or more
68.	function example5() {
69.	  const ss = SpreadsheetApp.getActiveSheet();
70.	  const cellD3 = ss.getRange("D3");
71.	  const attendance = ss.getRange("E3").getValue();
72.	  const exam = ss.getRange("F3").getValue();

Lines 68-72: As before.

74.	  if (attendance >= 0.8 && exam >= 0.7) {
75.	    cellD3.setBackground("green");
76.	  }

Lines 74-76: 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.

78.	  else {
79.	    cellD3.setBackground("red"); 
80.	  }
81.	}

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

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.

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.

83.	//Open sheet from name in cell B5 and paste student's data
84.	function example6() {
85.	  const ss = SpreadsheetApp.getActiveSpreadsheet();
86.	  const sheet1 = ss.getSheetByName('Sheet1');
87.	  const name = sheet1.getRange("B5").getValue();

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

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

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

89.	  if(name === "John") {
90.	    var figures = sheet1.getRange("A7:E9").getValues();
91.	  }

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 89: 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 90: If it is “John”, then we get John’s data from the range A7 to E9 and store it in the figures variable. Note, we need to use var here as we will be using the variable outside this if statement, so outside this scope. So, let or const wouldn’t work as they wouldn’t be “seen” or defined.

93.	  else if(name === "Ringo") {
94.	    var figures = sheet1.getRange("A10:E12").getValues();
95.	  }
96.	  
97.	  else if(name === "Paul") {
98.	    var figures = sheet1.getRange("A13:E15").getValues();
99.	  }
100.	  
101.	  else if(name === "George") {
102.	    var figures = sheet1.getRange("A16:E18").getValues();
103.	  }

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.

Assuming we’ve written one of the 4 names, we have the student’s name (stored in name) and the data (stored in figures), we need to add it to their individual sheet.

105. const studentSheet = ss.getSheetByName(name).activate();
106.	  studentSheet.getRange("A1:E3").setValues(figures);
107.	}

Line 105: 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 106: 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 in one go.

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 or to format the student sheets beforehand.

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.

109.	//Return back to Sheet1
110.	function example6b() {
111.	  const ss = SpreadsheetApp.getActiveSpreadsheet();
112.	  const sheet1 = ss.getSheetByName('Sheet1').activate();
113.	}

Lines 110-113: The key line here is to get the sheet called Sheet1 and to open it. In the menu on the spreadsheet, I’ve called this “Return to Sheet1”.

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.

115.	//Ask which sheet user wants to open and then open it
116.	function example7() {
117.	  const ss = SpreadsheetApp.getActiveSpreadsheet();
118.	  const ui = SpreadsheetApp.getUi();
119.	  const response = ui.prompt('Enter name:');

Line 118: 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 119: 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.

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

121.	  if (response.getSelectedButton() === ui.Button.OK) {
122.	    const name = response.getResponseText();
123.	    ss.getSheetByName(name).activate();
124.	  }
125.	}

Line 121: 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 122: 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 123: Then, we open the sheet with the name stored in the variable name, by using getSheetbyName() and activate().

Lines 124-125: Close the if statement and function.

Here, I haven’t used an else statement, as if they just close the dialogue box, I don’t want anything to happen, so an else statement isn’t needed.

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

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

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.

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

Referring back to example2, 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.

127.	//Using ternary operator-If less than 80% set background red, otherwise green
128.	function example8() {
129.	  const ss = SpreadsheetApp.getActiveSheet();
130.	  const cellB3 = ss.getRange("B3");
131.	  const attendance = cellB3.getValue(); 
132.	  (attendance < 0.8) ? cellB3.setBackground("red"):cellB3.setBackground("green");
133.	}

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

Line 130: We get the cell B3 and store it in the variable cellB3.

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

This has the following format:

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

Line 132: 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:

To simplify the line even further, you could also write this with the following syntax: cellB3.setBackground((attendance < 0.8) ? “red”:”green”))

I.e. put the ternary conditional in the method setBackground’s brackets.

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 Sheet’s 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 the previous chapter 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.

135.	//Set background colour for all students' attendance figures
136.	function example9() {
137.	  const ss = SpreadsheetApp.getActiveSpreadsheet();
138.	  const sheet1 = ss.getSheetByName('Sheet1');
139.	  const attendanceFigures = sheet1.getRange("I3:I6").getValues();

Line 138: First, get the sheet called Sheet1.

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

141.	  for (i=0;i<attendanceFigures.length;i++){

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 141: 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++.

143.	    let r = i+3;
144.	    let rangeToChangeColour = sheet1.getRange(r,9);

Line 143: 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, which is the array index.

Line 144: We get the cell we’re currently looping on, so this will start on row 3 and column 9 (i.e. I3) and go to row 6 and column 9. (I6).

146.	    if (attendanceFigures[i] < 0.7) {
147.	      rangeToChangeColour.setBackground("red");
148.	    }  

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

Line 146: 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 147: 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.

150.	    else if (attendanceFigures[i] < 0.8) {
151.	      rangeToChangeColour.setBackground("yellow");
152.	    }

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

154.	    else {
155.	      rangeToChangeColour.setBackground("green"); 
156.	    }
157.	  }
158.	}

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

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

Run the code and you will see it has filled the cells in the appropriate colours.

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

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

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

a