https://sites.google.com/site/groupleadershipproject2011
Angie: Hello, in this video we will show you how to use a new powerful feature in Google Apps. It’s called Google Apps Script and it lets you automate tasks across Google Products.
Alex: Before we show you how it works and code some examples, I will give you a short description of what it is and the technologies behind it.
What is a Script? How does it work?
Alex: No, it's not this summer's biggest blockbuster. We're talking about what could be your Google Apps workflow's biggest hit! Creating small, self-contained programs that work with Google Apps to automate!
A script is a set of instructions given to a computer through a compiler as an executable, to process in a specific order. In other words run the task given as it is told to, sound complex? Compare it to giving your students an assignment. First you describe what they are going to do, how they will do it, and what the result is expected to be. A computer program works in the same sense as we teach a course, and so it can have similar requirements. It needs to know the content, what it means, how to process it, and what to do with it. Much like using critical thinking skills, we need to assess what we want to accomplish, what resources we have available, and how we can utilize them to our advantage. A script or program needs to take into account what information it is given, and what to do with it.
A script is created using a script editor, this can be a specific program designed for scripting, or it can be a simple text editor. Once the script is written out, it is compiled by a compiler. The compiler’s job is to make the script executable, it transforms the text that was written out into something the computer can understand and run as a program.
Let’s learn about Google Apps Scripts!
What is Google Apps Scripts?
Alex: Google Apps Scripts is a JavaScript based engine that is processed in the cloud. This scripting platform is run on Google Servers, so the computing is done entirely over the Internet on remote servers, alleviating the need to compile the scripts on the user’s computer. These scripts are used in conjunction with Google Apps to extend the functionality for a user, or an entire organization.
The purpose of this service is to allow any user, programmer or non-programmer to further expand the automation abilities of various Google Apps. The scripts allow users of all comprehension levels, beginner, intermediate and advanced, to create or utilize these tools for their needs. With a growing library of available scripts in the scripts gallery, a novice user can simply search for a script that could help them in their situation. An intermediate, advanced or more adventurous users can use the Script Editor, within Google Spreadsheets or Google sites, to create their own script to fit their more complex needs.
Anyone with some JavaScript syntax know-how can create customized scripts.
As with other Google services, these are all stored online, and can be accessed from anywhere with a connection to the Internet.
Now let’s learn how Google Apps Scripts can help you!
Angie:It’s easy. Don’t hate, automate! It’s a fact that everybody has to do some very tedious tasks while working. It is very common for educators to have a series of chores that have to be done in a weekly or monthly basis, that have nothing to do with teaching. For example, you may have to send emails to students or parents reminding them of a school activity or assignment. Or, maybe you have to keep several calendars for student appointments or school events. If your school does not have a Learning Management System, you might also have to generate your students grade reports.
Google Apps Scripts tries to make some parts of your job a lot simpler. With the help of a script you can make your students fill-in a form for teacher appointments that when submitted it automatically adds an event to your Google calendar or sends you an email notification. You can save several minutes by letting a script do this for you. That way you can focus on the important things like preparing your class or helping your students.
Angie: The fastest and easiest way to use Google Apps Script is to install an existing script from the Script Gallery in Google Spreadsheets or Sites. These scripts are public scripts contributed by members of the Google Apps Script team and Google Apps Script users. Where do you find them? Let’s begin with a Spreadsheet. Create or open an existing spreadsheet in your Gmail or Google Apps account. Under the Tools menu, find the option Scripts and then Insert. A dialog box with a large list of scripts will appear on screen. Browse the list of scripts by category, when you find one that resembles what you need click the Install button. Now you can use the script like you would usually add a regular function or a new menu option will appear at the top of your spreadsheet that will let you run the script. Some great scripts out there are:
ValMerge: A mail merge application for Google Spreadsheet. You can send custom and individual mails to list of contacts maintained in your Google Spreadsheet.
And
Flubaroo: Is a new and very powerful script that allows you to quickly grade and analyze assignments.
You can install them to your spreadsheets quite easily and both of them have good user documentation sites.
A script can also be added to a Google Site. Creating a website for you class can be very simple with Google Apps and now you can customize it even more by embedding scripts. The first step is to go to the More Actions menu and find the Manage Site option. On the left menu you will see the Apps Scripts section. After you click it, search for the button that launches the editor. A new browser window will pop-up, in the File menu option select “Add command sequence from Gallery”. The same search window from the spreadsheet Tool menu will appear. There’s a neat script in the Education section that lets you submit or upload assignments in Google Sites. After you install it, close the editor and refresh the App Scripts page, it should appear on your list of scripts. Now to embed your script, create a page in your site and clic the edit button. On the Insert menu find the Apps Script Gadget option. After you select it, a dialog box will appear where you can pick the installed script and it’s embedded on your page.
That wasn’t too hard! Was it? And the good news is that more and more teachers or developers in general are sharing their scripts. Why not take advantage of this growing community of scripters.
Are you ready to write your own script?
Angie: So Alex, according to the developers at Google: Anyone can Script! Would you agree?
Alex: Yes, with a little effort and motivation! Google Apps Script’s page offers some tutorials to get you started in making your own script. Along with that, a little research into JavaScript can help anyone become more comfortable in writing a script. Though, before diving into making a script, it is suggested to search the gallery and see if there is something you can use there before reinventing the wheel.
Angie: I agree. The gallery is a very good place to start, but if you don’t find exactly what you are looking for... then you can always modify some existing code or create it from scratch. You can start by looking at the begginer’s tutorials. Alex and I will walk you through your first script. It is a simple function that modifies a spreadsheet cell using a dialog box. Take it away Alex:
Alex: First: Using the Script Editor
For this example we will be creating a script that will use a spreadsheet containing contact information to email out a reminder for an assignment. Imagine having your students' emails and being able to send them all a reminder, or an announcement right to their inbox. You may be asking why not just use email? Well what if you don't have all of their emails. As we stated before you can use Google Spreadsheet to run a script, and if you use a Google Form, the data entered on the form ends up being stored and organized in a spreadsheet. So, you can create a form/survey with Google Forms and give your students the link. Once they submit their information, you can use the data collected to create a script that will use that information to run the email announcement/reminder task.
For this tutorial we will be using the Script Editor within Google Spreadsheet.
Transition to next image/slide with dissolve or wipe
As Angie mentioned earlier, open up a new or existing spreadsheet, click on Tools>Scripts>Script Editor. Now the editing window will pop up and look like this:
<--(Insert picture of blank script editor)
In this dialogue box, you will type the following text to create a script. This text is formatted in JavaScript syntax, which means that it must follow the language/organization/order rules for it to work.
Transition to next image/slide with dissolve or wipe
For the purpose of this example, we will ask you to copy and paste the following text as it is written here into the script editor. This is to help you become familiar with the code, and perhaps break the ice for any non-programmers. You may also find this text in our storyboard website under Part 6. http://bit.ly/GLPStoryBoardPart6
This text is an example of the code that is used in many of the scripts available in the Script Gallery inventory. Many times you will not need to alter much of the coding, if any at all.
Here is the code:
function sendEmails() {
var sheet = SpreadsheetApp.getActiveSheet(); //Get the current or active spreadsheet
var startRow = 2; // First row of data to process
var numRows = 5; // Number of rows to process, should be number of students
// Fetch the Range of cells A2:E6
var dataRange = sheet.getRange(startRow, 1, numRows, 5);
// Fetch values for each row in the Range.
var data = dataRange.getValues();
//For each row in the Range
for (i in data) {
var row = data[i];
var emailAddress = row[2]; // Third column, student Email
var subject = "Assignmet Due - " + row[1]; //Second column, assignment description
var message = "Dear " + row[0] + ": " + row[4] + " DUE DATE: " + row[3]; // First column student name, Fifth column comment, Fourth column due date
MailApp.sendEmail(emailAddress, subject, message); //Send the email
}
}
Here is what the spreadsheet looks like.
<---(Insert screencapture of sheet)
Once the information is in place, and the script has been setup, you can run it by clicking on Tools>Scripts>Manage. This will bring up the list of scripts, and you can select it and click Run.
It will then run the task!
Here's what the outcome looks like:
<--- (insert screen capture of email)
Angie: This script is designed for a class of five students. You can customize the script for whatever number of students you have in your class. Just look for the variable at the beginning of the script called “numRows” and change the default value of 5 to the number of students you will include in the spreadsheet. Instead of writing several personalized emails to your students, reminding them of their assignments, you can work much faster filling the spreadsheet and the let the script do the boring work of constructing the emails and sending them.
Alex: There are more resources available through Google’s documentation that can help you become an avid and savvy Google Script-er! Remember, the answers to most questions are just a Google search away!
Thank you for watching and we wish you....
Together: Happy Scripting! :D