Print

Import ActiveCampaign Data to Google Sheets

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

We'll first get an API key from ActiveCampaign, and then set up a request to pull in contact data from ActiveCampaign to your spreadsheet.

Contents

Before You Begin

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

Part 1: Get Your ActiveCampaign API Key

  1. Log in to ActiveCampaign and click Settings in the left side navigation menu.
    api-connector-activecampaign-img1
  2. The Account Settings menu will appear. Click Developer.
    api-connector-activecampaign-img2
  3. You should now see a page containing your API URL and Key. Keep the key handy as you'll need it in a moment. Congrats! You're now ready to use the ActiveCampaign API.api-connector-activecampaign-img3

Part 2: Pull ActiveCampaign API Data into Sheets

For this example, we'll get a list of contacts created before a certain date. Where it says your-domain, substitute in your own base URL. Retrieve your base URL by navigating to Settings > Developer as shown here: https://developers.activecampaign.com/reference/authentication

  1. Open up Google Sheets and click Extensions > API Connector > Open > Create request.
  2. In the request form enter the following:
    • ApplicationCustom
    • MethodGET
    • Request URLhttps://your-domain/api/3/contacts&limit=100&filters[created_before]=2020-03-08
    • Headers:
      • API-Tokenyour_api_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 contacts populate your sheet.
    activecampaign-response

Notes

  1. Change the endpoints as described in the documentation to retrieve different data. For example, changing it from /contacts to /campaigns will produce a list of all your ActiveCampaign campaigns.
  2. If your contacts utilize custom fields, these custom field values can be retrieved using the /fieldValues endpoint. As the fieldValues endpoint will only produce their ID, you can also use the /fields endpoint to get their name. Once you have all the values in Sheets, you can merge them together as desired using queries and other functions native to Sheets.

Part 3: Fetch and Append New Contacts Only

Rather than retrieving the entire data set each time you run your request, you can set your request to fetch new data only. Here’s one approach:

  1. Create an initial request to the /contacts endpoint. Use the field editor to select just the fields you want and assign them to specific columns in your report.
  2. Run the request, sending the response to a sheet called Contacts.
  3. Now in a second sheet called Max, get the maximum (i.e. most recent) contacts.id from the response data with a formula like =max(Contacts!A:A)
  4. Update your request to reference this cell in the id_greater parameter of your request.
    activecampaign-referencecell
  5. This will ensure that each request starts from the latest ID, i.e. the end of the prior data pull. Select Append mode to add each new data pull to the end of your existing dataset.
  6. Set your request to run on a schedule. You won’t need to update your request again.

Part 4: Handle Pagination

  1. Note Active Campaign's limits on the number of records returned on a response. By default, only 20 records will be returned unless you use the 'limit' and 'offset' parameters as described in their documentation. api-connector-activecampaign-img8

  2. With API Connector you can either add those parameters manually or loop through them automatically with offset-limit pagination handling (paid feature), like this:
    • API URL: enter your request URL as usual, including limit=100
    • Pagination type: offset-limit
    • Offset parameter: offset
    • Limit parameter: limit
    • Limit value: 100
    • Run until: choose when the request should stop running.
      pagination-offset-limit

Part 5: API Documentation

Official API documentation: https://developers.activecampaign.com/reference

Leave a Comment

Jump To