Solution
Naturally, the mid-term solution for the problem is building pipelines collecting data from each endpoint of ClickUp's extensive API, then transforming, flattening and merging these datasets with an ETL tool of your taste (in our case Xplenty).
For ad-hoc reports or exploratory analysis, you can write your own script in any language since we are communicating with ClickUp via regular HTTP requests.
One of the reasons I prefer F# over other languages is F# Interactive which lets you evaluate and run your script or specific parts, modules, functions of it within your editor. Type providers of F# Data Library further facilitate our job. Let me share how we can use JSON Type Provider and HTTP Utilities to process ClickUp Tasks.
Details
We need the team id and access token to authenticate. When running compiled exe you can use FSharp.Configuration to manage secrets. Here we are simplifying the process to run the code by F# Interactive.
To limit the dataset we are adding a start date to our query which should be in epoch format. ClickUp API starts counting pages from 0.
We will need F# Data Library to process responses from the API. JSON Type Provider lets us define type by an example document. Since we are interested in processing tasks, this is the body of the response from Task endpoint.
To paginate the requested pages we are looping over URLs constructed by amending our original one with the page number. Let us introduce a helper function converting our integer page numbers to strings.
ClickUp API presents all datetime attributes in epoch. We need to write another function if we would like to see them in local time.
Finally, we can collect tasks from the API. System.Collections.Generic is needed to use List constructor.
We are making the request by RequestString method of Http type in F# Data HTTP Utilities.
Let us check how many tasks we collected. Linq namespace is required here to count tasks we unloaded.
A bit more sophisticated queries
We can filter for specific tasks using query expressions. For example, a specific user's tasks are listed in the following way.
This way we can also group data by attributes nested at a deeper level. For example top tags on open tasks:
Plotting with Plotly
Xplot is a package that can draw charts with both Google Charts and Plotly APIs. Plotly is broader and has a higher number of chart types and options.
You can define the representation of data and some options by piping members of Chart type. Naming speaks for itself:
We cannot follow the way of aggregation in the query to draw a more complex chart. All the values must be there to let Plotly find statistics for box-plot.
Id can be captured from URL of your list of tasks: it is the id after "&s=". The query listing estimations in Data Backlog:
We have to convert JsonValues to floats. Since the API passes estimation figures in milliseconds, we are applying another function as well to report in hours.
We can see that the most typical task is estimated to last 5 hours, 3 quarter of them is within 10 hours, the largest task is planned for 15 hours.
Stay tuned for the next article on data wizardry titled Ad-hoc reports from GitHub in F# coming soon.
ClickUp is a feature-rich, fast growing project management app we use at Bitrise. As with any other application, developers there focused on the core features of their product at first, and though we see new features released each month, reporting has unfortunately not been among them yet. Read on to see how we created a workaround using their API.
