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