Connect Karbon API to Excel

  • 26 March 2022
  • 7 replies
  • 226 views

Userlevel 7
Badge +18
  • Sr. Karbon Community Guide
  • 600 replies

Skip ahead:

Introduction

The goal of this post is to help you connect the Karbon API to Excel.

I will be updating this post with better code (more comments), and instructions on how to get this set up. I may do a series of posts loosely related to pulling in data from Karbon to model and use in Excel using Power Query and Power Pivot.

If you use any of these functions or have better ways of executing this code, I would love to see your comments.

Specific use cases should be addressed in new topics and then linked back to this topic. The most useful and interesting use cases posted in the comments will get added to this OP.

Template Files

Link: Human Accounting Mastermind Document Repository

In the link above, you will find template files for Excel and Power BI (coming soon). These files have everything you need to get started, but there are some additional setup steps that I will cover below.

How to add your tokens

You need two tokens to access Karbon’s API:

  • Bearer Token sent to you by Karbon when you register
  • Access Key found in your Karbon settings:
     

Once you have your tokens, open the Karbon API-connected Excel Template file (see link above). Follow these instructions to add your bearer token and api access key to the file:

  • Add your bearer token to the token_bearer() query “Current Value” field
  • Add your API Access Key to the token_access_key() query “Current Value field

Next, you’ll need to select each query and tell it to connect anonymously:

After the API runs, you’ll start seeing a preview of your data right away. Make sure you complete this step for all the queries in the list. Your list should look like this:

If everything looks good, tap the close and load button on the Home ribbon:

 

How to model your data

Before you can use the data that comes from the carbon API, you need to connect it so that it makes sense. Get started by taking a look at the tables in your data model.

 Next you will connect your data tables. This can get a little complicated, and a lot can go wrong, so don’t expect your reports to work flawlessly right away. Depending on the kind of reports you want to get out of Karbon, you will connect your keys in different ways. The scope of building specific reports is beyond this post. Here’s an example of how you might setup your data model:

[I plan to release a file that has the connections already in place, but I’m working through a technical limitation right now. Eventually, you’ll be able to download template files with complete reports built out]

Use-Cases and Examples

[Under construction]


7 replies

Badge

Thank you so much for sharing your code and tips Max. This is really helpful, and quite insightful too! 

 

@amlbmo - An innovative way to improve our capacity planner :)

Userlevel 7
Badge +18

I’ll continue to add to and refine this post. At this point, it’s a rough draft. 😀

Userlevel 7
Badge +18

OP Update: Added instructions for how to add code examples to Excel.

Userlevel 7
Badge +18

OP Update:

  • Condensed base API function to one instead of two.
  • Added the timesheet data function with instructions.
Userlevel 3
Badge +9

Hi @max, I see in other threads you mentioned that you bulk create work items using Karbon API <> Excel. Have you happened to post anywhere how that is done? 🙂🙏🏾

Userlevel 7
Badge +18

I have not yet. It’s a little hacked together and has hard coded secrets and firm data.

I’ll be posting it on GitHub when I get it cleaned up. 

Userlevel 7
Badge +18

[Update] I added a folder with a template document and updated instructions about how to connect your own keys and model your data.

There are a few extra steps you'll have to take after you connect your API keys to model your data. I've asked carbon to give me a default data set so that I can set the data model for the template. And all you would have to do is download it and add your keys. As soon as that happens, I will update the original post with a new template file that will be simpler to use.

Reply