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

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

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

So, we will be getting data from these columns:

3Variables - 17

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

3Variables - 16

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

3Variables - 20

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

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

The code

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

3Variables - 3

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

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

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

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

3Variables - 4

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

3Variables - 5

We scroll down and select the getActiveSheet() method.

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

3Variables - 18

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

3Variables - 6

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

3Variables - 21

3Variables - 7

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

3Variables - 22

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

3Variables - 8

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

3Variables - 9

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

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

3Variables - 10

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

3Variables - 23

3Variables - 11

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

3Variables - 24

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

3Variables - 12

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

3Variables - 13

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

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

As we can see, this has formatted the header.

3Variables - 25

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

3Variables - 14

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

3Variables - 15

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

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

3Variables - 1

3Variables - 2

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

3Variables - 16

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

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

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

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

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

Here’s the full code:

3Variables - 19

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

Baz Roberts (Google+Flipboard / Twitter)

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)

Individual questionnaire feedback spreadsheet

Questionnaire links spreadsheet (including the short URL code above)

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

Baz Roberts (Google+Flipboard / Twitter)

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

Apps Script Basics (1) – First Script

Here I want to show you how easy it is to write some code using Apps Script. This is the code that sits in the background of some of the Google Apps. This post is for those taking their first steps into writing code, so I will explain everything step-by-step.

First Google Sheets Script

First, you’ll need to open the Script editor. This can be found in various places, but here we’re going to be using a spreadsheet, so we’ll open the one connected to that.

Let’s dive straight in and write our first bit of code. We’ll start off in a Sheet. So, open a new Sheet from Drive. Then from the “Tools” menu, select the Script editor.

First Scripts - 2

Open the Script editor and you’ll be presented with a screen like this:

First Scripts - 5

The main white part of the page is where the script is written. By default, it already adds a bit of code in there for you. In a later post, I’ll explain the editor a bit more, but for now you’ll just need to type in the code, save it and then run it.

Let’s go through it step-by-step.

First Scripts - 17

We’ll leave what’s already on the page, as all programs need a function to run the code. A function is just a way of grouping a bunch of code together, then by giving it a name will let the computer know that running that function, will run the code inside it.

Line 1-3: The syntax for this is, function + the name of your function (we’ll leave it as myFunction but you can name whatever you like) + two parenthesis and then two curly brackets. In between the two curly brackets, is where you put your code. They show the start and end of it.

What we’re going to do in this little piece of code, is add the text Hello! to cell A1. To do this, we will need to:

  • get the active spreadsheet
  • get the active sheet
  • get the range (cell)
  • add the text to the cell

Note, the structure here, we’re getting the overall spreadsheet, then getting a particular sheet within that spreadsheet. Then we’re telling the computer which particular range we want on that sheet, then finally stating what text we want to add.

In this example, we’re going to add just one line of code.

First Scripts - 18

Line 2: First, type in SpreadsheetApp then follow it with a dot. This is what’s called a Class and is the top-level of the structure and the SpreadsheetApp tells the computer that we want to work with a spreadsheet. Make sure you use a capital S and capital A.

When you type the full stop a menu will appear with various options. This is the content assist, which helps you write the code quicker by offering you possible options and where you can click on them to save you typing them.

First Scripts - 19

We want to get the active spreadsheet, so scroll down the list until you find getActiveSpreadsheet(), then click on it. This will add it next to what you’ve already written. The level below a class are the methods. These are just specific instructions telling the computer what to do. What you will see in Apps Script is that the names of the methods are very intuitive and in most cases are written in plain English.

The methods are written in Camel Case. What’s that? The first word is in lowercase and the first letter of words after it have a capital letter. So, the method we just used was written like this getActiveSpreadsheet(). This makes it easier to read and must be written like this as JavaScript and Apps Script are case-sensitive.

First Scripts - 20

Add a dot to the end of the row so far and another menu will appear, this time with different options. These are the things we could do to the spreadsheet.

First Scripts - 21

Now, we want to get the active sheet. So, as you might have guessed we want the getActiveSheet() method. Scroll down the list and click on it, or alternatively you can start writing the method, for example, getA, and this will filter the list to the methods starting with those letters.

First Scripts - 22

Add a dot again and another menu will appear.

First Scripts - 23

Now we want to get a particular range on the sheet. The method we want is getRange(), but as you’ll see there are 4 different options. For now, we’re going to use the bottom one, as in between the brackets we’ll add a normal cell reference, i.e. A1.

First Scripts - 24

Delete the part in between the brackets that says “a1Notation”.

First Scripts - 25

And write the cell reference with quote marks.

First Scripts - 26

Add a dot again.

First Scripts - 27

This time we want to state what we want to put in the cell. We can do all sorts of things, like add colours, borders, change the format, but here we want to add a piece of text, so we will need to set its value, then state what that value is.

First Scripts - 28

So, from the menu, choose the setValue() method.

First Scripts - 29

Then in between the brackets write “Hello!”, remembering to include the quote marks, as this is text.

First Scripts - 30

Generally, when we end a line of code, you should add a semi-colon.

First Scripts - 31

So, the code should now look like this. So, as we go from left to right, it’s drilling down from the overall spreadsheet to the specific cell and then stating what we want to do with that cell.

First Scripts - 49

Now, we’re ready to run our code. First, make sure you save it, by clicking on the disk icon under View.

If you haven’t already, you’ll need to name your script project. Just type a name into the box and click OK.

First Scripts - 8

First Scripts - 9

First Scripts - 49

Then click the play button to run the code. The first time you run any code, Google will require you to authorise it. This is a safety mechanism and allows you to check that you are giving access to the parts that you were expecting to.

First Scripts - 10

Just click Review Permissions.

First Scripts - 11

This tells you that you’re going to view and manage a spreadsheet, which is true. So, we just click the blue Allow button.

First Scripts - 50

First Scripts - 51

At the top of the screen you’ll see the status messages as the code is running. Once the code has finished they will disappear.

First Scripts - 32

Open the spreadsheet tab and you’ll see in cell A1 your text.

Well done! You’ve created your first script! Not the most exciting I know, but it introduces you to not only how to write a script but also the basic structure of building up a piece of code.

If you’re just starting out, I would recommend starting off in Sheets, as personally, I found it the easiest to get my head around how Apps Scripts works and the sheet and cell structure of a spreadsheet is easier to understand, but it depends on your circumstances.

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

Baz Roberts (Google+Flipboard / Twitter)

Placement Test

Students who want to study a language at a language academy, have to do a placement test, which confirms their current level in that language and then tells the tester in which level they should be placed.

Here we’re going to see how a combination of Google Forms and Sheets, the functions VLOOKUP and ARRAYFORMULA and conditional formatting, can produce a simple but effective placement test.


First, we create the test using Forms. If you’re new to using Forms, then see my posts on how to use Google Forms.

In the test, I want the students to record some contact information and then below will be the test questions.

Here we have the fields to record their email address, name and telephone number, all of which are obligatory.


To set up the email address field so that it checks to see it’s a valid one, we go to the settings cog (on the right here).


Then tick “Collect email address”. Then click “Save”. This will add an email address field with data validation, which will check to see if an email has been entered. Note, that this always puts this field at the top of the form. Personally, I’d prefer to be able to move this, but at present that’s not possible.


Then we add the multiple-choice questions in our test.


I want the test to automatically correct this, so  click on the settings cog and this time select “Quizzes”. Then select the “Make this a quiz” option. Under “Release mark” choose “Later, after manual review”, so that the student doesn’t have the option of seeing their marks as soon as they’ve finished the test. I also untick all the options under “Respondent can see”.


Back in the questions part, we now need to tell the form,which questions are correct. Click on a question and then “Answer Key”.


Then click the correct answer. This will add a green tick next to it. Also, change the points to 1.


Repeat this for all the questions. Now we need to link the form to a Sheet. Under “Responses”, click the green Sheet icon.


We want to create a new spreadsheet, so leave the option selected and click “Create”.



This will open the Sheet and you will see the different fields from the Form on the sheet.


I usually format this a little and sometimes move the columns around to suit my needs, for example, like this:


Add a new page and let’s call it “Levels”. This is where we’re going to add the levels which correspond with the result from the test. In this example, we have 3 possible levels A1, A2, B1. In columns A and B and add the ranges for each of those levels. So, for example, if someone gets 6/10 in the test, the level they will go in is A2.


I’ve added a row (row 3) with just zeros, and nothing in the level column. This is important as we will see later, so that if there is nothing in the results column on the form responses sheet, nothing will be added to the levels column.

Now, let’s see if it works. The first time, I fill it out with the correct answers just to check everything is working ok. It also allows us to check future responses against this answer key, as we’ll see below.


As we can see the first response have been stored in the sheet fine.


Now we want the sheet to automatically add the appropriate level for the result. To do this we use two functions, VLOOKUP and ARRAYFORMULA. The VLOOKUP function, looks at the results in column B from B2 to the end of column B (it’s open-ended and will automatically continue as new rows are added), then looks up that result in the table on the Levels page in range A3:C6, and gets the level that’s stored in the 3rd column.

For more information on VLOOKUP see this post.

We add this to the ARRAYFORMULA function, which will automatically add this formula to all the rows below, so there’s no need to add the formulas for every row.


When we submit another test, as you can see the responses are added to the row below and the level is automatically added.


Note, that no level is added where there is no responses, this is because of the zeros line we added earlier in the levels table.


Sometimes we also want to see which answers the students got right and wrong. We could do this back in the form, but usually we’re only working with the sheet, so it can be useful to highlight here.

Select the answers from the second response (row 3 (e.g. G3:P3).


Right-click and select Conditional formatting.


This will open the conditional format rules side bar. See my post on Conditional Formatting for more info.


First, let’s apply this formatting to all the columns from G to P. Currently we only have G3:P· selected.


Change the range to an open-ended one, so that the range will always go down to the last row on the sheet. This means even if more rows are added, all responses received will be formatted.


Click on the drop-down menu “Cell is not empty”. Then select “Custom formula is”.


Here, add the formula “=G3=G$2”. This will check if the answer in G3 is the same as G2 and if it is, it will fill the cell green. This will also check all the other cells in the range and will update the cell references accordingly. So, it will check H3 is the same as H2 and so on. The use of the dollar sign means in rows below, the answer row reference won’t change, e.g. it will check that G10 is the same as G2 and so on.


Now we need to add an extra rule to show the incorrect answers in red. Click on “add new rule”.


Here, we want to check if G3 is different from G2 and if so to fill the cell in red. So, we use the not equal symbols: <>


The problem with this is that as we are using an open-ended range, this will fill all the cells on the sheet in red that aren’t the same as the answer key.


So, we need to change the formula so that it checks to see if the answer is different AND if the row isn’t blank. We use the following AND function, which checks both conditions.


Now, it only highlights the rows with answers in them.


Here’s the link to make a copy of the Sheet and Form.

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

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)

Apps Script – Clocking in & out system

In this post, we’ll look at setting up a simple clocking in and out system using Google Forms and Sheets. This idea originally came from a real life situation where we had to implement a clocking in and out system to meet a change in Spanish law. With our teachers working both on-site and off-site, we had to come up with a flexible solution to be able to do this. The best things about it are that the teacher can do it wherever they are teaching via their mobile phone, it was quick and easy to implement and it’s free!

This post will introduce using Apps Script to change and update forms based on form submissions, plus adding formulas to sheets when a form is submitted.

How’s it work?

The employee fills in a Google Form on their mobile (see below). They enter their individual code and press “next”. Note, there is a reminder at the top, telling them when they last clocked in or out, which helps them do it correctly. This is updated via Apps Script.


They then tap “clock IN” or “clock OUT” and press “Submit” to register the time. There’s also a reminder of whether they need to clock in or out. Again, this is updated via Apps Script.


The form submission is then stored in the linked Sheet. When they clock out, the hours they worked is added to the sheet, via Apps Script.



First, we create a new spreadsheet and create a new form, which automatically links it to the spreadsheet.


Then in the Form, we set up the first page, which will require the employee to enter their individual code.


To get the Form to check the code is correct, click on the question, select “Data validation” from the 3 dots menu.



Then change the default options by clicking on the drop-down menus, to “Number” and “Equal to”. Then type in the number you want the employee to type in. You can also add some help text, in case they enter something wrong.


Make this a Required question to make sure they can’t progress without completing it.

Then we create a second section, with the following information and questions:


Now the form and sheet are ready, we add the two pieces of code.

  1. Connected to the Sheet – to work out the hours worked
  2. Connected to the Form – to update the form, reminding the employee when they last clocked in/out and whether they need to clock in or out next


From the Sheet, open the Script Editor from the Tools menu.


Here I’ve renamed it “Hours Register”.



Lines 1-2: I’ve named the function onFormSubmit(). Then we get the active Spreadsheet.


Line 5: We get the active sheet and it’s important to note, that this will be the sheet that has received the form submission. So, if we have a spreadsheet with multiple sheets connected to different forms, the code will know which sheet to get the latest information from.

Line 6: Then we get the last row, via getLastRow(), which is where the latest submission will be.


Line 9: Here we want to get the clocking in or out status the employee submitted. To do this we get the range from column D and specify the row by using the variable lastRow we collected earlier. We combine the two to make a cell reference simply by using the plus sign, e.g. D15. Then we get the value in that cell.


Line 12: Here we want to get the ‘hours worked’ cell in the latest row. Similar to above, but this time we get column E and add the lastRow variable. Here we just want the cell reference and not the value, as we’re going to need this when we add a formula in that cell.

Here’s a reminder of what the sheet looks like and which columns correspond with what data.



Now, if the employee has clocked out, we will add a formula in the ‘hours worked’ column, which will get the time clocked out and subtract the time clocked in from it.

Line 15: First, we set up an IF statement to check if the employe has clocked out. We do this by checking if the value stored in the variable finish, which we set up in line 9, is equal to “clock OUT”. If it does we run the code in lines 16 and 17.

Line 16: Here we will set a formula in the hours worked cell. We get the cell from the variable hoursWorked and use the setFormulaR1C1() method.

Instead of using normal cell references, we are going to use row and column references and we are referring to how many rows or columns we are away from the ‘hours worked’ cell. The formula that actually gets added to the sheet will be in the normal format, i.e. column letter and row number (e.g. B15-B14).

The timestamp data we want is in column B, which is 3 columns to the left of the ‘hours worked’ column (E). We will need the cell in that column from this row (clock out time) and from the row before (clocked in time). So, we put R[0] (same row) C[-3] (three columns to the left) minus R[-1] (1 row above) C[-3]. Note, the formula is within quote marks.

Line 17: Finally, I want the time format to be hours:minutes, e.g. 2:45. I get the hours worked cell and using the setNumberFormat() method, I state the format I want, i.e. “H:mm”.


Line 21: The codes we use, are connected to employee’s identification numbers, which sometimes can start with zeros. So, to ensure the code is displayed with all 4 digits, we get the last cell in column C.

Line 22: Then, like in Line 17, we use setNumberFormat() to state the format we want. In the brackets we add “0000”.


Just to tidy things up, I also want to centrally align the information, except for the comments column.

Line 25: First, we get the last cells from column B to column E. Here, it’s combining column B with the last row number with a colon (:) and column E with the last row e.g. B15:E15.

Line 26: Then, we set the horizontal alignment to ‘center’ by using the setHorizontalAlignment() method. Line 27 closes the function.

To get the code to run automatically, we need to set up the onFormSubmit trigger. Click the triggers clock symbol on the toolbar.


This will open the triggers dialogue box. Click on “No triggers set up…”.


Change the Events to “From spreadsheet” and “On form submit”. Then click “Save”.


To set up the trigger the first time, you will need to authorise it. Click “Review Permissions” and then “Allow”.




In our context, our teachers have to clock in and out many times during the day, and they sometimes forget whether they last clocked in and out. This little code updates their form to help them remember when they last did it and what they need to do the next time.

It also provides an opportunity to show how forms can be updated automatically from form submissions.

Open the Script editor in the form via the 3 dots menu.


Let’s go through the code step-by-step:


Line 1: Call the function onFormSubmit ().

Line 3: Then we get the active form by using the FormApp class and getActiveForm() method.


We need to get the form responses that have been submitted.

Line 6: First, we get the form responses using the getResponses() method and store them in the variable formResponses.

Line 7: To get the latest response, we’re going to use the length property, which will tell us how many responses there are.

Line 8: Then, we want the last response in formResponses. We’ll use the responseLength to get the last position. We subtract one as this is an array. So, for example, if there are 10 responses, the last one will be array position [9].


Above, we got the responses from different form submissions, and got the latest one and stored it in the variable formResponse. Now, we need to get the responses to the questions within that last form response. First, we want to get the clock in or clock out response, which is in the second question.

Line 11: Here, we get the responses to the questions in the last form response, using the getItemResponses() method, then store it in itemResponses.

Line 12: Now, let’s get the clock in / out question, which is question 2, i.e. in the itemResponses array at position 1.

Line 13: Then, we get the response in that question, using the getResponse() method and store it in the variable startStop. Now, we have whether the employee submitted clock in or clock out.


I also want to add a message to state when the last time they clocked in or out was. So, I will need to get the timestamp from the last form response. As the timestamp format will include the full date and time and I only want to display a shortened date and time, we’ll have to format the date.

Line 16: I get the last form response, already stored in formResponse and use the getTimestamp() method to get the time it was submitted.

Line 17: To format the date, we need to know the time zone we’re working in. Here, we use Session.getScriptTimeZone() (more info here).

Line 18: Finally, we use the Utilities class and the formatDate() method to format the date the way we want it. There are 3 parts to it in the brackets: timestamp, timezone, date format we want.

Now, we need to change the text in the form based on whether they clocked in or out.


Line 21: We set up an IF statement to see if they clocked in. This checks the value stored in startStop is the same as “clock IN”.

The small the under the title of the form is the description and it’s there where I want to add the text, telling the employee when they last clocked in or out.

Line 22: We get the active form using the variable form and change the description to what we want, using the setDescription() method. In the brackets, we use a mixture of strings and the variable timeDate to get the text we want.

I also want to add a reminder for what they need to do this time, on the clocking in and out page.

Line 23: So, first I get the number of items there are in the form. Items are questions and titles in the form.To do this, we use the getItems() method.

Line 24: We get the item we want by getting the length of the items variable, i.e. finding out how many items there are in total in the form. Then we subtract 3, as the title we want to change is 3 from the last one, as we can see below in the form. This gives us the position in the items variable we want to change. Then we use the setTitle() method to change the title to the text in the brackets.



Lines 27-32: We repeat the same to check if they have clocked out. Obviously, we need to change the wording in the text to correspond with this. Then we close the function in line 32.

As with the spreadsheet code, we need to add an onFormSubmit trigger, so that it works automatically when a form is submitted. Click on the clock symbol as before.


The trigger should have these settings:


Click “Save” and authorise it as before.

Let’s just see what the employee sees on the form. At the top of the first page, they will see the message telling them the last time they clocked in or out and a little reminder as to what to do this time.


After entering their code, they go to the second page. Under their name, they see a reminder message telling them what to do. This is the title we set up above.


Let’s assume they clocked out. Then the form updates itself and the next time they open it, the text in the description has been updated.


And the reminder message in the title too.


This system is likely to be used with multiple users, so you will need to make copies of the form and link them to the same spreadsheet.

Here’s the link to make a copy of the Sheet.  You’ll then have a copy of the Sheet and the linked Form. You will find the code already in the sheet and form. To run it automatically, you will need to authorise both triggers.

Further reading

length property – More information and examples on how to use this.

Apps Script: Form reference guide – Google’s reference on using Apps Script for Forms

Apps Script: Script services – Google’s reference on the script services in Apps Script, e.g. Utilities.formatDate





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

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)

Multiple FOLDER maker

Making multiple copies of folders in Google Drive is a slow, boring task. You select the folder you want, make a copy, then have to rename it, then repeat this again and again. Here you’ll learn how to create multiple folders and name them individually, really fast using Apps Script.

If you’ve already read my post “Multiple FILE maker”, then this code is essentially the same, just with two main differences, we use the methods getFolderbyId() and we use createFolder(). So, if you read it, you may want to skip through and just note the differences.

First, we have a Sheet where we will enter the details. There are three parts.


In cell A3 we type the fixed part of the folder name want, i.e. The part that is the same in all the newly-created folders.


In column B, we type the variable parts of the folder name, i.e. The parts we want to be unique in each folder.


Finally, in cell C3 we paste the full URL of the folder we want to put the new folders into.


We then choose ‘Make Multiple Folders’ from the Creator menu.


Here are the folders it made, all individually named:


Obviously, it works best when there are a lot of folders to make.


Let’s go through the code step-by-step:


Line 1: First, we set up our function, here I’ve called it makeFolders().

Line 4: Then, we get the current active spreadsheet, using getActiveSpreadsheet().

Line 5: Then, we get the active sheet, using getActiveSheet().

Line 6: Now, let’s get the fixed name. It’s in row 3, column 1 (I.e. A3), so in the getRange() method we add (3,1), then get its value using getValue().

Line 7: Now, we need the variable names. First, we need to get all the cells in the second column (B), from row 3 to the bottom row. We need to find out where the bottom row is, so here I’ve used getMaxRows() which gets the last row on the sheet, whether it has any values or not. I subtract 2, as we will be starting from row 3, so need to discount the first 2 rows.

Line 8: We use getRange() again, but this time we’re getting a series of values, so we need to tell it where to start and where to end. In the brackets we state: starting row, starting column, number of rows, number of columns. So, here we’re starting in row 3, column 2, down to the bottom row using the bottomRow variable, and we only want 1 column. Then we get all the values with getValues(), which will store them in the variableNames array variable.

Lines 10 to 22, are ways to check the user has entered information into the relevant cells. The program could work without them, but often it’s important to include some kind of checks to deal with users doing things that they shouldn’t, as invariably, they will always do something, that’s not expected or what in your mind wasn’t logical! Here I’m going to use some simple if statements to check that they’ve filled in the sheet correctly before running the program.


Line 11: First, I want to check that they’ve entered something into the fixed name cell. So, I’ve used an if statement to check if the variable fixedName is blank, using the double speech marks.

Line 12: If it is blank, I want it to show an alert message, telling the user it’s blank. To do so, we use getUI() and the type of message is an alert. Then in the brackets we add the text we want to display. Line 13 closes the if statement.


Line 16: Here I get the URL from cell C3 (row 3, column 3) and store it in the variable folderUrl.

Line 17: Then, I check if the variable folderUrl is empty.

Line 18: if it is empty, as before, I want to display an alert.

Now, I want to check that both fixedName AND fileUrl are NOT empty. If they aren’t empty it will run the rest of the code and make the copies, if one is empty, it won’t run the code. This means that if one of the alerts is shown above, it won’t run any code afterwards, making sure no copies are made by mistake.


Line 22: Here we first check if the fixedName isn’t equal to a blank. Then, use the double ampersands, which mean AND, to also check if the folderUrl isn’t blank. If they both aren’t empty it runs the following code.


Line 25: Now, we need the folder ID from the folder URL, in order to create folders in that folder. We use the match() method (more info here) and use some regex to extract just the ID part. Don’t worry about how this works exactly, but if you’re interested in the use of regular expressions, go to this page.

Line 27: Now, we use that ID stored in folderId, to get the folder by its ID, using the DriveApp class and the getFolderById() method, which basically, gets the folder we want to create folders in, then stores it in the variable getRootFolderId.

Now, we finally get to the point where we make the multiple folders. We’ll use a simple for loop for this. We want it to loop down column B making copies as it goes down, but then stop when it reaches an empty cell in that column, i.e.  The end of the list.


Line 31: I’ve set up a counter variable called n, and we’ll keep going down until we hit the bottom row.

Line 32: But to prevent it making files with no variable names, I’ve included an if statement to check to see if the current variable name is blank, using variableNames and the current array number in the square bracket. Remember this is looping down the array variable variableNames, so it’s going from zero to potentially the bottom row. If it does find a blank it breaks out of the loop, i.e. It doesn’t continue going through the array. Also, note the use of a double equals sign, not a triple one, as we are comparing an array value with something empty and they are not the same type, so a triple equals sign will fail.

Line 34: This is the line that creates the folders we want. We use the folder ID stored in the variable getRootFolderId we created in line 27. Then, use the createFolder() method and in the brackets state the name we want. Here, we’ll going to join the fixed name with the variable name). Line 35 closes the loop.


Line 36: Finally, just to tell the user the process has finished, I’ve added a toast message, which will pop up in the bottom right-hand corner, when the program has finished. Here it frees to the active Spreadsheet ss, then in the brackets contains 3 parts: message, title, number of seconds it will be displayed.

Line 37 and 38: This closes the if statement from line 22, and the function.

Adding a menu

To run the program from a menu in the spreadsheet, I’ve also added a separate script file called onOpen, which will add a little menu to run the code.

Line 1: Call the function onOpen(), so that it opens automatically every time the spreadsheet is opened.

Line 2: Get the active spreadsheet, note I’ve left a comma at the end not a semi-colon, as the next 2 lines are connected to it.

Lines 3-4: Set up a variable called menuItems to add the menu details. The keyword var isn’t needed here as it using the one from line 2. Then we set up an array with the sub-menu name and the function it will run. Carefully note the syntax used here.

Line 5: Now, we add the menu to the spreadsheet using the addMenu() method to the active spreadsheet. In the brackets, we include 2 parts: menu name, and the variable menuItems.


Here’s the link to the Sheet and code.

Here’s the complete code:


The regular expression in line 25 is from here: 

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

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)