We use many tools internally here at Vokke, one of which is Harvest. It’s a great time tracking tool as it’s both readily adopted by team members and has fantastic out-of-the-box reporting.

Recently though, we wanted to get more out of our data, so we decided to connect Harvest’s API into PowerBI. Now, Harvest at the time of writing does not have a native connector to PowerBI, but that’s no worries. With a few lines of code inside PowerBI, we can connect it up.

The game plan – what I was after

I wanted to call Harvest’s Reporting API for each month in the given year, and then report on that data visually.

Starting off – preparing the date ranges

The Reporting API requires both a start and end date and reports project metrics based on that duration. This isn’t exactly what I was after; I wanted project metrics broken down per month.

For this, I simply created a quick Google Sheet with two columns: the start date and the end date with the aim of joining this sheet to Harvest’s API.

You can import a Google Sheet quite easily. Simply go to “Get data” and select “web”:

Then, for the URL, use the following template but replace the ID with your own Google Sheet’s ID. Make sure the Google Sheet has public access first though:

https://docs.google.com/spreadsheets/d/{YOUR_ID_HERE}/gviz/tq?tqx=out:csv&sheet={sheet_name}

Connecting Harvest

Now the fun part – for each date range in the calendar table above, we want to invoke Harvest’s API. For this, add a new column on the calendar table we imported and use the following code in the Power Query Editor. Make sure you have a Harvest Private API key first though (insert your own data where the asterisks are below; also note that I’ve assumed the date columns are called “Start Date” and “End Date” but you can adjust as necessary).

let
    Source = Json.Document(Web.Contents("https://api.harvestapp.com/v2/reports/time/projects?from="&Date.ToText([Start Date], "yyyy-MM-dd")&"&to="&Date.ToText([End Date], "yyyy-MM-dd"), [Headers=[#"Harvest-Account-Id"="******", Authorization="Bearer ******"]])),
    results = Source[results],
    #"Converted to Table" = Table.FromList(results, Splitter.SplitByNothing(), null, null, ExtraValues.Error)
in
    #"Converted to Table"

Once you’ve added this custom column you will have a new table per row on the calendar table. Simply keep clicking the expand icon to reveal all the Harvest fields!

Photo by Estée Janssens on Unsplash