Import Chargebee Data to Google Sheets

Import Chargebee Data to Google Sheets

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 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’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://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: ENTER VALUES INTO API CONNECTOR

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 > Create New API Request.
  2. In the Create Request interface, enter the Request URL we just created
    chargebee-img3
  3. Under Headers, enter Authorization as your Key, and Basic {base64-encoded token} as your Value, like this:
    AuthorizationBasic {base64-encoded token}

    Replace {base64-encoded token} with the encoded value you got above in step 4 above.
    chargebee-img4

  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-img5

PART 4: CHARGEBEE API + GOOGLE SHEETS NOTES AND EXPANSIONS

  1. 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
  2. By default Chargebee limits responses to 10 at a time, with a max of 100 (documentation). If you want more than 10, you need to add the limit parameter to your URL, like this:
    your-site.chargebee.com/api/v2/subscriptions?limit=100
  3. If you have more than 100 responses, you’ll need to use their offset parameter too. 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.chargebee.com/api/v2/subscriptions?limit=100&offset=["1558137600000","14780876"]
  4. To complicate matters a bit, that value needs to be encoded. In Google Sheets, you can encode values using the ENCODEURL function. For example, if the 'next_offset' value was returned to cell AX2, you could use the formula =ENCODEURL(AX2) to produce a value like %5B%221558137600000%22%2C%2214780876%22%5D. Now your new request would look like https://your-site.chargebee.com/api/v2/subscriptions?limit=100&offset=%5B%221558137600000%22%2C%2214780876%22%5D.

    If you're taking this approach, I recommend setting up your query URLs in Google Sheets, and using API Connector's function to reference cells in your request.

IMPORTANT SECURITY NOTE

Anyone with Owner or Edit access to your Google Sheet can view all the information you've saved within API Connector, including API keys and other credentials. Treat these keys as passwords and limit access to your sheet accordingly.

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.