Thursday, November 7, 2013

Speed Blogging: Detention Tracking System

I saw a post on one of my Google+ communities where +Patrick Reid was asking about a script for a Detention Tracking system based on students showing up for tutoring.  I just learned about how to write to a cell while writing a different script (that I will blog about later) so I whipped up a quick script to do what he was asking.

Here is the spreadsheet (you can make a copy): http://goo.gl/NCgXQY

When students don't show up for tutoring, the teacher will mark an "n" in the column. These students need to be assigned detention.

The script is run from the detentionTracker menu
The script will assign the student detention on the Detention List tab and will fill in the column to make sure they are only assigned 1 time (otherwise, they would be assigned detention every time the script was run).

Here is the script:

// The onOpen function is executed automatically every time a Spreadsheet is loaded
function onOpen() {
menuEntries.push({name: "Assign Detention to students who have not shown up for tutoring", functionName: "detentionTracker"});

}

function detentionTracker() {
var sheet = ss.getSheets()[0];
var detentionSheet = ss.getSheets()[1];

// Get the range of cells from that sheet
var studentInformationRange = sheet.getRange(2,1,sheet.getMaxRows()-1,sheet.getMaxColumns());

// 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
var currentStudentAttendTutoring = currentStudentObject.didTheyShowUp;

if (currentStudentAttendTutoring =="n") //if they did not show up (there is an "n" in the column) then put them on the detention list, otherwise continue on to the next student
{
if (currentStudentObject.detentionAssigned !== 'Yes, detention has been assigned.') //check to make sure they have not been assigned yet
{
//Find the first empty row, thanks to Don Kirkby at Stack Overflow at http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script
var column = detentionSheet.getRange('A:A');
var values = column.getValues(); // get all data in one call
var ct = 0;
while ( values[ct][0] != "" ) {
ct++;
};
var firstEmptyRow = ct+1;
// firstEmptyRow is the first empty row

//Fill the detention list starting with the first Empty Row in the detention Sheet.
var detentionFirstNameFillCell = String('A'+ firstEmptyRow ); // you can change the column it writes by changing the 'A'
var detentionFirstNameFill = detentionSheet.getRange(detentionFirstNameFillCell).setValue(currentStudentObject.firstName);
var detentionLastNameFillCell = String('B'+ firstEmptyRow );
var detentionLastNameFill = detentionSheet.getRange(detentionLastNameFillCell).setValue(currentStudentObject.lastName);
var detentionEmailAddressFillCell = String('C'+ firstEmptyRow );
//fill Detention Assigned Column on the tutoring list so the student is only added 1 time
var studentDetentionAssignedFillCell = String('E'+ (i+2) );
var studentDetentionAssignedFill = sheet.getRange(studentDetentionAssignedFillCell).setValue('Yes, detention has been assigned.');
}
else{
continue; //this is the else part of the if loop continue on to the next student, used when they need detention but have already been assigned.
};
}
continue; //this makes the else part of my if loop continue on to the next student, used when the student Attend Tutoring is blank
};

}

Friday, October 4, 2013

Using formRanger to update a Parent-Teacher Conference Scheduler

It is that time of the year. What time is that do you ask? Pumpkin Spice Latte time is what my Inbox is telling me from Starbucks...unfortunately they do not have the Pumpkin Spice Latte in Singapore so I need to move on and think of better times...like Parent-Teacher Conferences!

There is no better time to use the power of Google Automagic (to steal a line from +Jay Atwood ) in my mind than for scheduling Parent-Teacher Conferences. I hate the email exchange to find a common time because it never seems to work and takes forever to setup. So I spent my time instead creating a workflow that uses vlookup magic along with a Google Form. It went well last year, but the only bad part was that it would not eliminate a time choice when it was full.
Now we can programatically and scriptomagically change the question choices in a form using +Andrew Stillman's awesome formRanger script.
You can check out +Jay Atwood's great tutorial on formRanger here on Google+. It solves the problem of parents choosing a time slot that is no longer available.

Here's how:

Here is a template where you can make a copy: Get a copy of my template

I create a spreadsheet that has a list of my teacher times available:

This will later be used to create the schedule for the teacher to look at to see who they will be meeting.

Then I use the Concatenate formula to create all of the possible teacher times in 1 column in another tab in the sheet (column J in the picture below):
In the next column (K) I use the vlookup formula:

=if(isna(vlookup(J2,'Raw Data'!$C$2:$C512,1,false))=TRUE, " ", vlookup(J2,'Raw Data'!$C$2:$D$521,2,false)) Essentially, it looks in the form responses for the Timeslot and teacher choice. If the teacher hasn't been chosen yet, it leaves Column K blank, " ", If it has been chosen, it will paste the name of the person who chose that time slot (which comes from the next question on the form. The picture below shows this change: In Column L, I use vlookup again: =if(isna(vlookup(J3,'Raw Data'!$C$2:$C$520,1,false))=TRUE,J3," ") Essentially it looks in column K to see if it is blank. If so, then I copy the timeslot from Column J and it is allowed to be a choice on the form. If not, then I make it blank, " ". Then, I clean up the form choices. If I don't do anything the dropdown choices on the form questions will look like swiss cheese with all the blanks where the choices have already been selected. So, I clean it up by using the Unique formula to eliminate all but 1 blank in Column M: =UNIQUE(L2:L49) Now, use formRanger to push Column M out to the form question and set your time trigger to refresh the form on form submission and it will run automagically! Now that the form is working, I make my schedule back on the tab that it all started with and pasted this formula under the teacher heading at the times specified: =if(isna(vlookup(concatenate(B7," ",$C$5),'LC1 TimesAvailable'!$J$2:$K$193,1,false))=TRUE, " ", vlookup(concatenate(B7," ",$C$5),'LC1 TimesAvailable'!$J$2:$K$193,2,false)) In the picture below that formula found that Mr. McGowan Testing chose time 8:25 for Teacher 1 in the form and it pasted it in there. This fully completes the cycle. I use formula references so I just enter the Teacher Name in the Schedule tab and call those to populate the data for the form entries. Things I would like to improve: 1. Make a script to create a popup window with instructions 2. Create a google calendar to add events and publish the calendar with updates with each event 3. Create a script that will populate the template with the appropriate amount of teachers and the requisite data. 4. Somehow automate adding the columns to the formRanger script for questions, this might be a script or a beefier version of formRanger?? Any other suggestions/ideas, ask me on my Google+ page: gplus.to/MrMath or on twitter: @jmacattak. Monday, September 23, 2013 Speed Blogging: Script that will add the contents of a spreadsheet cell to the end of a Google Document Here is a script that will add the contents of a spreadsheet cell to the end of a Google Document that is specified in the Student File Key (here is a spreadsheet where you can Make a Copy): // The onOpen function is executed automatically every time a Spreadsheet is loaded function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = []; // When the user clicks on "writeToDoc Menu" then "Add the Student Note to the end of the Google Document given by the Student File Key", the function writeToDoc is // executed. menuEntries.push({name: "Add the Student Note to the end of the Google Document given by the Student File Key", functionName: "writeToDoc"}); menuEntries.push(null); // line separator // add more entries to the menu by using the method: menuEntries.push({name: "Menu Entry 2", functionName: "function2"}); ss.addMenu("writeToDoc Menu", menuEntries); } 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 from that sheet. Logger.log(sheet.getMaxColumns()); var studentInformationRange = sheet.getRange(2,1,sheet.getMaxRows()-1,sheet.getMaxColumns()-1); // 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. //find the end of the body of the document var body = doc.getActiveSection(); //get the note for the student from the spreadsheet var studentNote = currentStudentObject.studentNote; //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 seatProblem = body.appendParagraph(studentNote); }; continue; //this makes the else part of my if loop continue on to the next student, used when the Student File Key is blank } } Thursday, September 19, 2013 Using Doctopus to create additional connections with students in class +Andrew Stillman is a scripting demi-god. I have been using Doctopus in conjunction with gClassFolders. It has completely changed my workflow and my classroom. I have always used a lot of technology in my math classes, but Doctopus allows me to connect with my students in a meaningful way in real time. I have already detailed in an earlier blog post about how I launch assignments from the gClassHub to my students. Once my students have access to the document, they work on the assignment. Something unexpected happened at this step and changed my fundamental outlook on my teaching philosophy. I was clicking through the links on the Doctopus sheet to see the progress that my students were making and I was able to leave some comments for the students at points where they were making some mistakes or were unclear how to proceed. It was a great interaction and I was able to impact students in real time in their documents when they were reluctant to admit they needed help. Having this alternate way to interact with them was really cool! Speed Blogging: Google+ Hangouts Toolbox I blogged earlier about how much I am loving the Google+ Hangouts on Air for screencasting. One of the cool features is the Hangout Toolbox. Download this app to add some extras to your video, like your name bar, country flag, and anything else you want to show in the lower third. Speed Blogging: Using Google+ Hangouts On Air as a Screencaster One of the things that stops me from screencasting more is the software involved. I used to use Camtasia and Jing, but they both had their limitations and sometimes I forgot to upload them after the time it took to save the video (seemed like hours). I just started using Google+ Hangouts on Air and it is perfect for what I want to do. Just click on Hangout on Air: Then you are in your Google+ Hangout on Air! I don't invite anyone and just ScreenShare to make my video. It goes live on youtube while I make it and then it is saved on Youtube afterwards for anyone to see and me to share, no uploading!!! Speed Blogging: Creating a URLencoded text The Google Chart API wants URLencoded text. To create URLencoded text, I use Mathematica, but you can also type it directly in here.: http://meyerweb.com/eric/tools/dencoder/ Speed Blogging: Adding a menu item to a Google Spreadsheet by script Here is the code to add a Menu to a Google Spreadsheet by code: //creating the Menu in the onOpen function from: https://developers.google.com/apps-script/reference/spreadsheet/spreadsheet#addMenu(String,Object) // The onOpen function is executed automatically every time a Spreadsheet is loaded function onOpen() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var menuEntries = []; // When the user clicks on "addMenuExample" then "Menu Entry 1", the function function1 is // executed. menuEntries.push({name: "Add teachers that are going to be out to the Calendar", functionName: "subCalendarEditor"}); menuEntries.push(null); // line separator // add more entries to the menu by using the method: menuEntries.push({name: "Menu Entry 2", functionName: "function2"}); ss.addMenu("Substitute Calendar Editor", menuEntries); } Speed Blogging. How to type pretty math: Google Charts API to render LaTeX items Did you know you can still use LaTeX in Google? Google used to allow LaTeX directly into their Insert->Equation in Google Docs. Now that has been depricated. But, the fuctionality still exists (albeit in a limited scope) in the Google Chart API! Just enter the URL: http://chart.googleapis.com/chart?cht=tx&chl= and finish it off with the URLencoded LaTeX you want to use. Don't know LaTeX? You can use the snippets from the CodeCogs LaTeX Equation Editor. I have my math 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). Speed Blogging I have seen a lot of conference talks posted about speed geeking and some great 1 minute instructional videos on youtube. What holds me back from blogging more is creating fully functional, comprehensive blog posts. I am trying to blog more and sometimes I can't even find some smaller snippets of information that I really want (they are hidden in these long blog posts). So I am going to start "Speed Blogging". My goal is to have short blog posts with only 1 or 2 main points that are succinctly described (and will be easier for me to find in the future). Sunday, September 8, 2013 Sidebars in Google Docs So as of May 22, from the I/O Google (you can watch it here) released an API (or maybe an SDK, I am not positive of the acronym but it is the documentation for the classes and methods) for Documents and some other great stuff. I have been playing around with creating some scripts that are container bound (they can only be used in one product). One of these has been the sidebar creation in Documents. You can see here I created a sidebar called "My Custom Comments". You can also create your own menu items and I created a menu called "My Custom Comments Menu" as well. Here is the code I used to create the sidebar: //create a sidebar with width 300 called My Custom Comments. The inside of the sidebar is populated //by index.html function showSidebar() { var ui = DocumentApp.getUi(); var html = HtmlService.createHtmlOutputFromFile('index').setTitle('My Custom Comments').setWidth(300); ui.showSidebar(html); } Now the important part of this script to run is I need to add the html file to the script. I go to File->New->Html file. This is the really cool part!!! I can insert Html code into the sidebar content!!! Here is what I did with a quick snippet of code. You can see that the 4th line of code tells the html to run the method addParagraphSign() in the script that I have in the Code.gs. The html does limit some things, like iframe, embed, and some other things (unfortunately you still cannot embed Wolfram CDF files. Here are the methods addParagraphSign and addParagraph: //repeat this code for each custom comment you want to put just change the text in the quotes ' add a new paragraph here' function addParagraph(){ //From: https://developers.google.com/apps-script/reference/document/cursor // Insert some text at the cursor position and make it bold. var cursor = DocumentApp.getActiveDocument().getCursor(); if (cursor) { // Attempt to insert text at the cursor position. If the insertion returns null, the cursor's // containing element doesn't allow insertions, so show the user an error message. var element = cursor.insertText(' add a new paragraph here '); // possibly try to use .insertInlineImage to add an image blob if (element) { element.setBold(true); } else { DocumentApp.getUi().alert('Cannot insert text here.'); } } else { DocumentApp.getUi().alert('Cannot find a cursor.'); } } function addParagraphSign(){ //From: https://developers.google.com/apps-script/reference/document/cursor // Insert some text at the cursor position and make it bold. var cursor = DocumentApp.getActiveDocument().getCursor(); if (cursor) { // Attempt to insert text at the cursor position. If the insertion returns null, the cursor's // containing element doesn't allow insertions, so show the user an error message. // possibly try to use .insertInlineImage to add an image blob var pilcrowUrl = "http://www.merriam-webster.com/top-ten-lists/top-10-words-you-didnt-know-vol-2/top10_unknown2_pilcrow_sm.gif"; var pilcrowBlob = UrlFetchApp .fetch(pilcrowUrl) .getBlob() .setName("pilcrowBlob"); var element = cursor.insertInlineImage(pilcrowBlob); if (element) { } else { DocumentApp.getUi().alert('Cannot insert text here.'); } } else { DocumentApp.getUi().alert('Cannot find a cursor.'); } This is pretty cool to now have a Custom Menu for items to insert into a document inline by clicking on the button. (Thanks to +Michael Wacker for the #autoAwesome tip in the #gafesummit talk, take multiple screenshots and then upload to G+ to create the animated gif!!!) Saturday, September 7, 2013 Adding images into Forms with Scripts While I have been working on inserting images into a Doc, I have been playing around with the forms scripts APIs and I made a quick script to add images as questions (I use LaTeX coding pushed through the Google Chart API to make nice math type). It is a bit clunky, especially since image items aren't questions, but if you put a blank text item afterwards, it will be the response repository for the image item. Here is a link to the Form and my script is contained in the tools: https://docs.google.com/a/saisstudent.sg/forms/d/1385oZ3maxBYO3nPHmVBo2Tik2IKAPCSNoTtGq3pXFFk/edit?usp=sharing Here is the script: function addLaTeXItem() { // Open a form by ID and add a new image item from a URL var form = FormApp.openById('1385oZ3maxBYO3nPHmVBo2Tik2IKAPCSNoTtGq3pXFFk'); var problem = UrlFetchApp.fetch('http://chart.googleapis.com/chart?cht=tx&chl=4+x-3+y'); form.addImageItem() //add an image item .setTitle('Your Seat Problem: Please write your answer in the box below the problem') .setHelpText('Solve This Problem') // The help text is the image description .setImage(problem); //the actual image form.addTextItem() //puts a blank text question below so the student can answer the imageproblem } Tuesday, September 3, 2013 gClassHub saves me days of my life during 1 school year! In my last post I detailed how I begin the Googlefication of my classes using gClassFolders. The folder structure setup is awesome. But the real ROI for me comes from the gClassHub. I try to run almost all my assignments through my Google Drive and distributing these documents to students can be time consuming. Enter the gClassHub. What do you need to access the gClassHub? 1. You need to have used gClassFolders to setup your Google Drive structure. Once you run Create New Folders and Shares, an extra option comes up in the gClassFolders menu. Before: After: The new option of Get gClassHub URL will generate the URL for us. Click on this option and the popup tells you to click the link. Click the link. Sometimes you will get an error if you are logged into multiple Google email accounts. You can avoid this by doing 1 of 2 things: 1. Use the Incognito window. 2. **My preferred method** Use Google Chrome Users: Chrome User Setup Instructions. This will go to a website where you can choose your class and then launch 1 of the 3 prepopulated scripts. These scripts were created by +Andrew Stillman (who is awesome and has tons of great scripts). +Jay Atwood also has tons of great Youtube videos with walkthroughs of some of the scripts. Check out his awesome playlist of videos on scripts! Now you can Launch the script of your choice. In my next post, I will talk about Doctopus! gClassFolders Revisited So it is the beginning of the school year again and I have a bunch of new classes and new students that I want to share Google Docs with. The first step in my Googlefication process is to use gClassFolders to create a Google Drive structure that connects me to my students. In my book +Bjorn Behrendt and +Andrew Stillman are demi-gods for creating this script. The bonus from this is the creation of the gClassHub URL, which I will detail in a future post and is essential for my workflow. Here is how I setup my class (techwise) at the beginning of the year: 1. Start by running gClassFolders. You can also go here and File->Make a Copy: gClassFolder Google Doc 2. You will first need to Initialize the gClassFolders scripts by clicking Initial Settings on the header: You will need to Authorize the script to run during this step. 3. After Authorizing, nothing will happen. You need to click on Initial Settings again: 4. After clicking Initial Settings, the Popup window will ask you to choose I don't alter these settings at all and just hit Save. This will cause a script to run that sets up the Spreadsheet for you to enter the student information. It also creates another tab in the Sheet, but you will not need to do anything on this tab. It will also create new menu options under gClassFolder 5. Enter the student Data into the Spreadsheet. 6. Create new folders and shares from the gClassFolders list. This is where the White Magic happens (to quote +Jay Atwood ). You will see the script running and it will create a new Properties Tab (you don't need to mess with this at all) and then populate the sheet with all of the student names with a bunch of information about the folders it created for the students to share in your Google Drive (again, you don't need to worry about this unless you want to move the student or delete only that student). Now, you will have all of your students in a Google Drive structure where: 1. You have a folder for each class 2. Within each class folder, you have 4 folders, 3 of which are shared with students: • Class View-where each student in the class can only view any item put in the folder. • Class Edit-where each student in the class can edit any item put in the folder. • Assignment Folder-contains 1 folder for each student in the class. Only yourself and the student can edit the items in the folder. I put all of these class folders in a parent folder called 2013-2014 school year for better organization: This is a great way to disseminate information to students, especially utilizing other white magic scripts like Doctopus and following up with the Goobric grading rubric (a future post to come). 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. Tuesday, April 23, 2013 Creating a Parent Teacher Conference Scheduler using Google Spreadsheets and Scripts As I have stated earlier on my site, I love using Google Scripts. I created a workflow using Google Forms and Spreadsheets to create a Parent Teacher Scheduling Tool. The main engine of the scheduler is the FormEmailer Script and its ability to automatically run (a huge shout out to Henrique Abreu who developed the script!). Here are the steps I used to deploy the scheduler to the parent community. Resources needed: My Google Spreadsheet Template (with attached Form): Spreadsheet Template My Google Site Template to embed Published Sheet and Form: Site Template Short version of how I do it: 1. For each teacher, make a copy of the Template. • Change the times that teacher is available in the Scheduled Times Availability sheet • Edit the Form to reflect these times • Go into the Script Editor and set the Trigger to have the FormEmailer Script run automatically (only need to do this 1 time) • Publish the Scheduled Times Availability sheet to the Web. Copy the link to it. • Go to a Google Site and Paste the link and then Embed the Google Form below it. • Repeat for the next teacher 2. If you only wanted to have 1 schedule with all of your teachers, you could just put: Teacher Name, Wednesday, April 24 3:30-4:30 instead of Wednesday, April 24 3:30-4:30 It won't affect how the tool works as long as you put the EXACT choice in the form (I copy and paste to eliminate any typos or spaces missing). More detailed explanation of how I do it: 1. Click on the link to get my template: Template 2. Click on File->Make a Copy to get a copy of your own so you can Edit the spreadsheet. 3. Now you need to figure out the timeslots you want to be available for the conference. Go to the Schedule Times Availability sheet by clicking on that tab on the bottom of the sheet. 4. To change or add a timeslot, just type it into a cell on the Scheduled Times Availability sheet. You will need to copy the formula from cell B2 and enter it to the cell to the right of any time slot you add. This formula is what fills the timeslots with the parent's name: =if(isna(vlookup(A2,'Raw Data'!$G$2:$H$90,2,false))=TRUE," ",vlookup(A2,'Raw Data'!$G$2:$H\$90,2,false))

Essentially this formula looks at what is typed in the cell directly to its left. It then goes to the Raw Data sheet and looks for a match (the chosen timeslot, which can be found in column G on the Raw Data sheet) and copies from the Raw Data sheet the name of the person who requested that slot (the entry in column H).

It will only find the first instance of this time and will not write over it if a person requests it at a later point in time.

5. Now we want to add the timeslot choice into the Form so a parent can choose it. Click on
Form->Edit Form.
6. For the 1st question on the Form, where all of the time slots to choose from, enter all of the times from the Scheduled Times Availability sheet. The nice part of the new version of Google Forms is that it allows you to copy and paste a list from a spreadsheet to enter as values for the Choose From a List question type.
I create all of the available timeslots first and then just copy and paste them in.

7. If you want to collect any other data from your parents you can enter them as questions on the form and they will fill into the Raw Data sheet.

8. After you finish editing the Form you can close it and return back to your spreadsheet.

9. Everytime you copy the spreadsheet you need to enable the FormEmailer script to run. You can do this by clicking on FormEmailer->Settings:
The popup window will ask you to Authorize the script:
Once you click OK, you will be able to run the script.

10. Now that the script is live, you want to make it run automatically. The best way to do this is by using a Trigger. You can access the trigger by clicking on Tools->Script Editor to go to the script editor

From there, you click on Resources->Current Project's Triggers

You want to "Add a new Trigger"

You want to add a Time Driven trigger with a Minutes Timer that runs Every Minute.

I also like to add a notifications email in case anything goes wrong, it will email me Immediately.

Now your script will automatically run every minute!

11. What will the script do? It will send an Email that you will create in the FormEmailer->Setting tab that will fill in the email with data from the spreadsheet. More importantly, it will copy down a formula from Row Data rows G:I from the formulas typed in Row 2 in those columns. You can change this in the FormEmailer->Settings->Advanced tab if you would like:

In my template, I have a formula in G2 that just copies what the desired timeslot is for the form entry from the parent:

The formula in H2 is the Parent's Name:
And as a precaution, the formula in I2 is the order in which the parent submitted their form in case there are any discrepancies.

These formulas are essential to the tool running successfully since they are the columns that the Time Sheet looks into to fill the time slots.

12. The FormEmailer will automatically run and looks for the FormEmailer Status. If the Status is empty the script will run on that row data, so if you need to re-email a parent just delete the contents in that cell (only the FormEmailer status cell in that row) and it will re-run.

Now, your tool is ready to deploy. It should collect data from the Form and populate the Raw Data sheet. The FormEmailer Script will automatically run every minute on any row data where the Status is empty. When it runs, it will copy down the formula from G2, H2, and I2 into that Row. Then at the same time, the formulas on the Scheduled Times Availability sheet will look for any new times chosen and enter the parents name into the Time Slot.

13. Create the email message to be sent. FormEmailer will send an email as it processes a row. To create the email message click on FormEmailer->Settings
If you want to add data from the row, use the dropdown tab in the upper right to choose the data you want to use and click on the placeholder next to the appropriate box.

14. Now that you are done we need to make a way for parents to see the Scheduled Times Availability and then choose a time on the Form. I used a google sites page, but you can embed both on any webpage using HTML.
The main part is to publish the Scheduled Times Availability sheet to the web. Click on File->Publish to web.

I have found that it is most effective to publish all sheets and not just a specific sheet, it will update more seamlessly.
Once published, Google will automatically republish when changes are made but only about every 3 or 4 minutes, not instantaneously. This is why it might take a few minutes for the full process of a parent submitting a response to a form, getting the email, and seeing their name reflected in the Timeslot they chose.

Once you publish the sheets, you need to get the html link to access the cells you want to publish. In this case it is only the Scheduled Times Availability Sheet.
In the template case, the only cells I need to show are from A1 to D18 (if you add more times you will need to change this to display the appropriate timeslots)
Then, copy and paste the link to see the published cells. You can just paste this link in any browser (or embed in any webpage with html).

15. Now you are done! Create a webpage that shows the Available Times and then I put the Form below for parents to choose their preferred time slot.
Here is a template site: Template Site