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