Apps Script Basics – SpreadsheetApp & for in loop

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

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

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


G Suite Services

7SpreadsheetApp - 28


Spreadsheet Service

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

7SpreadsheetApp - 33

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

7SpreadsheetApp - 347SpreadsheetApp - 35

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

7SpreadsheetApp - 36

There is a hierarchy to these:

SpreadsheetApp > Spreadsheet > Sheet > Range

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

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


SpreadsheetApp

7SpreadsheetApp - 29

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

7SpreadsheetApp - 30

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

7SpreadsheetApp - 31

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

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

7SpreadsheetApp - 37

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


Creating menu – SpreadsheetApp.getUi

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

7SpreadsheetApp - 1b

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

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

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

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

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

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


Global variables

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

7SpreadsheetApp - 2

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

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


Example 1 – Creating a new spreadsheet

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

7SpreadsheetApp - 24

7SpreadsheetApp - 3

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

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

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

7SpreadsheetApp - 13


Example 2 – Creating a spreadsheet with limited rows & columns

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

7SpreadsheetApp - 25

7SpreadsheetApp - 4

Line 25: Set up the function called example2.

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

As we can see it created a spreadsheet as before.

7SpreadsheetApp - 14

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

7SpreadsheetApp - 38


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

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

7SpreadsheetApp - 5

Line 30: Set up the function example3.

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

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

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

7SpreadsheetApp - 39

7SpreadsheetApp - 12


Example 4 – Creating multiple spreadsheets with different names

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

7SpreadsheetApp - 6

7SpreadsheetApp - 42

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

7SpreadsheetApp - 40

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

The syntax for a for in loop is:

for (counter variable in array name) {

Do something x the number of items in the array

}

7SpreadsheetApp - 6

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

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

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

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

7SpreadsheetApp - 15


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

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

7SpreadsheetApp - 27

7SpreadsheetApp - 11

7SpreadsheetApp - 41

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

7SpreadsheetApp - 7

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

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

7SpreadsheetApp - 16

7SpreadsheetApp - 17


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

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

7SpreadsheetApp - 26

7SpreadsheetApp - 8

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

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

7SpreadsheetApp - 18


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

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

7SpreadsheetApp - 19

7SpreadsheetApp - 9

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

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

7SpreadsheetApp - 10

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

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

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

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

7SpreadsheetApp - 20

7SpreadsheetApp - 21

7SpreadsheetApp - 22

7SpreadsheetApp - 23


The code can be found here on GitHub.

You can make a copy of the file here.

Link to the google reference page on SpreadsheetApp

More info on for in loops


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

Baz Roberts (Google+Flipboard / Twitter)


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

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

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

Creating a menu

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

Open the Script Editor from the Tools menu.

First Scripts - 2

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

First Scripts - 5

6If - 31

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

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

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

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

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

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

6If - 38

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


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

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

6If - 32

6If - 1

Line 2: Start with the function line.

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

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

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

If statements have the following structure:

If (a condition) {

Do something if the condition is true

}

6If - 2

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

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

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

Line 9: Close the function with another curly bracket.

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

6If - 39

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

6If - 40


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

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

6If - 3

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

6If - 4

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

6If - 5

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

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

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

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

6If - 41

6If - 39


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

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

6If - 6

Lines 29-31: As before.

6If - 7

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

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

6If - 8

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

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

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

6If - 9

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

6If - 42


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

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

6If - 33

6If - 10

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

6If - 11

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

6If - 12

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

6If - 43

6If - 44

6If - 45


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

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

6If - 33

6If - 13

Lines 64-67: As before.

6If - 14

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

6If - 15

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

6If - 45


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

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

6If - 34

6If - 35

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

6If - 46

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

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

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

6If - 17

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

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

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

6If - 18

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

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

6If - 19

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

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

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

6If - 37


Example 6b – Return back to first sheet

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

6If - 20

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


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

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

6If - 22

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

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

6If - 47

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

6If - 23

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

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

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

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

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

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

6If - 47

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

6If - 48

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


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

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

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

6If - 32

6If - 24b

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

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

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

6If - 25

This has the following format:

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

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

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

6If - 39

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


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

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

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

6If - 36

6If - 49

Line 134: First, get the sheet called Sheet1.

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

6If - 27

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

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

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

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

6If - 50

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

6If - 51

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

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

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

6If - 52

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

6If - 53

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

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

6If - 54


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

6IF 100

Click the your account.

6IF 101

Then click ‘Allow’.

6IF 102


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

You can find the code here at GitHub:

onOpen menu

Examples


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

Comparison and logical operators

if, else if , else, ternary operator

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

menus

UI


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

Baz Roberts (Google+Flipboard / Twitter)


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

LINK

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.

https://docs.google.com/spreadsheets/d/1i2paFxUjxun6pvLgfAf247Uw6eJGIwK19SLWrMD4Vdc/copy

Here’s the form:

https://docs.google.com/forms/d/1j9hC0S-P7BsbX5873v9BGptw2PbELOI8uuWb5QvjrdM/copy


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

https://docs.google.com/spreadsheets/d/1Dt3n4-vvSY1OFLrHXsATfHJBVjbG7JyoG1jgOLWwBFU/copy

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)


Apps Script Basics – 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.

https://docs.google.com/spreadsheets/d/1eB0HMYsfaJbL7snqVIJ7QI79pSRi-UC2m5c-Jtl6teU/copy

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)


Questionnaires – Automatically send feedback to individuals

It’s common to use questionnaires at the end of a course to get feedback on the course and teacher. When you have a lot of teachers, it can be hard work analysing that feedback and splitting into the different teachers. Here we’re going to look at how we can automatically send the individual feedback to each teacher, as soon as its completed by the student. Plus, retain a master feedback sheet with all the teachers on it for the head of department’s use.

There are 4 documents we’re going to create:

  • Questionnaire form
  • Questionnaire master responses sheet
  • Individual feedback sheet per teacher
  • Questionnaire links sheet

Here’s how they link together:

Questionnaire - 42

Those who have been following my posts will see that this one combines various previous posts into one. Namely, the use of the QUERY and IMPORTRANGE functions, setting up forms, and creating short URLs.


Setting up the Form

Let’s start with the form. Create a new form and add the questions you want the student to answer. An example of one is below:

Questionnaire - 1

Questionnaire - 2

Questionnaire - 3

Then connect that form to a spreadsheet to store the responses. Click on “Responses”, then the green spreadsheet icon.

Questionnaire - 4

We want a new spreadsheet, so just click “Create”.

Questionnaire - 5

This populates the Sheet with our questions.

Questionnaire - 6


Setting up the master feedback sheet

We have a sheet called “Form Responses 1” where all the form responses will be stored, and now we need a sheet for each teacher, where we’re going to store the feedback of that particular teacher.

Add a new sheet and type the name of the first teacher in cell A1.

Questionnaire - 7

In cell A2 we’re going to add a QUERY function, which will filter the feedback on the Form Responses 1 sheet and will display only the feedback for Barrie Roberts. Here’s the formula. The QUERY function is explained in detail in my post on the QUERY function.

Questionnaire - 9

In summary, it looks at the Form Responses 1 sheet, gets all the responses, looks for ones where in column E it matches the name in cell A1 (i.e. Barrie Roberts), then sorts it by date (column A), with the most recent at the top. With no responses, you’ll get the error message “#N/A”, which will disappear as soon as you start receiving responses.

Here, I’ve named the sheet “Barrie”.

Questionnaire - 8

To just test it, I’ve filled out the form with a response for Barrie Roberts and on the sheet “Barrie” the feedback automatically appears. I normally use this as an opportunity to format the sheet a little, e.g. change the column widths, etc. You can always delete this test responses from the form responses 1 page.

Questionnaire - 37

Then duplicate this sheet for every other teacher you have. The only things you will need to change is the name in cell A1 and the sheet name.


Setting up the Individual feedback sheet

The spreadsheet above has everyone’s feedback on it and usually we won’t want to share that with all the teachers. So, let’s set up the teacher’s individual feedback sheet.

First, create a new spreadsheet. Then we’re going to copy one of the teacher’s sheets to the new file. We do this, so that the formatting is the exactly the same in the new sheet, saving us some time and ensuring they look the same. So, from the master spreadsheet, click on a teacher’s sheet and select “Copy to”.

Questionnaire - 11

Then, click on “Recent” and select the new spreadsheet you created. Here, I had already called it “Questionnaires – Barrie Roberts”.

Questionnaire - 12

The sheet will appear and by called “Copy of…”. Delete Sheet 1 as it’s not needed and rename the ‘Copy of’ sheet.

Questionnaire - 17

Now, we will need to delete the name and QUERY function from cells A1 and A2.

Questionnaire - 13

Then add an IMPORTRANGE function in cell A1. This will look at the master sheet, and import the information from the sheet and cells specified into the individual sheet. For more information on how the IMPORTRANGE function works, see my IMPORTRANGE post.

In the brackets and between quote marks, I add the URL of the master sheet, add the sheet called Barrie, and state the cell reference I want. Note, I’ve used an open-ended one, so that it will important all the data from the sheet, no matter how many rows there are.

Questionnaire - 14

We then need to authorize the connection between the 2 sheets. Just hover over cell A1 and click “Allow access”.

Questionnaire - 15

As we can see, it’s imported the same information from the master sheet and will update automatically as more form responses are received in the master sheet.

Questionnaire - 38


Setting up the Questionnaire links

The last thing to do is to set up the links to the questionnaire. To aid analysis afterwards and for the whole system to work automatically, it’s essential that the information is in a specific format.

The most important thing in this example, is that the teacher’s name is always the same, i.e. “Barrie Roberts”. As, if we left it up to the students to type in the teacher’s name, we could get all sorts of variations, like Mr Roberts, B Roberts, etc. Then the QUERY function wouldn’t work. In our academy we also want feedback on things like the rooms, so, we also make sure we control the format of those.

To do this, we just create a sheet where we add the information about each class, then use that to create a pre-filled link, which will be shared with the students. In this example, that pre-filled form will include the class name, level and the teacher’s name. The good thing is that you only need to set this sheet up once.

Going from left to right, we add columns for the class, level, and teacher. Then a column for the short link we’re going to create. A column for where the full link we be created. In column F we’ll copy and paste the pre-filled link from the form. Then in columns G to I, we will take the infomation in columns A to C and automatically replace any spaces with “%20”, which will make sure the URL we finally create doesn’t have any spaces in it, which can break the link.

Questionnaire - 19

So, in cell G2 we add this substitute function. This will replace any spaces in cell A2 with %20. See my post on creating short URLs for more information.

Questionnaire - 20

We do the same for column H and I, for the level and teacher.

Questionnaire - 21

Questionnaire - 22

So, adding the class, level, and teacher to our sheet, we can see that in columns G to I, its replacing the spaces with the %20 character.

Questionnaire - 39

Now we need our pre-filled link from the Form. Open the form and click on the 3 dot menu and then “Get pre-filled link”.

Questionnaire - 23

Then complete the fields you want to pre-fill with placeholders. Here, I’ve just used the same name as the questions, so CLASS, LEVEL, and TEACHER.

Questionnaire - 25

Click Submit and at the top of the page, a pre-filled link will appear. Click on that and press ctrl+C to copy it.

Questionnaire - 26

Then paste it in cell F2.

Questionnaire - 24

Finally, in cell E2, we need to replace the placeholders with the information on our sheet in that particular row. To do this, we use the SUBSTITUTE function. This is looking at the URL in F2 and replacing the word CLASS with the contents of cell G2 (the class name), replacing LEVEL with cell H2 (the level), and TEACHER with I2 (the teacher’s name).

Questionnaire - 27

In cell E2, we now have a link which we could share with the students, which will open the form and already have the class, level, and teacher pre-filled out.

Questionnaire - 40


URL shortener

This next part is optional, but the one problem with the link we’ve created above is that it is very long. So, I usually create a short URL from that, which is much easier to share and use. The following is explained in more detail in my post on creating short URLs.

The code here is a little different from my earlier post on creating short URLs, in that you don’t need to select the range of URLs with this code, it automatically creates short URLs for all the full URLs in column E.

Add this code in the script editor.

Questionnaire - 35 (1)

To be able to run it you’ll have to activate the UrlShortener service. Click on Resources, then Advanced Google services.

Questionnaire - 30

Scroll down and turn UrlShortener on. Then click on Google API console at the bottom in blue.

Questionnaire - 31

In the bottom right corner you’ll see URL Shortener API. Click on that.

Questionnaire - 32

Then click Enable.

Questionnaire - 33

Click the arrow back and click OK.

Questionnaire - 34

The code is now ready to be run. Click Shorten URL and Shorten.

Questionnaire - 29

The first time you run it you’ll have to accept the usual authorisation checks, just click the blue buttons.

Then your short link will appear. This is what you would share with the students.

Questionnaire - 36

Clicking on the link will open the questionnaire:

Questionnaire - 28

As you can see, it has the fields we wanted pre-filled out.

I wouldn’t suggest going through all these steps just for just one class, as the sheet is set up to create links for numerous classes. Just copy down the cells E to I to as many rows as you like and the program will create URLs automatically for all of them based on the information you enter in columns A to C. So, you’ll end up with something like this:

Questionnaire - 41

Here are the links to make copies of the files above:

End of course questionnaire spreadsheet (the form is linked so will copy automatically)

https://docs.google.com/spreadsheets/d/1F4TXCUFBz5GREd8z8ilxDaSZpU6WKyqhY9-RK1NUrmI/copy

Individual questionnaire feedback spreadsheet

https://docs.google.com/spreadsheets/d/1ZnZ8S2_9oK6NNOE3ETgp0URI5EjM2_0D8VX6K4BrtVE/copy

Questionnaire links spreadsheet (including the short URL code above)

https://docs.google.com/spreadsheets/d/14bf_xG_cRfYOPj5Ku8ymLy-m3OZeXRYIZ-oZcHxhScQ/copy


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

Baz Roberts (Google+Flipboard / Twitter)


Apps Script Basics – First Docs and Forms Scripts

Following on from my last post on creating your first script, here we’re going to edit a Google Doc and also a Google Form. This will show you how you can access Docs and Forms, not just Sheets with Apps Script and it will introduce you to how it’s possible to add and edit the text in those documents.


First Google Docs Script

In this little script, we’re going to write some text in a Google Doc.

To open the Script editor in Docs, click on “Tools” from the menu and then “Script editor”:

First Scripts - 3

We leave the function that is already written in there and in between the curly brackets, we type DocumentApp. Like the SpreadsheetApp in the previous post, this is telling the computer which class to access, in other words, whether it is a Google Doc, Google Sheet, etc.

Then add a dot and various possible options will appear in the menu.

First Scripts - 33

First, we want to get the current active document. So, scroll down and you’ll see the method called getActiveDocument(). Click on it to add it.

First Scripts - 34

Add a dot again and a new menu will appear.

First Scripts - 35

Now we have the document we want to get the document’s body. So, scroll down until you see the method getBody() then select it.

First Scripts - 36

Add a dot and another menu will appear.

First Scripts - 37

Now, we want to add some text into the body of the document. To do that, we need the method setText().

First Scripts - 38

As you can see, it’s asking you to add some text in between the brackets.

First Scripts - 39

Type in the text you want, remembering to write it between quote marks. End the line with a semi-colon.

First Scripts - 40

So, the code should look like this now:

First Scripts - 41

So, to summarise, we’re getting the Document app, getting the active document, then getting the body of that document and then adding some text to it.

Press the play button on the toolbar to run the program.

First Scripts - 49

The first time you run it, you’ll have to review the permissions as always. Click “Review permissions”.

First Scripts - 12

Then click “Allow”.

First Scripts - 13

As we can see, it’s added the text to the document.

Docs and Forms Scripts - 25


First Google Forms Script

Now, let’s look at how we can edit text in a Google Form. Here, we’re going to change the title of the form.

Create a new form. At the moment, as it’s a new one, the title will just say “Untitled form”.

First Scripts - 42

To open the Script editor in Forms, click on the 3 dots menu and then “Script editor”:

First Scripts - 4

This time we want to edit a form, so we use the FormApp. Type that in and add a dot to bring up the menu.

First Scripts - 43

Now, we want to get this active form, so we scroll down and click on the getActiveForm() method.

First Scripts - 44

Add a dot and you’ve guessed it, another menu will appear.

First Scripts - 45

This time we want to change the title of the form, so we scroll down and select the setTitle() method.

First Scripts - 46

As you can see, like the Doc example above, it’s prompting you to add some text in between the brackets.

First Scripts - 47

Here, I’ve added “Questionnaire”. Then I end the line with a semi-colon.

First Scripts - 48

Press the play button to run the program.

First Scripts - 49

The first time this will prompt you to review the permissions. Click on “Review permissions”.

First Scripts - 14

Then click “Allow”.

First Scripts - 15

As you can see, this changed the title from “Untitled form” to “Questionnaire”.

First Scripts - 16

Hopefully, you can start to see how with a little bit of Apps Script knowledge you will be able to edit Docs, Forms, and Sheets allowing you to do this automatically rather than manually.

Here’s a copy of the Google Doc, which includes the code.

Here’s a copy of the Google Form, which also includes the code.


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

Baz Roberts (Google+Flipboard / Twitter)


Apps Script – Creating & sharing Class folders in Drive

Teachers using Drive often need to set up folders for their class and for their individual students. Doing it in Drive is not the most exciting job to do. So, to make your life easier, here’s a script, which will set up your folders for you. I’ve made it so there is a little bit of flexibility depending on what folders you need, but of course the beauty of having the script is that you can always tinker with it to meet your exact needs.

It will create a class folder in My Drive, a folder for each student, and if you want a separate classwork folder and a separate homework folder. It will share the class, classwork, and homework folders with all the students, but only share the individual folders with the specific students.


Class Folder Creator sheet

Here’s the Sheet that we will use to fill out the information required and to run from the script from.

Class Folders - 3

Class Folders - 4

At the top, you add the class name, and then Y or N (yes or no), as to whether you want a classwork folder or homework folder or not.

Class Folders - 5

Underneath you add the students’ names and email addresses. These could easily be pasted in if you already have a list.


The code

Here are the main steps of the code:

  • The code will get the details entered on the Sheet and store them in the variables.
  • Then create the Class folder in My Drive.
  • Add the students as editors to that folder.
  • Check to see if you want a classwork folder or a homework one, and if so, create them.
  • Finally, it will create the individual student folders.

There are some similarities to the code I used in my post “Multiple folder maker“, so here, I will focus more on the new parts. Let’s look at it line by line:

Class Folders - 10

Lines 1 and 2: First we set up the function and get the Sheet.

Class Folders - 11

Line 5: We then get the sheet called “ClassFolders”.

Line 6: We get the class name we entered in cell B2. Here I’ve used the number of rows and columns, i.e. (2, 2). I could have in fact just used “B2” in the brackets.

Class Folders - 12

Line 9: Here we find out if the user wants a classwork folder or not by getting the value in cell B3, i.e. (3,2). We store it in the variable classwork.

Line 10: Similarly, we find out if the user wants a homework folder. We store this in the variable homework.

Class Folders - 13

Line 13: We get the row with the last student name and student email on it, which will the last row on the Sheet.

Line 14: Now we need to get the list of students. Here we start at row 6 and column 1 (A) and go down until the last row minus 5 (we don’t count the first 5 rows as we started on row 6), and we are just getting 1 column so we finish the range with a 1, then get the values in that range. We store these values in the variable studentsNames.

Class Folders - 14

Lines 17 and 18: We repeat the same for the students’ email addresses, which are in column 2 (B). We store these in the variable studentsEmails.

Class Folders - 15

Now we need to create the class folder.

Line 21: We’re going to create it in My Drive, so we use the DriveApp class followed by the createFolder() method.  Then we add the class name variable, className. Then we store this in the variable newFolder.

Line 22: We then get the ID of that new folder. Note, we could save a line of code here, by just adding .getId() on the end of line 21, like this:

Class Folders - 22

But, I’ll leave the steps in, so you can see what’s happening more clearly.

Line 23: Then we need to get that folder by its ID to be able to use it. We use getFolderById() to do so. Then we store it in the variable classFolder.

Class Folders - 16

Now, we add the students as editors of that folder. We do this by looping through the students’ emails and adding them one by one to the folder.

Line 26: We set up a simple for loop and continue it while its less than the number of email addresses (using the length of the array studentEmails).

Line 27: Each time we go around the loop, we want to add a student as an editor to the folder. We get the folder (classFolder) and use the addEditor() to add them. We use the variable s in square brackets to show where in the array we are. So, it will start in position 0 and continue to the end of the list.

Class Folders - 17

Now we want to see if the user wants to create a classwork folder and if so, create one in the class folder we recently created.

Line 31: We use an If statement to see if the classwork cell contains a “Y”.

Lines 32: This is similar to line 21, except here we’re going to create the folder not in My Drive but in the class folder. So, we get the classFolder variable and create the folder using that, and call it “Classwork”.

Class Folders - 18

Lines 36-38: We do exactly the same for the homework folder.

Class Folders - 19

Now we want to create the individual student folders. The folder creation is similar to before, except this time we will be looping down the list of student names previously stored in the variable studentsNames.

Line 41: We set up a for loop to go down the list of student names, using the i variable to track where we are in the list.

Line 43: We then create a folder in the class folder and name it with the current student name we are at in the loop. We do that by using studentsNames[i].

Lines 44-45: Then we get the ID of the student just created and get the folder by that ID.

Class Folders - 20

Now, we need to change the access rights for the student folders. As the student folders will be created in the class folder, they automatically assume the same access rights as the class folder. So, for each individual folder, we need to remove all the students except for the one who needs access to their personal folder. To do that we remove all the students as editors, then add the specific student.

Line 48: We set up a second loop, this time using the variable j to keep count. This will loop down the list of student emails.

Lines 49-50: Every time the loop goes around, it will remove a student email from being an editor of this folder. To do this we use the removeEditor() method and pass the studentsEmails variable to it. Then we close the j loop.

Lines 51-52: Then we need to add the student we want, who is the same as the student we created the folder for. So, we use addEditor() to add them and we use the i variable to identify which student it is. Then we close the i loop.

Class Folders - 21

Lines 55-56: Finally, I think it’s good to tell the user that the process has finished. So, here we display a toast message, using the toast method. Then in line 56 we close the function.

To run the code from a menu on the Sheet, I’ve also added this onOpen function (explained in previous posts):

Class Folders - 23


Running the code

Now let’s run an example with the information entered as below:

Class Folders - 4

Class Folders - 5

Class Folders - 24

Click on the “Folders” menu and then “Make & share class folders”.

The first time you’ll be asked to authorise the script, Just click the blue boxes.

Class Folders - 1

Class Folders - 2

This will take about 10 seconds and once you see the toast message, we will see the new folder called “Maths 101″in our My Drive.

Class Folders - 6

Double-clicking on that, we can see the sub folders we have created. There are the classwork, homework, and student ones.

Class Folders - 7

If we look at the access rights the class folder has (and also the Classwork and Homework have), we’ll see it’s been shared with everyone on my little list. Note, as I included myself in the list of “students”, I’m both a student and owner of the folder.

Class Folders - 9

Looking at the access rights of the “Baz Roberts” folder we’ll see it’s only been shared with me (Barrie Roberts-the owner) and the student “Baz Roberts”.

Class Folders - 8

The students get an automatic invite to the class folder and an invite to their individual folder.

Class Folders - 27


Hopefully, this will save you some time and effort. You can make a copy of the file and code here. In the same file, I’ve included this script, and the ones from previous posts: file maker and folder maker.

Here’s the full code:

Class Folders - 25Class Folders - 26


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

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)