Daily Exports to Excel Directly or via Json

Tips, Tricks & How to's
1

Hello,

I am pretty new to work with Clockify (and API’s in general) but I am looking to find a way of integrating the detailed reports with Excel so that I can do further drilling down and analysis with it.

Is it possible to use the Daily Export add on to do this? Can that pump out a JSON file to our server that I can pick up in excel or is there a neater way of doing it? Could I just call the API from Excel?

Any help or guidance would be greatly appreciated.

Cheers
Ian

2

Hi Ian,

you can do it directly in Excel via the PowerQuery extension. There you define the JSON query and anything around you can do in Excel VBA.
Here is a snippet of what I developed:

Blockquote let
//Parameter lesen
apiKey = Excel.CurrentWorkbook(){[Name=“ApiKey”]}[Content]{0}[Column1],
wsId = Excel.CurrentWorkbook(){[Name=“WorkspaceID”]}[Content]{0}[Column1],

startDate = Excel.CurrentWorkbook(){[Name="Start"]}[Content]{0}[Column1],
endDate = Excel.CurrentWorkbook(){[Name="End"]}[Content]{0}[Column1],
prjId = Excel.CurrentWorkbook(){[Name="ProjektID"]}[Content]{0}[Column1],

//URL konstruieren
url="https://reports.api.clockify.me/v1/workspaces/" & wsId & "/reports/detailed/",

//Abfrage aufbauen
content =
   "{""dateRangeStart"": """ & startDate & """,""dateRangeEnd"": """ & endDate & """
    ,""detailedFilter"": {""page"": ""1"",""pageSize"": ""1000"",""sortColumn"": ""DATE""}
    ,""sortOrder"": ""ASCENDING"",""exportType"": ""JSON""
    ,""projects"":  {""ids"":[""" & prjId & """]}
    ,""amountShown"": ""EARNED"",""rounding"": ""true""
    }",      
Source = Json.Document(
                 Web.Contents(url,    [Headers=    [ #"X-Api-Key"=apiKey
                                                     ,ContentType="application/json"
                                                     ,#"Content-Type"="application/json"
                                                    ]
                                      ,Content=Text.ToBinary(content)
                                      ] 
                              )
                          ),
timeentries = Source[timeentries],
#"Converted to Table" = Table.FromList(timeentries, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Expanded Column1" = Table.ExpandRecordColumn(#"Converted to Table", "Column1", {"description", "timeInterval", "amount", "rate", "projectName", "taskName", "clientName", "userName"}, {"description", "timeInterval", "amount", "rate", "projectName", "taskName", "clientName", "userName"}),
#"Erweiterte timeInterval" = Table.ExpandRecordColumn(#"Expanded Column1", "timeInterval", {"start", "duration"}, {"Datum", "duration"}),
#"Renamed Columns" = Table.RenameColumns(#"Erweiterte timeInterval",{{"duration", "Time (sec)"}}),
#"Inserted Division" = Table.AddColumn(#"Renamed Columns", "Division", each [#"Time (sec)"] / 3600, type number),
#"Inserted Rounding" = Table.AddColumn(#"Inserted Division", "Round", each Number.Round([Division], 2), type number),
#"Renamed Columns1" = Table.RenameColumns(#"Inserted Rounding",{{"Round", "Zeit (dec)"}}),
#"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Division"}),
#"Inserted Division1" = Table.AddColumn(#"Removed Columns", "Division", each [rate] / 100, type number),
#"Renamed Columns2" = Table.RenameColumns(#"Inserted Division1",{{"Division", "Rate"}}),
#"Inserted Multiplication" = Table.AddColumn(#"Renamed Columns2", "Multiplication", each [Rate] * [#"Zeit (dec)"], type number),
#"Renamed Columns3" = Table.RenameColumns(#"Inserted Multiplication",{{"Multiplication", "Betrag"}}),
#"Removed Columns1" = Table.RemoveColumns(#"Renamed Columns3",{"Time (sec)", "amount", "rate"}),
#"Renamed Columns4" = Table.RenameColumns(#"Removed Columns1",{{"clientName", "Client"}, {"userName", "Person"}, {"projectName", "Projekt"}, {"description", "Beschreibung"}, {"taskName", "Leistungsphase"}}),
#"Reordered Columns" = Table.ReorderColumns(#"Renamed Columns4",{"Datum", "Person", "Client", "Projekt", "Beschreibung", "Zeit (dec)", "Rate", "Betrag"}),
#"Grouped Rows" = Table.Group(#"Reordered Columns", {"Datum", "Person", "Client", "Projekt", "Leistungsphase", "Beschreibung"}, {{"Zeit (dec)", each List.Sum([#"Zeit (dec)"]), type number}, {"Betrag", each List.Sum([Betrag]), type number}}),
#"Sorted Rows" = Table.Sort(#"Grouped Rows",{{"Datum", Order.Ascending},{"Client", Order.Ascending}, {"Projekt", Order.Ascending}, {"Beschreibung", Order.Ascending}}),
#"Changed Type" = Table.TransformColumnTypes(#"Sorted Rows",{{"Betrag", Currency.Type}}),
#"Analysiertes Datum" = Table.TransformColumns(#"Changed Type",{{"Datum", each Date.From(DateTimeZone.From(_)), type date}})

in
#“Analysiertes Datum”

Sorry for the names and comment being in german… :wink:

Hope this gets you started - cheers,
Holger

1 Like
3

Hi Holger,

Thank you very much for your assistance and providing a sample of your work, I really appreciate it.

I managed (after a lot of trial and error) to get Postman to build a request that worked for me and then started messing around with the Power Query to get it to do what I wanted. It is mostly there at this stage.

I am a firm believer in sharing knowledge with the masses, to better educate and in the hopes it might help someone else who is having the same issues I am, so here is what I managed to get working:

let
yesterdayStart = DateTime.ToText(DateTime.LocalNow() - #duration(365+1,0,0,0), “yyyy-MM-ddT00:00:00.000Z”),
yesterdayEnd = DateTime.ToText(DateTime.LocalNow()-#duration(1,0,0,0), “yyyy-MM-ddT23:59:59.999Z”),
url = “https://reports.api.clockify.me/v1/workspaces/[WORKSPACE.ID]/reports/detailed”,
body = Text.ToBinary(“{ ““dateRangeStart””: “”” & yesterdayStart & “”“,”“dateRangeEnd”“: “”” & yesterdayEnd & “”“, ““detailedFilter””: { ““page””: 1, ““pageSize””: 50 }, ““exportType””: ““CSV”” }”),
headers = [
#“Content-Type” = “application/json”,
#“X-Api-Key” = “[YOUR.API.KEY]”
],
response = Web.Contents(url, [Headers=headers, Content=body]),
jsonResponse = Csv.Document(response)
in
jsonResponse

This is the power query that I have been using to reproduce the .csv export I get whenever I use the Detailed report export function on the website. For now it is just pulling in data for the past year.

My end goal would be to have some form of a user defined variable that they can input (maybe via cell in one of the sheets) and it pulls the data via the API. This should allow us to get all of out time entries filtered on by the project code, rather than a time frame as shown above.

But that will be the next iteration, for now this is workable. :thinking:

Again, thanks very much for sharing, I will probably use some of your query to help build the next phase of mine. :innocent:

Cheers
Ian

4

Hi Ian,

in my snippet you can see three variables I pull in from Excel cells that you can use for a start.

I like your idea to just pull the same as the CSV Export from the website. This could also be interesting for me. I think I’ll give it a try.

Cheers,
Holger

1 Like