Apps Script Basics (9) – Sheet Class

In previous posts, we’ve looked at the SpreadsheetApp and Spreadsheet classes. Now let’s look at the next level down, which is the Sheet class.

9Sheet - 38

Here, we’ll look at how we can work with sheets in a spreadsheet, and in particular, I’ll highlight the following common tasks:

  • Copying and renaming a master sheet
  • Adding data from one sheet to another
  • Hiding, inserting, and deleting rows and columns in a sheet
  • Appending data to a list and then sorting that list
  • Automatically adjusting the column width
  • Getting a row of data from a full list, creating a new sheet and adding the individual’s info
  • Speeding up writing to a sheet by using arrays

In the examples below we’ll be using a file which has 4 sheets: name, teachers, classes, and a hidden one called master.

9Sheet - 20

9Sheet - 21

Example 1 – Copying a hidden master sheet and renaming it

Here, we’re going to make a copy of a master sheet (below) for a teacher to fill out, rename it with the teacher’s name, which is on the sheet called “name”, and add their name to a cell on the newly created sheet.

9Sheet - 24

9Sheet - 1

Line 2: Set up the function and we’ll call it example1.

Line 3: Get the active spreadsheet and store it in the variable ss.

Line 4: To tell the code where to put the new sheet, we need the spreadsheet ID. As we’re going to copy the sheet to the same spreadsheet, we just get the active spreadsheet, ss, and get its ID.

Line 5: Getting its ID isn’t enough, we also need to open it by its ID. Let’s store that in the variable destination.

Line 6: Now, let’s get the master sheet we want to copy, by getting it by its name.

Line 7: To copy a sheet, we use the copyTo() method. This can copy a sheet to another spreadsheet or in this case, to the same spreadsheet. We get the master sheet and copy it to the same spreadsheet using the spreadsheet’s ID.

9Sheet - 2

Line 9: As the master sheet in this spreadsheet is hidden, we need to show the newly created sheet. By default, the copied sheet will be hidden if the sheet its being copied from is hidden. The master sheet doesn’t have to be hidden, but I often hide sheets that are not in use for the user.

Line 10: The teacher’s name we want to name the sheet is on the sheet called “name” in cell A1. So, we get the sheet by its name, get the cell A1 and its value.

9Sheet - 19

Line 11: Then we rename the sheet with the setName() method.

Line 12: I also want to add the teacher’s name on the new sheet, so we get cell A1 and set its value to the teacher’s name.

Run the code by selecting function example1 from the toolbar and press play. The first time, you will go through the authorisation process. Follow the instructions and click “Allow” at the end. See my previous Apps Script Basics posts for more details.

9Sheet - 25

Open the spreadsheet and you’ll see the new sheet with the teacher’s name.

9Sheet - 26

Open the sheet and you’ll see it’s copied the master sheet and added the teacher’s name. Note, that by using the copyTo it doesn’t copy over the cell’s background colour. This could be added by getting the range and using setBackground().

9Sheet - 27

Example 2 – Hiding and inserting rows and columns

Here, we’re going to use the list of classes on the classes sheet and hide some rows and columns to display only certain information.

9Sheet - 23

We’re just going to leave the list of teachers, the timetables, the number of students and we’re going to insert an extra column, so that the user can add some notes to the rows.

9Sheet - 28

9Sheet - 3

Lines 17-18: We set up the function and get the active spreadsheet.

Line 19: We then get the sheet called “classes” and store it in the variable classes.

Line 20: To hide columns we use the hideColumns() method and in the brackets we state which column we want to hide. In this case, it’s column B (the second column).

Line 21: I also want to hide the columns E to G. Fortunately, we can hide a range of columns in one go. We use the same hideColumns() method, but this time we include 2 arguments, firstly the position of the first column we want to hide, in this case column 5 (column E). Then, we state how many columns we want to hide, so, in this case 3.

Line 22: We can hide rows in a similar way, this time using hideRows(). Here, I’m hiding the header row.

Line 24: I want to insert a column next to the student numbers in position 5. To do so, we use insertColumns() and add the column position number.

9Sheet - 4

Now, I want to delete all the blank rows below the table. To do so, we need to find out which is the last row with values in it and which is the last row on the sheet.

Line 25: First, let’s find out the last row with information on it. We use getLastRow() to do this. This will return the row number, in this case, 5, and we’ll store it in the variable lastRow.

Line 26: Next, we get the last row on the sheet. We use getMaxRows() to do this. Again this returns the row number, in this case, it’s 1,000 and store it in the variable maxRow.

Line 27: Now, we use these two figures to work out how many rows to delete. We subtract lastRow from maxRow and store it in the variable blankRows.

Line 28: Now, we can delete the rows we want by using deleteRows(). There are 2 arguments, the first, which row we will start from, and the second is how many rows. So, we want to start from the row below the last row with text, so lastRow + 1. Then the number of rows is the figure we calculated before, which is in blankRows.

9Sheet - 5

I also want to delete the blank columns to the right of the table. This is done in a similar way as deleting the rows, but of course, this time we’re working with columns not rows.

Run the example2 function and you’ll be left with this:

9Sheet - 36

If you run example2, you will have to manually reset the sheet, if you want to run it again. Otherwise, you’ll get a different result the second time as a column has been inserted and has changed the position of the columns to the right of it.

Example 3 – Appending a name to a list and sorting it alphabetically

This time we’re going to get a teacher’s name from the “name” sheet and add it to the list of teachers on the “teachers” sheet. Then, we’re going to sort that list alphabetically and also, adjust the column width automatically, so that the names fit in the cells.

This could be useful for example, if the name has been received from a form submission and then you’re adding that name to some kind of master list. To keep the code simple, we’re just going to get it from a specific cell, which is cell A1 on the “names” sheet.

9Sheet - 19

Here’s the current list on the “teachers” sheet.

9Sheet - 22

9Sheet - 6

Lines 38-39: Set up the function and get the active spreadsheet.

Line 40: Let’s get the name we want from the name sheet in cell A1.

Line 41: Now, let’s get the sheet we want to edit, which is the “teachers” sheet.

Line 42: To add the name at the bottom of the list, we use the appendRow() method, which will automatically work out where the bottom of the list is and add the name under it. Note that, it expects an array here, so, the teacher’s name needs to be in the square brackets.

The Google documentation for appendRow() shows want parameter type is required:

9Sheet - 37

As you can see, it states “Object[]” and in the description states it’s an array it needs.

9Sheet - 7

Line 44: Now, let’s adjust the column width so that all the names fit within the cells. We do this with autoResizeColumn() and state which column on the sheet we want to adjust, which in this case is column 1 (column A).

Line 45: Finally, let’s sort our list alphabetically. We do this with the sort() method and in the brackets state which column will be sorted.

Note, if you have other columns with data that’s connected to the column you want to sort, they won’t be changed, so, this could mess up you data. So, only use this sheet sorting for individual columns.

Run the example3 function and as you will see, it’s added the new teacher’s name, sorted the list and made the column width wider to accommodate the longest teacher’s name.

9Sheet - 29

Example 4 – Extracting data from a table and creating a new sheet with that data

Here, we’re going to extract a particular teacher’s data, insert a new sheet, and add that teacher’s data to the new sheet.

9Sheet - 23

9Sheet - 8

Firstly, we’re going to ask for the row number corresponding to the teacher we want. I’ve done this just to keep the code simple and also the user will only have to type in a number, rather than a full name. Lines 50-54 prompt the user to enter a row number and that response is then stored.

Line 50: Access the spreadsheet user-interface with SpreadsheetApp.getUi().

Line 51: Display the prompt dialogue box, using prompt(). In the brackets state the title of the prompt, the question you want to ask, and the buttons you want to show, in this case, there will be an OK and Cancel button.

Line 52: Then, we need to get the response if they clicked the OK button. Here we get the selected button and if it equals the OK button then it will run line 53. If cancel button is clicked, the prompt box close and nothing happens.

Line 53: We get the text of the response by using getResponseText() and store it in the rowNumber variable.

Line 54: Close the if statement.

9Sheet - 9

Line 56: Get the active spreadsheet.

Line 57: Get the sheet called “classes”.

Line 58: Get all the values on that sheet and store them in the variable teachers.

9Sheet - 10

Line 60: Now, we just want the specific teacher’s row. We do that by getting a specific ‘row’ and ‘column’ within our array teachers. The first number in the square brackets is the row number. As this is an array we have to minus 1, as for example, row is in position 0 in the array. The second number is the column number, again this is zero-based, so the first column is in position 0.

Line 61: Now, we need to insert the new sheet We do this by using the insertSheet() method and add the name of the sheet in the brackets.

9Sheet - 11

I want to get the headers and then the teacher’s row.

Line 63: To get the header row, we can use the shift() method with our array. This will remove the data in the first position of our array and we’ll then store it in the headers variable.

Line 64: As shift() has removed the headers data from the array, the data in row 2 is now in position 0 in our array. So, to get the teacher’s data we need to get the row number and instead of subtracting one for our array, we need to subtract 2. Here, we just use one set of square brackets as we are going to get the data in the whole row, so we don’t state which column we want.

9Sheet - 12

Now, we need to add the headers row and teacher’s information.

Line 66: First, we append the row with the headers to the new sheet.

Line 67: Finally, we append the row with the teacher’s info.

Run the function example4. Open the spreadsheet and you’ll see the prompt box asking you for the row number. Enter the number you want and click OK.

9Sheet - 30

The new sheet will be added with the teacher’s name.

9Sheet - 31

Open the sheet and you’ll see that the headers and teacher’s information has been added. Note, this is unformatted as we only got the values from the original sheet, but the sheet could easily be formatted.

9Sheet - 32

Example 5 – Extracting data from a table and creating a new sheet with that data (quicker method)

The above code is fine but here I want to show you why we would avoid making multiple appendRow calls. Every time, we interact with the spreadsheet, we are calling for information and that takes an amount of time. What we should try to do in our code, is to get and set the information we want in one go, to reduce the number of calls and therefore, the time taken.

The code is the same as above until line 86.

9Sheet - 139Sheet - 149Sheet - 159Sheet - 16

9Sheet - 17

We’re going to create an array in which we will add the headers and teacher’s information.

Line 88: First, we set up the empty array. We do this by assigning a variable to the empty square brackets.

Line 89: Then, we add the data we want into the array. One way of doing that is to use the push() method. So, first we add the headers info, which will be in position 0 of our array.

Line 90: Then, we push the teacher’s info, which will be in position 1.

9Sheet - 18

Line 92: In the previous example, we appended each row separately, but here we’ll get the range on the new sheet and add the contents of the info array in one go. Note, that we need to get a range that is the same dimensions as our array, otherwise it’ll throw an error. We know it’s 2 rows and 7 columns of data, so here we start in row 1, column 1, then extend it 2 rows high and 7 columns wide, i.e. (1, 1, 2, 7).

Running the function will produce the same result as before. If you ran the previous example before, make sure you either delete the sheet that was made before running this one, or choose a different teacher this time, otherwise it will try to make a sheet with the same name and this isn’t possible, so an error will appear.

The reason I added this last example, was to show you the difference in time taken to complete the same overall task. We can see the times by opening the Execution transcript in the View menu of the Script Editor.

9Sheet - 33

Below is part of the transcript from Example 4. Appending the 2 rows took 0.149 seconds (0.104s + 0.045s).

9Sheet - 34

This is example 5 and as you can see it only took 0.03s to complete the same task.

9Sheet - 35

While in this simple example, we’re only talking a difference of just over a tenth of a second, example 4 was 5 times slower than example 5. In a larger program, this could mean that either the user has to wait longer for the program to finish or at worst, will mean the program reaches the 6 minute runtime limit and stops working. So, it’s important to think of not just how you get something done but the quickest and most efficient way to do it.

This is also why we use getDataRange() (e.g. in line 80) to get all the data in one go and store it in an array. We then work with the array, rather than calling for different ranges from our sheet, which would be slower.

You can find the Sheet class documentation here.

You can find the code here at GitHub.

You can make a copy of the spreadsheet containing the code here.

You can find more information on the push() method here and the shift() method here.


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

Baz Roberts (Google+Flipboard / Twitter)

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:

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 (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


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)

Make & Send Kids Reports

In this post, we’re going to look how we can create kids reports from a Google Sheet and convert them into individual PDFs made from Google Docs, then email them to the parents. This could of course be adapted to send any types of reports.

Kids Reports - 54

The teachers will fill in the spreadsheet and as its a Google Sheet will be able to do it at the same time as each other, using drop-down menus to make it quick and easy to do. The code will convert this information into individual PDF reports, which will be stored on our Drive and then sent via email directly to the parents. The process is completely paperless and free!

Setting up the spreadsheet

In one sheet, I’ve set up the columns as below:

Kids Reports - 2

Kids Reports - 3

We have two classes with a few students in each. Then we have the areas we want to report to the parents. In the blue part, their language skills and knowledge; in the pink part their behaviour; their attendance, exam marks, and the recommendation for the following year. In the final column, we will add the links to the individual PDFs.

Create a second sheet and call it “Options”. This is where we’re going to store the options for some drop-down menus on the main sheet. First, let’s set up the drop-down menus for the language feedback.

Kids Reports - 4

Select the cells where you want to add the drop-down menus.

Kids Reports - 5

Right-click and select Data validation from the menu.

Kids Reports - 6

In the dialogue box that will appear, click on the box next to “List from a range”.

Kids Reports - 7

This will ask you for where that data is. Instead of typing in the box, open the Options sheet.

Kids Reports - 8

Then select the feedback options for language.

Kids Reports - 9

This will add the range in the dialogue box.

Kids Reports - 10

Click ok, then in the main dialogue, I usually select “Reject input”, so that teachers can’t add any other comments in this columns. Click Save.

Kids Reports - 11

As we can see, it’s now added the drop-down menus.

Kids Reports - 12

Clicking on one of the cells, will open the menu and the teacher can just select the comment they want to add.

Kids Reports - 13

Now do the same for the behaviour comments.

Kids Reports - 14

So, you should have a different set of feedback options this time.

Kids Reports - 17

Now in the exam marks, let’s prevent the teachers adding numbers that are not between 0 and 10. Here in Spain, the norm is to receive a mark out of ten, which sometimes our teachers don’t realise or forget and they add a percentage mark. Select the cells, then open the data validation dialogue as before.

Kids Reports - 18

This time select “Number” from the criteria and “between”. Then add 0 and 10 in the boxes. This time I’ve ticked the “Show validation help text”. This will show the message in the box, if they don’t write a number between 0 and 10.

Kids Reports - 19

Here’s the help text they would see.

Kids Reports - 20

Finally, as this is the end of the year, the parents will want to know, if their child can go to the next level, needs to repeat the year, or can jump a level. So, for each we will add the 3 options. As the options will depend on their current level, we’ll need to do this per level. So, first select the recommendation cells for class A1.

Kids Reports - 21

Open the data validation and select the 3 options next to Class A1. As you can see, the menu options don’t have to be in columns, but can be across rows too.

Kids Reports - 26

Kids Reports - 25

As we can see, it’s added the options available for that level. We then do the same for Class A2, and so on.

Kids Reports - 24

Now the teacher fills out the sheet, choosing the options from the menus we’ve created, which makes it really quick for them to fill out. They also add the attendance figures and exam results. I usually add some conditional formatting to the menus, as it makes it more visually clear who’s doing well or not. See my post on Conditional Formatting if you’re not sure what to do.

Kids Reports - 27

Kids Reports - 28

Report template

Now, we need a blank report template. Here, I’ve created one in a Google Doc using tables to organise the information. As we’ll see in the code later on, accessing tables is easy to do, and so we will be able to add our students’ data with ease.

Kids Reports - 29

Create a folder where you’re going to store the finished reports. Then get the URL of the folder and paste it into the Options Sheets in cell A10, and the URL of the report template into cell A12.

Kids Reports - 50

The code to make the PDF reports

Once the teachers have filled in the data, we can run the program to make the individual reports in PDF format. From the Google Sheet open the Script Editor from the Tools menu. Delete the default code in there. I’m going to assume you know the basics of Apps Script, but I will explain what each step is doing.

Kids Reports - 30

First, I want to add a menu where we can run our code from.

Line 1: Call the function onOpen() so that when the spreadsheet is opened, the menu will appear.

Line 2: To set up a menu we need to get the spreadsheet UI (user interface). We do that by using getUi().

Line 3: We then create the menu on the menu bar using createMenu() and in the brackets give it a name. This is the name of the menu on the menu bar. Note, no semi-colon at the end.

Line 4: Then we need to add an item to it, which will run the createReports function we’re going to write. So, first we use addItem(), then in the brackets we need to state the name of the menu item, and the function we want connected to it. So, here I’ve called it ‘Make pdfs’ and it will run the createReports function when clicked.

Lines 5-6: Finally, we need to add it to the UI. We do that by using addToUi(). Then we close the function with a curly bracket.

You’re likely to use this chunk of code for numerous programs, so I would save it as a script editor file in your Drive, so you can just copy and paste it in.

Kids Reports - 31

Line 8: Now, we write a new function called createReports.

First, we need to get some of the data from our active sheet. Make sure, when you run the code that the first page with the report information is open and not the Options page.

Line 10: Store the active sheet in the variable ss.

I’ve used getActiveSheet() here and not the sheet by name just because if you have lots of students, you may split them into different sheets, so you would open the sheet you wanted and then run the code for that set of students. However, if you have a smaller set of students, you may prefer to do it all on one sheet, and therefore, use the getSheetByName() method.

Line 11: Then let’s get all the data on our sheet and store it in the variable ssData. We do this by using getDataRange() then getValues(). Later on we will get the data we need from this variable, rather than get it from the sheet every time, which would slow down the program.

Line 12: Next, we need to know where our data ends, so we get the last row number, using getLastRow().

Line 13: The start row number will be row 2, let’s set up the variable rowNumber with 2. This is the counter we’re going to use to go down each row and get that particular student’s data.

Line 14: We will sometimes be using row numbers and sometimes the row number within an array, and as arrays numbers start at 0 not 1, we need a number that is 1 less than the rowNumber. We’ll store it in the variable rowNumbersA. I could have just written = 1, but if we ever change our sheet and the start row number changed, we would only have to change one variable.

Now, let’s get the report folder URL and the report template URL from the Options sheet.

Kids Reports - 50

Kids Reports - 32

Line 17: First, let’s get the Options sheet by name, using getSheetByName().

Line 18: Then, we get the URL which is in A10 (row 10, column 1).

Line 19: We’ve got the URL but what we need is the folder ID. We can extract that from the URL by removing the first part of the URL up until /folders/. Here, we get the URL stored in reportFolder and use the replace() method, to replace one thing with another. In the brackets, I’ve added the first part of the URL, which we want to remove, then after the comma, add two quote marks, to show that we’re replacing the text with nothing. I.e. We’re removing it.

Kids Reports - 33

Line 20: Similar to before, but this time we get the report template URL.

Line 21: File URLs are a little more complicated, as we need to remove the first part, like above and also the /edit part at the end. The best way to do this is to use a regular expression. This will look for a particular pattern in the URL and remove the parts we don’t need. First we use the match() method and then in the brackets add the regular expression. It looks strange, but just add what’s there, believe me it works!

Note, all the code written so far, is before the loop we’re about to make, as it only needs to be run once, so we don’t want it within the loop, as it would be run multiple times, slowing down the program. Now, we create a loop to move down the rows of data, and for every row, it will grab the data needed and create a report from it.

Kids Reports - 34

Line 24: We set up our for loop, starting at row number 2, continuing until the row number is equal to the last row (the last student on our sheet), and it will move down 1 row at a time.

Now, let’s get the data about the student, which we stored in ssData. Every line is similar, it’s just getting a different position each time which corresponds with the different columns on the sheet.

Kids Reports - 35

Line 27: We set up the variables and start off with the student’s name, which we’ll store in studentName. The variable ssData is an array of data, as it contains all the data from all the rows and all the columns on our sheet. So, to get the specific data we want we need to tell it, which ‘row’ it’s on and the ‘column’. Our references will have to be array references so will be one less than are on the sheet. The row we use the rowNumberA variable, so the first time we go around the loop, this will be 1 and this relates to row 2 on the sheet. The column is 0 as this relates to column 1 on the sheet.

At the end, add a comma, to connect it to the next variable, without having to repeatedly write var every time.

Continue doing the same for all the pieces of data we need and on the final line, close it by adding a semi-colon.

This chunk of code is optional, as we could put the ssData parts directly in the report part below, but using easy to understand variable names it helps us remember which piece of information is which, so we don’t mix any up.

Now, we need to make a copy of the blank template.

Kids Reports - 36

Line 46: First, we get our template file by its ID. Use the DriveApp Class and getFileById() and add the variable reportTemplateKey where we stored the ID in.

Line 47: Now, make a copy of it, by using makeCopy() of the variable reportTemplate we just created. In the brackets we state the name of the new file. Here, I’m going to call it “Informe-Class name-Student’s name”, so I state the text, then, as the class and student’s name will come from the data for that particular student, we add the variables class and studentName. So, we will have something like, “Informe-Class A1-Barney Pebble”.

In case you were wondering, I’ve just realised I left the first bit (“Informe”) in Spanish, yours would be ‘Report’. The code I’ve adapted this from, was for our reports, which are all in Spanish.

Line 48: Finally, we need to get the ID of the new file, so we can then work with it. Use getId() for this.

Now let’s get our new report and add the student’s data into it.

Kids Reports - 37

Line 51: To edit the document we need to ‘open’ it by using openById() and adding its ID in the brackets.

Line 52: To write something on a document, we first need to get its body. So, logically we use getBody() and store this in the variable body.

Now, we need to access the tables one by one and the rows and cells within each one. We then set the text in this cells using the variables we created in lines 27 to 44.

Kids Reports - 38

Line 55: First, we need to get the first table in the document. We get the body, then get the table using getTables() and state which table number we want. As with arrays, the first table will be table 0, so we add a 0 in square brackets.

Line 56: Now, we need to get the table row we need and the cell number on that row. We use getRow() to get the row, and getCell() to get surprise, surprise, the cell. So, for the student’s name we need the first row and the second cell in that row, so we state getRow(0) and getCell(1). Then we use setText() to state what text we want to add in the cell. In the brackets we add the studentName variable.

The rest of the lines are in a similar format, so for example, the class name is in row 2 (1) and cell 2 (1).

Kids Reports - 39

We then get the next table and repeat the same process.

Kids Reports - 40

Kids Reports - 41

Kids Reports - 42

With the exam marks, I want to add the number to one decimal place and add some text to show it’s out of 10.

Lines 79-80: In the brackets for setText we state the variable written and add the toFixed() method and as we want 1 decimal place, we add 1 in the brackets. Then to show it’s out of 10,  we add a plus and the text ” /10″.

Kids Reports - 43

Line 84: As we ‘opened’ the report to edit it, save and close it to save all the changes. We get the new file’s ID in openReport and use the saveAndClose() method.

Now, let’s convert this file into a PDF.

Kids Reports - 55

Line 87: Get the new file by its ID and convert it to a PDF by using getAs() and in the brackets ‘application/pdf‘. Store that in pdf.

Line 88: Then, we need to give the PDF a name. Here, I just want it to be the same as the Google Doc. So, we use setName() and in the brackets, we get the name of the Google Doc using getName() and add the .pdf extension.

Line 89: Now, we create the new PDF using the DriveApp Class and createFile() and in the brackets we pass the pdf variable.

Line 90: Finally, get the ID of this new PDF, which we will use to add its URL to the sheet and in the emails.

This has made a Google Doc and a PDF in our My Drive, so as we want the PDF in our reports folder we need to move it there. Unfortunately, there isn’t a move method, so we’ll have to add a copy and then remove the original.

Kids Reports - 56

Line 93: Get the report folder by getting its ID from the reportFolderKey variable we created in line 19.

Line 94: Add a copy of the pdf to the report folder by getting the folder ID, then using addFile() and in the brackets adding the report PDF file.

Line 95: We then remove the original PDF file from My Drive. With My Drive, we just use DriveApp then the removeFile() method then state the file we want to remove.

Line 96: Finally, we want to remove the original Google doc on our My Drive. Removing a Google document is a bit different from removing a PDF. Here, we get the file ID using getFileById() and then use setTrashed() and state true in the brackets. This will remove it to the trash.

Now, let’s add the link to the new PDF in our report folder on our sheet, so we can access it directly from the Sheet and so that we can add it to the email to the parents.

Kids Reports - 57

Line 99: We get the current row using rowNumber and get column 20(T) the PDF URL column.

Line 100: Then, we set the formula in that cell to show a hyperlink to the PDF report. We do this by using setFormula() then adding our formula. The text parts need to be between quotes and variables without. Pay close attention to the use of quote marks here.

Note, it is possible to get the file’s URL and add that directly here, but it tends to be a bit longer, as it contains unnecessary information at the end of it, so I prefer to do it this way.

Kids Reports - 58

Line 102: We’re at the end of our loop, so we need to increase our array row number counter by one, so we state the variable rowNumberA and add two pluses.

Line 103: Close the loop with a curly bracket.

Kids Reports - 59

I always like to display a message to the user, to show the program has finished. So, here, we’ll add a toast message, which will pop up from the bottom of the screen telling us the reports have been made.

Line 104: Get the active spreadsheet and use the toast() method. Then state the message, title, and how many seconds you want the message displayed.

Line 105: Close the function with a curly bracket.

Making the reports

Now, we’re ready to make the reports. Refresh the page, so that the Reports menu appears. Then click Reports>Make pdfs to run the program. The first time you run it, it will ask for authorisation, click the review and allow buttons.

Kids Reports - 60

As we can see on the sheet, it has added the links to the PDFs for each student.

Kids Reports - 51

In our reports folder, it has created a PDF report for all our students.

Kids Reports - 52

If we open one of the reports, we can see it has been populated with that student’s information.

Kids Reports - 53

It takes about 50 seconds to create all the reports. As there is a runtime limit of 6 minutes, normally you can only make about 50 reports this way in one go. To make more, either store the students on different sheets, or what we do is tweak the code, to ask for a starting row number and finishing row number, so the reports are made in batches. This also means we can make the reports as soon as a particular class’s data is ready.

Emailing the reports to the parents

The final part will be to send the reports to the parents. We could include this code within the one above, but as we don’t want our program running too long, I’ve set it up so it runs separately.

First, let’s update the onOpen() function, so that an “Email reports” option appears in the menu.

Kids Reports - 72

Here, I’ve just added line 5. When we open the report spreadsheet again, we will see both menu options.

Kids Reports - 71

We’re going to send the email below:

Kids Reports - 70

It contains our school’s logo (a fictitious one), it’s personalized by including the student’s name in the email title and in the main text, and it contains a link to the report PDF.

So, how do we do this?

In a new script file (from File>New) write the following code.

Kids Reports - 62

Line 1: Set up a new function called sendEmails.

Line 2: Get the active sheet and store it in sheet.

Line 3-4: Get the last row and column, for later use.

Kids Reports - 63

As we’re going to add the school logo to make it more professional looking, we need to get the image, which is stored on our Drive.

Line 7: We get the image’s ID (the random numbers and letters part in the URL) and use getBlob(). The blob will contain the data of the image.

Kids Reports - 64b

Now we need to set up a loop to go down the list of students.

Line 10: Set up a for loop, starting on row 2, and which will stop when it gets to the last row and sends the last student’s report.

Kids Reports - 65

Now, we need to get the student’s name, email and pdf link.

Line 13: First, we get the data on the latest row and store it in data.

Lines 14-16: Then, let’s store the student’s name, email, and pdf link in separate variable ready to use in the email. For example, the student’s name is in the first column and so we get it from position 0 in the data array.

Now, we need to set up our message and store it in the variable message.

Kids Reports - 66

Line 19: First, let’s start with the school logo. Here, we’re using a bit of HTML to add the image (using the img tag) and we’ll state the id as logo. I’ve also controlled the height and width here. Pay attention to the quote marks.

Now, let’s add the rest of the message.

Kids Reports - 67

Lines 21-26: Each line is adding to the message variable using +=. To keep it simple, each line will be a separate paragraph (using the < p > tags). We open the < p > tag add the text we want and then close it using < /p >. Text needs to be within quote marks and variables like name, need to be without but are connected with the plus marks.

Kids Reports - 68 (1)

Finally, we need to send the email. This contains 3 main parts: email you’re sending to, email title, and email message.

Line 29: We use the MailApp and sendEmail() to send emails.

Line 30: We use emailTo to state the current email address we want to send to. Then in the email title I want to call it “Report-and the student’s name”, so the parents can see it’s a message about their child. The message part we’ll just add 2 double quote marks, as the next lines will add the details of the message. End the line with a comma.

Line 31: In curly brackets we’ll add the options we want. First, add the inline image, using inlineImages: then within another set of curly brackets, state the id logo: and the variable image (where the image blob is stored).

Line 32: Then we also need to add the body, which is a HTML body (htmlBody:) and the message variable, which contains the message we wrote above.

Lines 33-34: Close the loop and then close the function.

In seconds, this will send the personalised emails to all the parents.

Kids Reports - 69

Kids Reports - 70

We introduced a system similar to the one I’ve described above, which replaced an antiquated card report system and its made the whole process so much easier for everyone involved, whilst providing the necessary information to the parents in a more modern way.

One limitation with this system is that you can only send 100 emails a day, so if you have more students and want to send all the emails in 1 day, you will need to use a Google Sheets add-on like YAMM and pay for more emails (e.g. up to 400 in a day), or send the emails from different email accounts, not ideal but a workaround.

Here’s a copy of the spreadsheet with the code and here’s the report template.

You can find the code here at GitHub.

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

Baz Roberts (Google+Flipboard / Twitter)

Apps Script Basics (5) – Arrays, Logger, Execution Transcript

In this post, we’re going to look at another key area in Apps Script and indeed many coding languages, that of arrays, which are just special variables which allow you to store multiple items in a single variable. I’ll also introduce you to the logger, where you can store see what’s happening in your code without for example, having to print it on a sheet. Plus, the execution transcript, which shows you the steps your code is taking and how long they take.

Introducing the logger

Following on from my post on loops, let’s take a look at some looped data in the logger. Here we have some data in a sheet. We’re going to loop down the data and put it one by one into the logger.

5Arrays - 1

5Arrays - 2

With all the examples in this post, we’re going to get the active sheet, so line 3, you’ll see repeated for each of the functions below. You’ll use this line repeatedly for a lot of programs you write, where you’re working with a spreadsheet and the current sheet.

5Arrays - 3

Now we want to loop down the list on the sheet.

Line 5: Here we know we have 20 numbers, so we set the loop to start at row 1 and finish at row 20 (<21). Here I’ve used the variable r as the counter.

Line 6: Then we get the cell at that point in the loop using getRange() and a row and a column number. The row will be the variable r and the column will always be 1 as it’s the first column, A. Then we get its value and store it in the variable listOfNumbers.

5Arrays - 4

Line 8: Now, still within the loop we log the numbers in the logger. This is easy to do, just use Logger.log() and in the brackets state what you want to add to it. In this case, it’ll be the contents of the variable listOfNumbers.

Lines 9 and 10: We close the loop with the curly bracket and then the function with another one.

5Arrays - 17

If you’re using a copy of this file I’m using, you’ll need to select the function you want to run. Just click on “Select function” form the toolbar and in this case “logNumbers“. Then press the play button to run the function. The first time you run it you will have to authorise it.

5Arrays - 15

Once run, you’ll want to open the logger. Go to the “View” menu and click “Logs”.

5Arrays - 16

As we can, it’s logged the values each time in went around the loop. So, it got “2” from row 1, then “4” from row 2, and so on. Each time placing them in the logger.

The logger is an excellent way to debug your code and to see what’s happening in your code and any point in it. You can put Logger.log() on any line and it’s really useful to see what’s going on, especially with what’s being stored in your variables.

Single items and multiple items

OK, now we have a place we can see our data, let’s move on to look at arrays. In this example, we’ll look at the storing individual items and multiple items in variables.

5Arrays - 5

Lines 13-14: As above.

Line 16: Here, I’ve stored the text string “Fred” in the variable item.

Line 17: This time I want to store 4 different names in the variable items. To do this, I need to set up an array. The contents of an array is shown by whatever is in between the square brackets. So here we have four names all within one array called items. To separate them, you need to use commas and as these are words we’ll needs to use quote marks.

5Arrays - 6

Lines 19-21: Now, let’s log the two variables in the logger and then close the function.

5Arrays - 18

Choose “array1a” from the toolbar and press play.

5Arrays - 19

As we can see, it shows the text string “Fred” and then the array below it. In the logger, it displays the array with square brackets, so you know it’s an array.

Accessing values in arrays

We often want to get at the information stored in an array. Let’s see how we do that. Here we’re going to access the two variables item and items and add them to our sheet.

5Arrays - 7

Line 24-28: The same code as the previous example.

5Arrays - 8

Line 30: Let’s add the content of item to the cell D1.

5Arrays - 9

Line 32: Now, let’s do the same with the content of items to cell E1.

5Arrays - 10

Finally, let’s get a specific item from our array and put it in cell F1. To do so, we need to state in which position in out array is the name we want. In arrays, each item of data has a position, starting in position 0. So, “Joan” is at position 0, “Paula” is in position 1, and so on. A common mistake is to forget that array are zero-based, i.e. they start at 0 not at 1.

Line 34-35: So, for example, to get “Paula” we need to get the array items at position 1. We do that by stating the variable name and in square brackets stating its position, e.g. [1].

Run the code and let’s see what we get.

5Arrays - 20

“Fred” has been added in cell D1 as expected.

However, in cell E1 we were expecting to add the contents of items but we only have the first name in the array! This is because we’re using setValue() which is expecting a single item. You may then think that we could use setValues() to get multiple values, but it doesn’t work like that with the array we’ve set up. This is because the variable items is still a single array, it just happens to contain multiple values (mini arrays). This is why we need to state the position of the items within the array. As we’ll see below there are of course ways to access multiple data.

Finally, in cell F1 we wanted to add the second name on the list and we have indeed, so the index we used has worked.

Using get and setValues() to get a range of values in one go

Here, we’re going to get a range of values, add them to the logger to see what they look like. Then add those values to our sheet and pick out a value from that range and add it to the sheet.

5Arrays - 11

Line 41: Let’s get the range of numbers we had in the first example from column A. Here, I’m using getRange() and stating the range in which those numbers are (using cell notation). Then, as there is more than one value, we use getValues() to get all the values in one go and store them in listOfNumbers.

5Arrays - 12

Line 43: Then, log the contents of listOfNumbers.

5Arrays - 13

Line 45: Now, let’s add that range of numbers to our sheet. Here, I’m going to put them in column H. I get the range of cells I want to add to. Note, this has to be the same number of cells as the original data, otherwise it will throw an error. Then, similar to getValues() we’re going to use its opposite, setValues() to add the values to those cells.

5Arrays - 14

Line 47-48: Finally, let’s extract one of the numbers from listOfNumbers and add it to cell I1. Similar to above, we’ll need to use setValue() then state the variable we want and the position of the data we want. So, here I want to get the second item, so I need to state position 1. Then close the function.

Run the code and you’ll see the list of numbers has been added to column H and that we have picked the second number out and added it to cell I1.

5Arrays - 22

Open the log and we’ll see that line 43 logged our data.

5Arrays - 21

Note, that this time every number has a set of square brackets around it and that all the data is enclosed within a second set of brackets. This shows that the data is stored in lots of little arrays within 1 larger array, what is known as an array of arrays. This is why, we can use setValues() as here we have multiple items and we can add them to our sheet in one go. Whereas, in line 32 we couldn’t use setValues() as the data was one item, as we can see in the log output below:

5Arrays - 19

This can all sound a bit complicated, but basically, we either have an array on its own. So, 1 array with multiple items in it. Or we have arrays within an array (an array of arrays). So, the code knows this array has multiple items, whereas the other one it treats it as a single item.

Looping through an array

A useful technique is to loop through an array. In this example, we’ll loop through an array of names and add those names one by one to a sheet, with each name on a different row.

5Arrays - 23

Lines 51-53: The same as array1a, we set up an array with 4 names.

5Arrays - 24

Line 55: First, we set up a for loop. This will go from 1 to 4. Here, we’re using the variable i to keep count. Note, that i is often used in loops, so you’ll see it a lot when you look at code examples.

Line 56: Now, we want to get a cell on the sheet (we’re going to use column K) and set its value to one of the names in the array. So, first we get the range and get the cell location, by adding the cell letter “K” to the current number in the loop (i), e.g. K1, K2, etc. Then we will set the value by getting the array items and getting one word at a time, starting at 0, so we need to subtract 1 to get the array position, e.g. [0], [1], etc.

Lines 57-58: We close the loop and the function.

5Arrays - 25

As we can see, on the sheet, it has taken the contents of the array and added the names in each of the cells.

Setting up an empty array and adding to it

Here, we’re going to loop down one list, add the values one by one to an array, then add those values one by one to another part of the sheet. We’re going to see how we create empty arrays, and how we can add items to an array.

5Arrays - 26

Line 63: First, let’s set up an empty array. We do that, simply by assigning a pair of square brackets to it. So, at the moment the variable listOfNames is empty but it’s an array.

5Arrays - 27

Line 65: Then, let’s set up our for loop, to count from 1 to 4.

5Arrays - 28

During each loop, we will get the value from the cell on the sheet then add it to the array.

Line 67: First, we get the cell using the getRange() method using the row and column. As we want to go down the rows from 1 to 4, we add the variable a in the row part. The column will remain fixed as there is only one column (K the 11th column). Then we get its value.

Line 68: Now, we need to add it to the array. To do this, we use the array method push(). So, we state the name of the array, add a dot then the word push. In the brackets we state what we want to add to the array. In this case, it will be the value we just got, which is in the variable originalList.

5Arrays - 29

Line 70: I’m going to log the names, so below we can see what’s happening in the array.

5Arrays - 30

Line 72: Now, as in the previous array example, we get the cell we want on the sheet, this time in column M, and add the names one by one.

5Arrays - 31

As we can see, it’s copied the names from column L to column M. Not the most exciting thing to do, but we can use a similar technique to copy data from one sheet to another or to another spreadsheet or even completely different place like a Google Doc, you can start to see the power of this.

5Arrays - 40

The log clearly shows us what’s happening, each time we go around the loop. It’s adding one word at a time at the end of the array.

5Arrays - 32

Also, in the View menu, there is the Execution transcript. Click on that to open it.

5Arrays - 335Arrays - 34

This shows you every action the code is taking, right from getting the active spreadsheet to adding the last name on the sheet. Along with the log, this is a vital tool to find out what is going on in your code and will help you troubleshoot. If the code comes across an error and stops, it will show you where it stopped by showing you the line number. It’s important to remember that this doesn’t always mean the problem is in that line, it may be due to something else not running correctly elsewhere but because there is some reference here to that, it is stopping here.

The other thing that is useful is to see how long the steps are taking and the overall time the code took to complete. Here it took 0.821 seconds. It doesn’t sound long, but as we’ll see in a moment, this can be reduced. There is a maximum runtime limit of 6 minutes, so you can imagine a program of hundreds of lines long will mean every second counts. Plus, you want your program to run as quick as possible, as you don’t want to sit there twiddling your thumbs while your program works it’s magic!

Reducing the execution time

The above code is fine and is useful in certain circumstances, but for the job we just did, that of copying one set of data and pasting it somewhere else, we can do it quicker.

5Arrays - 35

We start off as before.

5Arrays - 36

Line 81: Let’s get the range of values in one go, by using the getRange() method which needs 4 arguments: start row, start column, number of rows, number of columns. So, here we have row 1, column 11(K), 4 rows, and 1 column. Then get the values, as we saw in array2.

5Arrays - 37

Line 83: Then, we get the range we want to add the values to (column O) (remembering it has to have the same number of cells as the original data), then set the values from the variable originalList2.

5Arrays - 38

As we can see, it added the 4 names.

5Arrays - 39

Looking at the execution transcript, we first of all see it’s much shorter than the previous example and due to that it is also quicker. It took only 0.152 seconds, over 5 times faster.

The difference being that in the previous example, we got a value and set it, then got another value and set it, etc, four times. Whereas, in this example, we got a group of values and set them in one go.

Here’s the link to the sheet with the code in it:


Have a play around with the arrays, it’s the best way to learn how they work and what can be done with them.

If you want to know more about arrays and array methods, I recommend checking these two links out: ARRAYS and ARRAY METHODS

Here’s the full code:

5Arrays - 415Arrays - 425Arrays - 43

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

Baz Roberts (Google+Flipboard / Twitter)

Apps Script – Issues reporting form, log & email

In this post, we’ll look at a way to create a quick and simple system to report maintenance and IT issues in the classroom.

The teacher fills out a Google Form on their phone, this gets logged on a Google Sheet, and as we work in a multilingual environment, it uses Google Translate to automatically translate the issue before emailing the relevant people.

Issue reporting - 32

Setting up the form and sheet

I will assume you know how to create Google Forms and link them to Google Sheets. If don’t then check out my posts on Google Forms.

I’ve created a simple form for the teacher to fill out. They fill out the problem, whether it’s an IT or maintenance issue, their name, and choose one of the rooms from the drop down menu. So, nice and quick to fill out.

Issue reporting - 3

Then link that with a Google Sheet and you should then have the questions in that sheet, as below (on the sheet called “Form responses 1”):

Issue reporting - 4

Then, add 3 more columns on the end and label them, Translation, Comments, and Status. Plus, I’ve added a bit of formatting.

Issue reporting - 5

Then, add a second sheet and rename it “Emails”. Add in the first column, the emails of those who will receive a maintenance issue and in the second column, those who will receive an IT issue.

Issue reporting - 11

The code

Now, we need to add the code to the sheet. Open the Script Editor from the Tools menu.

Issue reporting - 6

Rename the file, e.g. Maintenance-IT Log.

Issue reporting - 7

Delete the default code that’s in there.

Issue reporting - 18

Line 1: First, we set up the function. Here, I’ve called it onFormSubmit, just to remind myself that I will need to set up an onFormSubmit trigger later on.

Line 2: We then need to get the active spreadsheet and store it in the variable ss.

Line 3: Then we need to get the sheet where the responses will appear, called “Form responses 1”, using the getSheetByName() method.

Issue reporting - 19

In this part, I just want to format the data on the sheet, so that the data is all centered, it all has text wrap, and that it has borders around it. Basically, to makes it look nice and more readable.

Line 6: We get the sheet with frSheet and get the range of all the data currently on that sheet, using the getDataRange() method. We then add our formatting to that range. Starting with centering the data, using setHorizontalAlignment(). Note, there’s no semi-colon at the end here.

Line 7: Then we add text wrap to all the data, using setWrap() and setting it to true. Here, as we are still attached to the getDataRange() part, we only need to use a dot and then setWrap(true).

Line 8: Finally, we add the borders to the data, by using setBorder(). There are 6 potential places for the lines (top, left, bottom, right, vertical, horizontal), but as I want all the borders to have lines, I state true for each position. This time we add a semi-colon, as we’ve come to the end of formatting this range.

Issue reporting - 20

Now, we want to get the last row, which will contain the latest data, and the last column. We’ll use this later on.

Line 11 & 12: To do this we use getLastRow() and getLastColumn().

Now we want to add a couple of pieces to the latest row. The first will be the status of the issue, which will be open at first. Then, as we want to be able to work with multiple languages, I want to translate the problem reported into Spanish, as I’m working in Spain and the maintenance and IT guys are Spanish.

Issue reporting - 21b

Line 15: First, let’s get the status cell on the latest row. As this is in the last column, we simply get the range using the lastRow and lastColumn variables we just set up. Then, set the value to “Open”.

Issue reporting - 22

Line 19: First, we need to get the cell where we’re going to add the translation. Here, I’m going to use A1 notation with the getRange() method. So, I know it will be in column F, and I already know the last row number, which is in the variable lastRow, so I combine the two together, using the plus sign.

Line 20: Here, we’re going to add a formula to the translation cell. We’re going to use the GOOGLETRANSLATE and DETECTLANGUAGE functions. See my post on how these work in Sheets. As the cell reference will be different every time, we need to refer to the numbers of rows and columns away from the translation cell the original problem text is.

So, in the brackets, we add single quote marks, then our formula. This will look at the cell on the same row as the translation cell (row 0) but 4 columns to the left (row -4, i.e. column B), it will detect what language it is and translate it to Spanish (“es”).

Here’s what it will look like:

Issue reporting - 10

Issue reporting - 23

Now, let’s get the data from the latest row, as we want to include that in our email to maintenance or IT. We could just write the lastRowValues parts directly in the email part, but remembering which number refers to what information, can be confusing sometimes, as in our email the information isn’t in the same order as it is on the sheet, so let’s store them in easy to understand variables.

Line 23: First, we get the values all along that latest row and store them in lastRowValues.

Lines 24-29: Now, we get the individual pieces of information stored in the lastRowValues array. So, for example, to get the timestamp, we look at position [0][0]. The first 0 is always 0 as we only have 1 line of data, so the row will always be 0. And the second 0, refers to the place in the array the timestamp is, which as it’s the first piece of data (column 1), the position is 0. Remembering that arrays always start with 0 not 1.

Issue reporting - 24

Dates, are always a bit more complicated to deal with, as if we use the timestamp as it is, it will come out like this: Fri Apr 14 2017 18:43:13 GMT +0200 (CEST), not the most succinct date. So, we need to shorten it to a format we want.

Line 32: First, we need the time zone we’re working in. Use Session.getScriptTimeZone() for that. Note, the capital “S” in Session, as it’s a class.

Line 33: Now, let’s shorten the timestamp. To do this, we use Utilities.formatDate(). This needs 3 arguments, the date & time you want to convert, the timezone, and the format of the date and time you want. The new format is between quote marks.

Issue reporting - 25

At the bottom of the email to maintenance or IT, I want to add a link to the log, so they can access it directly from the email, to update what they’ve done in the Comments column.

Line 36: This is easily done, just get the URL of the active spreadsheet (ss), using getUrl().

Now, we need to prepare the email we want to send. If you’ve read my previous posts on sending automatic email from form submissions, then you’ll know how easy it is. In those posts, I just used basic formatting in the email. Here, let’s use some basic HTML to make the email easier to read and a little more professional. Plus, see this site for more info on HTML.

Now, we need to state what we want to include in the body of the email. Lines 40-51 is all stored in the variable emailBody. Below is the email format I want. There’s an “Issue” header, then the the various bits of information from the form. Note, the title of each is in bold. Plus, I’ve added some lines, organise it a bit better. Then, at the bottom is the link to the log sheet.

Issue reporting - 31

Issue reporting - 26

Line 39: First, we need a subject for our email. Here, I want it to state “Issue -“plus the date of the issue, using the shortTimestamp variable.

Line 40: As a general rule, the fixed text we want to add needs to be in quote marks and the variables we use to add the form data, are without them. So, first I want the title “issue” to be an underlined header. So, I’ve added a header 3 tag < h3 >and an underline one < u >. Then, I close the tags with </ h3 > and </ u>. Note, that every line except the last one will need a backwards slash \.

Line 41: I want the word ‘Problem’ in bold, so I use the < strong > tag to do that. Then I close it before the form data, so that it’s not bolded. Note, the use of the pluses to connect the information together. Plus, I use the line break tag < br /> to create a new line.

Most of the other lines are similar. In Line 43, I want to add a horizontal line to separate the information, so I use the < hr /> tag to do that.

Line 51: At the end, close the quotes and end with a semi-colon.

Issue reporting - 27

Now, we need the email addresses of those we are going to send the email to.

Issue reporting - 11

Line 54: As you’ll remember, we set up an Emails sheet. So, first we get that sheet.

Line 55: Then, let’s find out how many rows have email addresses, so let’s just use getLastRow() to do that. Note, if you have one column with more emails than the other, it doesn’t matter, as if you’ve got some blanks as the email service will just ignore them.

Now, we need to decide which column we’re going to use. To do this, we use an if statement, which will look to see if it’s an IT issue, and if not, it will default to the other emails.

Issue reporting - 28

Line 57: We check to see if the value in column C (“IT or Maintenance?”) is equal to “IT”. Remember to use the triple equal signs for ‘equals to’ not a single equals sign.

Line 58: If it is, then it gets the email addresses from column 2.

Line 59-61: The alternative is that it isn’t (else). In which case, it gets the email addresses from the first column.

Issue reporting - 29

Now, we put it all together, to send the relevant people the email.

Line 64: We use GmailApp.sendEmail() to send the email. We have 3 main arguments: who the email is going to (emailTo), the email subject (emailSubject), the body of the email (emailBody). As we’re using HTML, we also need to add the option of htmlBody. Options go between curly brackets. First, write htmlBody: then the emailBody variable.

I also want a specific person to receive an email if someone replies to this email. Remember this email is being sent from the account in which the code has been set up under, but you may not want that account to be emailed to. To do so, just add replyTo: and the email address you want between quotes.

Line 65: Close the function with a curly bracket.

Setting up the onFormSubmit trigger

We want the email to be sent automatically when the form is submitted, so we need to set up the onFormSubmit trigger to do this.

Issue reporting - 12

Click on the clock symbol in the toolbar.

Issue reporting - 13

Click “No triggers setup”.

Issue reporting - 14

Here, you’ll need to change the Events.

Issue reporting - 15

Change “Time-driven” to “From spreadsheet”.

Issue reporting - 16

Change “On open” to “On form submit”.

Issue reporting - 17

Then press “Save”.

The first time press the play button on the toolbar, to prompt the authorisation of the script.

Issue reporting - 8

Click “Review permissions”.

Issue reporting - 9

Then click “Allow”.

Reporting an issue

The teacher fills in the form and submits it.

Issue reporting - 10

The information appears in the sheet and the code then adds the translation and the Open status. It then send the relevant parties an email with the summary:

Issue reporting - 31

As you can see, the issue is clearly presented, allowing the person to take action.

Once the issue is resolved, the person would then add the action taken in the comments on the sheet and change the status to Closed. As the sheet is shared with everyone, the open issues are clearly visible, prompting quick resolution of them.

Here’s the link to the sheet containing the code.

Here’s the form:

Here’s the full code:

Issue reporting - 33Issue reporting - 34

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

Baz Roberts (Google+Flipboard / Twitter)

Apps Script Basics (4) – Loops

Here, we’re going to look at loops and how they can make repetitive tasks really easy and how they can save you so much coding. For example, if we want to print the word Hello! a number of times down a column without a loop, we would have to repeat the same line of code for each of the rows, as you can in the code below:

4Loops - 30

The only thing changing each time is the row reference in the getrange() method. Imagine if we wanted to write this 100 times, that’s a lot of code! With a loop, as we’ll see in the examples below, we can simplify this to just 1 loop and 3 lines of code. A loop just allows you to repeat a section of code as many times as you like.

Below, we’re going to look at seven simple examples of how loops can be used. In the spreadsheet, there are 2 sheets, one called Numbers and the other called Names.

Loop 1 – Print “Hello!” 20 times down column A

4Loops - 4

Open the script editor in your Google Sheet and replace the default code with the above one.

Line 2: We start with the function and here let’s just call it loop1.

Line 3: Now we want to get the sheet called Numbers. So, we use the SpreadsheetApp class, then get the active spreadsheet (i.e. the current one we’re using), then get the particular sheet we want, in this case called “Numbers”. For that we use the getSheetByName() method and add the name between quotes in the brackets. I’ve stored it in the variable ss, as we’re going to refer to the sheet again in the next part, so instead of writing out the SpreadsheetApp… part again, we can just use the variable name ss.

Now we want to set up a loop which will add the word Hello! to cell A1, cell A2, and so on, down to cell A20. Here, we’re going to use the common for loop. There are two main parts: the counter, in this case counting from 1 to 20, and what you want the loop to do each time it goes around.

4Loops - 5

Line 5: Start with the keyword for then open the brackets. There are 3 parts within the brackets:

  1. Starting point of the loop.
  2. Condition you want to check; if it’s true it continues the loop, if it’s false it stops the loop and continues on with the program.
  3. How much you want to increase the counter by each time it goes around the loop.

So, here we have the variable r (for rows) and we’re starting at 1, as we want to start at row 1.

Then we want to continue until 20, so the condition will be to continue while the variable r is less than 21.

As we want to put the name in all the rows, we want to increase r by 1 each time, so that it will print Hello on row 1, row 2, row 3, etc. Here I’ve used the common shorthand way to increase a value by 1, i.e. using a double + after the variable (r++).

All of this is contained in normal brackets. Then we need to tell it what to do, during each loop. To do this, we use the curly brackets, so we open the curly bracket ready to put some instructions in it.

4Loops - 5

Line 6: Now, we want to get the range and set its value to the word Hello!. So, first we get the sheet we want using the variable ss, then use the getRange() method. Here, we’re going to use the getRange method, that needs a row and column, to determine which cell it is. In the brackets, we have two arguments: (row number , column number). The column will always be the same, so we write 1, for the first column, A. But the row we want to change each time we go around the loop, so we use the variable r from our for loop. Then we set the value of that cell with the word Hello!.

Line 7: We close the for loop with a closed curly bracket.

Line 8: We close the function in the same way, with a closed curly bracket. So, you can see that the function does everything within its curly brackets, and the for loop does everything within its curly brackets.

When we run the code, the first time around the loop, r will be 1, so the range it will get will be row 1, column 1 (i.e. cell A1). Then it will add Hello! in that cell. The counter r will go up by one, then the second time it goes around the loop r will be 2, so it will get row 2, column 1 (i.e. cell A2), and so on, until it reaches row 20. Then the condition will be false, as r will be 21 and isn’t less than 21, so it will continue down the program.

Let’s run the code and see what happens. As I’ve already set up the 7 different loops, we need to select loop1 to run it.

4Loops - 18

So, from the toolbar where it says “Select function”, click on “loop1”.

4Loops - 19

Then click the play button to run the code.

4Loops - 2

The first time you run it, it will ask for authorisation, just click “Review permissions”.

4Loops - 3

Then click the blue “Allow” button.

4Loops - 20

As we can see it’s written Hello! in cells A1 to A20, just as we wanted.

Loop 2 – Print numbers 1 to 20 down column A

This time let’s use the numbers in the loop and add them to the sheet. We’re going to add the numbers 1 to 20 in column A.

4Loops - 6

Line 11 to 12: Same as loop 1, except call it loop 2.

4Loops - 7

Line 14: Same as above

Line 15: This time we want to set the value of the cells to be the current number in the loop. So, we get the range as before, and this time set the value to be the variable r. So, the first time around the loop, r will be 1, so it will put 1 in row 1. the second time it will be 2, so it will be 2, etc.

Lines 16-17: We close the loop and function as before.

Select loop 2 from the toolbar and run the program.

4Loops - 21

As we can see it added the numbers 1 to 20 to column A.

Loop 3 – Fill cells A1 to A20 in blue

It’s not just values that we can use loops with. Let’s add some colour to our sheet.

4Loops - 8

Lines 20-21: As before.

4Loops - 9

Line 23: As before.

line 24: This gets the range as before, but this time set the background colour to blue, using the setBackground() method.

As we can see it’s filled the cells in blue.

4Loops - 22

Loop 4 – Fill cells A1 to A20 in blue and print numbers 1 to 20

We can carry out more than one instruction within our loop. Let’s fill the cells with blue and add the numbers 1 to 20.

4Loops - 10

Lines 29-30: As before.

4Loops - 11

Line 32: As before.

Line 33: Similar to loop3, we set the background colour, but this time, don’t add a semi-colon at the end of the line. This allows us to add other instructions to the same range.

Line 34: Type dot and the method you want, in this case, setValue(). Here we’re going to add the variable r, i.e. the current number in the loop, to the cell.

4Loops - 23

As we can see, it added both the colour and the numbers.

Loop 5 – Print the numbers 1 to 20 across 10 different columns

So far, we’ve just added values and colours down the same column but we can of course move across the columns too. Here, we’re going to look at how two for loops can be used together, to add numbers down the columns and to add them to different column across the page.

4Loops - 12

Lines 39-40: As above.

4Loops - 13

Lines 44-46: This is pretty much the same loop from loop2, i.e. it will add numbers from 1 to 20 down a column. The only exception is that instead of keeping the column value as a constant 1, I’ve added the variable c (for column), which will change as the loop goes around.

Line 42 and 47: Wrapped around the above loop, we have another loop which will change the column number from 1 to 10, or in other words from column A to J. Here I’ve called the variable c and it starts at column 1, then increase by 1 until it reaches the tenth column (i.e. c less than 11).

Running the program, we can see that it added the numbers 1 to 20 to column A, then to column B, and so on until column J (the tenth column).

4Loops - 24

So, how does this work exactly?

4Loops - 13

At the start, c is 1 and r is 1, so it gets the cell A1, and adds the number 1 (r) in the cell. It then gets trapped in the r loop, and goes around that one a second time. So, this time c is still 1, but r is now 2, so it adds 2 (r) to cell A2. It continues around the r loop until it hits 20, then it goes down a line and hits the end curly bracket of the c loop (line 47).

It then goes back to line 42 and goes around again. This time c is now 2, and the r loop is reset back to 1. It goes around the r loop like before until it hits 20, then it goes back to the c loop, which will now be 3, and so on, until the c loop hits 10. At which point it finishes.

Loops within loops can be complicated to follow and if I’m struggling to follow what’s happening, I usually look at it step by step and see what’s happening to the variables each time they go around the loop.

Loop 6 – Create 5 documents and name them Document1 to Document5

This time let’s see what else you can do with a loop. Adding numbers and colours to a sheet is fine, but you can do so much more with them. Here, let’s create some Google Docs and number them individually from our loop.

4Loops - 14

Lines 51-52: As above.

4Loops - 15

Line 54: I want to create 5 documents, so let’s set the condition to less than 6.

Line 55: Every time it loops, we want it to create a document and name it “Document” plus the current number from the variable r. To create a new Google Doc we use the DocumentApp class and the create() method. In the brackets we state the name we want to give the document. So, it will be “Document” plus the number from r.

Lines 56-57: We close the loop and function as before.

Running the program, we will see in our My Drive folder, 5 new documents, all titled individually, 1 to 5.

4Loops - 25

Loop 7 – Create 4 documents each with names from the sheet

It’s not just numbers we can add to our documents, but we can also get text from our sheet and name the documents accordingly. Here we’re going to create documents for 4 students and append each document title with their name.

4Loops - 16

Line 61: Use the Names sheet for this one, where I’ve already added the names of the 4 students.

4Loops - 27

4Loops - 17

Line 63: Set the for loop up to count from 1 to 4.

Line 64: First, we need to get the student’s name from the sheet. We’ll get a different name every time we go around the loop. Set up a variable called studentName and then get the range which is r, 1. So, the first time around the loop it will get the name in row 1, column 1, i.e. “Joan”, the second time it will get row 2, column 1, i.e. “Paula”, and so on. then get the value in that cell, i.e. the name.

Line 65: Then using DocumentApp.create we create a new document which will be titled “Document-” plus the student’s name.

Line 66-67: As always we close the loop and function.

4Loops - 26

As we can see, in our My Drive, it’s created 4 documents individually titled for each of our students.

The important thing here is that we are getting information from a sheet, using a loop to move around the sheet to get different bits of information and then using it elsewhere. This is extremely useful and allows you to do all sorts of things.

Here’s the link to the sheet, which contains the code:

Here’s the complete code with all 7 loops:

4Loops - 284Loops - 29

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

Baz Roberts (Google+Flipboard / Twitter)

Forms & Sheets – Attendance System

In this post we’re going to make an attendance system using Google Forms and Sheets. The user will fill in the attendance on the form (possibly on their mobile) and the data will then be stored and organised automatically per student. This will use a Form add-on called FormRanger and will use a range of Sheet functions to do organise the data for you.

We will make an attendance sheet like this:

Attendance - 75

And an attendance summary like this:

Attendance - 76

Setting up the form

Create a new form. The form itself is very basic and just includes the date of the class and a question where we’re going to populate the class list. Make the date question a required one, just to make sure it’s always entered.

Attendance - 1

Click on the settings cog and under General, the only option I select is “Edit after submit”. This can be useful if a student arrives a bit late and I’ve already done the attendance. It will allow me to go back to the class list and update it.

Attendance - 2

Under Presentation you may want to tick the Show link to submit another response, if you do, the attendance of more than one date at a time. Change the default confirmation message.

Attendance - 3

Then we need to link the form to a Sheet. Click on Responses and the green sheet icon to create a spreadsheet.

Attendance - 4

By default, it’ll create a new one. You may want to change the title here. Click Create.

Attendance - 5

The Sheet will open and we can see the 3 columns it’s set up.

Attendance - 6

Create the following sheets:

Attendance - 24

I’ve changed the name of the ‘Form Responses 1’ sheet to G1, partly so we now it’s referring to group 1 and partly because it’s a shorter name! I’ll go through each sheet later on.

For now, just add your class list to the ‘ClassLists’ sheet like this:

Attendance - 7

Populating the class list on the form

We could just type in the student names on the form, but I often use an add-on called FormRanger, which connects a question in the form to a range on a sheet. This then updates the form automatically if there are any changes to that range. In other words, if the students change in the class, the form will be updated.

If you don’t already have it, download the add-on by clicking on the 3 dots menu and choosing add-ons.

Attendance - 56

Then type in formranger and click on the blue ‘Free’ button, to add it.

Attendance - 55

Click on the jigsaw piece icon.

Attendance - 8

Then select formRanger.

Attendance - 9

Click Start.

Attendance - 10

A menu will appear which has two main parts, the top part is where you link the question to the sheet and the bottom grey part is where you can set it up to populate the question automatically.

Attendance - 11

Click on the class list question in your form, then tick “Populate from values list”.

Attendance - 12

Then click on the grey drop-down menu and select “new values list”.

Attendance - 13

This will open a dialogue box, which contains 3 parts: Which spreadsheet you want the information from, which sheet, and then name the list.

Find the spreadsheet you want to use. If you’ve just been using it, it normally appears first. Click “Select”.

Attendance - 14

Now we need to tell it which sheet we want. Click on the “Select Sheet” drop-down menu.

Attendance - 15

Then select the sheet you want. Here, I want the sheet “ClassLists” where we added the class list earlier.

Attendance - 16

Now we need to tell it which column we want to use on that sheet. As our class list is in the first column, the column with the header “G1” is already showing. If you have a sheet with a number of classes on it, you’d have to click on the “Select column header” drop-down menu and select the specific column.

Note, as this works with columns, it’s important not to have anything else in the column, as this would appear in the form too. So, when adding class lists, just use that sheet to add columns of class lists.

Attendance - 17

On the right-hand side, we can see that it shows us the contents of that column, which we can see is the class list we want.

Attendance - 18

Finally, we need to name this value lists, with whatever name that makes sense.

Attendance - 19

So, for example, in the “Give this values list a name” box write “G1”.

Attendance - 20

Then, click the blue button.

Attendance - 21

On our form we’ll see the class list is now populated with the contents from that sheet.

Attendance - 22

If you expect possible changes to the class list or maybe you want to use the form for a different group in the future, then you can toggle the auto-populate questions options to on, so that the form checks every form submission or hour, for any changes and updates itself if there are.

Attendance - 23

Setting up the spreadsheet

Now, let’s set up the spreadsheet. We have 5 sheets. The data from the form will arrive and be stored in “G1”. Then a copy of it will be stored in “G1c”. Then this will be converted into an attendance sheet in “G1A”. The overall attendance figures per student will be displayed on the “Attendance” sheet, pulling in the data from “G1A”.

Attendance - 24

So, first let’s set up sheet “G1c” so that it automatically makes a copy of the data on sheet “G1”. In cell A1 type the following ARRAYFORMULA function. This will get all the data in column A on sheet “G1” and fill column A on sheet “G1c”. Copy and paste this into cells B and C, to do the same for those columns.

Attendance - 26

So, you should now have 3 columns like this:

Attendance - 25

Why do we need to make this copy? If we didn’t then what happens is that any row numbers we use on the next sheet will change as a form submission comes in. For example, if we want to use the data in cell A3, when a new form submission comes in, it will push that cell down, so that it becomes A4, and then it will update the reference on the other sheet to A4, which continue to be blank, as it should still be looking at cell A3, which has the data we want. The work around for this is to create a copy sheet as described above. (If anyone reading this knows a simple way to get around this I’d love to know it!)

Setting up the attendance sheet

Now let’s set up the sheet “G1A”. This is where we’ll see the attendance data more visually. We’ll see the list of students and their attendance will be shown per class date in the rows below.

Attendance - 29

At the top I’ve added the start and end dates.

Attendance - 27

As these will also appear on the first page “Attendance” then let’s add a reference to those cells. So, in cell B1 add this reference:

Attendance - 28

Cell B2 will have the same except it will refer to Attendance!B2.

Now, let’s add the list of students in row 3. On the ClassLists page the list is vertical, but here we need to show it horizontally, so we need to use the TRANSPOSE function to change it from vertical to horizontal.

In cell B3, type the TRANSPOSE function below:

Attendance - 30

The list of students will appear in each of the cells. Any changes to the ClassLists page will be replicated here automatically.

To work out the attendance, we first need to know how many classes there were in that particular period. To do this, we’ll count how many class dates are shown in column A from cell A5 downwards.

Attendance - 31

So in cell A4 we write the following COUNTA formula:

Attendance - 57

Now, we want to filter the attendance so that we only show the attendance of the classes between the start date and end date. To do this we’ll use the following QUERY function:

Attendance - 58

Phew! Long isn’t it? Don’t worry let’s go through it part by part.

First, we want to look at the data on the “G1c” sheet and we’re only interested in column B (the class dates).

Attendance - 59

Then we select just the class dates (column B).

Attendance - 60

Then we want to filter the dates by the dates that are the same or later than the start date on the Attendance sheet. Be careful with the syntax with this.

Attendance - 61

AND we want to filter the dates by the dates that are the same or earlier than the end date.

Attendance - 62

Finally, we sort the results in ascending date order.

Attendance - 63

At the moment, nothing exciting happens as we have no data, but you should get a message saying that the output is empty.

Attendance - 39

If you want to know more about how the QUERY function works, check out my QUERY post.

If the student attended, I want to add an X in the cell, if they didn’t then to leave it blank.

Attendance - 64

In cell B5, type the following formula:

attendance new1

It looks scarier than it really is. Let’s look at it in parts.

attendance new2

First, I use ARRAYFORMULA so that this formula works all the way down the column, without having to copy and paste the actual formula into the cells.

Then we have check to see if the cell B3 is blank, and if so, it leaves the cell blank. So, basically it’s checking to see if a student’s name is in cell B3, if it isn’t, it leaves it blank. More info on the IF function here.

attendance new3

Finally, if there is a student’s name and there’s a class date, it looks to see if that student’s name appears in the form responses on the “G1c” sheet. Using the VLOOKUP function, it looks for the student’s name from B3, then looks up the date from column A and looks on sheet G1c for that date and looks at the students that attended in column C on that date. If it finds the student, i.e. that they attended, it adds an X in the cell, otherwise, it leaves it blank. The IFERROR function is just there to leave it blank if it doesn’t find anything.

The final part on this sheet, is to count how many times a student has attended. We can do this by counting the number of Xs in that student’s column.

Attendance - 40

In cell B4, type the following formula:

Attendance - 41

This checks to see if B3 is blank, i.e. there’s is no student’s name. If it is, it leaves the cell B4 blank. If there is a name, it counts how many Xs there are in that column. See my post on countif if you want to know more about that function.

Copy cells B4 and B5 and paste them across to cells K4 and K5, so that there are 10 columns with the same formulas in them.

So, you should be left with a sheet like this:

Attendance - 29

I usually add a little bit of conditional formatting to show the Xs (i.e. class attended) more clearly. I just use the “Cell is not empty” option and fill the cell with a colour. See my post on conditional formatting if you don’t know how to do that.

Attendance - 71

Calculating and showing the students’ attendance

Now, the final part. I want a sheet that displays a summary of the students’ attendance for that period.

On the “Attendance” sheet, set up a table like this:

Attendance - 65

Now let’s grab the data from the different sheets. First, let’s add the class list.

Attendance - 47

In cell A4, add this formula:

Attendance - 67

This will get the class list from column A on the ClassLists sheet.

Then, let’s add the classes attended per student.

Attendance - 49

We get this from the “G1A” sheet (attendance sheet) from row 4. We use the TRANSPOSE function to convert it from horizontal information to being stored vertically. In cell B4 write the following formula:

Attendance - 50

We then want to add the total number of classes there were.

Attendance - 69

So, again we get that from the “G1A” sheet and the number was in cell A4. I’ve added an IF function, so that if there isn’t a student name in column A, it leaves the cell blank. In cell C4, write the following formula:

Attendance - 52

Finally, we work out the attendance percentage for each student.

Attendance - 68

We divide column B (attended) by column C(total). We need to add the full range (i.e. B4 to B13) as we are using the ARAYFORMULA function. I’ve also added an IF function to leave a space if there is no data in column B. In cell D4, write the following formula:

Attendance - 54

This leaves us with a summary of our class. Note, the total is 1 as even though we haven’t entered any classes yet, it’s counted the QUERY function as 1. You could change the total formula to get rid of this, but once the classes start being added this doesn’t get included.

Attendance - 66

I usually add a bit of conditional formatting to the attendance % column, to highlight those with bad attendance (in red) and those with good attendance (in green).

Putting it into practice

Let’s add some attendance and see what the final product is like.

The teacher opens the form on their mobile or on the computer and quickly adds who was there and submits the form.

Attendance - 70

Once submitted, they have the option of editing that response or submitting another.

Attendance - 72

The responses arrive in the “G1” sheet.

Attendance - 73

These are copied in the “G1c” sheet.

Attendance - 74

On the “G1A” sheet, this data is converted into the an easy to understand attendance sheet.

Attendance - 75

On the “Attendance” sheet we can clearly see the attendance of all our students.

Attendance - 76

To view the attendance for different periods all you have to do is change the dates and it will update the attendance accordingly.

You can make a copy of the sheet and linked form here.

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

Baz Roberts (Google+Flipboard / Twitter)

Apps Script Basics (3) – Variables and getting & setting values

In this post, we’re going look at how variables are set up and how they can store various pieces of information. We’re also going to look at reading and writing data from a spreadsheet, which is one of the most common tasks when working with one.

I’m going to use a simple example, where we have some data about three different students and we want to put all that data together in one single table in a different part of a sheet. Then, we’re going to format it a little bit to make it look better.

So, we will be getting data from these columns:

3Variables - 17

And writing it into these columns, all with Apps Script.

3Variables - 16

So first, what is a variable? It’s basically a container that stores information. You give it a name and state what’s in the container. All variables start with the keyword var. Then followed by the name for that variable. Apart from using keywords, you can call your variable pretty much anything you like.

3Variables - 20

Above, we have two examples of variables. The first is variable called studentsName and we have assigned the name John to that variable. So, every time we refer to studentsName we are referring to the name John. The same goes for numbers. The second variable is called numberOfStudents and we have stated that the number of students is 10.

Note, that text (or a “string”) is written between quote marks. Whereas, numbers don’t need quote marks, if they are being used as numbers. We use the equals sign to show that the text or the number has been assigned to that variable. In other words, that is what has been put in that particular container. The equals sign doesn’t mean equals to as in maths. For that a double (==) or triple (===) equals sign is used.

The code

Create a new spreadsheet and open the script editor from the Tools menu.

3Variables - 3

Leave the default function text in there. In between the curly brackets, I’ve created a variable called ss and I’ve assigned the class SpreadsheetApp and getActiveSpreadsheet method to it.

So, why have I done that? SpreadsheetApp.getActiveSpreadsheet() refers to the current active sheet in my spreadsheet and if I want to do anything in that spreadsheet I need to refer to this. What a variable allows me to do is instead of writing out SpreadsheetApp, etc every time, I can put it in a variable and just refer to that variable from now on. So, every time I refer to ss now, I’m actually referring to the active spreadsheet.

Next, as we’re going to refer to the sheet numerous times when reading and writing data to and from it, let’s store the active sheet in a variable called sheet. We now need to get the active sheet, so we need the getActiveSheet() method. We could write it out long hand like this:

SpreadsheetApp.getActiveSpreadsheet().getActiveSheet() but as we already have the first part stored in the variable ss, we can just use that.

3Variables - 4

Start by typing ss followed by a dot. As we can see it brings up the possible methods we can use with getActiveSpreadsheet().

3Variables - 5

We scroll down and select the getActiveSheet() method.

OK, now we want to start to create our table in a sheet.

3Variables - 18

First, type sheet follow by a dot. This refers to the current active sheet. We then need to get a range on that sheet. As we can see above, there are various options with the method getRange. For now, let’s just use the last one, which will require us to state the cell references we want to get.

3Variables - 6

Now, type the rest of the line. This will get the range A1 and set its value to the word Name. In other words, it will write the word Name in cell A1 on our sheet, like this:

3Variables - 21

3Variables - 7

Lines 6 and 7 are similar. We get cells B1 and C1 and write the words Subject and Mark. It sets up the header row like this:

3Variables - 22

Now we have our header row, let’s get the student data from columns F to H.

3Variables - 8

Here I create another variable called student1. This time I’m going to get the data in a row of 3 cells (F2 to H2). As there is more than one piece of data, we use the method called getValues(). This will store the three pieces of information: John, Maths, 65 in the variable student1. Yes, variables can store more than one bit of information!

3Variables - 9

Lines 10 to 11: We then do the same for the other two students. Now we have the 3 details of the three students in three different variables.

Now we’ve read the data in the sheet and stored it, we need to write it on the other part of the sheet.

3Variables - 10

I want to write the first student’s data in row 2, so I get the range A2 to C2 and then set the values of those cells to the contents of the variable student1.So, what will happen is that in cell A2 the John will be written, in B2 Maths and in C2 65, as we can see below:

3Variables - 23

3Variables - 11

Lines 14-15: We carry out the same process for the other two students, this time writing the data to rows 3 and 4, by using the variables student2 and student3. So, now we have our basic table:

3Variables - 24

Let’s make the table a little prettier by adding some formatting.

3Variables - 12

First, let’s get the header row, which is A1 to C1. We use the getRange() method and store that range in the variable header. Now, we have that header range we can use it to then add some formatting to it.

3Variables - 13

Line 18: Let’s change the background colour to yellow. To do this, we get the header range (header) and use the method setBackground(). Then we put the colour we want in the brackets with quote marks.

Line 19: Let’s also add bolding to the words. To do this, we again get the header and this time use the setFontWeight() method and in the brackets we add the word bold in quote marks.

As we can see, this has formatted the header.

3Variables - 25

Finally, let’s align all the cells in the table centrally.

3Variables - 14

Line 21: First, we need to get the range of cells in the table (A1 to C4). Here I’ve stored that range in the variable table.

3Variables - 15

Line 22: We then get that table range, add the setHorizontalAlignment() method to it and in the brackets state center with quote marks.

So, let’s run the program. This will read the data, write the data, and finally format it all in one go. The first time we run it, it will ask us to authorise the access we want. Just click “Review permissions” and then the blue button “Allow”.

3Variables - 1

3Variables - 2

As we can see this has now centred all our cells and the table looks much better.

3Variables - 16

By using variables, we were able to write a shorter piece of code and this code now also allows us to create a table with data for a different set of students.

Apart from the use of variables, hopefully, you can see how easy it is to get data from a sheet and to write data to it.

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

If you want to learn more about variables visit the W3schools site: Variables

If you’d like to learn more about formatting your sheet, check out my post: Pimp up your sheet

Here’s the full code:

3Variables - 19

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

Baz Roberts (Google+Flipboard / Twitter)