Connect Karbon API to Excel


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


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

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

Userlevel 7
Badge +19

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

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 +15

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

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 +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 3
Badge +4

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

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

Userlevel 3
Badge +4

🙏

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 3
Badge +4

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

 

 

Userlevel 7
Badge +19

What do you see here?

 

Userlevel 3
Badge +4

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

@max 

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 3
Badge +4

Booked. Thanks much!

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 3
Badge +4

is there any way to make the excel file update automatically without my having to open the file?

Userlevel 7
Badge +19

Hi @Brian Davis, can you tell me what you mean? What are you trying to do?

Userlevel 3
Badge +4

i’m trying to use Timely to track my time. It automatically tracks what i do during the day so i don’t have to remember to start and stop timers. I want to record that to a project - i would like that project to be pulled from Karbon (when i create it in Karbon) to Timely. Then i can tag my time to that project… which i can then push to an invoice that will push to QuickBooks.  I don’t want to have to manually remember to create a project in Timely becuase that slows me down. i just want the project name to pull directly from Karbon.

 

Userlevel 7
Badge +19

I looked at Timely and it integrates with Zapier, so you could do something like this:

Dealing with webhooks is not difficult, but it’s also not straightforward.

  • You’ll subscribe to a webhook following the instructions in the Karbon API documents
  • Your webhook will send new work items to Zapier in real-time (just the key and what happened are sent, not the work item details)
  • You’ll make Zapier take the key from the webhook message and request the rest of the information from Karbon with a GET request to the Karbon API
  • Using the full information received from the GET request, add the project to Timely

Does that make sense?

Userlevel 3
Badge +4

Can i just pay you to set this up for me?  

Userlevel 1

Why is the integration with Avrio no longer available?

Userlevel 7
Badge +19

Hi Elisa,

That “Avrio” integration is something Karbon will set up for you. It won’t be called Avrio, it’ll be called something related to your company.

I realize now that I’m unclear in my original post. I won’t get around to updating it until later in the year, though. 😀

Head to the developer page and register here: https://developers.karbonhq.com/#getStarted

 

Hello,

Has anyone had success pulling Client Manager, Client Owner, and User Role data in query? I am getting null data no matter what I do. I am trying to associate user roles, client manager, and client owner with work projects.

Thanks!

Mike

Reply