API Connector Documentation

Print

Import Chargebee Data to Google Sheets

Last Updated OnMarch 19, 2024
byAna
< Knowledge Base

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.

Before You Begin

Click here to install the API Connector add-on from the Google Marketplace.

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.

     

  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: Pull Chargebee API Data into Sheets

Now let's get some data into Sheets. For this example request we'll request a list of active subscriptions. Enter your own domain where it says your_site (you can find your own site-domain value by looking in the URL bar when you're logged in), and your encoded key where it says your_encoded_key.

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • Application: Custom
    • Method: GET
    • Request URL: https://your_site.chargebee.com/api/v2/customers
    • Headers:
      • Authorization: Basic your_encoded_key
  3. Create a new tab and click Set current to use that tab as your data destination.
  4. Name your request and click Run. A moment later you’ll see a list of customers in your Google Sheet:
    chargebee-response

Part 3: 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 try the following:

  • https://YOUR_SITE.chargebee.com/api/v2/subscriptions
  • 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 4: Handle Pagination

  • 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
  • With API Connector you can either run these request URLs manually or loop through them automatically with cursor pagination handling, like this:
    • API URL: enter your request URL, including limit=100
    • Pagination type: cursor
    • Next token parameter: offset
    • Next token path: next_offset
    • Run until: choose when to stop running the request
      chargebee-pagination-cursor

Part 5: API Documentation

Official API documentation: https://apidocs.chargebee.com/docs/api

4 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! By default Chargebee limits responses to 10 at a time. If you want more, you can add the limit=100 parameter to your URL, and 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"], so to retrieve the next page of results, this value needs to be added to the end of your URL, e.g.
      your-site-18679208-test.chargebee.com/api/v2/subscriptions?limit=100&offset=["1558137600000","14780876"]
      To do this automatically, you can loop through records with pagination handling.

      Reply

  2. How do we only update new records added daily?

    When we set the API to do a request daily that appends, it adds the data as new completely.

    Reply

    • Chargebee's docs for the customers endpoint show they enable a "created_at[after]" parameter. So you can do a process like this to make sure you fetch new data only:
      1. Run an initial data request to fetch existing or historical customer data
      2. Create a sheet called maxDate with a function that fetches the maximum, i.e. most recent, value of the list.customer.created_at field, e.g. =max('Customers'!H:H)
      3. Now switch to merge or append mode and plug that date into your request, e.g. https://mixedanalytics-test.chargebee.com/api/v2/customers?created_at[after]=+++maxCreate!A1+++
      Every time it runs it will automatically plug in the latest create date such that it only fetches new data.

      Reply

Leave a Comment

Jump To