API Connector Documentation

Print

Import BigCommerce Data to Google Sheets

Last Updated OnApril 6, 2024
byAna
< Knowledge Base

In this guide, we’ll walk through how to pull data from the BigCommerce API directly into Google Sheets, using the API Connector add-on for Sheets.

Contents

Before You Begin

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

Part 1: Get Your BigCommerce API Key

  1. Log in to BigCommerce and go to Settings › API Accounts 
  2. Under API accounts, click +Create API account
  3. Set the token type to V2/V3 API token, and enter a name. Note the ID in the API Path, we'll refer to this as your_path_id later.
    bigcommerce-path
  4. Select the scopes to which you'd like your token to have access (info).
  5. Click Save and note the value in the Access token field. Keep it handy as you'll need it in a moment.
    bigcommerce-accesstoken
  6. Congrats! You're now ready to use the BigCommerce API.

Part 2: Pull BigCommerce API Data into Sheets

For this example, we'll get a list of orders. Substitute in your own path ID and access token where it says your_path_id and your_access_token.

  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://api.bigcommerce.com/stores/your_path_id/v2/orders?min_date_created=2023-03-01&limit=250
    • Headers:
      • Content-Type: application/json
      • Accept: application/json
      • X-Auth-Token: your_access_token
  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 data populate your sheet.
    bigcommerce-response

Part 3: Handle Pagination

By default, BigCommerce limits API results to 50 records. To get more, set the limit parameter to the maximum of 250. Then, to get more than 250 records you'll need to paginate through multiple pages of data as described here:

  • Type: Page parameter
  • Page parameter: page
  • Run until: choose when to stop fetching data
    bigcommerce-pagination

(Note: the BigCommerce API seems to run very slowly with pagination and may time out after about 3-4 pages).

Part 4: API Documentation

Official API documentation: https://developer.bigcommerce.com/docs/rest-management/orders#get-all-orders

2 thoughts on “Import BigCommerce Data to Google Sheets”

  1. The API is getting quite slow after selecting more than 3 pages of data, so I was thinking to populate historical data with different data runs. After I add all the historical data I want to set it on an output mode merge to populate future data automatically without losing the historical data.

    I tried to add &max_date_created=2023-03-31 to the request URL, but that didn't do the trick.

    Am I doing something wrong with the above method? Or do you have any other recommendations on how to pull in the historical data.

    Reply

    • Hey Robin, first, please make sure you've included the Content-Type: application/json and Accept: application/json headers. Without them, BigCommerce will return XML, which takes a lot longer to process. If you include those headers you should be able to fetch a lot more data at once.
      As for your question about the max date, that looks right to me. If data isn't merging, it could be related to your merge key or configuration. What happens if you just use append mode? Seems like you don't need to merge anything if each period doesn't overlap.

      Reply

Leave a Comment

Jump To