Reporting data from ClickUp (or any) API in F#

ClickUp is a feature-rich, fast growing project management app we use at Bitrise. However, for reporting, we had to create a workaround using their API. Read on for the details.

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.


module Params =
  let accessToken = "pk_secret"
  let teamID = "123456"

  // tasks after jan 1 2018
  let firstTaskTime = "1514764800000"

  // include closed tasks as well
  let includeClosed = "true"

  // include subtasks in the response
  let subtasks = "true"

  // order by
  let orderBy = "created"

  // by deafault tasks are shown in a descending order
  let reversed = "false"

  // concatenate parameters
  let url = "https://api.clickup.com/api/v1/team/" + teamID 
            + "/task?include_closed=" + includeClosed 
            + "&subtasks=" + subtasks 
            + "&order_by=" + orderBy 
            + "&reverse=" + reversed 
            + "&date_created_gt=" + firstTaskTime 
            + "&page="
  
  // number of pages
  let maxPage = 20
  
  // page numbers starts at 0
  let maxPageMinus = maxPage - 1 
Copy code

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.


open FSharp.Data

type TasksType = JsonProvider<"""{
"tasks": [
  {
    "id": "4fyu4",
    "name": "Write Article about ClickUp API and F#",
    "text_content": "Text Here!",
    "status": {
      "status": "🚀 in progress",
      "color": "#7b68ee",
      "type": "custom",
      "orderindex": 1
    },
    "orderindex": "0.00000000000000000000000000000000",
    "date_created": "1529938135048",
    "date_updated": "1529938381080",
    "date_closed": null,
    "creator": {
      "id": 261003,
      "username": "Tamás Srancsik",
      "color": "#2a9a04",
      "profilePicture": "potrait.jpeg"
    },
    "assignees": [
      {
        "id": 261003,
        "username": "Tamás Srancsik",
        "color": "#2a9a04",
        "profilePicture": "portrait.jpeg"
      }
    ],
    "tags": [
      {
        "name": "clickup",
        "tag_fg": "#1AFC8E",
        "tag_bg": "#5eb765"
      },
      {
        "name": "fsharp",
        "tag_fg": "#FAEA7A",
        "tag_bg": "#51c0bc"
      }
    ],
    "parent": null,
    "priority": {
      "id": "2",
      "priority": "high",
      "color": "#ffcc00",
      "orderindex": "2"
    },
    "due_date": null,
    "start_date": null,
    "points": null,
    "time_estimate": null,
    "list": {
      "id": "4250743"
    },
    "project": {
      "id": "3302540"
    },
    "space": {
      "id": "342238"
    },
    "url": "https://app.clickup.com/team/space/t/4fyu4"
  }
]
}""" >
Copy code

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.


let int2String (x: int) = string x
Copy code

ClickUp API presents all datetime attributes in epoch. We need to write another function if we would like to see them in local time.


open System

// convert epoch to local datetime
let toDateTime (timestamp:int) = 
    let start = DateTime(1970,1,1,0,0,0,DateTimeKind.Utc) 
    start.AddSeconds(float timestamp).ToLocalTime()
Copy code

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.


open System.Collections.Generic

// our list for tasks
let allTasks = List<TasksType.Task>()

for page in [0..Params.maxPageMinus] do
  let ps = int2String page
  
  let data = 
    // Run the HTTP web request
    Http.RequestString
      ( Params.url + ps, httpMethod = "GET",
        headers = [ "Authorization", Params.accessToken ])
        
  let responseData = TasksType.Parse(data)
  let tasks = responseData.Tasks

  // counter for Tasks on a page
  let counter = ref 0

  for task in tasks do
    allTasks.Add(task)
    incr counter
    printfn "Page %i - Task %i added." page counter.Value
Copy code

Let us check how many tasks we collected. Linq namespace is required here to count tasks we unloaded.


open System.Linq

allTasks.Count
Copy code


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.


let tamasTasks =
  query {
    for t in allTasks do
      for a in t.Assignees do
        where (a.Username = "Tamás Srancsik")
        select (t.Id, t.Name, t.Status.Status, t.Space.Id, t.DateCreated)
  }
Copy code

This way we can also group data by attributes nested at a deeper level. For example top tags on open tasks:


let topTagsOpen =
  query {
      for t in allTasks do
        where (t.Status.Status = "Open")
        for a in t.Tags do
          groupBy a.Name into g
          sortByDescending (g.Count())
          select (g.Key, g.Count())
          take 10
  }
Copy code

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:

Top Tags on Open Tasks

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:


let estimations =
  query {
    for t in Tasks.allTasks do
      
      // filter for our backlog
      where (t.List.Id = 1220700)
      
      // exclude missing values
      where (t.TimeEstimate.JsonValue <> JsonValue.Null)
      select t.TimeEstimate.JsonValue
    }
Copy code

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.


// function converting JsonValue milliseconds to float hours
let toHours (x : JsonValue) = x.AsFloat() / 3600000.0
  
// apply function on each element
let estimationsInHours = 
  estimations
  |> Seq.map toHours

let trace = Box(y = estimationsInHours, 
                name = "estimations",
                boxpoints = "all",
				jitter = 0.3,
                
                // move points left to chart
				pointpos = -1.8
				)

trace
|> Chart.Plot
|> Chart.WithTitle "Time Estimatied in Hours - Tasks in Data Backlog"
|> Chart.WithHeight 700
|> Chart.WithWidth 700
|> Chart.Show
Copy code

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.

No items found.

Explore more topics

App development

Best practices from engineers on how to use Bitrise to build better apps, faster.

Community

Meet other Bitrise engineers, technology experts, power users, partners and join our BUGs.

Company

All the updates about Bitrise events, sponsorships, employees, and more.

Insights

Mobile development, latest tech, industry insights, and interviews with experts.

Mobile DevOps

Learn why mobile development is unique and requires a set of unique practices.

Releases

Stay tuned for the last updates, new features, and product improvements.

Get the latest from Bitrise

Join other Mobile DevOps engineers who receive regular emails from Bitrise, filled with tips, news, and best practices.