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

In move to pwn boys, Girl Scouts to introduce game developer badge | Ars Technica

And so I break my blogging silence this week (life happens) with this article from Ars Technica. Why? Because we’re raising three Girl Scouts and it’s good to know that a group is doing this if the girls have an interest (sure, this is out West, but still).

On the heels of the Boy Scouts introducing a merit badge for game design, the Girl Scouts are going one better by developing a badge program to bring girls into the world of video game development. The effort is a collaboration between Girl Scouts of Greater Los Angeles and Women in Games International.

[…]

The Boy Scouts introduced a Game Design merit badge in March, but its description doesn’t specifically call for actual game software development. The WIGI-GSLA effort will use Gamestar Mechanic, an educational development tool from E-Line Media, to allow scouts to develop and create their own video games.

via In move to pwn boys, Girl Scouts to introduce game developer badge | Ars Technica.