Automatic Google Spreadsheet Wordcount Updates from Scrivener

This post has been a long time in the making, namely because although it is just a series of hacks, it technically counted as “code” according to my employer, so I needed approval to share it. Honestly, this isn’t code, it’s just a collection of shell hacks, repurposed code someone else wrote, and me, stringing it all together. I must officially add, Literature and Latte, the Scrivener developers, and anyone else associated with the project had nothing to do with this, didn’t consent to this, and probably will shudder and cry when they see the hackery. OK, on to the original post I wrote when I did this back in July and it was all a bit more relevant. For the record, I’m still running the scripts nightly, even if my process has changed a little to meet my current needs.

Early this summer, Jamie Rubin published his collection of scripts for automatically updating a Google spreadsheet with the word count from his writing in Google docs. Jamie is a science fiction writer and icon for paperless automation (I think it says that under his “Evernote Ambassador” plaque), so I was quick to take a peak at his scripts to see how easy they were implemented. It took me a few tries, but eventually I got them working, got all of my short stories in progress moved to Google docs, and had a few days of working with the setup.

On the one hand, it’s pretty slick to have metrics in a spreadsheet auto-update every night. What I’ve quickly discovered, though, is that I don’t like working in Google Docs. They’re great for work, or for that quick one off document I need to generate, but when it comes to fiction writing I find it missing features on the iPad app, and on the desktop it still strikes me as a bit clunky. Plus, it just didn’t work into my (evolving) work methodology.

What I threw together one morning was this – and caveat lector! – it works for me. I’m sharing because that’s what you do with this kind of knowledge, but I have no illusions of this being the best or most mature approach, nor that what I have here is even pretty. Bash and python coders alike will look at this mess and just weep uncontrollably for hours at the way I thrashed lines of code together like a baboon with a sledgehammer. While the concept came from Jamie, it was Matt Cutt’s page on using python with google spreadsheets that I obviously lifted and reused (after long being a critic of any language that uses space indentation as a structural element), so credit is as credit due. My butchering of concept and languages is not the fault of these two folks. I had a long, convoluted version of this written using Perl and a backup directory like Jamie, but ended up abandoning it as overly complicated. That said, this is, I cannot stress enough, a cheat that happens to work for now.

Prerequisites

  1. I assume you are already using Scrivener.  
  2. Specifically, Scrivener on a Mac – you could probably get this working on Windows if you have the right tools handy, and of course on Linux it’s a no-brainer.
  3. There is no number 3.
  4. I assume you have some familiarity with the command line and related concepts (cron, vi, etc.) – I’m not trying to be rude, but since this isn’t a packaged solution, even if I could teach you those concepts I’m not sure you’d want to be mucking around with this unless you’re already comfortable under the hood a little bit.

Set Up

In Google: You’ll need a spreadsheet. I assume your first row is a date column and your second row is for word counts. When you are looking at your spreadsheet in a browser, your URL will be something like:

https://docs.google.com/spreadsheet/ccc?key=SomeLongHashOfNumbersAndLetters#gid=0

Grab that SomeLongHashOfNumbersAndLetters between key= and #gid=0 – we’ll need that later.

Because I didn’t know how to do this in python, I resorted to the Sys Admin’s favorite quick hack tool, bash. This script simply runs through your Scrivener documents, pulls out the line that interests us, and keeps a tally. When it’s done, it prints that tally back.

[code language=”bash”]

#!/bin/bash
export IFS=’

DATE=`date “+%Y-%m-%d”`

wordcount=0
for file in `find /Path/To/Your/Scrivener/Saves -name “*.scrivx” -type f`; do
words=`grep “Words=” “$file”| grep $DATE|sed -e “s/.*Words=”//” -e “s/”.*//”`
if [ ${words} > 0 ]; then
wordcount=$(($wordcount + $words))
fi
done
printf “$wordcount”

[/code]

The following is the python code I’m using, which calls the word count script and updates my google doc.

[code language=”python”]
#!/usr/bin/python

import os
import time
import gdata.spreadsheet.service
import distutils.core

email = ‘YOURName@gmail.com’
password = ‘YourPassword’

# Get word counts first – this points to the script above
run = os.popen(‘/Path/To/Your/scripts/get_words’)
words = run.read()

spreadsheet_key = ‘SomeLongHashOfNumbersAndLettersFromAbove’
worksheet_id = ‘od6’

spr_client = gdata.spreadsheet.service.SpreadsheetsService()
spr_client.email = email
spr_client.password = password
spr_client.source = ‘Fiction Tracker’
spr_client.ProgrammaticLogin()

dict = {}
dict[‘date’] = time.strftime(‘%m/%d/%Y’)
dict[‘words’] = words
print dict

entry = spr_client.InsertRow(dict, spreadsheet_key, worksheet_id)
if isinstance(entry, gdata.spreadsheet.SpreadsheetsList):
print “Insert row succeeded.”
else:
print “Insert row failed.”

#clean up by copying everything from the current tree over into the archive tree
distutils.dir_util.copy_tree(source_dir,archive_dir)
[/code]

In order for this code to work, of course, I needed to do a few things behind the scenes. Mac’s come with python installed, but I did have to install the latest copy of the gdata-python module, which can be found here. I would have preferred to do all of this in one language (say, perl), but the perl modules for talking to google weren’t installing cleanly, and I didn’t want to break my mac for the sake of some perl.

Wrap all of this up in a bow (cron job, something like 58 23 * * * /path/to/python-script >/dev/null 2>&1) and you’re all set. Then clean up this code into something more reasonably usable, because this is a mess. But, it achieves our end – at the end each day (at 11:58 PM in my example) our script kicks off, tallies what Scrivener believes are the new words for a day, and inserts that sum into our spreadsheet, Fiction Tracker. What you do from there is your own adventure.

Enhanced by Zemanta

linux rant (blame Charles Stross)

[Quick Edit] Just in case, the “blame” is for making me think. In this day and age, however, its worth disclaiming that up front so there isn’t any misinterpretation. [/Quick Edit]

Had a good chuckle this morning reading Charles Stross’ blog (yes, that’s what I was doing instead of working on the practice novel, or organizing the six million stamps that are out of order and uncatalogued, or even instead of checking in on the sickly child). Talking about Xandros on the EEEpc, Stross comments:

Xandros didn’t so much tame the beast[linux] as lobotomize it and give it a Mickey Mouse hat to conceal the bandages.

This had me in stitches, probably due in part to the fact that I spent some time yesterday “sneaking” around and checking in on Gentoo, and in part for the social guilt I feel over the fact that I actually don’t have a linux workstation any more, any where (not really).

The latter is actually easier to explain than it might sound – while I dabble at writing, I have discovered that I really, really, really love using yWriter, which unfortunately only runs on Windows with the .Net framework (and I’ve tried running it under wine, and no, it doesn’t work, not completely). So I find myself compelled to use windows on my laptop/netbook so that I can keep using the novel building software I’ve chosen. At work, it became a matter of practicality – in the end, now that I waft near middle management, there is a convenience and time factor for just using a corporate image of windows and running linux in a vmplayer, where if it goes down oh well (I no longer have the luxury of spending 6 hours on trying to figure out why upgrading part X impacted parts Y and Z to present me with an unusable system).

But I won’t deny missing that life. Even seeing the list of bugs that tove is dealing with for the perl folks in Gentoo (begging the other question, how did it all fall on tove??), and the fact that despite being out for a year or two now, 5.10 still isn’t in the official tree. There was a certain joy and sense of living life at least in sight of the frontier working on a project like Gentoo. I am a geek; unfortunately, I am not the kind of geek that something like Gentoo needs (but I could be…I scored a 26 on the test I posted about earlier ;), and frankly I don’t have the time and sense of commitment that a project like that needs.

But looking at my netbook, and my aged refurbished laptop, I do wish I had a working command prompt. Mostly late at night, or when I wish I could just do a quick find/grep (without the hassles of cygwin). And knowing that the cd/dvd drive on this emachines laptop would start working again as soon as windows is off of it doesn’t help.

How did I get so tied to a stinking piece of software in the first place? Does yWriter really offer me everything I think it does, or am I being lulled by its ease of use? I’m using windows right now because for the most part, I don’t have to dick around with anything, It Just Works(tm). Mostly. Except for the whole cd/dvd thing. And the toolset. And the fact that the only windows-only software I’m using is yWriter – otherwise its all Abiword and web browsers (ok, so I am using Chrome, which isn’t available on linux yet, but that’s minor). 

Meh.

either the dumbest or most brilliant email i’ve ever sent…

I have to fess up – i just sent what will either be the most laughable email folks ever read, or things will work out and i won’t feel so stupid (’bout this anyway). to sum up the gist: i just emailed a ton of other distro perl maintainers (sun, mandriva, fedora, debian, freebsd) with this crazy idea about putting together a site to pool our patching resources at – a way for distributors of perl and perl modules (not cpan – next tier, the linux/bsd/solaris distributors) to share patches and lessons learned in getting packages working sanely, to avoid reduplication of effort and (didn’t say this in the email, it sounds too…wishy washy) foster a common community between vendors.

i still feel like a dolt for sending it, but the idea pops into my head every few months, and this time i figured it should either be shot down or not for once and all.