|Using Google App Script|
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 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:
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.
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.
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 "firstname.lastname@example.org", 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.
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 ...
- 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 ...