Why is automating simple tasks so hard?

It should be easy to configure office software to perform repetitive workgroup tasks -- but it isn't, even in the Google Apps era

It seemed like such a simple thing: Make charts and tables and share them with the team.

If I had to do that only once in a while, it would be straightforward to do it in Google Apps. But I need to do this daily, with fresh data. I have a Python script that fetches the data and makes the charts and tables. I envisioned two ways to reuse that script:

Option #1: Just upload the charts and tables to Google Drive.

Option #2: Re-create the charts and tables in Google Apps sheet, and use my script to refresh the data in the sheet.

I tried it both ways, and every avenue I pursued led to frustration. Option #1 is the moral equivalent of uploading a file using FTP, a simple thing to accomplish in any programming language. So I looked up the API client library for Python at developers.google.com and found a page that said: "OAuth use cases: Abandon all hope, ye who enter."

Just kidding. Here's what it actually said:

Acquiring client IDs and secrets

You can get client IDs and secrets on the API Access pane of the Google APIs Console. There are different types of client IDs, so be sure to get the correct type for your application:

- Web application client IDs
- Installed application client IDs
- Service Account client IDs

My script just wants to FTP to the server. What type is that?

Seeking guidance, I install the highest-Google-ranked Python library for working with Google Drive, PyDrive. The QuickStart says:

Go to APIs Console and make your own project..

On Services' menu, turn Drive API on.

On 'API Access' menu, create OAuth2.0 client ID.

Select 'Application type' to be Web application.
Click on 'more options' in hostname settings
Input http://localhost:8080/ for both 'Redirect URIs' and 'JavaScript origins'.

Oh. My script is a "web application." Who knew?

Time passes while I install PyDrive and more dependent libraries than I can remember, and run the sample. Which, to authenticate to Google Drive, launches a local Web server and serves up a page where I do the OAuth dance interactively. The outcome is cached, so on subsequent runs of the sample, I don't need to interact with browser, but it still pops up to report successful authentication.

This will work on my machine, but I want to schedule my reporting script to run headlessly in the cloud. I could automate that OAuth interaction with Selenium WebDriver. But that's beyond geeky. I just want to FTP some files! There's got to be an easier way.

So I consider option #2: make the charts and tables in Google Apps and then periodically refresh the data in them. I haven't worked with the GData API in a long time, so I look it up at developers.google.com. Another OAuth flow, another Python library recommended for working with it. More time passes while I: install more dependencies; run the sample; build my Google Sheet by hand-importing data and then charting it; capture the Google id of the sheet; tweak the sample to push data into it. In the end I realize two things. The library can only delete one row at a time, which takes forever. And, even after laboriously refreshing the data that way, the chart doesn't seem to notice and redraw.

By now the message is clear. If I want to do these things, I should learn how to do them in the Google Apps scripting language. But that seems wrong. I already have code that makes the charts and tables. I just need to FTP to the server!

For now I've gone back to option #1, as silly as it is. The script is scheduled to run once a day. If I'm around when that happens, the browser pops up and gives its OAuth partner a whirl.

I'm reminded, again, that it's no fun to be a toolsmith in an environment that doesn't make life easy for toolsmiths.

Office suites have always paid lip service to automation but, for some reason, have never realized the dream. When they jumped to the cloud, I hoped that would change. But in my experience, both Google's and Microsoft's cloud suites are as tricky to automate effectively as Microsoft's desktop suite has always been.

For standalone resources, like the charts and tables I'm creating, it's no big deal. There's no real benefit to hosting them in GDrive. In fact, there are drawbacks. For example, the first Python charting library I tried produced SVG images that worked fine standalone, but wouldn't render properly in the GDrive viewer. Plain old FTP and HTTP are the right ways to share and access these kinds of resources.

But the cloud suites aim to be more than newfangled file servers. Their spreadsheets, in particular, invite us to imagine them as interfaces to data services that toolsmiths can manage and query programmatically. We need a simple and standard way to do that.

Copyright © 2015 IDG Communications, Inc.