Thursday, May 23, 2013

More Scripting Success!

Now that I had a script that would work from specific values inputted, I wanted to make it more robust to read in data from the spreadsheet automatically. This was going to require some looping. I have some programming experience from college A LONG TIME AGO, but the basics are the same. It took me a couple of google searches to find the argumentation and I was able to find some examples of loops in scripts to steal from as well.
The main bug that came up for me a few times was using the wrong variable, like trying to call studentObjects.problemURL when studentObjects is a massive array without a value for problemURL. Once I realized this, I made a quick change to call the currentStudentObject and then could find currentStudentObject.problemURL.
Also, it was nice to find the for loop documentation from the google tutorial and use the studentObjects.length to have my ending, so it runs through all of the data in the Named Range that I specified in my spreadsheet.
The other difficult part for me was once it was running and accepting the Student File Key, it would work and write that cell to the Document. But, if the cell was empty the script would stop running. I did some googling to find the "continue" part and to use the ! as the negation (if the cell is not empty).
This still was not working and the debugger helped me out A LOT. The debugger told me that I was looking to see if the cell contained " ", when really it was "" (no space). Once I made this change it would work.
I am starting to get a better handle on the Javascript notation and coding flow so I am excited to continue to work on my scripting applications.
The current state of my script is below and it is an working phase now. The only change I am going to make is to add a column to my spreadsheet titled Next Class Date and add that in the end of the script so it will add the next class date in replacement of the ##-##-##.
My next post will detail how I will deploy Doctopus to create a Seat Problem Document for all of my students and copy it into their Assignment folders already created by gClassFolders. Then I will copy those File Keys into my Seat Problem Spreadsheet. This should write the problems into the specified student files and we should be good for a test run for class on Monday!

Copy of Current Script, feel free to Make a Copy :)
Current script code (sorry it is not in a great format, I just copy and paste it from the Script Editor):
***********

function writeToDoc() {
  //Try to use a named range to pull the problemURL out of each row data. from:   //https://developers.google.com/apps-script/guides/sheets#reading
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // Get the range of cells that store student data.
  var studentInformationRange = ss.getRangeByName('StudentData');

  // For every row of student data, generate a student object.
  var studentObjects = getRowsData(sheet, studentInformationRange);

  for (var i = 0; i < studentObjects.length; ++i)
  {
   var currentStudentObject = studentObjects[i]  //get the ith row of data from the studentObjects array
 
   //This fetches a document by ID (found in the URL) and opens it up.
   var docID = currentStudentObject.studentFileKey;
    if (docID!=="") //if the Student File Key exists then use it otherwise, continue on to the next student
    {
     var doc = DocumentApp.openById(docID); //open the document specified by the Student File Key //column entry
 
     //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 = currentStudentObject.problemUrl; //replace from student problem from column //named Problem URL in the student Data row.
     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);
      };
    continue; //this makes the else part of my if loop continue on to the next student, used when the      //Student File Key is blank
   
   }
}

Wednesday, May 22, 2013

Scripting struggles and a working Script!

I have been struggling with my script as I have been looking for too broad a use. I have a specific use in mind and have been trying to make it useful for all occurances. Once I made this mental switch, the problem was not as difficult and the major change I made from previous failed attempts was to name the range in my sheet to make it easier for my script to find.

As you can see, I tried to steal from previous scripts and the Google Developers tutorials are awesome. So here is my updated code that works to read a spreadsheet cell that contains website URL that contains my LaTeX problem and writes it to the body of the Google Doc.

It works for a given row (highlighted by the var currentStudentObject = studentObjects[0] command).
Next, I will run this through a loop to do it for each student and the Document Key will be part of the Spreadsheet Column Data as well. I will also make the Date get pulled from the spreadsheet and will make it look better in the document.

(As a side note, by putting // in the code, everything after it on that line is considered text and not code, so I put reminders and notes in there for myself).
*********************


function writeToDoc() {
  //Try to use a named range to pull the problemURL out of each row data. from:  https://developers.google.com/apps-script/guides/sheets#reading
 
var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheets()[0];

  // Get the range of cells that store student data.
  var studentInformationRange = ss.getRangeByName('StudentData');

  // For every row of student data, generate a student object.
  var studentObjects = getRowsData(sheet, studentInformationRange);
 
  var currentStudentObject = studentObjects[0]  //get the 0th row of data from the studentObjects array, to do: run a loop to do this for every student
 
  //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 = currentStudentObject.problemUrl; //replace from student problem from column named Problem URL in the student Data row.
 
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);
 
}

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:


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.