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

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 necessaryThe 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 all look too si…

Writing a Simple QR Code Stock Control Spreadsheet

At Theatre, Film & TV they have lots of equipment they loan to students, cameras, microphone, tripod etc. Keeping track of what goes out and what comes back is a difficult job. I have seen a few other departments struggling with the similar "equipment inventory" problems.

A solution I have prototyped uses QR codes, a Google Spreadsheet and a small web application written in Apps Script. The idea is, that each piece of equipment ( or maybe collection of items ) has a QR code on it. Using a standard and free smartphone application to read QR codes, the technician swipes the item and is shown a screen that lets them either check the item out or return it.

The QR app looks like this.

The spreadsheet contains a list of cameras. It has links to images and uses Google Visualisation tools to generate its QR codes. The spreadsheet looks like this.

The Web Application The web application, which only checks items in or out and should be used on a phone in conjunction with a QR cod…

Getting CSV data into Google Spreadsheets Automatically

Today I was attempting to get CSV data from Estates' Alarm System into Google Docs as a spreadsheet. There were two ways to try and achieve this...

Create an AppScript in Google that pulled a .CSV file from a web serverWrite a (python) script on the local machine that pushed the data into Google Spreadsheet by using the API. The Google AppScript Way As you know, my JavaScript ain't great, but it initially looked like it was going to work... Some code like this below and using the Array to CSV functions from here, looked promising.

function encode_utf8( s ){
//This is the code that "I think" turns the UTF16 LE into standard stuff....
return unescape( encodeURIComponent( s ));

function get_csv(){
var url =' BA Alarms.csv';// Change this to the URL of your file
var response = UrlFetchApp.fetch(url);
// If there's an error in the response code, maybe tell someone