Skip to main content

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?


  1. And the thing is....

    .... each of these "Boos" encourages you to create a workaround that makes anything you build, that bit more shonky, and with each layer of shonky you get closer to making something that doesn't work and worse, can't be maintained. Not even by you the author.

  2. So much of the time we only blog about success - it is just as useful to blog about the stuff that doesn't work.

  3. I may have workaround for lots of these, but it involves kind of "starting again".... I'm now working on a "Process" sheet/system. May work...

  4. Great Work!!!
    This blog is very interesting and informative... i like this post and i feel very happy to read this article... Keep it up!!!
    thanks for sharing...
    More info:- Gmail Technical Support

  5. Our Expert help you when the speed of the internet connection over the Motorola router continuously drops out and they totally understand what all issues our customers may face. Visit our Site or you can call on our toll free number 1-877-778-8969


Post a Comment

Popular posts from this blog

Inserting A Google Doc link into a Google Spreadsheet (UPDATED 6/12/2017)

This article looks at using Apps Script to add new features to a Google Spreadsheet. At the University of York, various people have been using Google spreadsheets to collect together various project related information. We've found that when collecting lots of different collaborative information from lots of different people that a spreadsheet can work much better than a regular Google Form. Spreadsheets can be better than Forms for data collection because: The spreadsheet data saves as you are editing. If you want to fill in half the data and come back later, your data will still be there. The data in a spreadsheet is versioned, so you can see who added what and when and undo it if necessary The commenting features are brilliant - especially the "Resolve" button in comments. One feature we needed was to be able to "attach" Google Docs to certain cells in a spreadsheet. It's easy to just paste in a URL into a spreadsheet cell, but they can often

A Working Booking System In Google Sheets

Working with Andras Sztrokay we had another go at a booking system. This time it was to enable staff to book out a number of iPads over a number of days. You select the days you want, then select the Booking menu. Andras did an amazing job. It even creates a daily bookings sheet so you can see who has which iPads. To see this in action, go  here  and  File > Make a Copy (I won't be able to support you this is just provided to maybe give someone else a leg up, good luck!)

One-To-Many Relationship in a Google Spreadsheet

It's often the case that you want and need to be creating a database to store your data, but Google Spreadsheets are just so handy aren't they? But Google Spreadsheets are very good at relational data. Here's an example where, you want to have one column for the name of your recipe and another for the ingredients ( comma separated ). How you use this script is you click on the cell you want to be relational and choose the Admin > Show Relationship Editor. This opens up a dialog window showing you all the options included so far. You then alter the ingredients and it saves a comma separated list into the spreadsheet. Here's the spreadsheet . Use File > Make a copy to see it work and rummage around in the code. If anyone can help make the UI prettier I'd be grateful, thanks.