Apps Script Basics (8) – Spreadsheet Class

In the my last post, we looked at the SpreadsheetApp class. Now, let’s look at the next group related to spreadsheets, which is the Spreadsheet class. This class allows us to:

  • copy spreadsheets
  • work with sheets, such as moving, inserting, and deleting them
  • add or remove collaborators to the spreadsheet
  • display messages, in the form of the toast message

8Spreadsheet - 22

Go to the Google documentation following this link, and you’ll see all the methods available to the Spreadsheet class.

8Spreadsheet - 25

You will see that there are a lot of methods available but in fact, there are many that can be found in the Sheet class too, which I’ll cover in a future post. So, here I’m going to focus on a few common ones, which are unique to the Spreadsheet class. Here’s a list of ones relating directly to the Spreadsheet class:

8Spreadsheet - 238Spreadsheet - 24


Example 1 – Copying a spreadsheet and renaming it & using the toast message

To start, let’s make a copy of a spreadsheet and rename it by getting the name of the original spreadsheet and adding to it. Then we’ll let the user know the process has finished by displaying a toast message in the original spreadsheet.

8Spreadsheet - 1

Line 3: Set up the function and call it example1.

Line 4: First, I want to make a copy of the current spreadsheet that’s open (i.e. the active one) so we use getActiveSpreadsheet() to get it and we store it in the variable ss1.

Line 5: As we’re going to use the name of this spreadsheet, we need to get its name. So, we use the variable ss1 and then use getName(). We then store it in the variable ss1Name.

Line 6: Now, let’s copy the original spreadsheet and we put the name of the new spreadsheet in the brackets. We’re going to name it using a combination of the original spreadsheet’s name and add “-example1” at the end. So, we state the variable ss1Name, use a plus sign to join the two parts together and then in quote marks add the text we want.

Line 7: Finally, let’s advise the user that the spreadsheet has been copied and named using the toast message. As we are working in the original spreadsheet, we get the variable ss1 and then add toast() to it. In the brackets, we add the options we want. Here, I’m using the toast message option which allows us to add a message, a title, and state how many seconds the message will be displayed for.

I often use toast messages, either as a message to show the user the progress of the process, if it’s a particularly long one, or just to let them know when everything is finished. The good things are that the message doesn’t stop the code working in the background, so doesn’t delay it, and it doesn’t require user interaction like an alert message would. Note, when you run the code, go back to the spreadsheet, otherwise you won’t see the toast message, as it only shows while you are in the spreadsheet and not the script editor.

Line 8: Then we close the function with a curly bracket.

Run the code from the toolbar by selecting “Select function”.

8Spreadsheet - 13

Then from the list choose “example1”.

8Spreadsheet - 14

The first time you run the code, you will have to go thorough the usual authorisation process. Just click, “Review permissions”, the email account you want to use, and “Allow”.

8Spreadsheet - 15

8Spreadsheet - 16

8Spreadsheet - 17

For this example, as soon as you’ve clicked the play button to run the code, open the spreadsheet. When the code has come near the end, you’ll see the toast message pop up in the right-hand corner of the screen.

8Spreadsheet - 21

In your My Drive, you’ll see the new copied spreadsheet, with the name we set up.

8Spreadsheet - 7


Example 2 – Adding editors or viewers to a spreadsheet

When creating a new spreadsheet we sometimes want to share it with certain people. We could do that manually by going to the share settings in the spreadsheet, but a quicker way is to include it in the code, when you are creating the spreadsheet.

8Spreadsheet - 2

Line 11: Set up function example2.

Line 12: Let’s make a copy of an existing spreadsheet. First, let’s open it by its ID.

Line 13: Here, I’m going to make a copy of it so that it appears in your My Drive. Normally in the code you wouldn’t need this step. Let’s make a copy and temporarily call it “NEW”.

Line 14: Here, I’m going to rename it “example2” using the rename() method.  Obviously, normally you wouldn’t copy a spreadsheet and give it a name then rename it straight afterwards, but I just wanted to show the rename() method.

Line 15: Now, we’re going to add another editor. This is simply done by using addEditor() and in the brackets adding their email address between quote marks.

Line 16: Similarly, we can add viewers (i.e. those without edit rights). Here, we’re going to add more than one person at the same time, so we use the plural viewers not viewer and we need to add the email addresses as an array. So, as you can see, in the brackets we add square brackets and list the email addresses with a comma between them.

Note, addEditors() also exists as does addViewer().

Run the code, and you’ll see the new spreadsheet in your My Drive. As you can see by the people symbol to the right of the name, it has been shared.

8Spreadsheet - 8

Click on the sharing icon at the top of the screen.

8Spreadsheet - 12

Go to advanced, and you’ll see that we have indeed given edit rights to one user and view access to two other users.

8Spreadsheet - 18


Example 3 – Moving a sheet to a new location

This time, let’s get the sheet called “All”, which is currently the second sheet in the spreadsheet and move it to the last sheet position on the right.

8Spreadsheet - 6

8Spreadsheet - 3

Line 21: Get the active spreadsheet and store it in currentSs.

Line 22: Let’s make a copy of it, so you have a copy in your My Drive, and let’s call it “example3”. We store the new spreadsheet in a variable called ss3.

Line 23: Now, let’s get the sheet we want by using getSheetByName(). So, we use this with the spreadsheet ss3 and add the name of the sheet in the brackets between quote marks.

Line 24: As we’re going to use the moveActiveSheet() method to move the sheet, we first need to activate this sheet. So, we use the variable sheetAll and use activate().

Line 25: Finally, we get the spreadsheet ss3, use moveActiveSheet() and state which position we want the sheet to move to. As here it will be at the end and there are 5 sheets, we need to put 5.

Run the code and you’ll see the new spreadsheet in your My Drive.

8Spreadsheet - 9

Open it and you’ll see the sheet called “All” has been moved to the last position to the right.

8Spreadsheet - 19

To move it to the front we would put 1.


Example 4 – Moving a sheet to a new location using getNumSheets()

Sometimes, we don’t know how many sheets they are in our spreadsheet beforehand or maybe there are a lot to count, so instead of stating a fixed number upfront, we can get the code to count the number of sheets and then place the sheet where we want in relation to that number. Here, we’re going to move the “All” sheet again to the end.

8Spreadsheet - 4

Lines 29-33: As before, we get the active spreadsheet, make a copy, get the sheet “All” and activate it.

Line 34: This time, let’s get the total number of sheets in our new spreadsheet. We use getNumSheets() and we’ll store the number in the variable numOfSheets.

Line 35: Now, let’s move that sheet to the number in numOfSheets, i.e. 5, as there are 5 sheets.

Run the code, and you’ll see the new spreadsheet in your My Drive. As before, we can see that the “All” sheet is in the last position.

8Spreadsheet - 10

8Spreadsheet - 19

This is a useful method, not only to position the sheet at the end but to move it to other positions in relation to the end. For example, we could position it in the penultimate position, just by subtracting one from the number of sheets, i.e. (numOfSheets-1).


Example 5 – Inserting and deleting sheets

In this final example, we’re going to insert a sheet at the end and delete the first sheet in a spreadsheet. We’re also going to see how we can get a sheet by its position, not just by its name.

8Spreadsheet - 5

Lines 39-41: We get the active spreadsheet and make a copy.

Line 42: Let’s get the total number of sheets in our spreadsheet, using getNumSheets() and store the number in numOfSheets.

Line 43: Let’s insert a sheet at the end. To do so, we use the insertSheet() method and then add the variable numOfSheets in the brackets.

Line 44: To delete the first sheet, first we need to get the sheet. Here, we’re going to get the sheet by using its position. We get the spreadsheet and then use getSheets() and in square brackets put the position we want, so in this case, 0. It’s 0 as we’re using an array. getSheets() actually gets all the sheets in the spreadsheet and stores them in an array, which we can access just by stating the array position.

Line 45: Now, we can delete that sheet by using deleteSheet().

As we can see, it’s created the spreadsheet and in the spreadsheet it’s inserted a new sheet called “Sheet2” and deleted the sheet called “Sheet1”.

8Spreadsheet - 11

8Spreadsheet - 20


You can see how easy it is to use the Spreadsheet class to copy and name spreadsheets, and move, insert, and delete sheets.

Click here to make a copy of the spreadsheet and the code within it.

You can find the code on here at GitHub.

Link to Spreadsheet Class page.


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

Baz Roberts (Google+Flipboard / Twitter)


Apps Script Basics (7) – 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:

https://developers.google.com/apps-script/reference/spreadsheet/

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.


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 (6) – 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)