A Simple Example Booking Project in Google Spreadsheets

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 "Book me" hyperlinks to point at your new web app

f. Tools > Script Editor > File > Manage Versions > Save New Version - to update the app

g. Publish > Deploy as web app 

....and as simple as a,b,c,d,e,f,g you will have your own Booking System. 

Don't expect any help with this, you're on your own. No, really. It's only really meant for people used to working with Apps Script and spreadsheets.

Here is the spreadsheet link.

Anyway, it looks like this... 

By: Unknown On 4:01 AM

Continue Reading

A Difficult Day With Google Apps ( Boo! )

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 create a GMail draft message. Boo 1!

So I thought, OK, what if I use a Google Doc as a simple template with %SomeTags% in so that the people running this process could easily author these emails. The %SomeTags% in the document could be replaced with data from a Named Range in the their spreadsheet.  I discover that with with Apps Script there is no way to get a list of named ranges. Boo 2! 

Next, after experimenting with attaching scripts to buttons in the spreadsheet, that might say "Approve" I thought I'd have a go at cleaning up the interface by putting all the workflow actions in tidy menus with sub menus. I discover that you can't create sub-menus in spreadsheet menus.  I take the argument that this might be better interface design but still, Boo 3!

So I then think that maybe I can show the menu items based on the user's memberships, so that people only get to see the menu items based on their needs. I think I discover that the user would need to run the onOpen() function and authorize it for it to work. Boo 4!

I then remember our security guy's advice that allowing people to edit spreadsheets AND scripts is a bit hokey. There is no way to allow people to edit spreadsheet data and not the code in them. Boo 5!

I then thought I'd create a "Home" sheet in my spreadsheet, which would lookup the workflows status from the list spreadsheet using the =ImportRange() formula. I discover that if I delete a row in the listing spreadsheet that ImportRange brings back the wrong status. It's sort of hard-wired to a particular cell. Boo 6!

What a day! And as Oliver Reed might utter, What a lot of boos!

Google do seem to be tightening up some of the consistency of their Apps Script features lately, with varying results. And they seem to be listening to "developers" like myself and producing tools that support what they want to do rather than features that look nifty in a demo ( like that one about copying a bullet list in a Google Doc straight into a Google Form multiple choice question ) but all of these boos are things that other people have asked and argued well for.

So come on Google, how about searching Stackoverflow for "Google Apps Script" and "Can't do that" and "according to the documentation". There's a wealth of common-sense and small but valuable features that people are expecting to be there.

But first can you get on and let me do this....

var ss = SpreadsheetApp.getCurrentSpreadsheet()
var ranges = ss.getNamedRanges()

That's not a lot to ask is it?

By: Unknown On 2:57 AM

Continue Reading

Google Apps, New Possibilities for Old Tools?

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 - with added tools for adding references and citations.

We are going to see a flourishing of new custom-made add ons to Google Apps, mark my words. For example, Martin Hawksey has already been trying to create a "Document Map" feature, ala Word. There's a feature I'd love... and if only Martin and I want it, so be it.

More importantly, there's a high chance you might create some innovative tool yourself that makes Google Documents work the way you want them to. We'll see add ons for writers or for educators or for marketers or cheese makers or just you.

Ooooh, and it makes me wonder...

One of my long-standing criticisms of Google, from a UX perspective, is that many of their innovations seem isolated from the other ones - meaning simple features get implemented in one product, but not another creating an overall usability glitches of nothing working quite as you'd expect. For example, how search works, drag & drop, document ownership models, commenting, API access etc are just some of the things that work one way in one product and work differently in another.

The future is already here in Google Apps land, it's just not evenly distributed.

And so, whilst it's great that Google are starting to make scripting features available across two or three products, it does make you notice where the gaps are.

Google, being Google, often fail to see what they're sitting on and cock things up. For example, Google Wave should not have been a product, it should have been the real-time commenting system for all Google products. If delivered well, you would have barely noticed its existence ( except maybe you'd notice how appalling other systems were in this regard ).

So, What Should Google Do?

Mind The Gaps

There is something to the "small pieces loosely joined" idea that has legs. Originally it applied, I think, to blogs and RSS and other web2.0 tools that you could "wire together" to create newer, bigger, more complex things. It also applies to the ideas behind unix and maybe Galls Law ( "A complex system that works is invariably found to have evolved from a simple system that worked" ). Google seem to be creating a compelling landscape of tools wired together using Apps Script.

Google simply need to look at what they have and ask "What if the good stuff was everywhere?".

So, where are Presentations and Drawings apps in the "small pieces loosely joined" mix? They're noticeably missing. Now I imagine most people don't really use Google Presentations and Drawings much. I see most people still using Powerpoint ( uploading .ppxs into Google Drive ) and traditional desktop based graphics tools.

Except, what if you could add scripts to shapes in Google Presentations? Scripts that took the user to the next slide, for example, or went to a slide based on which button you clicked. You'd be able to create a narrative experience, or a quiz or a mini learning object that branched in all sorts of directions. Kids could use it.

And what if, like you can in Google Sites, you could insert videos and documents, or get data via Apps Script and put it into fields on screen? You would have an interface builder anyone could use. It would be like an online HyperCard - a tool with which people could pull various resources together and make them work the way they want ( without serious programming ).

If you could script the Drawing app you might be able to create simple animations, or maybe simple visualisations.

And the thing is, all that functionality is sort of sitting there already. It doesn't need much in the way of design, it just needs someone to connect the dots.

And no, Google Presentations or Drawings might not be the *best* tool for creating presentations in, but if they were scriptable, like Documents, they'd suddenly become a new thing, loaded with possibilities, rather than an old thing hanging about being slightly embarrassing.

I think this process of making sure that your innovations touch all parts of your product range is a cheap one to conceive and implement - it's dealing with lots of known knowns.

There are dozens of "Mind The Gap" innovations Google could make that I can guarantee would initiate a huge wave of creativity using Google Tools. How do I know? Well, people are just like that.

By: Unknown On 7:08 AM

Continue Reading

Improving Ethics Approval in Research With Google Forms and Spreadsheets

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 even knowing what language he was coding in. Brilliant.

One thing I'm starting notice over the last few weeks and months is how many people are working on processes... and/or process improvement. I keep meeting people with visual flow charts of their ideal worlds.  I wonder how far we should encourage Google Apps hacking ( where you potentially can get almost exactly what you want ) or whether we should be looking at tools that explicitly handle processes, like RunMyProcess ( shown below ) which even have their own tools to design your workflows and a gazillion integration plugins.

But then there's also an overlap with CRM and service desk applications. In most cases there's just stuff that requires stuff to happen that involves people to do stuff - which is pretty much what a CRM might do isn't it?

So, I'm wondering, given that I keep coming across people interested in creating workflows, should I be looking into a workflow lite app or heavyweight state of the art workflow system? Or should we keep crafting ( or crufting ) our own version?

By: Unknown On 7:43 AM

Continue Reading