Apps Script – Issues reporting form, log & email

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

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

Issue reporting - 32

Setting up the form and sheet

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

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

Issue reporting - 3

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

Issue reporting - 4

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

Issue reporting - 5

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

Issue reporting - 11

The code

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

Issue reporting - 6

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

Issue reporting - 7

Delete the default code that’s in there.

Issue reporting - 18

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

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

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

Issue reporting - 19

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

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

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

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

Issue reporting - 20

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

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

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

Issue reporting - 21b

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

Issue reporting - 22

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

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

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

Here’s what it will look like:

Issue reporting - 10

Issue reporting - 23

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

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

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

Issue reporting - 24

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

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

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

Issue reporting - 25

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

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

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

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

Issue reporting - 31

Issue reporting - 26

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

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

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

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

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

Issue reporting - 27

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

Issue reporting - 11

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

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

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

Issue reporting - 28

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

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

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

Issue reporting - 29

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

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

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

Line 65: Close the function with a curly bracket.

Setting up the onFormSubmit trigger

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

Issue reporting - 12

Click on the clock symbol in the toolbar.

Issue reporting - 13

Click “No triggers setup”.

Issue reporting - 14

Here, you’ll need to change the Events.

Issue reporting - 15

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

Issue reporting - 16

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

Issue reporting - 17

Then press “Save”.

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

Issue reporting - 8

Click “Review permissions”.

Issue reporting - 9

Then click “Allow”.

Reporting an issue

The teacher fills in the form and submits it.

Issue reporting - 10

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

Issue reporting - 31

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

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

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

Here’s the form:

Here’s the full code:

Issue reporting - 33Issue reporting - 34

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

Baz Roberts (Google+Flipboard / Twitter)

Forms & Sheets – Attendance System

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

We will make an attendance sheet like this:

Attendance - 75

And an attendance summary like this:

Attendance - 76

Setting up the form

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

Attendance - 1

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

Attendance - 2

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

Attendance - 3

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

Attendance - 4

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

Attendance - 5

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

Attendance - 6

Create the following sheets:

Attendance - 24

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

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

Attendance - 7

Populating the class list on the form

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

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

Attendance - 56

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

Attendance - 55

Click on the jigsaw piece icon.

Attendance - 8

Then select formRanger.

Attendance - 9

Click Start.

Attendance - 10

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

Attendance - 11

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

Attendance - 12

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

Attendance - 13

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

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

Attendance - 14

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

Attendance - 15

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

Attendance - 16

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

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

Attendance - 17

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

Attendance - 18

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

Attendance - 19

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

Attendance - 20

Then, click the blue button.

Attendance - 21

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

Attendance - 22

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

Attendance - 23

Setting up the spreadsheet

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

Attendance - 24

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

Attendance - 26

So, you should now have 3 columns like this:

Attendance - 25

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

Setting up the attendance sheet

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

Attendance - 29

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

Attendance - 27

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

Attendance - 28

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

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

In cell B3, type the TRANSPOSE function below:

Attendance - 30

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

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

Attendance - 31

So in cell A4 we write the following COUNTA formula:

Attendance - 57

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

Attendance - 58

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

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

Attendance - 59

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

Attendance - 60

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

Attendance - 61

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

Attendance - 62

Finally, we sort the results in ascending date order.

Attendance - 63

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

Attendance - 39

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

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

Attendance - 64

In cell B5, type the following formula:

attendance new1

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

attendance new2

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

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

attendance new3

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

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

Attendance - 40

In cell B4, type the following formula:

Attendance - 41

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

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

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

Attendance - 29

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

Attendance - 71

Calculating and showing the students’ attendance

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

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

Attendance - 65

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

Attendance - 47

In cell A4, add this formula:

Attendance - 67

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

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

Attendance - 49

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

Attendance - 50

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

Attendance - 69

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

Attendance - 52

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

Attendance - 68

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

Attendance - 54

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

Attendance - 66

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

Putting it into practice

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

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

Attendance - 70

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

Attendance - 72

The responses arrive in the “G1” sheet.

Attendance - 73

These are copied in the “G1c” sheet.

Attendance - 74

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

Attendance - 75

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

Attendance - 76

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

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

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

Baz Roberts (Google+Flipboard / Twitter)

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)

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+)

Automatically emailing info from a form submission

Here we’ll look at how to set up automatic emails, which contain information submitted by the Google Form user. The beauty of this is that the information is sent to you (and others) without you having to do anything and without you having to check the spreadsheet to see if there has been a submission. This is building on the code from my previous post, so the areas which are the same I will only briefly describe here. If you want more details see my previous post.

This is the email that we’re going to send automatically:


Here’s the full code:


Let’s look at it step by step. First, we set up the function and then get the active spreadsheet.


Then we get the sheet which contains the information we want, which in this case is on ‘Form Responses 1’.


We want to get the values on the last row, which is the latest form submission. There are 3 steps to do this. We need to get the last row on the sheet, the last column and then get the range using that information.

Line 10: We get the last row of the form responses 1 sheet (using the getLastRow method) and store it in the variable called lastRow.

Line 11: Similar to the lastRow we get the last column (using the getLastColumn method) and store it in the variable lastColumn.

Line 12: Now we want to get the values in the last row, from the first column to the last column. We do that by getting the range (using the getRange method). This takes 4 pieces of information; starting row, starting column, number of rows, number of columns). So we start on the lastRow, we start at the first column, we only want 1 row, and we want to collect data until the lastColumn. Then we add getValues to get the values in that range.


Now we have an array variable called lastRowValues which contains all the data from that last row. Now let’s assign a variable for each piece of information on that row, so that we can use the variables later in the code and so we can clearly see what’s in those variables.

Lines 15-21: Here we’re extracting a specific bit of information from the lastRowValues array, and so need to state the position of the information. The array contains this at the moment:

[29/01/2017 15:43:01], [Ian Student], [], [123456789], [Mon/Wed 17:00-18:00], [06/02/2017], [31/03/2017], [To prepare for the CAE exam.]

There’s only one line in our array, so all the values we look up will start with position [0] . Then, for example, the name “Ian Student” is at position [1] in the array (remembering that the first item is [0] in an array). So, we write lastRowValues[0][1]; to get the name.

We then repeat the same format for each piece of information we want, just changing the position in the array each time.


In lines 19 and 20, we’re getting the dates from the form submission and we could use them directly from the sheet. The problem is that they won’t be in the format they are on the sheet. They will look like this:


Not the easiest date to read and normally we don’t want the full date, so let’s change the format of the date to the way we want it. For this email, I want the format to be DD-MM-YYYY, e.g. 06-02-17.

Line 24: First I check the user has in fact inputted a date. So, I write an IF statement to check if the variable startDate isn’t empty. The If statement syntax is:

if (variable + logic) {do something if it’s true}

!== means not equal; so I’m checking to see that the variable startDate isn’t equal to empty “”. If there is a date, lines 25 & 26 are run, i.e. the parts inside the curly brackets.

Line 25: First, let’s get the month from the date and add 1. This is because January is 0, so we need to add 1 to return a real month number. We use the getMonth() method to extract the month. Then we store it in the month variable.

Line 26: Now we need to create the date format we want. There are 3 main parts, date (day number), month, and year. To get the date, we use the getDate() method, then we add a hyphen, then we add the month we extracted in line 25. Then we add another hyphen, then we get the year with the getFullYear() method. This is then stored in the startDateEdit variable. So, it will create a date like this: 06-02-2017.

Line 28: What happens if the IF statement returns false, well I want it to put a few dashes instead of a date. Here we use the ELSE statement, which has the same syntax as the IF statement and will run if the above IF statement is false. So, here I just set the variable startDateEdit as three dashes “—“.


The finish date follows the same pattern as above.


I also want to include the spreadsheet URL, so I get that by using the getUrl() method and store it is the variable urlOfSS.


Now let’s create our email with the information we’ve extracted above.

Line 44: We set up the variable message, to store the information and in the first line type a short introduction. Note, at the end of every line from line 44 to 54, we have to add a “+” to show every line is connected together, and everything is within the message variable. Also, in the email, I want to list the details, one line after another, so we use “\n ” to add a line break.

Line 46: To add the name, next to the line break we type the text “Name: ” and close it with the inverted commas. Then we need to add the variable name, putting pluses (+) either side.

Lines 47 to 54 are written in a similar way. The final line of the email, line 55, ends with a semi-colon and doesn’t have a plus.


Now, we get the email addresses from our list on the sheet called “Emails”.

Line 58: Use the getSheetByName() method to get the sheet. Then we store it in the emails variable.

Line 59: Then we get the last row of the emails using the getLastRow() method and store it in the variable numRows.

Line 60: We get the values in the range using the methods getRange and getValues.


Here it’s getting the range from row 2, column 2, to the bottom of the list of emails, and only this 1 column.


Then we set the subject line of the email. Here I’ve added a fixed part in the title “Private Class Request – ” then added the variable name, so that the person’s name of that particular request will appear in the subject line. This is stored in the variable subject.


Then we send the email using the Gmail class and sendEmail() method. Then add the 3 variables, emailTo (the email addresses), subject (the subject line), and message (our email message).


Remember to activate the onFormSubmit trigger, as explained in my last post.

As this has touched on JavaScript areas such as, arrays, if, and else statements, if you’re not confident using these, I would suggest you read the following on the excellent W3schools website:

arrays / if else

Here’s the link that let’s you make a copy of the spreadsheet I’ve used, which will copy the spreadsheet, the linked form, and which contains the above code.

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

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)

Request form – Sending automatic emails

One of the most useful things I’ve learnt to do with Google Apps Script, is to email people automatically when a form is submitted. It has countless uses and in this example, we have a user requesting a private class via a Google Form. The relevant parties will receive an email which will contain a short message and a link to the sheet containing the details.

There are three parts to this:

  1. Setting up the Form (inc Email collecting and Data validation)
  2. Setting up the Sheet
  3. Writing the code

Part 1 – Setting up the Form to record details of class request

Open the Sheet and select Tools>Create a form, so that it is automatically linked to this spreadsheet.


To set up a question to collect the user’s email. Click on “Settings”.


Click “Collect email address. If you want the user to receive a copy of their request, then click “Response receipts”. You then have a choose of always sending them a copy or letting them choose this option in the Form. Here I always want them to receive it. Click “Save”.


Back in the Questions part, you can see it’s automatically created a question which will check for a valid email.


Then I add a telephone question with a short answer. Here I want it to check the user has only entered numbers. Click on the 3 dots on the bottom-right of the question, and then click “Data validation”.


I then change the data validation option to “Number”.


Then click on “Greater than” and change it to “Is number”.



Then I add start and finish date questions. With the new AI, when I type “Start date, it automatically changes the question type to “Date”.



Finally, I add a Details questions, which automatically changes it to a Paragraph style question.


Part 2 – Setting up the spreadsheet

Open the spreadsheet and on the ‘Form Responses 1’ tab you’ll see the questions in row 1. I like the fact that the sheets with a form linked to it, now have a GForm symbol on it.


Add a second tab and rename it ‘Emails’.


In that sheet, type in the email addresses you want the request to go to. In column A I’ve added their names and in column B their emails.


Part 3 – Writing the code

In the Sheet, click on Tools>Script Editor.


Click on “Untitled project” and give it a new name.


Then click “OK”.


We want the program to run when there’s a form submission, to get the spreadsheet URL and then email a group of people a short message telling us there’s a new request, including the URL, so we can easily click on it to open the sheet to see the request details. Here’s the code we’re going to use:


Let’s look at the code section by section. Note that you don’t need to add the comments (the parts after //) but it can help you follow the code and remember what each bit does.

First we set up a function called onFormSubmit() and open the function with the curly brackets.


Then we want to get the active spreadsheet. Here we set up a variable called ss, which we will refer to throughout the program. Then we use the SpreadsheetApp class with the getActiveSpreadsheet method.


Now we want the spreadsheet URL. Here we set up a variable urlOfSS, which is where we will store the URL. Then we use the ss variable we just created and use the getUrl() method to get the URL.


Now we need a message in our email.

Here let’s create a variable message to store the message. We put the text we want within inverted commas, as it is a piece of text. I also want to add the URL, so I add a + at the end to show it’s connected to the text.

Then I want to put the URL on a new line, so I add “\n “ which in HTML is a line break. Then I add another + and add the variable urlOfSS.


Now we want to get the email address of those we’re going to send the email to. Let’s look at this line by line.

Line 17: Using the getSheetByName method we get the Emails sheet. Put the name of the sheet in the brackets between inverted commas. We’ll store this in the variable emails.

Line 18: We want to get the last row on the sheet that has data in it. Assuming the sheet is set up as describing in part 2 above, then this will tell us that the last row is row 3. This uses the Sheet class followed by the getLastRow() method. We store this in the variable numRows. We’ll use this info to get the correct range in the next line.

Line 19: Now we need to get the email addresses in sheet. We use the Sheet class again and this time get a specific range by using the getRange method. This has 4 parts: starting row, starting column, number of rows, number of columns, So, in our example, we’re:

  • starting in row 2
  • starting column 2
  • getting the number of rows from the variable numRows (which in this case is 3)
  • there is only 1 column

So, in the brackets we write (2, 2, numRows, 1)

Important: This will get the ranges but not the values within those ranges. So, we need to add another method, getValues() which will stored the actual email addresses in the variable emailTo.


Now we need a subject line for our email. We store the text we want in the variable subject.


Finally, we of course want to send the email. Here we use the GmailApp class followed by the sendEmail method. In the brackets we add the three pieces of information we collected earlier:

  • emailTo (Email addresses we’re sending to)
  • subject (Email subject line)
  • message (Email message)

To close the function we always add a curly bracket at the end.


Note that all the lines except lines 2 and 26 have a semi-colon at the end.

This program uses the onFormSubmit trigger, which allows the program to run automatically when a Form is submitted. This needs setting up, otherwise it won’t run automatically, but it’s simple to do.

In the Script Editor, click on the clock icon.


This will open the Current project’s triggers menu. A new program won’t have any set up. Click on the blue “No triggers set up. Click here to add one now.”.


Here you need to tell it what to run and how to run it. All of these are simple drop down menus. By default the onFormSubmit program will be selected. Now let’s add the event details.


Click on “Time-driven” and change it to “From spreadsheet”. This will change the other menus.


Click on “On open” and change this to On form submit.


You should be left with this. This runs the function “onFormSubmit” when a form is submitted to the spreadsheet. Click “Save”.


Forgetting to set up the trigger is a common mistake to make.

Here’s the email that is sent out.


Here’s a link to make a copy of the above spreadsheet. The linked form will be copied automatically and the code will already be in the Script Editor.

The email in this post is quite basic and performs a similar function to the automatic email you can receive when setting up Notification rules, found in the Tools menu. However, this does allow you to email a group of people, plus it’s a springboard for my next post, which will focus on how you can extract data from the latest form submission and include it in the email.

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

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)

Google Forms – Quizzes

In this post we’re going to look at the quiz option in Forms. Since its release earlier this year, I’ve been using it with various classes mainly to check their progress, and it really is a simple but highly effective tool, which I recommend everyone to use.

To start let’s just look at the 4 main areas you will need to use in order to set up your quizzes.

forms8-33 forms8-36

Settings>Quizzes – This is where you tell Forms that your form is a quiz and this then opens the quiz options. You can determine when the respondent sees their mark and whether they can see their answers corrected, the correct answers and the points allocated per question.


Settings>General – Here you can decide if the respondent will automatically receive a copy of what they’ve filled out or whether you give the option to do so in the form.


Questions – The bulk of the work is here, where you tell Forms which answers are correct and set the points per question. You can also set up automatic feedback for correct &/or incorrect answers, including links to websites or documents.


Responses – This is where the analysis happens after your respondents have filled out the quiz. You can see summary information for all the responses, including averages, range, problematic questions, and a summary per question so you can see which areas are causing the most problems. It also allows you to look at individual responses. Here a little summary of the main areas:


Setting up a quiz

Click on the Settings cog at the top of the screen.


There are 3 areas, General, Presentation, Quizzes. Click on “Quizzes”.


Then click the toggle switch “Make this a quiz”.


This will then open the options below. Under “Release mark” you have the choice of either allowing the respondent to see their mark as soon as they submit the form or you may decide to send it to them at a later date.

The latter is useful if you have questions which require your review first, for example, they are written answers, or you may decide to let everyone know their scores at the same time once everyone has filled in the form. To do this, the email collection is turned on, so that the score can be emailed to them.

The second part is controlling what the respondent can see when they see their marks. You can show them the answers they got wrong (“missed questions”), the answer key (“Correct answers”), and how many have been awarded per question.


Whilst in Settings, click on the “General” option. If you chose to release the mark later in the Quizzes menu, “Collect email address” will automatically be selected. However, even if you choose to release the mark immediately, you can manually select this open.


Underneath you have the “Response receipts” option. This allows the respondent to receive a copy of the form filled out with their answers in their email. Clicking in this opens 2 further options, the first “If respondent requests it” adds a question at the end of the form, to give the respondent the option of receiving the filled out form, and the second does it automatically, so doesn’t add a question.


Click “Save” once done.

If the “If respondent requests it” option is selected, the respondent will see this option at the bottom of the form:


They just click on the toggle switch if they want a copy.

Setting up the questions in the quiz

Now it’s time to tell Forms which questions are correct, etc. Click on the “Questions” tab. Here we can see that the email address collection has been added to the top of the form. You can’t move this.


I add a question as normal (see my post on adding questions). As you can see, at the bottom of the question, you have “Answer key”. Click on that to enter edit the answer key.


It’ll prompt you to choose a correct answer or answers. Just click on the answer that is correct.


You can also set the points value for the question. Annoyingly, this is always defaulted at 0. (Personally, most of the quizzes I’ve ever made are 1 point questions, so I think it would be better if the default was 1 point, or in the settings a global default setting could be set.)


You’ll then have your correct answer highlighted and the points value. If you select the wrong answer, click it again to remove it.


Adding feedback to your answers

You have the option to add automatic feedback to your questions. Click on “Add Answer Feedback”. You have 2 options, you can leave feedback for incorrect answers and/or correct answers.


To add feedback just type in the box, where it says “Enter feedback”.


You do the same for the correct answers, just by clicking on the “Correct answers” tab.


Not only can you leave text feedback but you can also add links. This is particularly nice, if you want to direct the student to some further reading related to the question or to some extra help.

Click on the link icon. There are two parts: “Link to” is where you paste in your link, and “Text to display” is where you add the name of the link, i.e. what the respondent sees to click on.


Here I’ve pasted in a link to a Google Doc with an explanation of this particular grammar point. The link can be to anything, YouTube video, a website, images, etc. Click “Add” then “Save”.


It shows you that this question has a link added to it.


Reviewing the summary of responses

Once you receive some responses, you will of course want to review and analyse them. Forms provides two main ways, either looking at summary of all the responses, or looking at the individual responses. Let’s look at the “summary” first of all.

Click on the “Responses” tab. Then if not already selected, click on “Summary”.


Under Insights, you’ll see the average (mean), median and range of the responses. Then underneath, a graph showing you the spread of the results. So, very quickly I can see that the majority of those who did my test, didn’t do particularly well, as I was hoping that most would get 4 or 5.


Under that Forms automatically highlights the most problematic questions, i.e. which ones respondents got wrong the most. Here I can see the last two questions caused the most problems, so immediately I can see where I need to focus my attention on in a future class.


Then you have the list of respondents, their total scores and when their scores were released. This is ordered by the release date, which I don’t find that useful, I would prefer it to sort by score. However, this is a nice, quick summary which will help you identify who needs help.

From this table, you can click on a line to go directly to that individual’s responses, again to see where in particular they need help.

Sending emails to respondents with their responses and the answer key

If the email setting was selected earlier, you can also send the students or particular students, a copy of their responses along with the correct answers and feedback, by clicking on “Release Scores”.


This opens up the Release Scores dialogue box. In here you can add a message to the respondents and then click on who you want to send the emails to. By default, all of the respondents are selected. Then click “Send Emails and Release”.


You can do this whether you selected early that they can see their result immediately or later.

Finally, you can also look at the summary of each question, to see not only which ones are causing problems but also what alternative answers the respondents are choosing. For example, in question 4, a lot of my students have chosen “a lot” instead of “a lot of”, so I can see that I need to remind of when we use one and not the other.



Reviewing individual responses

To see each individual’s response, click on “individual” near the top. It shows you each response in chronological order. It shows their email address, the response number, gives you the possibility of printing it, and the possibility of deleting it. To navigate through them, either click on the arrows or double-click on the number in the box and type in the response you want.

Below that it shows you the score for that respondent, if the score has already been released to them or not, and the option to release the score (similar to above).


Underneath it shows the questions, with whether they got them right or wrong, plus it shows the correct answers, any feedback that you set up, and any links that you added earlier. This is exactly how the respondents see it, when the score is released.




You also have the option of adding individual feedback, by clicking on “Add individual feedback” under any of the questions. This can be useful if you haven’t set up any automatic feedback or if you have questions that can’t be automatically corrected, for example, a piece of written text. Type in your feedback and if you want, you can add a link like we saw above. Then press “Save”.


How can respondents see their score?

If the “release mark immediately” option was selected earlier, when the respondent submits the forms, on the confirmation page, they will have the option “View your score”.


Clicking on that will take them to the form filled in with their answers, and depending on the options you chose, will have the answer key and feedback.

The other way I mentioned above is that they receive an email when the scores are released. This is the email they receive:


It contains their total and then when they click on “View”, it takes them to the form filled out with their answers and depending on the options you chose when setting up the quiz, the answer key, feedback and any links you added. this means that they have a permanent copy to review, which is better than only seeing it when they submit the form.


I’ve been using these quizzes since they were introduced a few months ago, and once set up they are extremely useful in quickly identifying where my students need extra help. In most cases I’ve found that as all the analysis is done within Forms I don’t need to set up a Sheet to analyse the data, which saves me a lot of work. However, with longer quizzes the initial set up can take a bit of time, especially if you’re adding feedback and links, but if you reuse this quizzes or share them with colleagues then they are definitely worthwhile.

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

Baz Roberts (Flipboard / Twitter / Google+)

Creating multiple short URLs in seconds

Wouldn’t it be better if instead of sharing a long link like the one below, you could get a shorten version of it? Well, you can and Google provides a URL shorten service, which generally reduces any URL to only about 13 characters.

For example, here’s a link that was created by Google Forms when I pre-filled some info in (see my post on personalizing Google Forms):

As you can see, it’s very, loooooong!

One way to shorten this is to go to Google URL shortener site at

Here paste in your long URL in the box and click the blue “Shorten URL”.

Short URL - 1

On the right-hand side, your short URL will appear, highlighted ready to copy. As you can see it’s much shorter than the one above.

Short URL - 2

Below it you will see what your page looks like, in this example it’s a form with the fields pre-filled out.

To share your new short URL, just press Ctrl+C (Cmd+C) to copy it and paste it where you want it.

This is fine if you don’t have many URLs to make, but what about if you have 10, 20, 100 to make? A long, slow, tedious process. But you’ve guessed it, there’s a better, quicker way!

Using the spreadsheet I created in my post about creating personalized Google Forms, I’m going to show you how a little bit of Google Apps Script, can quickly create these URLs.

What’s Google Apps Script? I hear you ask. This is a programming language which sits in the background of your Google Apps (Drive, Docs, Sheets, etc) and with which you can tell it to do some wonderful things. If you’re new to Google Apps Script, don’t worry to set this up, you don’t need to know how it all works, just follow the steps.

The main steps are:

  1. Open the Google Sheet you want to put the shortened URLs in.
  2. Open the Script Editor and paste in the code.
  3. Enable the Google URL shortener service. (only necessary the first time)
  4. Select the URLs you want to shorten.
  5. Authorize and run the code.

1) Open the Google Sheet you want. In this example, I have the 4 classes I wanted to create personalized Google Forms for. The long personalized URLs are in column E.

Short URL - 3 (1)

2) I want to put the Short URLs in the column to the left of the long ones. So, I right-click on the column where it says “E” and the menu will appear. Click “Insert 1 left”.

Short URL - 4

3) A new column is added in column E. Now I just give it a name, e.g. “short URL”. It can be any name.

Short URL - 3b

4) Now to add our script. Go to the “Tools” menu and click “Script editor…”.

Short URL - 5

5) This will open the Script Editor in a new window.

Short URL - 6

6) First, give the script a name. Click on “Untitled project” and type in a name, e.g. “Create short URL”.

Short URL - 7

7) In the main window, a new script always starts with the function below. We don’t need any of that so, highlight it all.

Short URL - 8

8) Copy all the code in the block below and paste it into the Script Editor.

//When the spreadsheet is opened it adds a menu called "Shorten URL" and a sub menu item called "Shorten"
//This relates to the function below called "short"
function onOpen() {
.createMenu("Shorten URL")

//Takes highlighted range and goes down row by row, adding a short URL to the column to the left
function short() {
var range = SpreadsheetApp.getActiveRange(), data = range.getValues();
var output = [];
for(var i = 0, iLen = data.length; i < iLen; i++) {
var url = UrlShortener.Url.insert({longUrl: data[i][0]});
//If you want to put the short URL in a different column, adjust the "-1" in line 20
//Negative number moves to a column to the left; positive number moves to a column to the right.

Here’s a link to the code:

Your Script Editor should now look like this:

Short URL - 10

9) Now click save (the disk icon) to save the script.

Short URL - 11

10) Now we need to enable the Google URL service. This only needs needs to be done once. From the “Resources” menu, click “Advanced Google Services”.

Short URL - 12

11) This opens the Advanced Google Services box. Scroll down until you find “URL Shortener API”. Turn it on, by clicking on “off” and then click OK at the bottom of the box.

Short URL - 13

Short URL - 14

12) Back in the Script Editor, from the “Resources” menu again, click “Developers Console Project”.

Short URL - 15

13) This will open a new window showing the “Developers console Project”. Click on the blue link “Create short URL”.

Short URL - 16

14) Under the “Use Google APIs” click “Enable and manage APIs”.

Short URL - 17

15) This brings you to the Google APIs page. Click on “URL Shortener API”.

Short URL - 18

16) This opens the URL Shortener URL overview. Click “Enable” to well, enable it. It will change from “Enable” to “Disable”.

Short URL - 19

Short URL - 20

Note: steps 10-16 only need to be done once. Just close the window after using the Google Developers Console.

17) Now it’s time to use your script. Go back to your Google Sheet and click the browser refresh button. This will load your script in the background, as this script is automatically loaded every time you open the Sheet.

Short URL - 22

18) Select the cells your long URLs are in.

Short URL - 23

19) When you refreshed the Sheet (in step 17), a special menu was also created called “Shorten URL”. Click on that and click on “Shorten” to run the script.

Short URL - 24

The first time you run the script you’ll be asked to authorize it, just click “Continue” then on the next step, click “Allow”.

Short URL - 25 Short URL - 26

20) In a few seconds, you shortened URLs will appear in column E.

Short URL - 27

There seems to be quite a lot of steps, but even when you run it the first time and have to enable to URL shortener, this whole process takes less than a minute. The next time it takes seconds!

About the script

The only part of the script you may need to change is the last line:


This tells the computer where to put the shortened URLs in relation to the URLs you’ve highlighted.

Generally, the only part that’s important is the second number, in this case “-1”. This is the column in which to put your URLs. If you want to put the short URL in a column to the left put a negative number; if you want to put the short URL to the right, put a positive number. The number is the number of columns away from the original URLs selected.

If you just want to use the above script for shortening URLs, where they are not linked to pre-filled Forms, I would recommend making a file just for this purpose, where you have one column for the long URL and on column for the shortened URL. Having a specific file for doing this, means that once you’ve enabled the URL shortener for the first time, you’ll only have to paste in your long URLs, run the Shortener script from the menu and voilà you have your short URLs.

This idea is adapted from one posted on If you’re using Google Apps Script, this is an amazing place to find out solutions, or to get your own problems resolved. Here’s the original post:

So, thank you to Alex.

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

Follow my Google+ Learning Google Apps Script Collection

Baz Roberts (Flipboard / Twitter / Google+)

Personalized Google Forms


Why would you want to pre-fill parts of a Google Form?

  • To ensure the information is in the format you want
  • The data format is consistent
  • Consistent data means it’s easier to analyze afterwards
  • It’s quicker for the person to fill out your form
  • It adds the personal touch, if you add their name on it
  • One form can collect data from many sources, and be sorted with ease later on in the same spreadsheet

Fortunately, there’s a way to do it, which requires a little bit of Google Sheets knowledge but if you follow the steps below, it’s pretty easy to do.


Forms PF - 1


1) Create a Google Sheet by going to New and clicking Google Sheets.

Forms Pre-Fill - 2

2) Add the information you want to add to your form. E.g. Columns A to D below.

In this example, I’m going to create an end of course questionnaire and want to send an personalized form to each class using the same form, but with 4 fields already filled out (Group, Level, Teacher, Classroom) so, later on the data will all arrive in the same place, but I can sort the data with ease into these areas. This allows me to look at the data not just for that particular class, but also more globally across all them.

Forms Pre-Fill - 25 (1)

3) Next to this, I need to add some columns, which will create the personalized link (e.g. columns E to I).

You can name however you want, but I’ve given them descriptive names so you can follow where the information is going. Don’t worry, what they do for now, I’ll explain later:

  • Substitute link with class details
  • Full pre-filled form link
  • Group %20
  • Level %20
  • Teacher %20

Forms PF - 2


4) Now we need the link to the form. Go back to the form and on the top-right click on the three dots next to the Send button. Click on “Get pre-filled link”.

Forms Pre-Fill - 6

5) This opens your form. Type in words into the fields you want to pre-fill. Normally, I give them the same names as what is on the Google Sheet, so it’s easier to remember which is which. Also, only type one word per field. At the bottom of your form, click “Submit”.

Forms Pre-Fill - 7 Forms1 - 4

6) A special link will appear at the top of the page. Click on it and press Ctrl+C (Cmd+C) to copy it.

Forms Pre-Fill - 8

7) Go back to your sheet and in column F, in F2, press Ctrl+V (Cmd+V) to paste it into the cell and press Enter.

Forms PF - 3


In the pre-filled form link (see below), we need to replace the words, GROUP, LEVEL, TEACHER, and CLASSROOM with the data that’s in the spreadsheet.

8) In column E (Substitute link with class details), type in the SUBSTITUTE formula below in cell E2.

Forms PF - 4

=substitute(substitute(substitute(SUBSTITUTE(F2, “GROUP”, G2), “LEVEL”, H2), “TEACHER”, I2), “CLASSROOM”, D2)

Look scary? It’s not as complicated as it seems. Let’s just look at a simpler example:


Here we are getting the contents of cell H3 (the last part of the form link), and substituting the word GROUP with the contents of cell J3 (“Mondays”).

Forms Pre-Fill - 16

This formula follows the same principal, but the only difference is, is that we are substituting 4 different things at the same time (Group, Level, Teacher, and Classroom).

=substitute(substitute(substitute(SUBSTITUTE(F2, “GROUP”, G2), “LEVEL”, H2), “TEACHER”, I2), “CLASSROOM”, D2)

So here, it gets the pre-filled link in F2, replaces the word GROUP with the contents of G2, replaces LEVEL with the contents of H2, replaces TEACHER with the contents of I2, and replaces CLASSROOM with the contents of D2.

DEALING WITH SPACES IN YOUR DATA (not always necessary to do)

If your data has spaces in it like mine here, that’s going to cause you problems with the links, as spaces cause links to break, but there’s a way to get round this.

Forms Pre-Fill - 25 (1)

9) In column G (Group %20), type in the SUBSTITUTE formula below:

Forms PF - 5   =SUBSTITUTE(A2,” “,“%20”)

Here you’re telling Sheets to look at cell A2 (where the Group info is), to find any spaces, and to replace any spaces found with %20 (which tells the computer to add a space without breaking the link).

10) Do the same for the level and teacher columns (changing the cell reference A2 to B2 for the level, and C2 for the Teacher).

Forms Pre-Fill - 20  =SUBSTITUTE(B2,” “,“%20”)

=SUBSTITUTE(C2,” “,“%20”)

Note: In this example, I didn’t need to do this step for the CLASSROOM, as there are no spaces in that data, e.g. A01. So, it takes the Classroom name straight from column D.

10) Hover over cell E2 and the direct link will appear. To try it out, just click the blue link.

Forms Pre-Fill - 22   Forms Pre-Fill - 23

Hooray! You can see that all the fields are pre-filled out from the information on the Sheet.


Now that’s done for one row, you can simply copy it down to the other rows, and it will pick up the information on each row automatically.

18) Select all the row from “full link” to the end “teacher %20”.

Forms PF - 6

19) Grab the little blue square on the end of the select row and drag it down to copy the first row to the other rows.

Forms PF - 7 Forms PF - 8

Clicking on the links in column E like before, shows the form with each row’s data added.

Forms Pre-Fill - 27     Forms Pre-Fill - 28

To summarize the columns created and why they are there.

Substitute link with class details

This substitutes the field names you add in the pre-filled form with the information from your sheet.

=substitute(substitute(substitute(SUBSTITUTE(F2, “GROUP”, G2), “LEVEL”, H2), “TEACHER”, I2), “CLASSROOM”, D2)

Full pre-filled form link

Where you paste the full pre-filled form link.

Group %20

This replaces any spaces in your text with “%20”, so that the URL doesn’t have any spaces in it.

=SUBSTITUTE(A2,” “,“%20”)

Level %20, Teacher %20

As per Group %20, just change the cell reference

Once you’ve set your Sheet up, if you reuse the form, you’ll only need to change the data on the Sheet to change the details that come out on the Forms.

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

Baz Roberts (Flipboard / Twitter / Google+)