Google Forms

I have used Google Docs in a very limited manner previously, but on the recent TSM course I was shown the real power of using them. All you need is a Google account, and you can then create documents, spreadsheets and presentations in the Google Drive area of your account. My initial understanding of this was that it was a bit like every other cloud based storage system: you can access you documents from anywhere, and share them with certain people.

But Google Docs goes one above this. Using Google Docs you can all edit the same document at the same time! No more "read only" copies because somebody else is working on the document. And you can also instantly see the updates that others have made.

Now clearly Google Docs has huge potential for the admin side of things in teaching (as in any other profession). For example, a shared scheme of work will continue to grow and adapt as all teachers in a department (or indeed between schools) can easily add items and resources they have found useful (and if needed the HoD can remove editing writes for a period as well).

But Google Docs can be used for so much more than this. You can have students collaborate on projects easily, submit work by sharing a document and so much more. A few places for more information are given below:

• Google in Education
• The paperless classroom with Google Docs
• Tips every teacher should know about Google Docs
• and many more...
  

In this post though, I am going to focus on using Google Forms, and how we can simply edit them to make them even more powerful. There are several elements we shall discuss:

1. Google Forms - the Uses
2. Google Forms - the Basics
3. Google Forms - Question Types
4. Google Forms - More Advanced Settings
5. Google Forms - Analysing Responses
6. Google Forms - Setting Up email Responses

---

Google Forms - the Uses

A google form is basically a survey or questionnaire, but there are many different things they can be used for in the (Maths) classroom. Below is a slideshow with 79 ideas, and below that is my top 10 ways specific to maths teachers.

79 interesting ways_to_use_google_forms_in_the from GrahamAttwell

1.    Collecting Relevant Data
When doing projects on data handling (such as the Average Student) it is always awkward to collect data before using it with the class. Obviously we like to use data that is relevant to the class, as this will prove so much more engaging, but it is tedious to input all their data into a spreadsheet or similar. With Google Forms, you can create a form in 10 minutes with a question for each piece of data, and then send this to all the students in your class. They fill it in as a homework, and the data is automatically put into a spreadsheet for you.

2.    Designing Questionnaires
Students need to be able to design good questionnaires, and this is one thing that is really useful in the real world of work too. But nobody does this using pen and paper any more. Get students to design a questionnaire to collect data about something to do with the school (travel, food, sport, etc), but have them use Google Forms to create a professional looking survey. This can then be emailed or put out in a school newsletter, so that they can get as many responses as possible. Surveys are no longer done on street corners!

3.    Self Assessment
Create a form for a particular topic, where each student must self-evaluate their performance and confidence in each of the learning objectives through that topic. You just write each learning objective, and use a Scale or Grid (discussed below) for students to evaluate. The results are automatically collected, and you can then use Conditional Formatting (also discussed below) to highlight the different levels of confidence. This self assessment tool can be used with students of any age, from primary to Sixth Form. It is basically a high tech version of traffic lighting, but students love using computers.

4.    Consolidation Homeworks
In a matter of minutes you can type (or copy) questions you would normally set as a worksheet homework as a Google Form. Insist students still do their working in the books (so you can check it if necessary), and write their final answers as their responses to the form. Their final answers are then imported automatically into a spreadsheet, and with a little Conditional Formatting you can mark them without actually having to do anything. Any that are wrong, you can then review their working, but saves you the time of checking all the working of those who got everything right!

5.    Frequent Checks
Creating a short 10 question form for students each week to answer a variety of questions on the topics that have been covered so far will get them reviewing their learning throughout the year, and not just cramming for tests and exams. If you use the reply systems (detailed below) students will have a record of every time they try it, and best of all, they can repeat them as many times as they want, and you get constant feedback of how they are doing.

6.    Getting to know them
When you have a new class you can use a form to ascertain their prior knowledge, and their strengths and weaknesses. This can be based on questions and their answers, and also on scales and grids to reveal how they feel about the different topics. This will give you a sound starting point to get to know their view of the subject, and their confidence levels, before you start to plan accordingly.

7.    Submitting Solutions to Weekly Problems
If you have some form of Weekly or Monthly problem running in your school, then you can get students to upload their written solutions (either typed or scanned) to Google Drive, and then submit the shared URL via a Google Form. This also gets round the problem of not being able to write maths on the forms, which you can do in a Google Doc. This will give you easy access to important information, and to the URL, within the spreadsheet. You can then write comments in the spreadsheet about each as well, and comment on their original document in a different colour as necessary. You can also use a Choose from a List question for them to choose which problem they are uploading, so they can continue to work on them after the new problem is posted.

8.    School-wide Competitions
I love cryptography and code-breaking (see my other site Crypto Corner), and enjoy running a club on this. There are two national competitions each year, but I also like to run a school based one as well. Every two weeks I give them a clue in the session, and time to work on it. In the alternate weeks sessions, we look at some of the theory of codes and ciphers. For each round, students must type out their full solution, and (as above) upload and submit via Google Forms. In this case, points are awarded on a time to solve approach, so the fact that Google Forms automatically records the time they submit is crucial. I can then easily see who was first, how long they took, etc. Similarly this could be used for uploading solutions to other competitions where the time they submit is important.

9.    Keep track of Praise/Discipline
You can easily create a form to select if you are awarding a merit or discipline in class, and then have this form open in the lesson to record things as they happen. You could have a text box to enter their name, a choose from a list to pick if it is praise or discipline, each of which takes you to a different page. Then on each page have a set of checkboxes for regular praiseworthy or offensive actions to quickly select from. This will then be recorded instantly in the spreadsheet. This could even be done department or school wide to keep track of things.

10.    With your form
Google Forms are also great for keeping up to date with your form (if you have one). You can ask them what extra curricular activities they have been doing, which they are interested in starting, etc. You can also use it with a new form to collect some information about them early on, such as an interesting fact about them. All the information you collect is then stored in the spreadsheet for you to refer to when in parent's meetings or writing reports.

Back to Contents List

---

Google Forms - the Basics

Google Forms are one of the documents you can create within Google Docs. When in Drive, click on the red "CREATE" button and you will get something that looks similar to the image shown to the right. You can then select "Form", and a new window will open with the template, and a pop-up will appear asking you to name your form, and choose a theme.

Now you are ready to start editing your form by inserting questions. Notice that when you hover over the title of the form, you can edit it, and you can also add a description of the form you are creating. This is used to give a general overview of what you are asking about in the form.

How to create a new form.

Below this is the first question box. For each question, you have several options:
Question Title - this is where you type the question you want to ask;
Help Text - here you can add any supporting information to go with the question. For example, give you answer to 1d.p., or any other helpful instructions;
Question Type - choose how you want the answer to be presented (we shall go through these below);
Required Question - if you select this box, then the respondent must answer this question to be able to submit the form;
Add item - choose this to add a new question.

The first view of your new form.

It is recommended that you make the first two questions on your form a "Name" and "email" box, with the Question Type "Text", and make them required. This will be particularly important if you are going to use any of the more advanced aspects described later.

The form with the first two entries written.

You then continue to add questions as necessary until you have a form you wish to share with people to fill in. As you move on to each new question, the previous will minimise to look like it will in the actual form. If you need to edit a previous question, just reclick on it, and you will find the editing box reappears.

As you add more questions, the previous revert to how they will look in the finished form.

Once you have a completed form, you look at the options in the final box on the page, shown below. This gives you some final options to decide on for the form. You can change the message that respondents will get from the standard "You response has been recorded." to anything you like by clicking on the box. You then have 3 choices to make for your new form, and your decision for each will depend on what you are using the form for. Once you have done this, click "Send form".

The "Confrimation Page" box.

A pop-up then appears with options on how you wish to share the form you have created. You can copy the link and post or email it, share it via one of the available social media sites, embed it on a site, or most easily, type in the email addresses of the people you want to send it to. When you click the box to type email addresses, you get some more options. When you have finished hit "Send" (or "Done" if not sending a direct email).

The Send form menu is where you select how you are going to share the completed form.

When you type emails, you get some more options.

Finally you choose where you want the responses that people submit are to be recorded. The default is to create a new spreadsheet in your Google Drive account that will store the responses, and this is definitely the easiest. To do this, simply click "Create". Now there will be a spreadsheet that will automatically update each time somebody submits a new response to your form.

In this menu, you choose where the responses will be recorded.

That's it. Your form is created, and has been sent to the people you want, and as they answer, their responses will be recorded in the new spreadsheet that has been created. You can then view these responses, export the answers to another spreadsheet markbook, and do various other things with them, like conditional formatting, sorting and totalling.

Back to Contents List

---

Google Forms - Question Types

There are several question types available to use in Google Forms and we shall run through each of them now. For each we shall see the input method, and what it looks like in the finished form.

1.    Text
The simplest option is the text option, where the respondent simply enters a short answer by typing it. This can be used to provide a specific answer (a numerical answer for example) or to answer a question for which there are endless possibilities (i.e. for a name).

Text type question input

Text type question final

2.    Paragraph Text
The step up from the Text option is the Paragraph Text question type, which allows respondents to give a fuller and longer response. This is great if they need to describe something, or write a short paragraph about something.

Paragraph Text type question input

Paragraph Text type question final

3.    Multiple Choice
The Multiple Choice question type is another excellent option if you want to give your respondents a choice from which they must choose one option. You can also have an "Other" option, where respondents fill in their own answer like Text. It is also possible to make the form jump to different pages depending on their answer (we shall talk more about pages later).

Multiple Choice type question input

Multiple Choice type question final

4.    Checkboxes
Checkboxes are similar to Multiple Choice questions, except the respondent can select as many of the possible answers as possible. It has the same option of having an "Other" box.

Checkboxes type question input

Checkboxes type question final

5.    Choose from a List
The same principle as Multiple Choice, but a different way of laying it out. Respondents still have to select one of the options, but they appear in a drop down menu instead of as individual selections. Again, there is the option to go to a new page depending on selected answer.

Choose from a List type question input

Choose from a List type question final

6.    Scale
The scale option allows respondents to choose one option from a scale. This is often used to see how people feel about a particular topic. You can choose how much of a scale you want, whether to start at 1 or 0, and you can give the endpoints of the scales labels if you want.

Scale type question input

Scale type question final

7.    Grid
A grid is used when there are multiple questions which want to be ranked on the same scale. Traditionally used for "Agree/Disagree" statements, it is an excellent way to ask questions on Probability.

Grid type question input

Grid type question final

8.    Date and Time
The final two options are to enter a date or a time. These could be good for collecting data about birthdays, or how long homework takes to complete.

The completed form with each of these questions is below, and you can fill it in and submit your responses. If you provide an email address (which you don't have to), then the form will automatically reply to you with a copy of your answers (we shall talk about how to do this further down).

Back to Contents List

---

Google Forms - More Advanced Settings

There are various other things you can insert into your Google Form, such as new pages and images. These appear in the little arrow next to "Add item".

You can use the image item to insert graphs taken from Autograph for use in questions, or images of complex equations which do not format particularly well in the basic text of the question.

The Section Header could be useful if you are creating a form with multiple sections, and you want to clearly break them up.

Using different pages is very helpful if you are asking lots of questions, as it does not look friendly if there are too many questions on one page. It is also possible to add new pages depending on the answers to certain questions. For example, if you ask if a respondent is interested in questions on equations, if they say yes, they could get a new page full of questions, but if they say no, then the form could be immediately submitted.

The best way to get to grips with these options is to play around with them, and this is easy to do with Googles drag and drop features.

Back to Contents List

---

Google Forms - Analysing Responses

Once your form is set up, and sent to the respondents, you don't need to do anything to collect their replies: the spreadsheet that the form created will automatically update with all their responses. The first column will always be the timestamp (that is the date and time they submitted their response). The rest of the columns will represent each of the questions (or part questions in a grid) that you asked in the form. Below is a dummy spreadsheet for a demo form I made with 5 arithmetic questions (with email addresses removed!). Notice that in the first image, the spreadsheet has automatically formated "7-12" as a date. To avoid this happening, click the box in the top left (above 1, left of A) to select the whole sheet, and then go to Format->Number->Plain text, and change any values back to what you want them to be.

The recorded responses of two "responses" to a demo form I made. Notice that the spreadsheet has turned 7-12 into the date 7/12/13

As above, but with the "date" corrected.

Quite often we might want to identify if somebody has answered with a particular answer (for example if they got it right). We can do this easily using Conditional Formatting. First you select the column you want to check (we shall start with column D), then click Format->Conditional formatting... to open up the pop-up menu shown.

The Conditional formatting menu

Depending on what you want, you choose the rule and the value or text to look for (in this case the answer to 5+3 is 8, and we want it to be exactly that). You then choose a format for the cells that satisfy this rule, for example making them green to indicate correct.

The Conditional formatting for getting a correct answer of 8.

We can then continue and do the same for each of the questions (that have a given answer). Doing this for the dummy responses shown above returns us with the image below. Unfortunately, Google Spreadsheet is not quite as powerful as Excel at the moment, and you cannot conditional format with respect to a given cell. However, there is a work around this using a little bit of coding, which I talk through in the next section.

The results with conditional formatting applied

Now we have a spreadsheet that will update automatically when somebody submits a new response to the form, and will also automatically identify the questions they got correct. You can use this to easily identify which individuals gave responses you wouldn't have expected (alternatively, you could have set up the conditional formatting to highlight if it was not the same as the answer, and maybe use red or yellow as the background). As a teacher, you can then use this information to quickly ascertain which pupils may need more help or practice on a particular topic.

Back to Contents List

---

Google Forms - Setting up Email Responses

Google forms are brilliant as they are, and they give you easy access to all the information you need from the form in a spreadsheet. However, for some of the things you might want to use a Google Form for, it would be handy for the students to have their responses. Fortunately, it is fairly easy to add a little bit of computer code to a Google Form to set up an email response.

The general set up for entering the code is the same for each situation, it is only the code itself that changes. First you need to open up the SPREADSHEET where the responses are recorded. When you open it, the top row will be greyed out and will contain all the questions in your Form. Go to Tools->Script editor... as shown below.

Where to go to enter some code

A new window will open, and the pop-up below will appear in the middle of the window. We are going to ignore this, so just click the "Close" button in the bottom right. If you are never going to use these tutorials or templates, then you can check the "Don't show again" box, but just in case it is worth leaving it unchecked. If you do tick it, next time you won't see this box.

The tutorial pop-up

You will now see the first window shown below. The big white box is where we are going to input our code. So choose which code you want to use from below, select it all, then back in the "Untitled project" window, replace everything in that white box with the code you have copied (to get something similar to the second window below).

The initial set up of the script editor interface

The script editor interface when you have copied across the relevant code (this is the Simple Reply Form)

For any of the four reply codes, you need to make one change: in the second line, replace "type your name here" with the name you wish to appear at the end of the email. Make sure your name is within double quotation marks. For example I would replace this with "Mr Clark".

The only thing left to do now is to tell Google when to use this code. To do this, go to Resources->Current project's triggers... At this point, if you haven't already given your project a name, you will be requested to do this now. I usually call it reply.

How to set triggers, which tell Google when to run the code

If you haven't named the code file yet, you will have to before setting triggers

The following pop-up will now appear, and you should click on the "No triggers set up. Click here to add one now." hyperlink.

The first screen to add triggers

This will then open up a rule to run the code. Leave it as it is apart from change the last dropdown box from "On open" to "On form submit". This tells Google to run the code whenever a fomr is submitted. Now click "Save". You will now have to allow Google to send emails as you, and two pop-ups will appear. Select "Authorize" and "Accept" as necessary. You can now close the window with the script editor. From now on, whenever somebody submits to this form, they will get a response via email as detailed by the code you chose to use.

This is how the box should look when you click save

When setting the triggers, you can opt to receive notifications of when and if the code fails to work. This is a default, and you can remove it. It shouldn't have any problems, so you should not get these emails anyway.

There are 4 different email responses, and finally a bit of code to make conditional formatting a bit less time consuming to set up:
Simple Reply Form
Reply Form with Simple Marking
Reply Form with Total Mark Given
Reply Form with Corrections Given
Code to Automatically Conditional Format the Spreadsheet

Simple Reply Form

This is the simplest of the codes. All it does is send an email to the respondent via the email address they supplied (if they did not supply one it will not send an email clearly). In this email they are told each of the questions, and the answer they provided for each question. It thus gives them a record of what they answered, and also when they answered.

If using this code, the first question on your Form MUST be asking for their name (full name or first name is fine) and the second questions MUST be asking for their email address. The name should be required, but in this case the email address can be optional.

You should edit the code in one place when you copy it across. In the second line, change "type your name here" to your name you want at the bottom of the email sent. Make sure it is in the double quotation marks. You can also edit the subject of the email being sent, in the last line with text in it, change "Your Answers" to what you want the subject to be. Again, it must be in double quotation marks.

  function onFormSubmit(e) {   
  var yourName = "type your name here";   
  var ss = SpreadsheetApp.getActiveSpreadsheet();   
  var sheet = ss.getSheetByName('Form Responses');   
  var numCols = sheet.getLastColumn();   
  var numQs = numCols - 3;   
  var dataRange = sheet.getRange(1, 4, 1, numQs);   
  var data = dataRange.getValues();   
  var timestamp = e.values[0];   
  var replyName = e.values[1];   
  var toAddress = e.values[2];   
  if (toAddress != "")   
  {   
   var qsText = "";   
   for (var x = 0; x < numQs; x++)   
   {   
    var q = x + 1;   
    var text1 = "\n\n Question " + q + ": " + data[0][q - 1] + "\n Your answer: " + e.values[q + 2];   
    qsText = qsText + text1;   
   }   
   var emailBody = "Dear " + replyName +   
    "\n\nThank you for your answers submitted on: " + timestamp +   
     "\nYour answers are shown below." + qsText +   
      "\n\n" + yourName;   
   MailApp.sendEmail(toAddress, "Your Answers ", emailBody);   
  }   
 }    

Reply Form with Simple Marking

This code is a slight improvement on the previous one. It mostly does exactly the same, as it sends an email containing each question and their response to this question. However it also tells them whether they are correct or not for each question.

In order for this to work you need to add the correct answer for each question to the spreadsheet. To do this it is worthwhile doing a test run of the form first, where you enter the correct answer each time. This will make sure you get the answer in the correct format. Now right click on the row number for the first submitted solution, and select "Insert 1 above". In this new row, enter the correct answers to each question.

Inserted row with the correct answers is needed if you want it to automatically mark the submitted form

If using this code, the first question on your Form MUST be asking for their name (full name or first name is fine) and the second questions MUST be asking for their email address. Both of these fields should be required.

You should edit the code in one place when you copy it across. In the second line, change "type your name here" to your name you want at the bottom of the email sent. Make sure it is in the double quotation marks. You can also edit the subject of the email being sent, in the last line with text in it, change "Your Answers" to what you want the subject to be. Again, it must be in double quotation marks.

  function onFormSubmit(e) {   
  var yourName = "type your name here";   
  var ss = SpreadsheetApp.getActiveSpreadsheet();   
  var sheet = ss.getSheetByName('Form Responses');   
  var numCols = sheet.getLastColumn();   
  var numQs = numCols - 3;   
  var dataRange = sheet.getRange(1, 4, 1, numQs);   
  var data = dataRange.getValues();   
  var timestamp = e.values[0];   
  var replyName = e.values[1];   
  var toAddress = e.values[2];   
  var qsText = "";   
  for (var x = 0; x < numQs; x++)   
  {   
   var q = x + 1;   
   var text2 = "";   
   var yourAns = e.values[q + 2];   
   var theAns = sheet.getRange(2,q + 3).getValue();   
   if (yourAns.toLowerCase() == theAns.toLowerCase()) {text2 = "You are correct";}   
   else if (theAns == "") {text2 = "";}   
   else {text2 = "You need to try again";}   
   var text1 = "\n\n Question " + q + ": " + data[0][q - 1] + "\n Your answer: " + e.values[q + 2] + "\n " + text2;   
   qsText = qsText + text1;   
  }   
  var emailBody = "Dear " + replyName +   
   "\n\nThank you for your answers submitted on: " + timestamp +   
   "\nYour answers are shown below." + qsText +   
   "\n\n" + yourName;   
  MailApp.sendEmail(toAddress, "Your Answers ", emailBody);   
 }    

Reply Form with Total Mark Given

This code checks each individual question, and marks it against the correct answer you provide. It responds to the respondent in the same way as the simple reply form, and does NOT tell them if they are right or wrong. At the end of the message, it gives them a mark out of how many questions were markable. A question is only markable if the answer is short and can match exactly. Hence paragraph answers are not markable. If you can't write an answer in the spreadsheet for a question, it will tell them that that questions was not counted towards the total mark.

As with each of the codes that mark the solutions, you need to first insert the correct answers into the spreadsheet that records responses. To remind yourself of how to do this, see the instructions on Simple Marking.

If using this code, the first question on your Form MUST be asking for their name (full name or first name is fine) and the second questions MUST be asking for their email address. Both of these fields should be required.

You should edit the code in one place when you copy it across. In the second line, change "type your name here" to your name you want at the bottom of the email sent. Make sure it is in the double quotation marks. You can also edit the subject of the email being sent, in the last line with text in it, change "Your Answers" to what you want the subject to be. Again, it must be in double quotation marks.

  function onFormSubmit(e) {   
  var yourName = "type your name here";   
  var ss = SpreadsheetApp.getActiveSpreadsheet();   
  var sheet = ss.getSheetByName('Form Responses');   
  var numCols = sheet.getLastColumn();   
  var numQs = numCols - 3;   
  var dataRange = sheet.getRange(1, 4, 1, numQs);   
  var data = dataRange.getValues();   
  var timestamp = e.values[0];   
  var replyName = e.values[1];   
  var toAddress = e.values[2];   
  var qsText = "";   
  var count = 0;   
  var total = 0;   
  for (var x = 0; x < numQs; x++)   
  {   
   var q = x + 1;   
   var yourAns = e.values[q + 2];   
   var theAns = sheet.getRange(2,q + 3).getValue();   
   if (yourAns.toLowerCase() == theAns.toLowerCase())    
   {   
    count = count + 1;   
    total = total + 1;   
    var text1 = "\n\n Question " + q + ": " + data[0][q - 1] + "\n Your answer: " + e.values[q + 2];   
   }   
   else if (theAns == "")   
   {   
    var text1 = "\n\n Question " + q + ": " + data[0][q - 1] + "\n Your answer: " + e.values[q + 2] + "\n This question is not included in your total mark.";   
   }   
   else   
   {   
    total = total + 1;   
    var text1 = "\n\n Question " + q + ": " + data[0][q - 1] + "\n Your answer: " + e.values[q + 2];   
   }   
   qsText = qsText + text1;   
  }   
  var emailBody = "Dear " + replyName +   
   "\n\nThank you for your answers submitted on: " + timestamp +   
   "\nYour answers are shown below." + qsText +   
   "\n\nYour total score is: " + count + "/" + total +   
   "\n\n" + yourName;   
  MailApp.sendEmail(toAddress, "Your Answers ", emailBody);   
 }    

Reply Form with Corrections Given

The final piece of code that sends replies to individuals marks their answers more thoroughly. As well as giving them the question and their response, it tells them if they are correct, and if not it gives them the right answer. Again, if the question is not markable in this way, then it just does not say if it is right or wrong.

As with each of the codes that mark the solutions, you need to first insert the correct answers into the spreadsheet that records responses. To remind yourself of how to do this, see the instructions on Simple Marking.

If using this code, the first question on your Form MUST be asking for their name (full name or first name is fine) and the second questions MUST be asking for their email address. Both of these fields should be required.

You should edit the code in one place when you copy it across. In the second line, change "type your name here" to your name you want at the bottom of the email sent. Make sure it is in the double quotation marks. You can also edit the subject of the email being sent, in the last line with text in it, change "Your Answers" to what you want the subject to be. Again, it must be in double quotation marks.

  function onFormSubmit(e) {   
  var yourName = "type your name here";   
  var ss = SpreadsheetApp.getActiveSpreadsheet();   
  var sheet = ss.getSheetByName('Form Responses');   
  var numCols = sheet.getLastColumn();   
  var numQs = numCols - 3;   
  var dataRange = sheet.getRange(1, 4, 1, numQs);   
  var data = dataRange.getValues();   
  var timestamp = e.values[0];   
  var replyName = e.values[1];   
  var toAddress = e.values[2];   
  var qsText = "";   
  for (var x = 0; x < numQs; x++)   
  {   
   var q = x + 1;   
   var text2 = "";   
   var yourAns = e.values[q + 2];   
   var theAns = sheet.getRange(2,q + 3).getValue();   
   if (yourAns.toLowerCase() == theAns.toLowerCase()) {text2 = "You are correct";}   
   else if (theAns == "") {text2 = "";}   
   else {text2 = "The correct answer is " + theAns;}   
   var text1 = "\n\n Question " + q + ": " + data[0][q - 1] + "\n Your answer: " + e.values[q + 2] + "\n " + text2;   
   qsText = qsText + text1;   
  }   
  var emailBody = "Dear " + replyName +   
   "\n\nThank you for your answers submitted on: " + timestamp +   
   "\nYour answers are shown below." + qsText +   
   "\n\n" + yourName;   
  MailApp.sendEmail(toAddress, "Your Answers ", emailBody);   
 }    

Code to Automatically Conditional Format the Spreadsheet

This last piece of code is slightly different to the other ones we have seen above. This one does not generate an email to be sent, but rather acts completely within the spreadsheet itself. It is a different way to apply conditional formatting to the spreadsheet (you can still use the other method).

The difference with this system is that it uses the answers you type into the second row of the spreadsheet (for a reminder how to do this see the Simple Marking code). This is then easy to change in that row if needed. Also, this code allows for using upper or lower case letters, where the other way must match exactly.

If you haven't already used another piece of code, then you follow the same method above to get to the Script Editor. If, however, you have already input one code, we need to open a new Project. To do this click Tools->Script manager... and in the pop up box that appears click "New". Give your new project a name ("format") by clicking the title.

Now copy and paste the code below into the code box (getting rid of everything already in there), and hit Ctrl-S to save the project. You do not need to set a trigger for this function as it will do it automatically whenever the spreadsheet is editted.

Your spreadsheet will now automatically mark responses as they come in, showing in green the correct answers. So a row of lots of green is good! As I said, the main benefit of this as opposed to the normal way to do it in Google Spreadsheets is that it doesn't matter if the letters used are uppercase or lowercase. It also saves time from setting the conditional formatting for every column, especially if there are a lot of questions!

  function onEdit() {   
  var ss = SpreadsheetApp.getActiveSpreadsheet();   
  var s = ss.getSheetByName('Form Responses');   
  var numRows = s.getLastRow();   
  var numAns = numRows - 2;   
  var numCols = s.getLastColumn();   
  var numQs = numCols - 3;   
  var values1 = Array();   
  var value2 = Array();   
  for (var i = 0; i < numQs; i++)   
  {   
   values1[i] = Array();   
   for (var j = 0; j < numAns; j++)   
   {   
   values1[i][j] = s.getRange(j + 3,i + 4).getValue();   
   value2[i] = s.getRange(2,i + 4).getValue();   
    if (values1[i][j].toLowerCase() == value2[i].toLowerCase() && value2[i] != "") s.getRange(j + 3,i + 4).setBackground("green");   
    else s.getRange(j + 3,i + 4).setBackground("white");   
   }   
  }   
 }    

Back to Contents List