Copying Spreadsheet with Google Documents List API
by Mike Levin SEO & Datamaster, 01/31/2012
Okay, one of the things I lost conceptually in recent months is to work in chisel-strikes. A day isn’t much time, and if you become distracted and let your mind get cloudy and your vision get fuzzy, the day slips by and you are no closer to your goal. You have to put yourself into a certain mindset that is very difficult to achieve. I think of it like how Dr. Watson observes Sherlock Holmes as picking up the scent of a clue and becoming like a bloodhound in pursuit of its quarry. Everything else has to fade into the background. All other priorities have to become low-priority, and myopic tunnel-vision has to set in. The think you’re currently working on has to become more interesting than anything else in your life that you may want to think about or do.
And yet, the very next step may be repulsively boring or intimidating. Therefore, it has to be reduced into a single chisel-strike. Contemplate it. Visualize the end result. Know how it’s going to contribute to the finished piece. And know that if you don’t actually strike away that piece of stone today to reveal the sculpture, you will be no closer to your goal at the end of the day than you were at the beginning—and there is shame in that—especially if you thought clearly about it toward the beginning of the day. This blog post is that clear thinking. And here is the problem.
I rely heavily on the Google Spreadsheets Data API, which is only one of various API’s used to interact-with and manipulate documents in Google Docs. Philosophically, the team that works on it doesn’t believe that visual formatting is the type of manipulation that you should do with such an API, and therefore even though I can use it to create documents from scratch, I can not make them visually look the way I want. I can’t even turn on or off line-wrapping, which can make a spreadsheet that’s auto-collecting data look really ugly.
The solution to this problem is to use the Google Documents List API (actually different from the Google Spreadsheets Data API) to copy-and-paste a pre-existing, pre-formatted spreadsheet as the starting point. I have resisted this, because it seems so much less pure than creating a new document every time. I like to think that my project has very few dependencies, like making sure a document named a certain thing resides in a certain directory—exactly the sort of thing that breaks apps when you want to instantiate lots of instances on different servers under different login accounts. But it’s pretty clear that I’m not going to get any formatting out of the Spreadsheets Data API, so I am forced to go this route anyway, and have to re-factor a little bit of code.
Okay, this is actually becoming fun BECAUSE I’m blogging it to the public as I go. The next step is to break out the code responsible for copying-and-pasting a new spreadsheet through Google Docs. This is precisely the hump that’s tough to get over. Re-factoring code that already works, because you need to make things slightly better according to criteria that is not your own, is a difficult hump to get over. No one likes to touch working machinery to make an improvement.
Okay, so 100% pure focus doesn’t exist. There are tons of little distractions fighting to get in at the edges: emails, text messages, social media sites, other projects… and gasp… yes, even the occasional phone-call. Sometimes it feels like one must be supernaturally disciplined to be productive in the modern age—open office environment or not. Once today is over, it’s lost forever. And it could have been exactly the day that made all the difference. Don’t lose that opportunity!
Okay, there are always 2 approaches to refactoring—in-location vs breaking out a stand-alone file. If you break out a stand-alone file, you isolate factors better, but you also have to transfer all the dependency stuff over. This is a place where functional programming shines over procedural, because there would not be any global state to have to carry over. Anyway, this is the documentation I need for copying documents with the Google Documents List API. As much as I like working with Google Docs as the user interface for API mashups, this documentation gets to why it can be frustrating and obtuse at times. They give the example:
duplicated_entry = client.Copy(source_entry, ‘MyTwin’)
…but that’s the only place they mention source_entry. So, there is an entry object that needs to be created, and it obviously represents a single already known document. So, we look elsewhere on the page for creating an instance of such a document, which will probably require its key, and we find this example under Exporting Word Processor Documents:
entry = client.GetDoc(‘document%3A0AdkQLChiemZjZ2M5’)
…which leaves us to wonder whether we should replace document with spreadsheet for making our call, and are we REALLY supposed to make the request with a pre-URL-encoded colon? Hrumph! Okay, I do things similar to this in my code already when I pick up where I left off in a document. Find that code. Okay, found. Isolate the code down to the minimal required to do what you need to do, in a new file. Okay, success. And here is that code:
import gdata.docs.client client = gdata.docs.client.DocsClient(source=myappname) client.ClientLogin(myemailaddress, mypassword, client.source) dockey = ‘*********************************’ resourceid = ‘document%3A’ + dockey entry = client.GetDoc(resourceid) dupe = client.Copy(entry, ‘MyTwin’)
Now I need only wrap this isolated example back into the main codebase. Today’s chisel-strike is not yet quite accomplished, but the most difficult part of it is done. There are very few unknowns now in this particular piece of getting my work ready for the public. This will be a very strong branding opportunity as well, because I will be able to incorporate in some graphics—although if I remember correctly, you can’t put a graphic in a Google Spreadsheet in the frozen row—the exact place where you would want to.