Tag Archives: google docs writing tracker

A Heatmap of Over 900 Days of Writing Data from My Google Docs Writing Tracker

A few days ago, I mentioned that I was looking to add a Github-style heatmap feature to the Google Docs Writing Tracker code. Well, I’ve got something to show for it. Keep in mind that I am still experimenting, and none of this code has been checked into the Google Docs Writing Tracker repo as of yet. But, here is what all of my writing data looks like going back over 900 days:

900+ Days of Writing Data

For each year represented above, the rows are days of the week (top row is Sundays, bottom row is Saturdays), and the columns are weeks of the years.

The scale goes from 0-250 words (the lightest green) to 1,500+ words (the darkest green). You’ll also note that in July 2013, there are two white cells. Those are the only days that I had no writing. The last day, July 21, 2013, was 770 days ago. I have not missed a day since then.

This was relatively easy to do thanks to the Cal-heatmap JavaScript library. After installing the library files, I exported my writing data (dates and words counts) to a JSON file. Once the JSON file was created, the rest was easy. The entire rendering of the heat maps looks like this:

Heatmap Code

The bulk of the code is customizing how I want the heatmaps to look. Now that I have the look I want there is only one more thing to do, and that is to automate the process of generating the JSON file from the Google Docs Writing Tracker spreadsheet. With that done, anyone who uses the Google Docs Writing Tracker will be able to render a heatmap like the one above.

You can see my writing heatmap in action. If you hover over the cells, you’ll get the word count for that day. Check it out, play around with it. Let me know what you think.

ETA (8/31 @ 1:30 pm): I managed to automate the process of generating the JSON file from the Google Docs Writing Tracker spreadsheet. In the near future, I’ll post the code to a new repo on Github since it isn’t directly related to the code for the Google Docs Writing Tracker itself.

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.

Working on a new feature for the Google Docs Writing Tracker: Projects!

I have started work on a new feature for my Google Docs Writing Tracker: Project Tracking. My goal with this feature is to close the loop of manual tasks that I tend to perform around my writing process. These are are two-fold:

  1. Tracking the progress on writing projects
  2. Tracking the “ROI” on my writing projects

Right now, I’m focusing on just the first of the two. As a freelance writer, I am sometimes given a deadline for a project, and I sometimes have to set my own deadline. Either way, I spend some of my time informally tracking my progress. If I could automate that tracking, I could eliminate some manual work that I do, which frees up time to do more writing.

Yesterday, I created a new branch on the Google Docs Writing Tracker1 to focus on project tracking. I wanted to keep things fairly simple, because project tracking can quickly get out of hand, and become overly complex. There are currently 3 components to my project tracking system for the Google Docs Writing Tracker

1. Project documents

Some projects I work on involve just a few documents: one for the first draft, one for the second draft, and one for the final draft. They are still all part of the same project, and if I want to be able to track the progress across all three documents, I need a way of tying them together.

To keep things simple, I am currently using the Description field of a Google Docs document to embed project information. I have a simple JSON format that I am manually entering into any project-related document. Right now, it looks like this:

Google Docs Writing Tracker Project Info

The information in the project description field is simply a JSON string that identifies two values: the first is the project title the second is the draft. In this instance, I am treating the individual draft as an entire project itself, but the point is that any documents that share this tag will be associated with the project.

2. Project progress

I have added a “Progress” tab to my Writing Data spreadsheet. This tab contains records of the day-by-day progress on a project. I modified the Google Docs Writing Tracker code to check to see if a document is part of a project. If it is, its word count still counts as part of the overall for the day, but it is also logged individually on the Project progress tab, along with the project name, and how much time I spent working on the document.

Google Docs Writing Tracker project progress

This allows me to capture project progress at the daily transactional level, which will make it easy to build automated charts that show the overall progress and time-spent working on the project. The time comes from RescueTime data for documents that match the document name of the project in question.

3. Projects

Of course, I also need a way of capturing and defining what a project is. So I have added a “Projects” tab to the Writing Data spreadsheet that allows me to define projects and provides some simple, but useful tracking tools. You can see the four upcoming projects for my novel drafts entered on the project tab below.

Google Docs Writing Tracker Projects

The green items are the only items that need to be entered manually, and usually at the time the project is first created. Everything in yellow is captured automatically. Here is what is in this table:

  • Project #: a unique number identifying the project.
  • Project Name: the name of the project.
  • Start Date: the date on which the project will begin.
  • Deadline: the deadline assigned to the project.
  • Est. Words: the estimated word count of the project.
  • Est. Completion: an estimated completion date based on the current progress, the current date, the deadline, and you 30-day rolling word count average. This is calculated and updated automatically.
  • Status: the status of the project. Pending means it has not yet started. Active means it is active and in progress. Completed means that it is finished. Overdue means that it is not finished but past deadline. This is calculated and updated automatically.
  • Daily Goal: the average daily word count you need to hit in order to finish this project by the deadline. This is calculated and updated automatically.
  • Current Words: the current total word count of the project across all project documents. This is calculated and updated automatically.
  • Writing Progress: the current percentage of the way through the overall word count for the project. This is calculated and updated automatically.
  • Timeline Progress: the current percentage of the way through the project timeline. This is calculated and updated automatically.
  • Days off Schedule: this is a fairly sophisticated calculation that gives number of days off-schedule based on all of the available information. Ideally the number will be 0 or positive. 0 means your are on schedule to deliver on your deadline date. 1.5 would mean you are scheduled to deliver 1.5 days ahead of schedule. A negative number indicates behind schedule. So a -2 would indicated you are scheduled to deliver the project 2 days behind schedule. As with the other yellow cells, this is calculated and updated automatically.

Automation is key

Because I don’t want to spend time tracking this stuff manually, automation is key. So far, the only manual tasks I have to take are at the very beginning of the project:

  1. Adding the project JSON code to project documents.
  2. Adding a record for the project and filling in the green items on the Project tab

That’s it. Once that is done, the project is tracked automatically, just like everything else in the Google Docs Writing Tracker. I can spend my time writing, and then glance at the Project tab to get a quick status check on my progress. Eventually, I’ll add some visual project trackers as well to my open.jamierubin.net site so that anyone can follow my progress on a given project.

Part 2: ROI tracking

I have not yet implemented part two of the project tracking, the part that closes the loop on the overall project. I’m still testing this stuff I’ve outlined above. Part 2 is really about tracking the return on investment in my writing. That is, I invest time (mostly) and when I sell something I get paid. Tying those payments back to a specific project can generate a useful metrics on the business side of writing, the part well all hate to deal with. Eventually, I should be able to see how much I am paid for the labor that goes into various projects.

Payment is not why I write–at least not right now since I am not making a living from my writing. But if I ever got to the point where I could make a living from my writing, then data like this could be useful in looking for ways to work more efficiently. Of course, I am looking for ways to automate this as well, so that all I am ever doing, when it comes to my avocation, is writing. The numbers just help to steer the ship.


 

I don’t yet know when I’ll have this branch checked into GitHub as it is still fresh and needs some testing on my end before I am comfortable putting it out there for others. Also, it requires an update to the underlying spreadsheet, and it is always a hassle to make a new version. But when I do get the new branch committed, I’ll let you know.

  1. I haven’t yet pushed the branch so don’t bother looking for it yet. I’ll you know when it is out there.