Import Chargebee Data to Google Sheets

In this guide, we’ll walk through how to pull subscription data from the Chargebee API directly into Google Sheets, using the 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 Settings > Configure Chargebee > API keys from the menu.
    chargebee-img1
  2. On the API keys and webhooks page, you’ll see that a full-access test key has already been automatically created for you. Click to copy it to your clipboard.
    chargebee-img2
  3. One last step: Basic Access Authentication requires us to encode our authentication info to base 64. You can do this by entering the Chargebee API key you just copied into this form. Your encoded credentials will appear underneath.

     The encoding script runs in your browser, and none of your credentials are seen or stored by this site.
     

     

     

    (If you have any problems with the above, check this post for some alternate methods of encoding your credentials).

  4. Keep your encoded string handy, as we’ll need it in a minute. Congrats, you now have access to the Chargebee API!

PART 2: CREATE YOUR API REQUEST URL

We’ll first follow the Chargebee API documentation to request a list of active subscriptions.

  • Base URL:https://YOUR_SITE.chargebee.com/api/v2/
    Example Base URL: https://mixedanalytics-test.chargebee.com/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://mixedanalytics-test.chargebee.com/api/v2/subscriptions

PART 3: PULL CHARGEBEE API DATA INTO SHEETS

We can now enter all our values into API Connector and import Chargebee data into Google Sheets.

  1. Open up Google Sheets and click Add-ons > API Connector > Open.
  2. In the Create tab, enter the Request URL we just created
    chargebee-img3
  3. Under Headers, enter Authorization as your Key, and Basic YOUR_BASE64_ENCODED_TOKEN as your Value, like this:
    AuthorizationBasic YOUR_BASE64_ENCODED_TOKEN

    Replace YOUR_BASE64_ENCODED_TOKEN with the encoded value you got above in step 4 above.
    chargebee-img4

  4. We don’t need any authentication, so leave that as None.
  5. Create a new tab, give it a name, and click ‘Set current’ to use that tab as your data destination.
  6. Name your request and click Run. A moment later you’ll see subscription details populate the Chargebee Subs tab in your Google Sheet:
    chargebee-img5

PART 4: MORE EXAMPLE API URLS

You can check the documentation for the full list of available API requests, but if you just want to jump in, you can play around with the URLs you enter in the API URL path field. Try the following (one at a time):

  • https://YOUR_SITE.chargebee.com/api/v2/customers
  • https://YOUR_SITE.chargebee.com/api/v2/invoices
  • https://YOUR_SITE.chargebee.com/api/v2/transactions
  • https://YOUR_SITE.chargebee.com/api/v2/plans
  • https://YOUR_SITE.chargebee.com/api/v2/coupons

PART 5: HANDLE PAGINATION

  1. Note Chargebee’s limits on the number of records returned on a response. By default, only 10 records will be returned unless you use the ‘limit’ and ‘next_offset’ parameters as described in their documentation.
    chargebee-img8   To get 100 records, you’d use the ‘limit’ parameter and to return more than 100 you’d then make a second request using the ‘offset’ parameter, which will be populated with the value that appears in a ‘next_offset’ field in your sheet whenever the response contains additional records. For example:
    page 1: your-site.chargebee.com/api/v2/subscriptions?limit=100
    page 2: your-site.chargebee.com/api/v2/subscriptions?limit=100&offset=["1558137600000","14780876"]
  2. In API Connector, you can either run these requests separately, or loop through them automatically using pagination handling (paid feature):
    chargebee-img9

2 thoughts on “Import Chargebee Data to Google Sheets”

  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?

    Reply
    • 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"]

      Reply

Leave a Comment