Import Mailchimp Data to Google Sheets

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

There are 2 ways to connect to the Mailchimp API:

• Preset “Connect” button (OAuth) premium
• Personal API key. Please check the appendix for detailed instructions to retrieve your key.

Contents

• Before You Begin
• Part 1: Connect to the Mailchimp API
• Part 2: Create your API Request URL
• Part 3: Pull Mailchimp API Data into Sheets
• Part 4: Get More Mailchimp Data
• Part 5: Handle Pagination
• Appendix: Connect with a Mailchimp API Key

Before You Begin

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

Part 1: Connect to the Mailchimp API

If you haven’t connected API Connector to Mailchimp before, you’ll first need to initiate the connection as follows:

1. Open up Google Sheets and click Add-ons > API Connector > Manage Connections.
2. In the list of available connections, find Mailchimp and click Connect.
   
3. You will be asked to authorize the connection. Click Allow.
   
4. You’ll then be returned to your Google Sheet, and can verify that your connection is active in the Connections screen.
   

Part 2: Create Your Mailchimp API Request URL

We’ll first retrieve information about members in an email list.

Base URL: https://YOUR_DATA_CENTER.api.mailchimp.com/3.0
Endpoint: /lists/YOUR_LIST_ID/members

Putting it all together, we get the full API Request URL:

https://us20.api.mailchimp.com/3.0/lists/32d8e317e4/members

You’ll need to substitute in your own values for YOUR_DATA_CENTER and YOUR_LIST_ID, which you can find as follows:

• YOUR_DATA_CENTER: check the URL when you’re logged in to your Mailchimp account
  
• YOUR_LIST_ID: navigate to Audience > Preferences Center > Settings > Audience Name and Defaults. Once you click this, you’ll see your list ID (which they’ve now begun calling an “Audience ID”).
  

Part 3: Pull Mailchimp API Data into Sheets

Now let’s add our URL into API Connector and get some Mailchimp data in Google Sheets.

1. Open up Google Sheets and click Add-ons > API Connector > Open.
2. In the Create screen, enter the Request URL we just created
   
3. Under OAuth, choose Mailchimp
   
4. We don’t need any headers so just leave that section blank.
5. Create a new tab 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 your Mailchimp data populate the Mailchimp tab in your Google Sheet:

Part 4: Get More Mailchimp Data

If you want to see other data, like a list of campaigns, you’d just change the endpoint. For example, this URL will produce a list of campaigns:

https://us20.api.mailchimp.com/3.0/campaigns

Part 5: Handle Pagination

Note Mailchimp’s limits on the number of records returned on a response. By default, only 10 records will be returned unless you use the ‘count’ and ‘offset’ parameters as described in their documentation.

With API Connector, you can either set these up as separate requests or loop through them automatically with pagination handling (paid feature):
URL: https://us20.api.mailchimp.com/3.0/lists/32d8e317e4/members?offset=0&count=1000
Offset parameter: offset
Limit parameter: count
Limit value: 1000
Number of pages: enter the number of pages you’d like to fetch

Appendix: Connect with an API Key

1. As an alternative to using the preset OAuth integration, you can connect with an API key. Log into your Mailchimp account and navigate to the API Keys section (or just click this link: https://us1.admin.mailchimp.com/account/api/)
2. Click the Create A Key button
   
3. You’ll now see your API key listed in a section titled “Your API keys”
   
4. To use, under Headers, enter “Authorization” as your Key, and “apikey YOUR_API_KEY” as your Value, like this. Keep OAuth set to None as you’re authorizing with an API key.