Learning How to Script

So I have been loving the workflow that I have gained by using the scripts from the Google Apps Scripts Demi-gods that I have run across: +Andrew Stillman, +Bjorn Behrendt, +Henrique Gonçalves Abreu, and all...
Now I want to learn how to script on my own to enhance some of the Google Workflows I have developed, like my Parent/Teacher Conference Scheduling and my Seat Problems.
I am going to try to document my learning process in this blog for a few reasons, mainly to share the process and possibly create a course so my students can learn to script.

The first project I want to tackle is adapt my Seat Problem workflow. Currently, my students get an email everyday (actually 3 times a week, since I don't see my students every day) with a math problem they need to solve. The answer to the math problem is the seat they will sit in during the next class. I love this for many reasons, but I feel like it is little to ask from them and I have always thought it would be cool to do!
Now, the only assessment I do on this problem is to see if they are seated in the correct seat. I would like to see their thought process on their solutions and to hold them a little more accountable for their own work.

My thoughts are I would like to have a Google Doc in my students' Google Drive Assignment folders (that were created with gClassFolders, which requires a more in-depth blog post from me). I would like this document to be a running, living document for the entire year of Seat Problems. My reasoning is 2-fold:

1. The students will always know where to go to find their Seat Problem.
2. I think it will be great for students to see their knowledge progress throughout the year. (I differentiate each problem based on student performance and connect them with the content we are working on in class).

I have my problems hosted in a Google Spreadsheet. (I create them using Wolfram's Mathematica, but again that process is for a future blog post) I use LaTeX form and push them through the Google Chart API so they have nice math type. For example if I wanted to have them simplify the expression 3x-2y, I use the URL:

http://chart.googleapis.com/chart?cht=tx&chl=3+x-2+y

which yields:  

This is helpful when I use exponents or fractions, because 

 and 

looks much better than 3x/2 and 2^((3x)/2).

So my goal is to take a URL which is returned as an image and write this to a specified Google Doc. After a few days trying to get DocAppender to write for me, I finally figured out the working code to add an image to a Google Doc body. As you can see I stole a lot from previous scripts. StackOverflow,  +Andrew Stillman's amazing cache of scripts, and the Google Developers Documentation were the main learning tools I used.

Here is the code I used:

function writeToDoc() {

  //stolen from Doctopus to open up the current spreadsheet and read rows data

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheetName = ScriptProperties.getProperty('sheetName');

  var sheet = ss.getSheetByName(sheetName);

  var data = getRowsData(sheet);

  

 //This fetches a document by ID (found in the URL) and opens it up.

  var doc = DocumentApp.openById('1Haex8jdw1n1G1dwKw9Y9i1iMbW3b74D9_o3EfrDvnp0'); //would like to replace this with the file key by student

  

 //You could also use var doc = DocumentApp.create(name) to create a new document instead of opening an existing one.

  

 // The following code fetches the picture from the problemUrl

  // and puts it in the document created above taken ideas from: https://developers.google.com/apps-script/reference/mail/mail-app

  //find the Url

   var problemUrl = "http://chart.googleapis.com/chart?cht=tx&chl=2+x-3+y"; //replace from student problem

   

  //turn the image into a blob

   var problemBlob = UrlFetchApp

                          .fetch(problemUrl)

                          .getBlob()

                          .setName("problemBlob");

  //find the body of the document

  var body = doc.getActiveSection();

  

  //put the image into the body of the document found above. Got information from here: https://code.google.com/p/google-apps-script-issues/issues/detail?id=2459

  var dateForClass = body.appendParagraph('Seat Problem for Class Date of ##-##-##');

  var seatProblem = body.appendImage(problemBlob);

  

}

It works!! You can see that it writes the problem in the Document body. You can also see the Document ID in the URL at the end, between the /document/d/     URL ID     /edit

Now, I need to automate it. I will use the templates from the Tutorial and cannibalize the DocAppender script to read a cell from a spreadsheet and write it to the correct Document based on the information in the sheet. 

Using FormEmailer to send custom emails to students based on responses

I love Google Apps for education! My favorite workflow is to use Google Spreadsheets/Forms to collect information from students (mainly responding to my math questions) and then have the Spreadsheet automatically "grade" the response and send a customized email back to the student.

Recently, I took a lesson from Yummy Math (who is awesome!) about the record Powerball drawing.

Here is how I implemented the lesson using the Google Apps for Education resources from my school site.

Yummy Math has great questions about the Powerball, so I utilized these as my framework. I embed them in my class Google Site, here is an example from my Grade 7 class:

I like Google sites for a lot of reasons, but the main one is the enhanced document capabilities. I can embed videos and interactive activites into my "worksheets". In this unit, I embeded a youtube video of the latest Powerball drawing (the one before the record). It was also helpful since I teach abroad and some of my students had no idea what the lottery drawing looked like.

By having them watch the video of the drawing, it makes the explanation of the premise of the problem much easier, especially for my EAL learners. In addition, it gives me a reference point to help scaffold my questioning to derive theoretical probabilities.  I can pause and ask them how many balls are left and how many of them are numbers they picked, helping them get the probability of them winning on the next ball.

I use the Google site subpages capability to create a break in the questioning. I have a lot of students who like to "power through" the questions if they are all lined up consecutively. By making them click to the next page for more (Powerball part 2 in the picture above), it at least creates another step for them to perform before moving on. Sometimes, I limit the Sharing capability so they can't see that subpage until I want them to (but I can prepare it in advance without worrying if they will see it or not).

So, I broke the assignment up into 7 parts, culminating with the Powerball Simulator as given by Yummy Math: 

My students LOVED running this simulator for days to see if they could win the jackpot. I had a few students who hit the $1million, but no jackpot winners. It was great to have them simulate for so long to see how much money they lost in the long run. 

The powerball Part 8 was where I got the most student buy-in. I embedded a Google Form in the Google Sites page that allowed them to "buy" an entry. They submitted their numbers and their "ticket" was created. I even closed the entries as the same time as the official Powerball drawing. (Which happened to occur right before our lunch time, making for great lunch discussion on the powerball and whether they were going to win the $479million).

How did I embed this? First I created the Form associated with it. (You can check this out in detail on my blog post from last year: Creating a Google Form.)

Once the Form is created, go to the live form and copy the URL from the live form. Then on the page that you want to embed the Form, click Insert->SpreadSheet Form (it is in the 2nd column on the bottom):

From there in the popup window, pick Forms:

You should see the Form you just created in the list and you just need to select it. You can edit the properties of how it will look in the Google site page. I like to leave the Width blank and then make the Height enough to get a view of the form.

Now once you hit SAVE, you will go back to the Site page you are editing and your Form will be in a greyed out box. 

You will see the final formatting of the form embed once you hit SAVE from the editing controls on the upper right of the screen.

Now your form will be embedded in the Google Site. Student responses that are submitted will automatically populate the associated spreadsheet.

This is where the power of FormEmailer will come in!

FormEmailer is a JavaScript script that was created by Henry Abreau (Info on formEmailer)

You need to install this script in your spreadsheet. Click on Tools->Script gallery...

Search the Public gallery for formEmailer and Install the first result:

Since FormEmailer is going to do some stuff on your Spreadsheet you need to Authorize it to do so.

After clicking on Authorize, a window will pop open confirming your response. Upon closing that, FormEmailer will be installed in your Spreadsheet and evident in the Tabs at the top.

Before you run the script the first time on your spreadsheet, you need to Install it.

This will create a new column all the way on the left called Emailer Status and a new sheet called FormEmailer. 

Now you need to create the email that will be sent based upon the data in the sheet. I wanted to email the students the confirmation of their chosen Powerball ticket. I clicked on the FormEmailer tab and chose Settings to popup the creation window.

The popup window is where I construct the email message with the "placeholders" that will be replaced by data from each of the student responses, in this case the numbers they chose for their Powerball entry.

Once I have constructed the message, I need to process the emails. I put my own data in the first row of data (row 2) to test out the email message.

So to process an email from row 2, click on the FormEmailer tab and Process Manually.

Here is the result in my Inbox:

With the following message:

I find this to be a powerful way to communicate with students with customized data!

My next post will be to detail the usage of formulas in the spreadsheet to "grade" the students and perform more complex tasks.

Embedding Wolfram CDF files in a Google Site

I have been working a lot with Wolfram CDF and I am excited by the release of Mathematica 9 and the ease in creating and deploying CDF files. One of the best uses it to create interactive manipulations for my students to use.

I like to embed these in my school Google sites. However, this has proven to be quite difficult since Google sites do not allow Javascripts that create scripts (the CDF embed script that I talk about here: Wolfram CDF embedding ).

Hence, we need to resort to using the iframe gadget to refer to a different page that has the CDF embedded.

In my previous post on CDF embedding, I talk about how I host my CDF files in the Public folder on Dropbox. I then use the Javascript script to embed my CDF in my blogger page. This does a great job of hosting my interactive CDF file.
Now, I need to embed this blogger page on my Google site. However, I don't want to use the whole webpage in my iframe since it will require too much scrolling to find exactly what I want.

So the method I use is to create a target location right above my CDF file in the blogger page. I accomplish this by using the ID anchor in the HTML code on my blogger page.

For example, right above the CDF embed script on the html, I add the code:
<br id="MyBreakName" />

Then, I add the CDF embed script:
<script src="http://www.wolfram.com/cdf-player/plugin/v2.1/cdfplugin.js" type="text/javascript">
</script>
<script type="text/javascript">
var cdf = new cdfplugin();
cdf.embed('TheWebsiteThatHostsMyCDFdocument', 400, 800);
</script>

So overall that snippet of code looks like this for my Plot Points CDF:


<br id="cdfFile" />
Plot:<br />
<div dir="ltr" style="text-align: left;" trbidi="on">
<br /></div>
<script src="http://www.wolfram.com/cdf-player/plugin/v2.1/cdfplugin.js" type="text/javascript"></script>
<script type="text/javascript">
var cdf = new cdfplugin();
cdf.embed('http://dl.dropbox.com/u/30779938/WolframCDFfiles/PlotPointManipulateOnlyGraph.cdf', 443, 489);
</script>


Now, it will create an anchor for me to jump to and gives a link to directly jump there.
The link is whatever the blogger URL is to get to that page with an #anchorName appended on the end.
So in my Plot Points CDF example, the blogger site is:
http://mathtechtips.blogspot.sg/2012/12/blog-post.html

Now, I created an anchor right above the Plot Point CDF on this page and called the anchor "cdfFile". To link directly to this point in my page, I use the link:
http://mathtechtips.blogspot.sg/2012/12/blog-post.html#cdfFile

This is the link I will reference in my iframe URL in the Google gadget that I use on my school Google Site.

To do this, I find the place in my Google Sites that I want to enter the CDF file and Insert->... More gadgets...

from there, choose iFrame:

and put the anchored URL link in the box, in this example: 

http://mathtechtips.blogspot.sg/2012/12/blog-post.html#cdfFile

This will put the Google Gadget (in my case the iframe box, pointing to my blogger page with the CDF file) in your Google Sites page.

Once I hit the SAVE button at the top, I will see the finished product on my Google Sites page:

This is the best way that I have found to embed my CDF deployments in a Google EDU site. Please comment if you have some suggestions on how to improve it!

Email Mail Merge: Creating Custom Comments in a Google Doc Spreadsheet

Now, that we have created a Form and are ready to collect data, I want to use the built-in functionality from Google Doc Spreadsheets to create more substantive comments on the assessed skills. The Form will automatically populate the spreadsheet when it is submitted.


Let's enter some test data into our Form to use while we create our customized email message:

Once we hit submit, our data will be populated into the spreadsheet:

From this data we want to expand on our entry for Skill#1, Skill#2, and Skill #3. We want to turn G, A, and B into more substantive comments.
First, we will create 3 new column headings labeled Comment for Skill #1, Comment for Skill #2, and Comment for Skill #3.

Notice that the new column headings are white and not grey like the previous headings (for Timestamp through Skill #3). This is because the grey columns are populated by the Form. One thing to remember is that if you add another question to the Form it will copy over the new column heading you created.

Now create a new sheet in our spreadsheet by clicking on the plus key at the bottom of the page:

Create 2 new sheets, 1 called Email Template (which we will use later) and 1 called Comment Sheet. Make sure to keep the Email Template sheet the second one from the left (important for our future purposes of creating an Email Mail Merge).

Now, go to the sheet labeled Comment Sheet. Here we are going to create the more substantive comments for each Skill that we are assessing and the 3 levels: Good, Average, Bad. For my example, I will use a soccer example for Skill #1 and Skill #2.


Now create the comment detailing each level of ability for each of the skills. For example for Skill #1:
(Notice that I have personalized it with names and gender specific comments. Later, we will edit this to put it in a different format so that Google Docs will Email Mail Merge it.)

Do the same for Skill #2 and Skill #3

Now that we have our substantive comments, we want to paste them back into the Data Collection Sheets. We will use a built-in function, IF, to do this.

You can also find a list of all the built-in functions here (where I got this info):https://docs.google.com/support/bin/static.py?page=table.cs&topic=25273&ctx=tooltip

So, in Cell I2 (which represents the Comment for Skill #1) use the IF function. This will check the entry in F2 (where we assessed Skill #1) to see which comment to use. If it is a G, we use the comment for Skill #1 that we created in the sheet titled "Comment Sheet", the Good comment is in cell B2.


Repeat this function for Comment for Skill #2 and Comment for Skill #3. The only thing you need to change is the cells that you reference. (For Comment for Skill #2, replace F2 with G2 and B2,C2, and D2, with B3,C3, and D3).
This will automatically fill in the cell with the comment we created in the other sheet.


I have detailed my steps in this video as well.

Email Mail Merge: Creating a Google Form

In this series of posts, I am going to detail how you could create and send personalized emails from data collected in a Form.
The first thing that you need to do is create a Form in Google Docs.

I like to have the questions in the Form be the information I am collecting. For example, I use First Name, Last Name, Email Address, Gender for all of my forms. I need this information to create my personalized email messages.

Now, the next questions I create in my form are the items that I want to assess. I put the skills in there and I will then have a measure for each skill when I enter the data in the form. (I also put a description of the shortcut I would like to use as the help text for the questions).

This finishes the form (you may add as many skills in as you want to assess as additional questions). I believe that Google Forms are great as a standalone tool to collect data, but the real strength comes from the spreadsheet that gets populated from the submitted data.