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?