Tutorials

This is the full example that Stefan Muntwyler developed for his class at UNIS Hanoi, modified by both Stefan and Paul Swanson for demonstration purposes.Click here to view and then copy the spreadsheet. Note that there are six functions in this script: onSubmit – this is what is triggered on form submission, and contains the main structure: build ...

As powerful as a script is on its own, it can be hugely more useful when combined with a Google Form and Spreadsheet.Take a look at this Google spreadsheet, and click File > Make a Copy to have a copy for yourself to work from.When you copy a Google spreadsheet that is a Response form, ...

This example builds on the previous one, but in addition to appending the text to a Google Doc, it emails the student with both the feedback and a link to the doc. Click here to open it and make a copy for yourself. It does so with an additional 4 commands (and comments) at the end ...

In this example we get into the real power of Google Scripts: the ability to interact with Google Docs, Sheets, and more. Click here to view the Example Script 07 – Modifying a Google Doc. function myFunction() { /* The array, data, has three values: studentEmail, message, and docId */ var data = ...

In Google Script, as in most coding, arrays are an important way of storing a series of variables. For a look at arrays in javascript, click here: http://www.w3schools.com/js/js_arrays.asp Take a look at the following example, or click here to make your own copy of the code: function myFunction() { var data = ["johnnysmith@school.edu","Johnny is doing well in ...

In this challenge, you want to pass two variables, ‘studentEmail’ and ‘message’, to the function emailStudent.To do this, you’ll need to add the variables as parameters to both the function CALL, and the function DEFINITION.Once you’ve done this, use the Logger.log command to log both variables and make sure they made it to the emailStudent ...

In the previous example, there was a bit of code that was repeated twice: the Logger.log series of commands. Whenever you see code being used more than once, it’s a good sign that you need to build a new function for it. However, in order for this to work in this case you need to ...

In this example we begin to look at variables. Just like in algebra, a variable can hold a range of values, but in coding they are also a little bit different. Variables in JavaScript have different types, the most basic of which are Strings and Numbers. Simply put, Strings contain text and Numbers contain numerical ...

In this challenge, we’ll take the principles from the previous example one step further. Here, in addition to simply defining functions, we are also CALLING a function: newFunction. function myFunction() { newFunction(); } function newFunction(){ } Note here that the first function, myFunction, calls newFunction. You can tell that it CALLS newFunction because the first time ...

Google Scripts Lessons 1-10 Click on a lesson below for a written tutorial demonstrating the lesson. Lesson 1 – “Hello World!” Lesson 2 – Challenge: Calling Functions Lesson 3 – Variables Lesson 4 – Variable Passing Lesson 5 – Variable Passing Challenge Lesson 6 – Arrays of Variables Lesson 7 – Modifying a Google Doc Lesson 8 – Modify Doc and Send Email Lesson 9 ...

In this final lesson, we will compile all the information we need into a new tab called “Merge data” and then we will send that out to all the students with the mail merge Add-on called Autocrat.Click here to view Example 9, then make a copy so you can play with it. First we review ...

In the last example, we used the functions INDEX and MATCH to reorient our data so that each student was listed on a single row with their peers assessments in columns to the right. If all of the groups were of equal size, we would be done manipulating the data. However, two of the groups ...

In our last example we reorganized our data set by student email instead of by feedback comment. Now we want to pull the data from the Sorted sheet over into the Unique sheet, so that we have separate columns for Score 1, Eval 1, Score 2, Eval 2, etc. Note that we could do the first Score ...

So far so good. We want to send out students the assessments that their peers turned in, so we organized the data by student receiving feedback.  However, in order to send the data out with an Add-On like Autocrat, we need to condense it so that there is only a single row of information for ...

In this lesson, we’ll be learning how to slice and dice, filter and sort your data with the function QUERY. Those of you who have worked with databases before and SQL (Sequenced Query Language) will find this familiar, but even if all this is new to you it’s definitely doable. See Example 5 and notice that ...

In the previous example we saw how you can use the function ARRAYFORMULA to make a function apply to a whole column, including new form entries that fall into that column. However, in that example there was no header row and there were lots of #N/A errors that looked pretty ugly. Here we’ll learn how ...

As you noticed in the last tutorial, you can’t simply copy down a formula and have it apply to new form submissions. This is because the response sheet actually inserts a new row for every submission, breaking up the block of formulas. How then, do we have the formulas automatically apply to new submissions?To solve ...

In this lesson we’ll continue our manipulations of SIS generated data.  One of the most common needs is to look up a student’s full name, email, parents’ emails, grade, etc. This becomes particularly important when we are sending out Google Forms to students. We know we can automatically collect their email addresses, but how do ...

One of the most common sources of information for teachers about students is their School Information System, and this information often comes in the form of a spreadsheet (.xlsx or .csv) file.  Sometimes you get all the information you need about a student, but sometimes you have to manipulate it a little bit. One of the ...