Questionnaires – Automatically send feedback to individuals

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

There are 4 documents we’re going to create:

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

Here’s how they link together:

Questionnaire - 42

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


Setting up the Form

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

Questionnaire - 1

Questionnaire - 2

Questionnaire - 3

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

Questionnaire - 4

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

Questionnaire - 5

This populates the Sheet with our questions.

Questionnaire - 6


Setting up the master feedback sheet

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

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

Questionnaire - 7

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

Questionnaire - 9

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

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

Questionnaire - 8

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

Questionnaire - 37

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


Setting up the Individual feedback sheet

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

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

Questionnaire - 11

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

Questionnaire - 12

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

Questionnaire - 17

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

Questionnaire - 13

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

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

Questionnaire - 14

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

Questionnaire - 15

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

Questionnaire - 38


Setting up the Questionnaire links

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

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

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

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

Questionnaire - 19

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

Questionnaire - 20

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

Questionnaire - 21

Questionnaire - 22

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

Questionnaire - 39

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

Questionnaire - 23

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

Questionnaire - 25

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

Questionnaire - 26

Then paste it in cell F2.

Questionnaire - 24

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

Questionnaire - 27

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

Questionnaire - 40


URL shortener

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

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

Add this code in the script editor.

Questionnaire - 35 (1)

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

Questionnaire - 30

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

Questionnaire - 31

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

Questionnaire - 32

Then click Enable.

Questionnaire - 33

Click the arrow back and click OK.

Questionnaire - 34

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

Questionnaire - 29

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

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

Questionnaire - 36

Clicking on the link will open the questionnaire:

Questionnaire - 28

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

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

Questionnaire - 41

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

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

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

Individual questionnaire feedback spreadsheet

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

Questionnaire links spreadsheet (including the short URL code above)

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


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

Baz Roberts (Google+Flipboard / Twitter)


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.


SETTING UP THE FORM

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.

level-test-1

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

level-test-2

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.

level-test-3

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

level-test-4

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”.

level-test-5

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

level-test-6

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

level-test-7

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

level-test-8

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

level-test-9b


SETTING UP THE SHEET

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

level-test-11

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

level-test-12

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.

level-test-13

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.

level-test-14

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

level-test-15

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.

level-test-17

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

level-test-18

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.


HIGHLIGHTING WHICH ANSWERS WERE CORRECT OR INCORRECT

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

level-test-19

Right-click and select Conditional formatting.

level-test-20

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

level-test-21

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

level-test-23

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.

level-test-24

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

level-test-22

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.

level-test-25

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

level-test-26

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

level-test-28

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.

level-test-29

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.

level-test-27

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

level-test-30


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.

clockinginout-36b

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.

clockinginout-37

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.

clockinginout-38


SHEET & LINKED FORM

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

clockinginout-39

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

clockinginout-33

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

clockinginout-40

clockinginout-41

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.

ClockingInOut-43

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:

clockinginout-35

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

SHEET CODE

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

clockinginout-1

Here I’ve renamed it “Hours Register”.

clockinginout-2

clockinginout-19

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

clockinginout-20

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.

clockinginout-21

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.

clockinginout-22

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.

clockinginout-38

clockinginout-23b

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”.

clockinginout-24

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”.

clockinginout-25

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.

clockinginout-3

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

clockinginout-4

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

clockinginout-8

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

clockinginout-9

clockinginout-10


FORM CODE

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.

clockinginout-42

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

clockinginout-27b

Line 1: Call the function onFormSubmit ().

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

clockinginout-28b

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].

clockinginout-29c

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.

clockinginout-30b

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.

clockinginout-31b

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.

clockinginout-35

clockinginout-32b

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.

clockinginout-3

The trigger should have these settings:

clockinginout-11

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.

clockinginout-14

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.

clockinginout-15

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.

clockinginout-16

And the reminder message in the title too.

clockinginout-17


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


SHEET – FULL CODE

clockinginout-18b

FORM – FULL CODE

clockinginout-26c


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:

class-request-v2-16

Here’s the full code:

class-request-v2-1class-request-v2-2

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

class-request-v2-3

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

class-request-v2-4

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.

class-request-v2-5

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], [brgablogse@gmail.com], [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.

class-request-v2-6

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:

class-request-v2-15

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 “—“.

class-request-v2-7

The finish date follows the same pattern as above.

class-request-v2-8

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

class-request-v2-9

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.

class-request-v2-10

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.

class-request-v2-11

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

class-request-v2-14

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.

class-request-v2-12

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

class-request-v2-13

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.

class-request-33

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

class-request-2

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”.

class-request-1

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

class-request-4

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”.

class-request-3

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

class-request-5

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

class-request-6

class-request-7

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”.

class-request-8

class-request-9

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

class-request-10


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.

class-request-11

Add a second tab and rename it ‘Emails’.

class-request-13

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.

class-request-12


Part 3 – Writing the code

In the Sheet, click on Tools>Script Editor.

class-request-14

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

class-request-15

Then click “OK”.

class-request-17

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:

class-request-24

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.

class-request-25

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.

class-request-26

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.

class-request-28

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.

class-request-29

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.

class-request-30

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

class-request-31

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.

class-request-32

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.

class-request-18

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.”.

class-request-19

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.

class-request-20

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

class-request-21

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

class-request-22

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

class-request-23

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


Here’s the email that is sent out.

class-request-34-1


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.

https://docs.google.com/spreadsheets/d/1fVkLj-dI4ig9MJT7uihhgiwWD7MDtANANsDOKmOQSLU/copy

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.

forms8-37

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.

forms8-34

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.

forms8-35

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:

forms8-32


Setting up a quiz

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

forms8-1

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

forms8-2

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

forms8-3

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.

forms8-4

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.

forms8-7

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.

forms8-8

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:

forms8-31

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.

forms8-9

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.

forms8-10

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

forms8-11

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

forms8-12

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

forms8-13


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.

forms8-14

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

forms8-15

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

forms8-16

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.

forms8-17

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”.

forms8-18

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

forms8-19


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”.

forms8-20

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.

forms8-21

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.

forms8-22

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”.

forms8-23

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”.

forms8-42

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.

forms8-24

forms8-25


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

forms8-26

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.

forms8-27

forms8-28

forms8-29

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”.

forms8-43


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”.

forms8-30

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:

forms8-44-1

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.

forms8-45-1


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

https://docs.google.com/forms/d/1cCk-eDEV81i2X9uua_pI15SGI4N5ZJq0HE-MIHHswq8/viewform?entry.1422654197=Monday%2019:15&entry.148162827=First%20Certificate&entry.2134513978=Mark%20Walsh&entry.349432024=A01&entry.1681636254

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

One way to shorten this is to go to Google URL shortener site at https://goo.gl/

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() {
SpreadsheetApp.getUi()
.createMenu("Shorten URL")
.addItem("Shorten","short")
.addToUi()
}


//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]});
output.push([url.id]);
}
//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.
range.offset(0,-1).setValues(output);
}

Here’s a link to the code: https://goo.gl/KXzdFI

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:

range.offset(0,-1).setValues(output);

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 stackexchange.com. 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:

http://webapps.stackexchange.com/questions/76050/google-sheets-function-to-get-a-shortened-url-from-bit-ly-or-goo-gl-etc

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

PRE-FILLING GOOGLE FORMS OUT

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.


SUMMARY OF THE MAIN STEPS

Forms PF - 1


SETTING UP THE GOOGLE SHEET

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


GETTING THE PRE-FILLED FORM LINK

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


ADDING THE INDIVIDUAL DATA TO YOUR FORM LINK

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.

https://docs.google.com/forms/d/1cCk-eDEV81i2X9uua_pI15SGI4N5ZJq0HE-MIHHswq8/viewform?entry.1422654197=GROUP&entry.148162827=LEVEL&entry.2134513978=TEACHER&entry.349432024=CLASSROOM&entry.1681636254

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:

=SUBSTITUTE(H3, “GROUP”, J3)

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.


COPY THE CELLS FROM ROW 2 DOWN YOUR TABLE

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


Google Forms – Sending them

Unless your form is for personal use, you’ll want to send it to people. Here’s how:

Open your form and click the big “Send” button on the top-right of the screen, to open the “Send form” box.

Forms7 Sending - 1

Here you have various options to send. First let’s look at emailing a link.

Emailing a link to your form

By default, the email option is already selected, so we just need to tell it who we want to send the form to.

Forms7 Sending - 2

Type in the email address(es), add a subject, and add a message.

Tip: If you have groups set up in your Contacts, you can just type in the group name and all those in the group will be added.

Forms7 Sending - 3

Then press “Send”. The recipients will receive an email with a link in it like this:

Forms7 Sending - 4

In the Send form box, there is the option to include the form in the email, but at the time of writing, I’ve found that although the form is included in the email, when you start filling it out, it still takes you to the Google Form on-line anyway, especially if you have more than one page. Plus, videos aren’t embedded in the email, they are only links.

You also have the option of adding collaborators, who can edit your edit form.


Getting the link to your form

You can also get the link to the form, which is useful if you are sharing this but don’t know the email addresses are the form-fillers. E.g. I share a questionnaire with our teachers and they share it with our students.

Forms7 Sending - 7

One nice feature they’ve added here is the option to shorten the URL right within Forms. Before you would have had to go to Google’s shorten URL page (https://goo.gl/) to shorten it.

Just click on the checkbox next to “Shorten URL” and the long URL above is converted into a shorter one.

Note, as they add the word ‘/forms/’ in the URL, this doesn’t actually create a URL  as short as going to the goo.gl site, but it’s short enough for most people and is more convenient this way.

Forms7 Sending - 8

Then click “Copy” and paste the link wherever you need it.


Embedding your form

The third option with ‘the greater than / less than’ icon, is to embed the form normally into a website. Just click on “copy” and then press Ctrl+C (Cmd+C) to copy the link. Then paste it into your site, blog, etc.

Forms7 Sending - 9

Here’s the random form I’ve put together over this series of posts, where I’ve embedded it using the option above.


Finally, you also have the option of sharing your form on Google+, Facebook, and Twitter, which are easy to do but is beyond the scope of this post.


eBooks now available on Drive, Forms, Sheets, Docs, and Slides:

Baz Roberts (Flipboard / Twitter / Google+)


Google Forms – Changing the background

We can make Google Forms a little more aesthetically pleasing by changing the background and so that it can look a little more fun or professional.

Changing the background colour of the form

By default, your form will be purple. You can very quickly change the colour by going to the colour palette menu.

Forms6 - 21

Select a colour.

Forms6 - 22

The background will change colour.

Forms6 - 23


Adding a theme

Go to the colour palette.

Forms6 - 21

Click on the image icon on the bottom-right.

Forms6 - 22

Here you’ll be presented with a whole range of themes, they’re not amazing, but at least they are a bit more interesting than the plain colours. Just find the one you like and click “Select”.

Forms6 - 24

Here’s what it looks like on the form:

Forms6 - 25


Adding your own image

You can also add your own images, which is particularly useful if you want to add your company’s or school’s logo to make the form look more professional.

Go to the colour palette as before and click on the image icon.

Forms6 - 22

Choose either “upload photos” or if it is already on your Drive, click Your albums. In this example, I’m going to add a photo. Either drag the photo onto where it says “Drag a photo here” or click “Select a photo from your computer”.

Forms6 - 26

Choose your photo and once uploaded, it will ask you to crop it so it fits on the screen correctly. Just move the rectangle to the part of the photo you want to show, and then click “Select”.

Forms6 - 27

Here’s what that photo looks like on your form. Note, it’s not exactly the same as the crop, as the height is a little less than the original crop, so take that into account when you crop it.

Forms6 - 28


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

Baz Roberts (Flipboard / Twitter / Google+)