Connect Karbon API to Excel


Userlevel 7
Badge +19
  • Sr. Karbon Community Guide
  • 679 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.

Instructions

How to add your tokens

Add your credentials to the workbook

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

I was able to get dummy data, so the information below is for you to use and learn, but the steps have already been completed in the template file.

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]

 

How to refresh your data

Pull in new data from Karbon

Once you enter your keys and model your data, you’ll need to refresh your data to update pull in any changes you make in Karbon.

  1. Navigate to the “Data” ribbon
  2. Tap “Refresh All”

In the bottom corner you’ll see the refresh running. Once it’s complete, your visuals should update automatically.

 

LIMIATAION: If you protect the sheet, the pivot tables may not update until you re-select any filters applied to the table.

 

Troubleshooting

If you get a firewall error

Excel and Power BI are careful not to send your data from one source to another source (like sending Karbon data to another API from which you are receiving data). For this case, you do not need to worry about it, because the only connection is to Karbon, so there is no concern if Karbon data gets mixed with Karbon data.

Navigate to privacy settings and select Ignore privacy levels:

 

Open the Power Query Editor by double-tapping on one of the queries in the query pane:

From the query editor, follow these instructions:

You may find it useful to try the top and bottom setting to get the queries to work properly.

 

Use-Cases and Examples

Basic Analytics

One of the template files now contains very basic analytics and charts. I would love to see what you come up with, so drop some screenshots in the comments if you put this to good use. See the repository link at the beginning of this post.


17 replies

Userlevel 7
Badge +19

[UPDATE] I added a troubleshooting section to the original post to address how to fix the firewall error @davideigner was getting. 😀

Userlevel 2
Badge +2

Booked. Thanks much!

Userlevel 7
Badge +19

Strange that it would be private. Strange that changing it to public didn’t work. Want to do a quick screenshare?

https://link.avriopro.com/karbon-community-meet-max

Userlevel 2
Badge +2

It was Private and I changed to Public, still getting this error.

@max 

Userlevel 7
Badge +19

What do you see here?

 

Userlevel 2
Badge +2

@max I’m still getting errors for timecards. It’s happing at the Invoked Custom Function step.

 

 

Userlevel 7
Badge +19

[UPDATE] I updated the template with a connected data model and some demo visuals. Once you connect your keys, you should see information flow through to the spreadsheet in a few minutes. Follow the instructions carefully to make sure you authorize each query properly.

@davideigner try the new template file and let me know if it works for you.

Userlevel 2
Badge +2

🙏

Userlevel 7
Badge +19

Something is messed up with stg_time_sheets. Let me take a look at the template and report back.

Userlevel 2
Badge +2

Hi @max Thanks so much for providing the template and instructions!

 

I’m trying to play around with it, but I’m getting an error for four tables, would you know if it’s an issue with my connection or with the template?

 

Thank you

Userlevel 7
Badge +19

[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.

Userlevel 7
Badge +19

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 4
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 +19

OP Update:

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

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

Userlevel 7
Badge +19

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

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 :)

Reply