More Updates to the Google Docs Writing Tracker

I recently pushed a new branch called “project-tracking” out to the Google Docs Writing Tracker on GitHub. This branch includes code for project-tracking that I wrote about a week ago. The changes have been working fine for me over the last 10 days or so. The one thing I haven’t done yet is update the template spreadsheet. The new code requires 2 new tabs in the spreadsheet, along with some additional settings. I’ll get to that eventually.

Meanwhile, I have been trying to figure out a way to simplify what happens each night the scripts do their processing. Right now, the scripts perform a comparison between the current working document, and a previous snapshot of the document in another folder. That snapshot mechanism takes up a lot of code, and is relatively inefficient. Over the last few weeks, I’ve been thinking about an alternative, and today, I tested that alternative out with positive results.

Every Google Document keeps a revision history of the changes to that document. Here is the revision history for a story that I worked on back in February:

Revision history

It turns out, that using the advanced Google Drive API, I can access the revisions through the API. Today I performed a test, which essentially compared the current document to the last revision of the previous day. That is essentially what the snapshot method that the script current uses does. But it does without needing to maintain two files. I can get all of the information I need from the previous revision. Ultimately, that simplifies the code for the scripts. It also simplifies setup.

There is a tradeoff, however.

You can only access the advanced Google Drive API via OAUTH2 authentication. That means configuring the scripts to be able to handle that authentication. It turned out to be a pretty straight-forward one-time setup for me, but I do this kind of thing for a living. For someone who isn’t technical, it may be a little tricker.

It will likely be a while before this major architectural change is available. There are several reasons for this:

  1. My priority each day is on getting my writing in. I do this scripting only if the writing is done, and I have time.
  2. If I were doing this just for me, it would be easy. The code I wrote today checks for the last revision from “yesterday” and compares that to the current document. Simple, right? But not everyone who uses these scripts writes every day. What happens if you skip some days. Then there is no revision from “yesterday” so the script has to know to look for the previous revision regardless of date. There are a few other uses cases that need to be considered as well.
  3. Once I have the code written, I like to test it for a few weeks before pushing it out, just so that I can work out any kinks.

That said, once this feature is in place, I think it will make for an enormous improvement. Since everything, including the revisions, is contained in the one document, there will no longer be a need to manage a snapshot folder at all, and all of that code can go away.

It also opens up the possibilities for analytics on the evolution of a document over time, which would be pretty cool, too.


  1. Hi Jamie,

    I have a “use case” that I’m not sure you’ve run across yet. I’ve been having trouble with the code running via a trigger. What happens is that it’s not picking up the new or updated stuff I’ve written for the day. It works fine, however, if I run the code manually. This had led me to believe that it is using the wrong date during the triggered version of the code. I think this happened because I manually ran the code (more than once) without turning test mode on, and it is storing the date that it has already ran for (?). Sorry, my grammar’s getting a little out of whack trying to explain this.

    Here’s an example. In the middle of the day, I realize that the code didn’t run last night. So, I run it manually, because I want to know how much I wrote the day before. I then continue on with my day and write some more. Later that night, when the code runs automatically, it does not pick up that I have written other stuff that day. Even if I delete the date and data in the spreadsheet that was already recorded.

    I’m not sure if this will be a problem with the revisions that Google Drive already keeps track of, but, then again, maybe it will.


    P.S. Other than that little issue, I love the code. It is one of the most useful bits of automation I’ve ever had the pleasure of using. And, it’s saved me hours trying to develop my own similar code to work with Evernote.

    1. Cassie, I haven’t seen this issue reported before. If it works when you run it manually, but not from the trigger, I’d ask two things:

      1. Is the trigger time set to run on the same day on which the changes are made. For instance, I have my trigger set to 11 pm – midnight. The script itself looks for files modified on the same day. So if your trigger is running at, say 1-2 am, then it won’t find anything because no files would have been updated (unless you were writing after midnight).

      2. Is the timezone on the script set correctly? There are instructions in the README for checking this.

      Those are two things that come immediately to mind.

  2. Hey Jamie,
    Thanks for getting back to me so quickly.

    Answers to numbered questions are below.

    1. When it was set up, it was set to 11pm-midnight. It’s not now because I got tired of it giving me false data. It did work a couple of times, then it didn’t, so I turned it off.

    2. I just checked and the timezone is set up correctly.



This site uses Akismet to reduce spam. Learn how your comment data is processed.