Fun with Google App Scripts for Online Classes

Using Google App Script

So for a while I have been trying to work out if there was some way to have pages in Google Sites display content specific to the logged in user. I mainly wanted to do this so that students in my classes could look at a page in the Google Site I am using for a class, and see a personalized view of which assignments they had completed etc.

Previously I used a technique cribbed from a UH math professor which was to display a table of everyone's assignment statuses, anonymized using the last four digits of each students ID number. For a couple of years I did this in Google Sites by pulling in a view of a Google spreadsheet.

The Old Embedded SpreadSheet View

The advantage was that it gave everyone an overview of where everyone was in the class. The disadvantage was that it was difficult to read and see where you were up to as a student. I had one student make some specific criticisms, but whichever way I tried to import a Google spreadsheet it didn't seem to work very well. Before the start of this semester, I finally set some bounty on a question in StackOverflow:

http://stackoverflow.com/questions/12064019/how-to-have-google-sites-pages-display-things-specific-to-the-currently-logged-i

The simple answer was Google App Scripts, which I believe are a relatively recent development, but then again ... anyway I then wanted a simple example of using a Google App Script to pull some data from a Google spreadsheet and display it in Google Site using the Google Site login as a key to look up data in the spreadsheet:

http://stackoverflow.com/questions/12264255/example-display-of-google-spreadsheet-data-in-google-site-via-google-apps-script

For some reason this got a "-1" in StackOverflow :-( but I eventually worked it out myself.  Perhaps I was being lazy, but I really love to have working examples of the kind of thing I want to do before burning hours and hours discovering something can't be done.  So anyway I answered my own question on StackOverflow with a link to a working demo.

Certain things became clear, such as the only information I can get about a Google Sites user is their email and their timezone, and I think it is a Google Plus login rather than specifically a Google Sites login, but that is another story.  I don't clearly understand how, but it seems you can have another email address, e.g. yahoo or whatever, as your email login for Google.  Google Sites is a great tool in many respects, although not as flexible as Ning in terms of using JavaScript, but it's free and pretty reliable.  For me, now that I know how to use it, it is real easy, but it requires you to have a Google account to edit, and I have had real trouble inviting non-technical users in.

Anyhow, Google Spreadsheets is just great as a simple database, since it's got such a fantastically convenient spreadsheet editing ability, and you can use formulas etc. So anyway, I was excited to get a Google App Script grabbing user-specific data from a Google Spreadsheet and displaying it to a Google Site user.

It took me a while to find what I needed in the Google App Script documentation, which seems rather sparse at the moment, particularly in terms of what JavaScript syntax you can and can't use:

http://stackoverflow.com/questions/12279357/is-there-a-complete-definition-of-the-google-app-script-syntax-somewhere

You do all your Google App Script editing in the Google online editor which includes debugging and libraries and lots of things that I am just getting the hang of; but basically I was able to achieve what I wanted for this semester's courses with a display of assignment statuses based on the Google spreadsheets I am using to keep track of student's progress.

Some frustrations include that I can't log in as the students to check what they actually see, although I have now rigged up a bit of test in the script editor to check the output for each student, although I see it in debug form rather than the complete HTML page view so that's a bit of a pain.  Also my University has Google backed emails, so students might not see the assignments view if they are logged in with their University accounts.  The only available solution here appears to be laboriously asking all students for all the email addresses they might be logged in with, and adding them to the spreadsheet for each student so any login email will work as a key for the right data.


I would like to have a single script that I am editing, however the mechanism for DRYing out Google App Scripts is creating libraries, which is fine, but when a library is in development mode it takes a real long time to load (at least 10 seconds) which kills my debug cycle; so at the moment I am swapping the code back and forth between the two classes as I add features like support for optional assignments etc.

It's not ideal, it's not DRY and I have lost bits of code once or twice, but I have gradually refactored out the code so that it now works generically across weeks of assignments, and pulls the entire assignment specification from another sheet in the same spreadsheet.  Ultimately once it is stable perhaps I can fix it  as a versioned library ...


Another thing I have struggled with is that I can't catch errors and have to display them to the end user. At least I haven't worked out how to display errors more gracefully ...

Also worth noting is that the first time a user sees the script they will get a warning message from Google, and they need to accept the script that has been created by me as indicated by my email address "tansaku@gmail.com", which doesn't look very professional and may put some people off, but it only displays the first time, and after that it stays gone, so it's perhaps not such a big deal.

And of course I cannot end without a shout-out to Coursera/Udacity/EdX who's online courses have inspired me.  Their smooth javascript web interfaces for all of them are really what I'm aiming at.  I just don't want to have my courses in their closed boxes .... Freedom!!!!

Google Sites is now much more fully featured with the addition of Google App Script.  It's allowing me to develop a dynamic web application ON TOP of the existing Sites wiki and I basically outsource all the boring login/wiki stuff to Google.  The restrictions are some limitations on what I can do with the scripts and ultimately if this is to scale I will likely need to drop the Google Spreadsheet and move to some sort of cloud DB, but with relatively small class sizes, this is working okay for now, and I can't imagine how I could easily re-create the kind of interface I have to Google Spreadsheets.

Google's providing some great tools here, and I've been getting reasonably fast feedback on StackOverflow, but Google App Scripts are so new there's not much to find in Google searches, although I think that's changing.  I worry a bit about what kind of stack I'm buying into here, and I really wish Google had an IRC chat room where I could talk with the developers ...

TODO features

• Allow completed weeks to auto-collapse, and be re-opened on request - complicated by Google's only allowing some JavaScript libraries - looks like jQueryUI is the way to go here
• Allow students to upload their assignments directly through the script so there's no professor missing submitted work gaps - this looks like it is possible, but there are interface issues, particularly that while the google app script can generate HTML output it doesn't seem to be able to inherit the surrounding site CSS style, so at the moment it's not clear how I can get BOTH the convenience of being able to edit a page of assignments in google sites wiki syntax AND have appropriate upload and submission elements ...
• One of the particularly frustrating things is that while one can pass URL parameters that are coming in at the top level through to the script, the script plugin box that goes in the wiki to add the google app script can't have any parameters added to it directly.  This is why I was forced to go down the library route to share code.  Really the only difference between the scripts in different classes is just the google spreadsheet ID, and that could easily be specified at the point that the Google App Script was plugged into the Google Site, very frustrating that I can't specify it there.  If I want to have lots of little file upload boxes in my assignment page I would need some way of indicating which part of the assignment they are - and the only way at the moment will be to create a script library and then a new script for each upload box - how tedious ...
• Make the Spreadsheet dependency more robust.  At the moment I am relying on the Google Spreadsheet having a certain format with sheets having particular names.  If anyone else was going to use this I'd need to make it more robust ...
• Support for peer assessment so students can enter assessments of each others work
• Dynamically adjust the height of the Google app script plugin to fit the content I'm serving from the HTML templates
• Get my features into getsatisfaction or uservoice ...

p.s. many thanks to Alexis Brille for the great tick and cross icons - and interestingly all images get pulled through a Google proxy so I guess I get my own CDN for free?  At least I haven't worried about re-sizing the image myself yet, but perhaps I should ...