Using Google Docs as a Time Tracker

I'm doing some consulting in between full time employment. It's been forever and a day since I did hourly work, so I briefly looked around to see what nice apps there were (either desktop or iOS) to help me track my hours for billing. Unsurprisingly, I was disappointed by what I found. They're all so complicated! I guess people that do this for real have multiple clients, multiple projects, want to interface with online billing systems, and so on. All I wanted was something quick to start work, stop work, and calculate the time spent.

Google spreadsheets + forms to the rescue!

I'm mostly putting this down just so I can remember it for the future.  If it's useful to you, great! Please leave a comment. It's likely you'll have to modify it some for your own use. Enjoy!

Step 1: Set Up the Form

• New google sheet
• Tools -> Create a Form
• Only question: Start or Stop. Multiple Choice, start and stop as the options
• make the question required
• (If you have projects, add them as a second question)
• Click the Preview button (an eye icon)
• Copy/paste the URL to a temporary spot for later

Step 2: Do Time Calculations
Go back to the spreadsheet. You'll see a tab labeled "Form Responses 1" and one labeled "Sheet1"

• In the Sheet1 tab, label the first column Timestamp and the second Duration
• In cell A2, put ='Form Responses 1'!A2
• In cell B2, put =if('Form Responses 1'!B2="Stop", 'Form Responses 1'!A2 - 'Form Responses 1'!A1, 0)
• This formula assumes you always alternate Start and Stop, and calculates the time between start and stop
• If tracking multiple projects, instead of =if(B2="Stop",... use =if(and(B2="Stop", C2="Proj1"),... with one column for each project.
• Copy the formula down for the rest of each of columns A and B
• Change column B to use Duration format: Format menu -> Number  -> Duration

Make a summary tab for easy viewing

• Create a new tab, name it Summary
• To get your total hours spent, use the formula =sum(Sheet1!B2:B30) (changing 30 to the final row of your list of durations

Step 3: Make it easy to use
Load the form on your phone. Using the 'send' icon in Safari, choose 'Add to Homescreen'. From now on, when you start or stop work, tap the icon for the form on your phone, tap Start or Stop, and submit. Submission will record the timestamp in the form, to be calculated by your spreadsheet formulas.

On your mac, open the AppleScript Editor. Create a new applescript with the contents:

open location "https://docs.google.com/blah/blah/blah"

changing the docs.google.com URL to be the URL for the form.  Save the applescript as an application and put it on your desktop. When you want to start or stop work on your mac, open the applescript (this will load the form in your default browser), check start or stop, and hit submit.

For extra fun, use Karabiner to bind a key or keycombo such as F10 to open the applescript. In private.xml:

<vkopenurldef>
    <name>KeyCode::VK_OPEN_URL_HOURS_APPLESCRIPT</name>
    <url>file:////Users/ben/Desktop/Hours.app </url>
</vkopenurldef>
<item>
    <name>F10 to Hours</name>
    <appendix>Launch hour tracker in default browser</appendix>
    <identifier>private.f10_to_hours</identifier>
    <autogen>__KeyToKey__
        KeyCode::F10,
        KeyCode::VK_OPEN_URL_HOURS_APPLESCRIPT
    </autogen>
</item> 

Conditions and Use Instructions

• When you start, load the form and submit with "Start"
• When you finish, load the form and submit with "Stop"
• You must alternate Start and Stop. If you have two starts in a row or two stops in a row, it will break.
• If you have two Starts or Stops in a row, just delete one or insert a new row with the one that's missing.
• If you forget to Start or Stop, you can either ignore it and add a new row later, or add a new row immediately then go and change the Timestamp to be correct after the fact.