Math Tech Tips: March 2014

I have been working on the updated Individual Math Quizzes Script and have made a lot of tweaks in the different incarnations.

Here is a spreadsheet with the most updated script: http://goo.gl/bfnmjC

Here is a quick video tutorial I made with Hangout On Air describing my workflow:
http://www.youtube.com/watch?v=5cJGHRI_L-Y

Some of the changes I have made after implementing it a few weeks ago in my classes. What the script does:

1. It creates individualized math quizzes for each student in your list.
2. You can then email students the URL of their quiz.
3. It will then grade the responses that students have submitted by marking them correct or incorrect. It will color the problems they got correct Green and the Incorrect ones Red. This was a huge help for me to see at a glance how the students performed.
4. It has an option to send an email to the students containing the Step By Step solutions to the problems they missed.
5. Then it will take the students response and copy it to a master grade sheet for each student.

I will have a series of blog posts that detail each step in the near future. I also will be slowly posting a repository of spreadsheets prepopulated with questions for anyone to use.

Here is the script (an unwieldy 648 lines of code, I am sure it could be cleaner but I am only a teacher :) :

/// The onOpen function is executed automatically every time a Spreadsheet is loaded
function onOpen() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var menuEntries2 = [];
menuEntries2.push({name: "Populate Step By Step Solution Cells", functionName: "getStepByStepSolution"});
ss.addMenu("Step By Step Solution Menu", menuEntries2);
var menuEntries = [];
menuEntries.push({name: "Create Individualized Quizzes", functionName: "numberOfProblemsUi"}); //runs the numberOfProblemsUi which asks for the number of problems
menuEntries.push(null); // line separator
menuEntries.push({name: "Send Students the URL of the Quiz by Email", functionName: "sendEmailsContainingQuizURL"});
menuEntries.push(null); // line separator
menuEntries.push({name: "Pull Most Recent Student Response from Quiz, Grade them, and Send and Email if wanted", functionName: "sendEmailsWithPullResponsesUi"});
ss.addMenu("Individualized Quiz Creator Menu", menuEntries);
var menuEntries3 =[];
menuEntries3.push({name: "Send Graded Results to Student Gradesheet", functionName: "sendResultsToMasterGradeSheet"});
ss.addMenu("Send Graded Results to Student Gradesheet", menuEntries3);

}

//generate the STepbystep solutions urls

function getStepByStepSolution() {
//get the student data from the spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Questions');
var studentInformationRange = sheet.getRange(2,1,sheet.getMaxRows()-1,sheet.getMaxColumns());
var studentObjects = getRowsData(sheet, studentInformationRange);
var lastEmptyOnColumnA = sheet.getRange("A1:A"+sheet.getLastRow()).getValues().join(",").replace(/,,/g, '').split(",").length; //from: http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script

//get the student info by row from the student sheet
for (var i = 0; i < (lastEmptyOnColumnA - 1); ++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.
Logger.log(currentStudentObject);
var stepByStepSolutionRegEx =currentStudentObject.stepbystepsolutionregex;
Logger.log(stepByStepSolutionRegEx);
var stepByStepSolutionFileName = stepByStepSolutionRegEx + '.stepByStepSolution.jpg';
Logger.log(stepByStepSolutionFileName);
var file = DocsList.find(stepByStepSolutionFileName)[0];//need to escape the \
var fileID = file.getId();
var fileURL = file.getUrl();
Logger.log(fileURL);
//write the fileURL in each row in column I (9th column)
var writeStepByStepSolutionURL = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Questions').getRange(i+2, 9).setValue(fileURL);

}

}

//ask for the number of problems
function numberOfProblemsUi() {
var numberOfProblemsApp = UiApp.createApplication(); //create app with help from here: https://developers.google.com/apps-script/guides/ui-service#DisplayingSpreadsheet
var title = numberOfProblemsApp.setTitle("Number of Problems Selection");

var grid = numberOfProblemsApp.createGrid(1, 2); //create a grid with 1 row and 2 columns
grid.setWidget(0, 0, numberOfProblemsApp.createLabel('How many problems do you want to make on your Quiz?'));
grid.setWidget(0, 1, numberOfProblemsApp.createTextBox().setName("numberOfProblems")); //set the ID to the user input to numberOfProblems

var submitButton = numberOfProblemsApp.createButton('Submit');
var handler = numberOfProblemsApp.createServerHandler('createForm'); //run createForm when the submit button is clicked
submitButton.addClickHandler(handler);
handler.addCallbackElement(grid); //these allow us to call back the info from the panel in the fundtion createForm

var numberOfProblemsPanel = numberOfProblemsApp.createVerticalPanel(); //create panel to show app
numberOfProblemsPanel.add(grid); //add the grid we just created to our panel
numberOfProblemsPanel.add(submitButton); //add the button to the Panel
numberOfProblemsApp.add(numberOfProblemsPanel); //add the panel to the app

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //get the spreadsheet
spreadsheet.show(numberOfProblemsApp); //show the app on the spreadsheet
}

//run createform after the Ui for number of problems

function createForm(e){
//get the student data from the spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var studentInformationRange = sheet.getRange(2,1,sheet.getMaxRows()-1,sheet.getMaxColumns());
var studentObjects = getRowsData(sheet, studentInformationRange);
//find the last Empty Row on Column C (to find the end of the names and stop the function
var lastEmptyOnColumnC = sheet.getRange("C1:C"+sheet.getLastRow()).getValues().join(",").replace(/,,/g, '').split(",").length; //from: http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script

//get the number of problems from the UiApp
var numberOfProblemsApp = UiApp.getActiveApplication();
var numberOfProblems = e.parameter.numberOfProblems;
Logger.log(numberOfProblems);

// You can access e.parameter.userName because you used setName('numberOfProblems') in the Ui for number of problems script and
// also added the grid containing those widgets as a callback element to the server
// handler.

//get the student info by row from the student sheet
for (var i = 0; i < (lastEmptyOnColumnC - 1); ++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 studentLastName = currentStudentObject.studentLastName;
var studentFirstName = currentStudentObject.studentFirstName;
var studentEmailAddress = currentStudentObject.emailAddress;

//on the first time through, create the column for Form Creation Verification and Email Results Verification
if (i==0 && SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(1,1).getValue()=="Student First Name")
{
var createFormVerificationColumn = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').insertColumnBefore(1);
var createFormVerificationHeader = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(1,1).setValue("Form Created?");
var createEmailSentColumn = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').insertColumnBefore(2);
var writeEmailSentColumnHeader = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(1, 2).setValue("Email Sent?");
}

//get the value from the formCreationColumn
var formCreationConfirmationValue = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange((i+2),1).getValue();

if (formCreationConfirmationValue=="Form Created"){//create the form only if the confirmation value is not there
var doNothing = "foo"; //can't figure out what to put here to make it skip over... tried continue; and return; maybe just ;?
}
else{
// Create a new form, then add image item followed by a blank text item for the student response from https://developers.google.com/apps-script/reference/forms/
var form = FormApp.create('New Quiz for ' + studentFirstName + " " + studentLastName);

//find out where the form is
var publishedURL = form.getPublishedUrl();
var editURL = form.getEditUrl();
//write the URLs to the spreadsheet on the Student Data sheet in column D (4th column) and E (5th column) respectively
var rowNumber = i + 2;
var publishedURLColumn = 6;

var writePublishedURL = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(rowNumber,publishedURLColumn).setValue(publishedURL);
var editURLColumn = 7;
var writeEditURL = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(rowNumber,editURLColumn).setValue(editURL);

var problemCellsStart = 8; //starting in column G

for (var j=0; j < parseInt(numberOfProblems); j++)
{
//find the question from column G on the Questions sheet (Need to randomize later)
//find the answer from column C on the Questions sheet (Need to randomize later)
//find the stepByStep Solution URL from column I on the Questions sheet (Need to randomize later)
var problemCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Questions').getRange('G'+ (i+j+2)).getValue();
var answerCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Questions').getRange('C'+ (i+j+2)).getValue();
var stepByStepSolutionCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Questions').getRange('I'+ (i+j+2)).getValue();

//add the question to the form
var problem = UrlFetchApp.fetch(problemCell);
form.addImageItem() //add an image item
.setTitle('Please write your answer in the box below the problem')
.setHelpText('Solve This Problem') // The help text is the image description ****Need to change this later
.setImage(problem); //the actual image with the question in LaTeX format
form.addTextItem(); //puts a blank text question below so the student can answer the imageproblem

//write the problem to the spreadsheet
var colNumber = parseInt(problemCellsStart)+parseInt(j)+2;
//on the first time through, write the headers for the Problems
if (i==0)
{
var writeHeaderCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(1,colNumber).setValue('Problem Number '+ (parseInt(j)+1));
}
//write the problem to the spreadsheet
var writeProblemCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(rowNumber,colNumber).setValue(problemCell);
Logger.log("problemCellsStart = " + problemCellsStart);
//write the answer to the spreadsheet
var problemAnswer = answerCell;
var colNumber = parseInt(problemCellsStart)+parseInt(numberOfProblems)+parseInt(j)+2;
Logger.log("colNumber for Problem " +j+"answer ="+colNumber);

//on the first time through, write the headers for the Answers
if (i==0)
{
var writeHeaderCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(1,colNumber).setValue('Answer to Problem Number '+ (parseInt(j)+1));
}
var writeAnswerCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(rowNumber,colNumber).setValue(problemAnswer);

//write the step by step solutions URL to the spreadsheet

//write the answer to the spreadsheet
var stepByStepSolutionURL = stepByStepSolutionCell;
var colNumber = parseInt(problemCellsStart)+2*parseInt(numberOfProblems)+parseInt(j)+2;
Logger.log("colNumber for Step By Step Solution URL for Problem Number " +j+" = "+colNumber);

//on the first time through, write the headers for the Step By Step Solutions
if (i==0)
{
var writeHeaderCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(1,colNumber).setValue('Step By Step Solution URL for Problem Number '+ (parseInt(j)+1));
}
var writeStepByStepSolutionURLCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(rowNumber,colNumber).setValue(stepByStepSolutionURL);
}

//write a confirmation in the cell if a form was created
var writeFormCreationVerification = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange((i+2),1).setValue("Form Created");
}
}
}

//send the email containing the form

function sendEmailsContainingQuizURL() {

//get the student data from the spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var studentInformationRange = sheet.getRange(2,1,sheet.getMaxRows()-1,sheet.getMaxColumns());
var studentObjects = getRowsData(sheet, studentInformationRange);
//find the last Empty Row on Column A (to find the end of the names and stop the function
var lastEmptyOnColumnA = sheet.getRange("A1:A"+sheet.getLastRow()).getValues().join(",").replace(/,,/g, '').split(",").length; //from: http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script

//get the student info by row from the student sheet
for (var i = 0; i < (lastEmptyOnColumnA - 1); ++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 studentLastName = currentStudentObject.studentLastName;
var studentFirstName = currentStudentObject.studentFirstName;
var studentEmailAddress = currentStudentObject.emailAddress;
var studentQuizURL = currentStudentObject.urlOfPublishedForm

MailApp.sendEmail(//send the email
{
to: studentEmailAddress,
subject: "Please go to this site to find your problems. The quiz might not have your name on it on the top though :) ",
htmlBody: studentQuizURL,
inlineImages:
{

}
});
}

}

//the Ui to ask if they want to send emails as they pull responses

function sendEmailsWithPullResponsesUi() {
var sendEmailsWithPullResponsesApp = UiApp.createApplication(); //create app with help from here: https://developers.google.com/apps-script/guides/ui-service#DisplayingSpreadsheet
var title = sendEmailsWithPullResponsesApp.setTitle("Send Emails when you pull responses?");

var grid = sendEmailsWithPullResponsesApp.createGrid(1, 2); //create a grid with 1 row and 2 columns
grid.setWidget(0, 0, sendEmailsWithPullResponsesApp.createLabel('Do you want to send emails when you pull the responses? Type YES if you do, NO if you do not'));
grid.setWidget(0, 1, sendEmailsWithPullResponsesApp.createTextBox().setName("emailSendQuery")); //set the ID to the user input to emailSendQuery

var submitButton = sendEmailsWithPullResponsesApp.createButton('Submit');
var handler = sendEmailsWithPullResponsesApp.createServerHandler('pullResponses'); //run pullResponses when the submit button is clicked
submitButton.addClickHandler(handler);
handler.addCallbackElement(grid); //these allow us to call back the info from the panel in the function pullResponses

var numberOfProblemsPanel = sendEmailsWithPullResponsesApp.createVerticalPanel(); //create panel to show app
numberOfProblemsPanel.add(grid); //add the grid we just created to our panel
numberOfProblemsPanel.add(submitButton); //add the button to the Panel
sendEmailsWithPullResponsesApp.add(numberOfProblemsPanel); //add the panel to the app

var spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); //get the spreadsheet
spreadsheet.show(sendEmailsWithPullResponsesApp); //show the app on the spreadsheet
}

//pullResponses after the Ui asking if you want to send emails or not

function pullResponses(e){

//get to send emails or not from Ui
var sendEmailsWithPullResponsesApp = UiApp.getActiveApplication();
var sendEmailOrNot = e.parameter.emailSendQuery;
Logger.log(sendEmailOrNot);

// You can access e.parameter.emailSendQuery because you used setName('emailSendQuery') int the Ui for number of problems script and
// also added the grid containing those widgets as a callback element to the server
// handler.

//close the app
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
sendEmailsWithPullResponsesApp.close();
spreadsheet.show(sendEmailsWithPullResponsesApp);

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheets()[0];
var studentInformationRange = sheet.getRange(2,1,sheet.getMaxRows()-1,sheet.getMaxColumns());
var studentObjects = getRowsData(sheet, studentInformationRange);
//find the last Empty Row on Column A (to find the end of the names and stop the function
var lastEmptyOnColumnA = sheet.getRange("A1:A"+sheet.getLastRow()).getValues().join(",").replace(/,,/g, '').split(",").length; //from: http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script
//get the headers row
var headerRowObject = sheet.getRange(1,1,1,sheet.getMaxColumns()).getValues();
//find out how many questions there were by looking at the headers written by createForm
for (var iterator = 0; iterator < sheet.getMaxColumns(); iterator++)
{
if (headerRowObject[0][iterator]== "Problem Number 1")
{
var problemStartCell = iterator;
}

if (headerRowObject[0][iterator]== "Answer to Problem Number 1")
{
var problemEndCell = iterator;
}

}

var totalNumberOfProblems = problemEndCell - problemStartCell;

for (var i = 0; i < (lastEmptyOnColumnA-1); ++i)
{
var currentStudentObject = studentObjects[i] //get the ith row of data from the studentObjects array
var studentLastName = currentStudentObject.studentLastName;
var studentFirstName = currentStudentObject.studentFirstName;
var studentEmailAddress = currentStudentObject.emailAddress;

var formURL = currentStudentObject.urlOfPublishedForm; //get the URL of the form for the student
// Open a form by URL and log the responses to each question. from: https://developers.google.com/apps-script/reference/forms/form-response
var form = FormApp.openByUrl(formURL);
var formResponses = form.getResponses(); //get all of the responses
for (var k = 0; k < formResponses.length; k++) {
var formResponse = formResponses[k]; //get each response (this is a submission)
var itemResponses = formResponse.getItemResponses(); //get all of the item responses
//to track the number of correct responses
var totalNumberOfStudentCorrectResponses = 0;
//to create the array for the stepByStepSolutionURLs for the incorrect problems
var stepByStepSolutionUrlArray = [];
//go through all of the item responses for a form response
for (var j = 0; j < itemResponses.length; j++) {
//the first time through, write the Student Response Headers
if (k==0)
{
var writeHeaderCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(1, j+problemStartCell+1+3*totalNumberOfProblems).setBackground("#66FFFF").setValue("Student Response to Problem Number " + parseInt(j+1)).se;

}

var itemResponse = itemResponses[j]; //take each item response 1 by 1
//now write the student response into the spreadsheet

var writeStudentAnswerCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, j+problemStartCell+1+3*totalNumberOfProblems).setValue(itemResponse.getResponse());
//compare the student answer to the problem answer and get the URL of the stepByStepSolution to the problem
var correctAnswer = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, j+problemEndCell+1).getValue();
var studentAnswer = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, j+problemStartCell+1+3*totalNumberOfProblems).getValue();
var stepByStepSolutionUrl = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, j+problemStartCell+1+2*totalNumberOfProblems).getValue();

if (studentAnswer == correctAnswer)
{
var gradedProblemResult = "Correct";
//turn the cell with the correct answer green
var setCorrectBackground = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, j+problemStartCell+1+3*totalNumberOfProblems).setBackground('Green');
totalNumberOfStudentCorrectResponses = totalNumberOfStudentCorrectResponses + 1;
Logger.log(totalNumberOfStudentCorrectResponses);
}
else
{
var gradedProblemResult = "Incorrect";
//turn the cell with the incorrect answer red
var setIncorrectBackground = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, j+problemStartCell+1+3*totalNumberOfProblems).setBackground('Red');
stepByStepSolutionUrlArray.push("Step By Step Solution for Problem Number "+ parseInt(j+1)+" : "+stepByStepSolutionUrl+"<br>");

}
//write the Graded Problem Result.
//the first time through, write the Graded Result Headers
if (k==0)
{
var writeGradedResultHeaderCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(1, j+problemStartCell+1+4*totalNumberOfProblems).setBackground('#FFFF99').setValue("Graded Result for Student Answer to Problem Number " + parseInt(j+1));

}
var writeStudentAnswerGradedResultCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, j+problemStartCell+1+4*totalNumberOfProblems).setValue(gradedProblemResult);
//after the last one, write the total number correct.
if (j==itemResponses.length-1)
{
var writeTotalCorrectHeaderCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(1, j+problemStartCell+2+4*totalNumberOfProblems).setValue("Total Correct Answers for Student");
var writeTotalCorrectSumCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, j+problemStartCell+2+4*totalNumberOfProblems).setValue(totalNumberOfStudentCorrectResponses);
var setStudentSubmissionBackgroundToLightGreen = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, 1).setBackground('LightGreen');//set the Student Name to Light Green to show they have submitted a response
}
}
}

var stepByStepSolutionUrlArrayString = JSON.stringify(stepByStepSolutionUrlArray); //idea from here: http://stackoverflow.com/questions/16453891/how-to-get-spreadsheet-data-into-clientside-array-via-htmlservice-google-app-sc

//use the response to send the email if set to YES. ****Change later to a radio button selection or depress a button

if (sendEmailOrNot == "YES" && SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, 2).getValue() != "Email Sent" && SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, 1+problemStartCell+1+4*totalNumberOfProblems).getValue()!="")//if they have not gotten the email yet and they have submitted a response (it will grade it if they submitted a response), send it
{

MailApp.sendEmail(//send the email with the step By Step Solutions URLs
{
to: studentEmailAddress,
subject: "Step By Step Solutions to the Incorrect Answers for "+ ss.getName() ,
htmlBody: "Here are the URLs of the Step By Step Solutions for the problems you missed. You might have gotten some of them correct, but the syntax might have been different than what the computer was looking for though :) <br><br>"+stepByStepSolutionUrlArrayString,
inlineImages:
{

}
});
//write the email Success Verification
var writeToCellForSuccessfulEmailSent = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Student Data').getRange(i+2, 2).setValue("Email Sent");
}
else{
continue; //they already got an email sent, so don't send it again
}
}
}

//send results to student grade sheet

function sendResultsToMasterGradeSheet() {
//get the student data from the spreadsheet
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName('Student Data');
var studentInformationRange = sheet.getRange(2,1,sheet.getMaxRows()-1,sheet.getMaxColumns());
var studentObjects = getRowsData(sheet, studentInformationRange);
var lastEmptyOnColumnC = sheet.getRange("C1:C"+sheet.getLastRow()).getValues().join(",").replace(/,,/g, '').split(",").length; //from: http://stackoverflow.com/questions/6882104/faster-way-to-find-the-first-empty-row-google-apps-script

//get the student info by row from the student sheet
for (var i = 0; i < (lastEmptyOnColumnC - 1); ++i)
{
Logger.log("i= "+i);
var currentStudentObject = studentObjects[i]; //get the ith row of data from the studentObjects array
//Get the student name
var currentStudentFirstName = currentStudentObject.studentFirstName;
Logger.log(currentStudentFirstName);
var currentStudentLastName = currentStudentObject.studentLastName;
Logger.log(currentStudentLastName);
//name of the file we are looking for ** Change for each unit***
var studentMasterGradeSheetFileName = currentStudentFirstName + " " + currentStudentLastName + " Grade Sheet";
Logger.log(studentMasterGradeSheetFileName);

//check to see if student has submitted a response. If so, it will have been graded
var currentStudentCheckForGradedWork = currentStudentObject.gradedResultForStudentAnswerToProblemNumber1;
Logger.log("currentStudentCheckForGradedWork= "+currentStudentCheckForGradedWork);
if(currentStudentCheckForGradedWork==""){
var skip = "foo"; //need to figure out how to make it iterate empty. have tried continue; and return; try ;?
}
else{

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

var file = DocsList.find(studentMasterGradeSheetFileName)[0];//finds the first one with the name of the studentMasterGradeSheetFileName
var fileID = file.getId();
var fileURL = file.getUrl();
//find the last row in the Master Grade Sheet
var masterGradeSpreadsheet = SpreadsheetApp.openById(fileID);
var lastEmptyOnColumnAInMasterGradeSheet = masterGradeSpreadsheet.getRange("A1:A"+sheet.getLastRow()).getValues().join(",").replace(/,,/g, '').split(",").length;
Logger.log(lastEmptyOnColumnAInMasterGradeSheet);

//write the student data row from the assignment sheet to the master grade sheet for each student

var getDataToWriteToGradeSheet = sheet.getRange(i+2,1,i+2,sheet.getMaxColumns()).getValues();
//Logger.log(getDataToWriteToGradeSheet);
// Logger.log(getDataToWriteToGradeSheet[0][0]);
//Logger.log(ss.getName());
//Logger.log(getDataToWriteToGradeSheet[0].length);

//get the header row data to write to the gradesheet
var getHeaderRowToWriteToGradeSheet = sheet.getRange(1,1,1,sheet.getMaxColumns()).getValues();
//Logger.log(getDataToWriteToGradeSheet[0][0]);
//Logger.log(ss.getName());
//Logger.log(getDataToWriteToGradeSheet[0].length);

for (var j=0; j< getDataToWriteToGradeSheet[0].length; j++)
{

var writeAssignmentNameToGradeSheet = masterGradeSpreadsheet.getSheets()[0].getRange(lastEmptyOnColumnAInMasterGradeSheet, 1).setBackgroundColor('Gray').setValue("Assignment Name");
var writeHeaderRowToMasterGradeSheet = masterGradeSpreadsheet.getSheets()[0].getRange(lastEmptyOnColumnAInMasterGradeSheet, (j+2)).setBackgroundColor('Gray').setValue(getHeaderRowToWriteToGradeSheet[0][j]);
var writeAssignmentNameToGradeSheet = masterGradeSpreadsheet.getSheets()[0].getRange(lastEmptyOnColumnAInMasterGradeSheet+1, 1).setValue(ss.getName());
var writeToMasterGradeSheet = masterGradeSpreadsheet.getSheets()[0].getRange(lastEmptyOnColumnAInMasterGradeSheet+1, (j+2)).setValue(getDataToWriteToGradeSheet[0][j]);
}
}
}
}

//---------------------------------------//---------------------------//

//Url encoder
//Thanks to Ahab from here:http://productforums.google.com/forum/#!topic/docs/U52IqYPziaU
//This custom function will UrlEncode the cell
function encodeUrl( r ) {
if( r.constructor == Array ) {
var out = r.slice();
for( i=0; i< r.length; i++){
for( j=0; j< r[i].length; j++){
out[i][j] = encodeURIComponent(r[i][j].toString() ) ;
}
}
return out ;
}
else{
return encodeURIComponent(r.toString() )
}
}

////////////
//Taken from the Google Tutorials: https://developers.google.com/apps-script/articles/mail_merge#section5

// getRowsData iterates row by row in the input range and returns an array of objects.
// Each object contains all the data for a given row, indexed by its normalized column name.
// Arguments:
// - sheet: the sheet object that contains the data to be processed
// - range: the exact range of cells where the data is stored
// This argument is optional and it defaults to all the cells except those in the first row
// or all the cells below columnHeadersRowIndex (if defined).
// - columnHeadersRowIndex: specifies the row number where the column names are stored.
// This argument is optional and it defaults to the row immediately above range;
// Returns an Array of objects.
function getRowsData(sheet, range, columnHeadersRowIndex) {
var headersIndex = columnHeadersRowIndex || range ? range.getRowIndex() - 1 : 1;
var dataRange = range ||
sheet.getRange(headersIndex + 1, 1, sheet.getLastRow() - headersIndex, sheet.getLastColumn());
var numColumns = dataRange.getEndColumn() - dataRange.getColumn() + 1;
var headersRange = sheet.getRange(headersIndex, dataRange.getColumn(), 1, numColumns);
var headers = headersRange.getValues()[0];
return getObjects(dataRange.getValues(), normalizeHeaders(headers));
}

// For every row of data in data, generates an object that contains the data. Names of
// object fields are defined in keys.
// Arguments:
// - data: JavaScript 2d array
// - keys: Array of Strings that define the property names for the objects to create
function getObjects(data, keys) {
var objects = [];
for (var i = 0; i < data.length; ++i) {
var object = {};
var hasData = false;
for (var j = 0; j < data[i].length; ++j) {
var cellData = data[i][j];
// if (isCellEmpty(cellData)) {
// continue;
// }
object[keys[j]] = cellData;
hasData = true;
}
if (hasData) {
objects.push(object);
}
}
return objects;
}

// Returns an Array of normalized Strings.
// Empty Strings are returned for all Strings that could not be successfully normalized.
// Arguments:
// - headers: Array of Strings to normalize
function normalizeHeaders(headers) {
var keys = [];
for (var i = 0; i < headers.length; ++i) {
keys.push(normalizeHeader(headers[i]));
}
return keys;
}

// Normalizes a string, by removing all alphanumeric characters and using mixed case
// to separate words. The output will always start with a lower case letter.
// This function is designed to produce JavaScript object property names.
// Arguments:
// - header: string to normalize
// Examples:
// "First Name" -> "firstName"
// "Market Cap (millions) -> "marketCapMillions
// "1 number at the beginning is ignored" -> "numberAtTheBeginningIsIgnored"
function normalizeHeader(header) {
var key = "";
var upperCase = false;
for (var i = 0; i < header.length; ++i) {
var letter = header[i];
if (letter == " " && key.length > 0) {
upperCase = true;
continue;
}
if (!isAlnum(letter)) {
continue;
}
if (key.length == 0 && isDigit(letter)) {
continue; // first character must be a letter
}
if (upperCase) {
upperCase = false;
key += letter.toUpperCase();
} else {
key += letter.toLowerCase();
}
}
return key;
}

// Returns true if the cell where cellData was read from is empty.
// Arguments:
// - cellData: string
function isCellEmpty(cellData) {
return typeof(cellData) == "string" && cellData == "";
}

// Returns true if the character char is alphabetical, false otherwise.
function isAlnum(char) {
return char >= 'A' && char <= 'Z' ||
char >= 'a' && char <= 'z' ||
isDigit(char);
}

// Returns true if the character char is a digit, false otherwise.
function isDigit(char) {
return char >= '0' && char <= '9';
}

// setRowsData fills in one row of data per object defined in the objects Array.
// For every Column, it checks if data objects define a value for it.
// Arguments:
// - sheet: the Sheet Object where the data will be written
// - objects: an Array of Objects, each of which contains data for a row
// - optHeadersRange: a Range of cells where the column headers are defined. This
// defaults to the entire first row in sheet.
// - optFirstDataRowIndex: index of the first row where data should be written. This
// defaults to the row immediately below the headers.
function setRowsData(sheet, objects, optHeadersRange, optFirstDataRowIndex) {
var headersRange = optHeadersRange || sheet.getRange(1, 1, 1, sheet.getLastColumn());
var firstDataRowIndex = optFirstDataRowIndex || headersRange.getRowIndex() + 1;
var headers = normalizeHeaders(headersRange.getValues()[0]);

var data = [];
for (var i = 0; i < objects.length; ++i) {
var values = []
for (j = 0; j < headers.length; ++j) {
var header = headers[j];
values.push(header.length > 0 && objects[i][header] ? objects[i][header] : "");
}
data.push(values);
}

var destinationRange = sheet.getRange(firstDataRowIndex, headersRange.getColumnIndex(),
objects.length, headers.length);
destinationRange.setValues(data);
}

////////////////////////

Math Tech Tips

Friday, March 14, 2014

gMath Add-on

Wednesday, March 12, 2014

Google Lunch for March 12, 2014

How to create an Add-on menu in the new Spreadsheets

Monday, March 10, 2014

Google Lunch

Thursday, March 6, 2014

Embed an http:// URL into a Google Edu Site

Parsing CDF files from Wolfram Education's Website to Embed into Google Edu Sites

Updated Individual Math Quizzes Script