Client reporting with ClickUp
Having been an engineering consultant in various capacities for the last 14 years now, I've wrestled with my fair share of methods for reporting on progress to clients.
In a mid and large-sized company, timesheeting is often the domain of some lumbering, enormous investment running on SAP or SyteLine that must keep the entire business compliant, and the prospects for integrating it with the dynamics of project management or presenting some personalised client report are slim. So customers get black-box-generated reports devoid of context, and that's the end of the story.
On the other hand, having founded and run a consultancy during the most 7 years, I've developed a keen appreciation of good client reporting as a bedrock of good engineering consultancy. Engineering consultants sell a service, and no customer is going to appreciate great engineering unless it's part of great service. Customers should be empowered to ask "what am I paying for, where is it up to, and what's the process from here" before they should be expected to care about how clever your signal conditioning circuit is or the shininess of your soldering job.
At my consultancy our reporting process was very much developed on an as-needed basis. And over time it developed some highly apt features - planning was very flexible, entering your timesheet entries (the bane of engineers everywhere!) was pretty low friction, weekly project updates were recorded and extracting the work log was automatic. So we got a lot of critical record keeping for our efforts, and customers got personalised updates. It was built on some bespoke Confluence templates for project management, a standalone AirTable database for timesheeting and orders, and a clear process to stitch it all together.
But as a critical business system, it's hard to make transformative improvements. Incrementally it remained effective, but it had fundamental shortcomings: the project plan (which ought to adjust to reflect learnings as projects unfold) could diverge from the invoicing schedule, and generating an invoicing report based on the timesheet was a clumsy word processing task that introduced ambiguity and delays.
Now that I'm not running a consultancy (a two year non-compete enforces a healthy change of scene!) I can revisit this important process without a payroll to worry about.
So I've been experimenting with ClickUp as a project management tool. Normally I'd shy away from anything resembling a Swiss-Army Knife approach (best of breed for me please!), but I was afforded the opportunity to use it in practice when I started freelancing for Informal. I found it tremendously well suited to freelancing - I made sure all the important context scattered amongst emails and Slack threads was attached to a task, requirements were tracked transparently, and blockers could be seen and progressed at a glance. Most importantly I could spell out my insistence on front-loading projects and demonstrate how that informed future milestones. For stakeholder presentations it was easy to create the doc in ClickUp itself, link to the resources already in tasks and keep the single source of the truth on record.
And then there was timesheeting. Informal use Harvest and take care of all the invoicing for their freelancers. Informal is a brilliant organisation, and this service is no exception. But given I have unpredictable working hours (enforced by two young children) I still needed to ensure I was keeping track of my time so I could update Harvest accurately. And it turns out ClickUp can do time tracking as well. Pretty well too - you can either start a timer against a task or add entries manually. By the way, there's a catch here - only assigning hours against tasks means that your tasks need to be comprehensive and exploratory work is hard to capture - I have strategies for leaky timesheets too, but that's a big topic for another time.
Where this really breaks, is in extracting the logged time from ClickUp. Transcribing the "Time Tracked" column each billing cycle is a recipe for disaster - writing a clear update while the plan unfolds and tasks switch order or scope changes, and trying to remember what's been billed and what hasn't, is going to end in tears. ClickUp offers CSV or Excel export, but the format is a roll of the dice, the time tracked field is not numerical, and the feature quietly breaks after 5 uses anyway, unless you pony up $12/month!
But hang on, ClickUp has an API... so couldn't I just make my own time tracking report?
Yep...
As with any project I start, I first thought long and hard about the right tool for the job. All I need to do is call a REST API and parse the JSON into a CSV so I can paste it into a report and import into my accounting software. Common wisdom would suggest I borrow some of Jeff Bezos' computers and create a lambda-Node-microservice-function-k8s spectacular and leave the user (me) to figure out the plumbing in front of the wall. Not being commonly wise, I wondered if there was another way.
How could I satisfy my ultimate life motivator (laziness) and eliminate anything superfluous? What if I set the goal of zero new accounts, and no new URLs and not a single source file on my hard drive to forget about? Well even minimalist me can't avoid having a business Google account, and I need a spreadsheet program anyway, so what if I could empower the spreadsheet itself to do it? Of course, spreadsheets have been programmable since the days of Lotus 1-2-3!
After a little experimentation I ended up with a Custom Function (function FetchTasksFromClickup()) in Google Apps Script. The script is accessible from the spreadsheet under the "Extensions --> App Script" menu. Lines 1 to 4 hidden in the screenshot is my Test_FetchTasksFromClickup() function with real world arguments for unit testing and debugging.
Then, back in the spreadsheet I simply enter the formula =FetchTasksFromClickup() in a cell (see cell B7) and pass my settings, which are stored in a sheet of their own. GSheets takes care of running the query in the background and either displaying an error as a tooltip, or if successful, placing the two-dimensional results into the cells below and to the right. I can then style and brand to my heart's content (including conditional formatting), and all future fetches will retain that style. I can even do neat spreadsheety things like hiding a column (notice F is missing) of fetched data and automatically do some post-processing. Column G turns the raw numerical invoice number placed in column F into descriptive text to suit the report. Finally, because automatic recalculation is enforced by the GMachine, I duplicate the sheet (see Report 2022-11-09) and paste "Values only" so I have a record of the report frozen in time. I can then do any final markup before attaching to a carrier pigeon with some chocolates for delivery. Next time I report, the outstanding work ready to be invoiced is plain to see.
The fields and their grouping is highly flexible. The phased nature of the tasks in this screenshot is only an example, and something like this tends to suit the bounded, outcome orientated engagements I seek out. For more never-ending Scrum style development it might need breaking into milestones or something. In general this will suit tightly scoped projects (or subprojects!), and is a good forcing function to ensure your project plan stays in sync with your reporting.
For those interested in the details, I found Google Apps Script pretty clunky, in that it is stupidly full featured for awfully complex use cases, so writing a single purpose script took some bumping around in the dark. The editor is classic Google bare bones and mysterious (errors are sometimes suppressed, and it changes so often that documentation is generally out of date), but actually has a very useable debugger and most of the essential navigation and inspection features. And Apps Script are written in Javascript, which I have no interest in gaining expertise in. But since I’m literally parsing JSON via an API, and performance implications are minute, it's nothing general expertise and a Stack Overflow search wont cover. In the end I’m very satisfied with the arrangement. Doing presentation and code live makes for a tight design loop in a small project. It does reek of “whole business is Excel macros”, but I’m also not going to rule these tools out simply because they are often abused. I would like to do independent version control, but I’m willing to give that up for the benefit of having a managed single source of the truth.
So there you have it - file this one under "40 year old programming paradigms, in technicolor and on the cloud", try out my Creative Commons licensed script, and let me know your thoughts on client reporting!
Script source code: https://gist.github.com/hraftery/cc8aea24e8643180fb1af0d3600a7b91