Collaborative Tools Project

BETTER VERSION HERE NOW I've had a number of requests for code examples for my Booking Spreadsheet. I have held off sharing the code mainly because it became very complex and wasn't very useful as a starting point for anyone else. But recently, I had to whip an Appointments spreadsheet together that didn't have personalised colouring of cells, that didn't create lots of sheets for a whole term, or permissions etc.  This spreadsheet is just a list of "Book me" links that passes some data through to a simple web form, and then saves the person's email into that cell's value. If you want to do something similar using this spreadsheet,  a. File > Make a Copy - to get your copy b. Tools > Script Editor > File > Manage Versions > Save New Version c. Publish > Deploy as web app - to copy your web app URL d. Change all the variables in the code, there's only a few e. Run the Setup code - to regenerate the ...

Yesterday was a difficult day with Google Apps. Almost everything I tried to create was stopped dead in its tracks. I am making some spreadsheets that support an approval workflow process and so far it had been going really well. I have a Google Form, that initiates the process. This keeps a list of the workflows. These are displayed in a web app. When the form is submitted, a copy is made of a master spreadsheet is made and the people fill in the data in that. At various points of completion, a controller person fires off scripts like "Initial Approval", "Approved by the Teaching Committee" and the relevant people are notified that they now need to "fill in their bits". So far so good. I began thinking that, at some points it would be really useful to assemble the relevant data, notes, reminders and links into a Gmail draft so that the controller person might check it over, add any extra message and then send it. But with Apps Script you can't...

Last week Google announced a number of new Apps Script features that have been added to Google Documents and Forms and Spreadsheets. The features themselves may not seem worth shouting that loudly about, but the ability to add Sidebars to documents and add menus and arbitrary user interface items that run Apps Script code means you can start to dream about how you could extend and combine these really powerful objects in new ways. Google Apps was already a collection of powerful objects ( Documents, Spreadsheets, Drive Files, Forms, Calendars, Sites ) that could be easily combined with Apps Script to create really useful applications, but with the arrival of these new features, the ability to combine them can be more elegant. And because you can create tools and interfaces within the documents you can extend the tools - rather than just combine them. We've already seen a demo of Bibsto , an Apps Script Bibliography manager that changes Google Documents into Research papers -...

I have just met with a colleague who is using Google Forms to improve the Ethics Approval process for research projects. They are taking a slightly different path to the one I might have plumped for, and are creating, on-the-fly Google Documents, filling in the appropriate sections and then sending them to people to add comments to etc. We thought he may have worked himself into a corner because although there is a function to capture when people submit a form ( onFormSubmit() ), there isn't one to capture if you "allow the user to edit responses" - there's no onFormUpdate(). We created a workaround by having a time-based trigger that just checks to see if the updated_timestamp is greater than the last_checked_timestamp. The best part, for me, of working with my colleague was when I referred at some point to my JavaScript skills being far from honed and he asked "So is this JavaScript then?" and laughed incredulously. He was happily coding away without...