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. We’ll first get an API key from Mailchimp, and then set up a request to pull in email campaign details from your Mailchimp account to your spreadsheet.
- Part 1: Get your Mailchimp API Key
- 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
PART 1: GET YOUR MAILCHIMP 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/)
- Click the Create A Key button
- You’ll now see your API key listed in a section titled “Your API keys”
- Congrats, that’s it! You now have access to the Mailchimp API, and can start pulling Mailchimp data into Google Sheets.
PART 2: CREATE YOUR MAILCHIMP API REQUEST URL
Base URL: https://YOUR_DATA_CENTER.api.mailchimp.com/3.0
Putting it all together, we get the full API Request URL:
Of course, you’ll need to substitute in your own values for
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 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
We’re now ready to enter all our values into API Connector and import Mailchimp data into Google Sheets.
- Open up Google Sheets and click Add-ons > API Connector > Open.
- In the Create screen, enter the Request URL we just created
- Under Headers, enter “Authorization” as your Key, and “apikey YOUR_API_KEY” as your Value, like this:
It should look like this:
Authorization apikey YOUR_API_KEY
- Create a new tab and click ‘Set current’ to use that tab as your data destination.
- 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:
- The Mailchimp API Playground is a great resource to test out different API calls.
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.
To get more records, use the ‘count’ and ‘offset’ parameters, like this:
With API Connector, you can either set these up as separate requests or loop through them automatically with pagination handling (paid feature).