Import Chargebee Data to Google Sheets – Step by Step Guide

Import Chargebee Data to Google Sheets – Step by Step Guide

Last Updated On May 30, 2019
You are here:
< Back

Chargebee is a subscription management tool. In this guide, we’ll walk through how to pull subscription data from the Chargebee API directly into Google Sheets, using the free API Connector add-on. We’ll first get an API key from Chargebee, and then set up a request to pull in metrics from Chargebee to your spreadsheet.

PART 1: GET YOUR CHARGEBEE API KEY

  1. Log in to Chargebee and click Configure Chargebee > API keys and webhooks from the left-hand menu
    chargebee-img1

  2. On the API keys and webhooks page, you’ll see that a test key has already been automatically created for you. You can use this existing key or create a new one by clicking the +New API Key button. For this example, I’ll make a new API key with read-only access:
    chargebee-img2

  3. Once you have your key, copy it to your clipboard.
    chargebee-img3

  4. One last step: Basic Access Authentication requires us to encode our authentication info to base 64. You can do this opening up Developer Tools in your browser (F12 on Windows/Linux or option + ⌘ + J on OSX). In the console, type in the following and click enter:
    You’ll need to substitute in your own Chargebee API key from above (the API key in this example has already been deleted). It should look like this
    chargebee-img4

  5. Copy the output that appears in Developer Tools to your clipboard as we’ll need it shortly. (If you have any problems with the above, you can also try a tool like Basic Authentication Header Generator to generate your header.)

PART 2: CREATE YOUR API REQUEST

We’re going to follow the Chargebee API documentation to request a list of active subscriptions.

  • Base URL:https://{site-domain}.chargebee.com/api/v2/
    Example Base URL: https://your-site-18679208-test/api/v2/
  • Endpoint: /subscriptions

You can find your own site-domain value by looking in the URL bar when you’re logged in. Putting it all together, we get the full API Request URL:
https://your-site-18679208-test.chargebee.com/api/v2/subscriptions

We can now enter all our values into API Connector to start importing Chargebee data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Create New API Request.
  2. In the Create Request interface, enter the Request URL we just created
    chargebee-img5

  3. Under Headers, enter a key-value pair like this:
    AuthorizationBasic {Token}

    Replace {Token} with the encoded value you got above in step 5 above.
    chargebee-img6


  4. Create a new tab. You can call it whatever you like, but here we’ll call it ‘Chargebee Subs’. While still in that tab, click ‘Set’ to use that tab as your data destination.
  5. Name your request. Again we’ll call it ‘Chargebee Subs’
  6. Click Run and a moment later you’ll see subscription details populate the Chargebee Subs tab in your Google Sheet:
    chargebee-img7

Following the above steps, you will now be able to import data from the Chargebee API directly into Google Sheets. By setting Google Sheets as a data source, you can also use this method to create Chargebee data visualizations and dashboards in Google Data Studio.

Comments:2

  1. Hi! Thaks for this, I’ve found it super useful! I’ve set it up, but I only get about 10 customers/ lines that appear whereas as per Chargebee, I would be expecting 100+. Any idea why only certain customers might be pulling through?

    1. Hi Agnes! You can check Chargebee’s documentation (https://apidocs.chargebee.com/docs/api#pagination_and_filtering) on pagination and filtering for info on this. By default they limit responses to 10 at a time (and have a max of 100). If you want more, you need to add the limit parameter to your URL, like this:
      your-site-18679208-test.chargebee.com/api/v2/subscriptions?limit=100

      If you have more than 100 responses, you’ll need to use their offset parameter. If your request returns more than 100 values, you’ll see a new field called ‘next_offset’ containing a value that looks like [“1558137600000″,”14780876”]. To retrieve the next page of results, this value needs to be added to the end of your URL, like this:
      your-site-18679208-test.chargebee.com/api/v2/subscriptions?limit=100&offset=["1558137600000","14780876"]

      However, to complicate things a bit, that value needs to be URL-encoded before it will work. You can do this directly in Sheets, using the function =ENCODEURL(A1), where A1 is your next_offset value. I suggest setting all of your API URLs up in Google Sheets and then reading from that value as described here: Create API Request Based on a Cell

Leave a Reply

Your email address will not be published.