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


Google Forms – Adding images and videos

In Google Forms, apart from text, we can add images and videos to enrich the form, and as always this is simple to do.

Adding an image to a form

You can add images to your form. On the right-hand side of your questions, you will see a floating menu with 5 options.

Forms5 - 8

Click on the middle one, “add image”.

Forms5 - 9

This opens the “Insert image” dialogue box. Choose one of the options at the top depending on where your image is.

Forms5 - 11

In this example, I’m going to add an image that is on My Drive. Type in the name of the file and press Enter.

Forms5 - 12

Click on the image you want and click “Select” at the bottom of the box.

Forms5 - 13

This adds the image on your form. At first, it may be the wrong size. Just click on it and drag a corner of it to change the size.

Forms5 - 14

You also have the option of aligning it to the left, centre or right, and adding a title to the image.

Forms5 - 15

Clicking on the 3 dots, also offers you the option of adding “Hover text”, i.e. when the person’s cursor is over the image, some text will appear. Clicking on it adds a hover text line under the image title, which you can edit.

Forms5 - 16  Forms5 - 17


Adding a video to a form

On the floating menu, click the fourth option (the play icon), “Add video”.

Forms5 - 10

Either you can type in a search term or you can click URL and paste in a YouTube URL.

Forms5 - 18  Forms5 - 19

Here I’ve typed in a search. Select the video you want and click “Select”.

Forms5 - 20

Back on the form edit page, you can align it and give it a title.

Forms5 - 21

Clicking on the 3 dots, gives you the option to add a caption under the video. This can be a better option than adding a big title.

Forms6 - 15 Forms6 - 16

The form-filler will see the video embedded in the form and they will just need to click the play button to watch it.

Forms6 - 17


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Forms – More ways you can create better forms

Once you’ve got to grips with the basics, it’s time to look at other ways you can make you form better. Here’s a selection of options Google Forms provides which are very easy to use but will allow you to use Forms in different ways.

Let’s start with what’s hiding in the Settings menu.

On the form edit page, click on the cog at the top of the screen.

Forms5 - 1 (1)

This will open the Settings menu.

Forms5 - 2

Can submit only 1 response – This ensures a person can only send one response, but to do this they have to have logged in.

Forms5 - 2b

Confirmation page (message for respondents) – By default, once the form-filler has clicked Submit, they will receive a message saying “Your response has been recorded”. This is fine, but a little impersonal and a different message may be more appropriate for your situation. So, to change it just type it in.

Tip: You can add links here, e.g. a link to a page on Google Drive with the answers to a test.

Forms5 - 3

Message on confirmation page:

Forms5 - 4

Show respondents a link to – This gives the respondents 3 options:

  • They can submit another response (this takes them back to the form)
  • They can edit this current response
  • They can see the summary of responses (which if selected is available to anyone filling the form in).

Forms5 - 5

Under presentation options, you can add a progress bar to your form, which is useful if you have lots of pages, and you want to encourage those filling it out that the end is in sight!

Forms5 - 6

Progress bar on the form:

Forms5 - 7

You can also shuffle the question order.


Adding a text

If you have a text that you want your students to answer questions on, using the question option makes the text too big, but there’s an alternative way. Use the “add title and description” option. Click on the double T icon from the floating menu.

Forms6 - 18

Give the question a title (optional). Then type in or paste in your text in the description box below. The questions are then below the text.

Forms6 - 19

This is what it looks like on the form:

Forms6 - 20


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

Baz Roberts (Flipboard / Twitter / Google+)


Google Forms – Managing your form’s responses

Once you’ve made your form and shared it, you’ll then want to see the responses  and probably want to have a visual summary of them. Google Forms provides a wonderful graphical summary of the responses automatically right within Forms itself, so there’s no need to create charts yourself.

Open your form and you’ll be in the form editor. There are two main parts to the editor, the questions and the responses. Click on “Responses”. Here it will tell you how many responses you’ve received and gives you the option of seeing a summary of them or seeing the individual responses.

Forms responses - 1

VIEWING A SUMMARY OF THE RESPONSES

Click on “Summary”. Here you’ll see all the typed in responses and for questions where there were limited options, you’ll see a graph.

In this question, I can quickly see that some of my students don’t know the capital of Wales, so I’ll need to do something about that in a future class.

Forms responses - 4

Here’s an example of a question asking for their comments:

Forms responses - 5

This one was a Checkbox question and I can quickly see the most popular sessions at this conference.

Forms responses - 6

This question was a linear-scale question. Here I can see that our customers are happy.

Forms responses - 7

This question was a multiple-choice grid and within one graph can show a lot of information. In this case, we asked them to rate the teachers on a set of criteria.

Forms responses - 8

With date and time questions, Forms will put the date or time entered along with the number of people who entered it, so you can see the most popular one.

Forms responses - 9 Forms responses - 10


VIEWING INDIVIDUAL RESPONSES

If you want to see want a specific person filled out on your form, click on “Individual”. You can flick through the responses by clicking on the arrows next to where it says. e.g. “1 of 9”.

Forms responses - 16

This shows you exactly what the form-filler completed. It also gives you the option of deleting a response, by clicking on the bin icon to the right.

Forms responses - 18


Back to the top of the page, there are some further options that you can change.

On the right, you can switch off “Accepting responses”, which means that no-one can submit more responses using your form.

Forms responses - 11

When you first create a form, you can link a spreadsheet to it, so that the responses are stored in a place other than within Forms. Either click on the green spreadsheet icon or the 3 dots and click on “Select responses destination”.

Forms responses - 15

You then have the choice of creating a new spreadsheet (and naming it) or adding a new page to an existing spreadsheet. If you want a new one, just click “Create”. If you want it to add to an existing one, click “Select existing spreadsheet”.

Forms responses - 13

Clicking the second option, opens up a dialogue box where you can choose the spreadsheet you want by clicking on it, then click “Select”.

Forms responses - 14


If you’ve already set up a spreadsheet, clicking on the green spreadsheet icon will open the spreadsheet where the responses arrive and live.

Forms responses - 12


There are also some other useful options by clicking on the 3 dots.

Get email notifications for new responses – By selecting this, Forms will send you an email every time someone fills out and submits your form.

Unlink form – Choose this if you want to disconnect a spreadsheet from a form.

Download responses (.csv) – This downloads the responses in .csv format which can be useful for uploading the data to another application.

Delete all responses – Sometimes you want to use your form with a different set of people, e.g. a new class, but you don’t want to mix the old and new responses. So, here you can delete all the responses from the form. Note, this deletes them from the form but those already collected in the spreadsheet remain.

Forms responses - 15


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

Baz Roberts (Flipboard / Twitter / Google+)