Updated Individual Math Quizzes Script

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);
}

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