Google Sheets and Mailchimp integration using Apps Script

In this post, we’re going to see how to setup a Google Sheets and Mailchimp integration, using Apps Script to access the Mailchimp API.

The end goal is to import campaign and list data into Google Sheets so we can analyze our Mailchimp data and create visualizations, like this one:

Google Sheets and Mailchimp integration for campaign data analysis

Mailchimp is a popular email service provider for small businesses. Google Sheets is popular with small businesses, digital marketers and other online folks. So let’s connect the two to build a Mailchimp data analysis tool in Google Sheets!

Once you have the data from Mailchimp in a Google Sheet, you can do all sorts of customized reporting, thereby saving you time in the long run.

I use Mailchimp myself to manage my own email list and send out campaigns, such as this beginner API guide (Interested?), so I was keen to create this Mailchimp integration so I can include Mailchimp KPI’s and visualizations in my business dashboards.

For this tutorial I collaborated with another data-obsessed marketer, Julian from Measure School, to create a video lesson. High quality video tutorials are hard to create but thankfully Julian is a master, so I hope you enjoy this one:

(Be sure to check out Julian’s YouTube channel for lots more data-driven marketing videos.)

If you’re new to APIs, you may want to check out my starter guide, and if you’re completely new to Apps Script, start here.

Otherwise let’s begin.

How to create a Google Sheets and Mailchimp integration to get campaign data

Analyze Mailchimp campaign data in Google Sheets

In the GIF above, you can see how I’ve added a custom menu to my Google Sheet and Mailchimp integration tool to retrieve campaign data. When I click the menu option, it fires the function to call the Mailchimp API, which sends back a packet of data containing my campaign results. I parse this data to extract just the information I want, and then add it to the data table you can see in the background. The chart in the foreground updates automatically to show the latest results.

Google Sheets Mailchimp integration setup

The Google Sheet setup is super simple for this example, since all we need initially is a row of headings like so:

Google Sheet Setup

Change the name of this sheet to Campaign Analysis (so it matches line 67 of our code below in the “Outputting the data into the Google Sheet” section).

Next, open up the Script Editor (Tools > Script editor...), clear out the boiler plate code and paste in the following code:

Your script editor window should look like this:

Apps script editor setup for Mailchimp integration

We’ll jump over to Mailchimp now and find our API key & email list ID, before we write any of the script.

You create your new API key under your Profile > Extras > API keys, which will look like this:

mailchimp api keys

Click on Create A Key to create an API key and rename it if you wish in the Label column. Now copy this API key value (the long string of letters and numbers) into the API_KEY variable in your Apps Script code, where it says Insert your API key here above.

The other variable we need before we get started is the id of the email list you want to analyze. This can be found under Lists > Settings > List name and defaults as shown here:

Mailchimp list ID

As with the API key, copy this list ID into the LIST_ID variable in your code, where it says Insert your List ID here.

Note, for both the API key and the List ID, make sure you leave the quote marks in place in your code, so the two variables are colored red in your script editor.

Getting data back from the API

Add this code to the script editor and change the us11 half-way across line 8 to match the last 4 digits of the API key that you got hold of above (it may well be us11 in which case you can just leave as it. This is the identification of the data centre for your account – read more.):

Note: the first time you run this function you’ll need to grant it permissions – like this example here.

When the script has finished running, open the logs (View > Logs) to see what data it returned:

Data from Google Sheets Mailchimp integration

If your logs show data like this then that’s great news! In this example, I extracted the number of campaigns (line 29) and their subject lines (lines 32 to 34).

You’re receiving data from the Mailchimp API, so the next step is to output that into a Google Sheet to complete the Mailchimp integration.

One other thing to note with this code is the query parameter on line 9:

By default, the Mailchimp API returns only 10 results, so I’ve added this query parameter ?count=100 to return up to 100 results (more than enough for my situation). You can modify this value to suit your needs.

Outputting the data into the Google Sheet

Next, update the code to print the results to your spreadsheet:

The full code is here on GitHub.

When you run this again, you’ll be prompted to grant access permission to the Spreadsheet Service this time.

The output in our Google Sheet Mailchimp integration looks something like this:

Google Sheets and Mailchimp integration showing campaign data

You may notice the words “undefined” in the campaign subject line. These were campaigns where I A/B-tested the headlines, so it’d require a little more digging into the API data to return these ones. However, since I didn’t need these subject lines to create my chart in this example, I was happy to leave them as they are.

Preparing the data to chart

You may notice that some rows in your dataset have no data, likely because they were draft or deleted campaigns, so we don’t really want to include them in our chart. Also, we only need the date, the open rate and the click-through rate for our chart, so we can make our life easier by creating a staging table with just those metrics.

So, in cell M3, next to the data table, add the following formula which will extract these columns:

"select B, avg(I), avg(J)
where F > 0
group by B
label avg(I) 'Open Rate', avg(J) 'Click Rate'"

You may observe that the inner workings of this formula look a lot like SQL (Structured Query Language, how we talk to databases). Well, you’re right, it’s Google’s Visualization API Query Language, which uses a SQL-like syntax to manipulate data.

For a comprehensive introduction to the QUERY function and its SQL-esque code, check out this post.

I’m using a where filter to remove any rows of data that did not send any emails, and I’m using the group by to take an average open/click rate on days where I sent multiple campaigns.

The nice thing about this QUERY formula is that it will always include all of our Mailchimp data, even when our script fetches new data. Therefore our chart will always be up-to-date.

I’ve used array formulas to add an average open rate and average click rate in the next two columns, so I can add these to my chart. Again, the array formula is perfectly suited here because it will create new formulas whenever any new Mailchimp data is pulled in.

The formula in cell P4 for open rate is:


And the formula in cell Q4 for the click rate is:


This gives us a chart data table as follows:

Campaign Chart Data table

Creating the campaign chart

This is the easiest step of all. Highlight your data table and Insert > Chart...

You can format it however you wish. I went for the smooth lines:

Google Sheets and Mailchimp integration for campaign data analysis

Here’s a link to the Google Sheet template. Feel free to make your own copy.

See monthly list growth from the Google Sheets and Mailchimp integration

Again, we start with a super simple Google Sheet (add a new tab next to your campaign analysis tab in your Mailchimp integration sheet):

Mailchimp list growth analysis

Make sure your sheet is named List Growth (so it matches our code on line 127).

Underneath the existing code, add the following function into your script editor:

The full code is here on GitHub.

Run this new function and you should find list growth data populating your Google Sheet:

List growth data from Mailchimp API

Lastly, you can create a pretty chart to illustrate this list growth over time:

Mailchimp list growth data from API

Here’s a link to the Google Sheet template. Feel free to make your own copy.


Getting started with the Mailchimp API official guide

Mailchimp API reference guide

Google Apps Script official documentation on the UrlFetchApp Class

A new post from Eivind Savio, showing another Apps Script example for pulling Mailchimp data into a Google Sheet, with a screenshot of the final Data Studio Mailchimp dashboard! 🙂

That’s all folks! I hope you enjoyed this tutorial. As always, feel free to leave a comment and share this with anyone you know who might be interested.

36 thoughts on “Google Sheets and Mailchimp integration using Apps Script”

  1. Thanks for sharing.

    I “hacked together” my own script yesterday to extract campaign data, and I chose a different approach, which means that I don’t get undefined if the newsletter was an A/B-test.

    From what I can see you are getting your data by returning “campaign” (, but if you instead returns “reports” (, you get more campaign data.

    I was thinking about digging into “List Growth” today, but you saved me from that. 🙂

    1. Great suggestion, thanks for commenting! Does it return the winning subject line in an A/B test then?

      The API is extensive so there’s loads of good information in there to explore. It’d be an interesting project to build a Mailchimp dashboard that auto-updated with all the latest data… some day!

  2. I didn’t include A/B variants in my data (but you can I see from the API). This means that in my report A and B are grouped together, and are reported as 1 campaign with 1 subject line (when the A/B-test was subject line).

    If the A/B-test was design, then it’s reported as 2 campaigns, but subject and campaign name is the same.

    If you are able to build that type of dashboard, you deserve a medal. 🙂

  3. Great tutorial! But when I copy the code and run it, I get the error ‘unexpected token’. What is the problem of that error?

    1. Did it have anything else in the error message? Difficult to know what caused that, but it often means a syntax error so worth checking all your code is correct and you have no typos (surprisingly hard to spot!). Also, if you copied the code from my blog post direct into your script editor check that no characters have come out funny from the copying… Hope that helps!


  4. This is great, but when doing nothing more than pasting in your code and changing the API key and list ID, I get an error when trying to run the List Growth function (the campaign functions works as expected). I get this:

    TypeError: Cannot call method “forEach” of undefined.

    Sorry to get all “support desk” on you, but I’d love to get this working. Thank you so much for the amazing resource!

    1. Hey Reid,

      🤔 It means that the listGrowth variable is undefined. Check that you’ve got the correct root for the list growth:

      var root = '';

      In this root variable, make sure that you swap the us11 for the last 4 digits of your api key (see here for more). And check for the correct endpoint:

      var endpoint = 'lists/' + LIST_ID + '/growth-history?count=100';

      Next, try adding two Logger lines just after the JSON.parse line and the listGrowth line, as follows:

      var json = JSON.parse(data);

      // get just list history data
      var listGrowth = json['history'];

      Hopefully this will illustrate where the issue is, and you can see where the data is not being returned correctly or not being parsed correctly.

      Let me know how you go.


      1. AH. Yes, that did it. I had changed the root variable in the first function, but not in the second one. Sorry for the bother, and thanks for your help! Now to spread this article wide and far (well, as wide and far as I can).

          1. Hi Ben,

            sorry to open this old story again here, but quickly got a question about :
            var listGrowth = json[‘history’]

            Was about to re-use these functions for a similar api endpoint, but while this is working flawless for arrays that are positions on “level1”, it seems to fail if the array I try to extract is lower down the tree.

            Am I right assuming that the line is looking for the ‘history’ array and storing the parsed json of that array in the new variable ‘listGrowth’?

            Currently I’m struggling to understand, why this same concept does not work if the array Im trying to look at is on another level?

            Thanks in advance

      2. To avoid this issue I think you can create a generic code that will handle dynamic “root”.

        Something like this:
        var API_KEY = 'XXXX-us5'; // MailChimp API Key
        var LIST_ID = ''; // MailChimp List ID
        var dc = API_KEY.split('-')[1];

        // URL and params for the Mailchimp API
        var root = 'https://'+dc+'';

        Found this solution from some old MC 2.0 API code.

  5. Hi,

    Thanks for the great post. I was surprised if we can find the detailed analysis (using Google Apps Script) for a campaign like e-mail Ids of audience who has opened, clicked, the mail .


  6. I’m having some interesting results. First, for Campaign Analysis, the data that is returned by the script into the sheet includes campaign data from more than one of my Mailchimp lists…but not all campaigns for all lists. Second, List Growth shows the correct data for the correct list, but stops populating data for number of subscribers after 60 data rows. Optins & Imported data continues to be returned beyond 60 data rows (up until the current month).

    I followed your link to get the full code from GitHub and pasted it into script editor, then I added my API key and the List ID and updated the var root as instructed…except that the last four digits of my API key are “-us5” and that returned an error…the error went away when I used only the last three digits of my API key or “us5”.

    Any help you can provide will be appreciated.

    1. Hey Jennifer! So the campaigns endpoint returns all campaign data from an account, so it isn’t restricted to a single list. You can pass a list_id parameter in the query to the api to restrict to one list. You should be able to change the endpoint line like this:

      var endpoint = 'campaigns?count=100&list_id=' + LIST_ID;

      where LIST_ID is your list id number you’ve specified at the top of the script. I only had one list in mailchimp when I wrote this article on my blog, so hadn’t appreciated this nuance 😉

      You can read more about this here:

  7. Hi Ben

    This is so useful – thanks. I also have a large amount of campaign data and would only want the report to return recent campaigns. Is there a way to edit the script so it is only campaigns sent since yesterday/in the last week. I know you can add ‘&since_send_time=’ to the endpoint, but is there a way of making this a dynamic rather than a static date?

  8. Hey Ben

    Is it possible to retrive all email adresses from a code in script editor?
    So you copi all your members email adresses into a sheet?


  9. Hi,
    My first foray in to apps scripts and I got it up and running! (You made it easy to copy/paste, thanks!)

    A few questions:
    1) Do I need to manually run the script every time I want to update the data?
    2) The data for the current month – is that true for up to the minute the script was run? In other words, if I run it again tomorrow, will that row update with new values?
    3) It seems as if MailChimp doesn’t have a parameter to cover that growth via 3rd party services like Unbounce and Zapier? My list is clearly growing however, 0 of that growth is coming from “Optins” or “Imported”. Does this make sense?

  10. Thanks to all that have contributed to this super script. I´m trying to include UNSUBSCRIBE per campaign, but didn´t find a way. Do you know how to do that? Any hint or help is much appreciated.

    Kind regards’

  11. I´m trying to add the UNSUBSCRIBE, HARD/SOFT BOUNCES per campaign, but didn´t find a way. Do you know how to do that? Any hint or help is much appreciated. Thanks.

  12. Hi Ben,

    The post is over a year old but still attracting a lot of views – certainly helped me! Thank you for that!

    Simple question I hope – I have multiple lists in Mailchimp. Is there a way to see them all in List Growth (either separately per list or altogether – whichever is easier) or do I need to change the LIST_ID every time?

    Thank you!

  13. I seem to get an error about AVG_SUM_ONLY_NUMERIC when trying to put the QUERY function in M3.

    Unable to parse query string for Function QUERY parameter 2: AVG_SUM_ONLY_NUMERIC

    This is when I’ve copy/pasted everything as is from the article. We have only had 2 email campaigns go out, but there are 4-6 other drafts and unsent emails that are also in this data set, but I keep getting #VALUE! in the M3 cell cause the query function must need a tweak of some kind or my data might…

  14. Hey Ben,

    thanks for this great post! Would love to set up this integration for MailChimp, too.

    Unfortunately, I am getting an error in the following line:
    // output the numbers to the sheet

    while var ss contains an object, var sheet is null. So the error I get is: “Cannot call method “getRange” of null”.

    Any idea why this might happen? Many thanks in advance!

  15. Great tutorial!

    I’m trying to add another parameter – the archive URL, so that I can pull that into Data Studio and allow users to click the link to view the campaign.

    Generally, what do you need to change to add another column to the sheet? I can see in the logs that the API is pulling the data in, but it’s not outputting it to the google sheet. Gives this error: [18-12-06 10:22:56:410 NZDT] Exception: Incorrect range width, was 8 but should be 9

    Could you provide some guidance? I’m sure it’s a small change but I can’t figure it out.


  16. Can we transfer data from google sheet to mailchimp auto response template emails contains variables depends on the data on the google sheet?

  17. i got this log
    [19-02-12 12:01:11:192 IST] []
    [19-02-12 12:01:11:308 IST] TypeError: Cannot read property “length” from undefined.

  18. Fantastic Post Ben! Thanks for the help. The issue I am having is on the List Growth data. (P.S. I am a complete noob) It is returning a lot of 0 for subscribers when in fact I have ~300+

    I began the list back in August of 2017 and it is only showing a subscriber count for 5 of the ~18 months

  19. Hello! Fantastic tutorial.

    Is there a way to adjust the monthly # of subscribers to a daily # of subscribers? I would like to have a running daily total so then when the report dates are selected in Datastudio, the number of new subscribers populates.

    Thanks in advance for your help!

Leave a Reply

Your email address will not be published. Required fields are marked *