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:

var API_KEY = 'Insert your API key here';
var LIST_ID = 'Insert your List ID here';

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.):

var API_KEY = 'Make sure your API key is in here';
var LIST_ID = 'Make sure your List ID is in here';

// call the Mailchimip API to get campaign data for your list
function mailchimpCampaign() {
  
  // URL and params for the Mailchimp API
  var root = 'https://us11.api.mailchimp.com/3.0/';
  var endpoint = 'campaigns?count=100';
  
  // parameters for url fetch
  var params = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': {
      'Authorization': 'apikey ' + API_KEY
    }
  };
  
  // call the Mailchimp API
  var response = UrlFetchApp.fetch(root+endpoint, params);
  var data = response.getContentText();
  var json = JSON.parse(data);

  // get just campaign data
  var campaigns = json['campaigns'];
  
  // Log the campaign stats
  Logger.log('Number of campaigns: ' + campaigns.length);
  
  // print out all the campaign headings
  campaigns.forEach(function(campaign) {
    Logger.log(campaign['settings']['subject_line']);
  });
}

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:

'campaigns?count=100';

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:

var API_KEY = 'Make sure your API key is in here';
var LIST_ID = 'Make sure your List ID is in here';

// call the Mailchimip API to get campaign data for your list
function mailchimpCampaign() {
  
  // URL and params for the Mailchimp API
  var root = 'https://us11.api.mailchimp.com/3.0/';
  var endpoint = 'campaigns?count=100';
  
  // parameters for url fetch
  var params = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': {
      'Authorization': 'apikey ' + API_KEY
    }
  };
  
  try {
    // call the Mailchimp API
    var response = UrlFetchApp.fetch(root+endpoint, params);
    var data = response.getContentText();
    var json = JSON.parse(data);
    
    // get just campaign data
    var campaigns = json['campaigns'];
    
    // blank array to hold the campaign data for Sheet
    var campaignData = [];
  
    // Add the campaign data to the array
    for (var i = 0; i < campaigns.length; i++) {
      
      // put the campaign data into a double array for Google Sheets
      if (campaigns[i]["emails_sent"] != 0) {
        campaignData.push([
          i,
          campaigns[i]["send_time"].substr(0,10),
          campaigns[i]["settings"]["title"],
          campaigns[i]["settings"]["subject_line"],
          campaigns[i]["recipients"]["recipient_count"],
          campaigns[i]["emails_sent"],
          (campaigns[i]["report_summary"]) ? campaigns[i]["report_summary"]["unique_opens"] : 0,  
          (campaigns[i]["report_summary"]) ? campaigns[i]["report_summary"]["subscriber_clicks"] : 0
        ]);
      }
      else {
        campaignData.push([
          i,
          "Not sent",
          campaigns[i]["settings"]["title"],
          campaigns[i]["settings"]["subject_line"],
          campaigns[i]["recipients"]["recipient_count"],
          campaigns[i]["emails_sent"],
          "N/a",
          "N/a"
        ]);
      }
    }
    
    // Log the campaignData array
    Logger.log(campaignData);
    
    // select the campaign output sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('Campaign Analysis');
    
    // calculate the number of rows and columns needed
    var numRows = campaignData.length;
    var numCols = campaignData[0].length;
    
    // output the numbers to the sheet
    sheet.getRange(4,1,numRows,numCols).setValues(campaignData);
    
    // adds formulas to calculate open rate and click rate
    for (var i = 0; i < numRows; i++) {
      sheet.getRange(4+i,9).setFormulaR1C1('=iferror(R[0]C[-2]/R[0]C[-3]*100,"N/a")');
      sheet.getRange(4+i,10).setFormulaR1C1('=iferror(R[0]C[-2]/R[0]C[-4]*100,"N/a")');
    }
    
  }
  catch (error) {
    // deal with any errors
    Logger.log(error);
  };
}

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:

=QUERY($B$3:$J,
"select B, avg(I), avg(J)
where F > 0
group by B
label avg(I) 'Open Rate', avg(J) 'Click Rate'"
,
1)

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:

=ArrayFormula(IF(ISBLANK(N7:N),"",AVERAGE(N7:N)))

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

=ArrayFormula(IF(ISBLANK(O7:O),"",AVERAGE(O7:O)))

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:

// call the Mailchimip API to get monthly list growth
function mailchimpListGrowth() {
  
  // URL and params for the Mailchimp API
  var root = 'https://us11.api.mailchimp.com/3.0/';
  var endpoint = 'lists/' + LIST_ID + '/growth-history?count=100';
  
  var params = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': {
      'Authorization': 'apikey ' + API_KEY
    }
  };
  
  try {
    // call the Mailchimp API
    var response = UrlFetchApp.fetch(root+endpoint, params);
    var data = response.getContentText();
    var json = JSON.parse(data);
    
    // get just list history data
    var listGrowth = json['history'];
    
    // blank array to hold the list growth data for Sheet
    var monthlyGrowth = [];
    
    // Add the list growth data to the array
    listGrowth.forEach(function(el) {
      monthlyGrowth.push([el.month, el.existing, el.optins, el.imports]);
    });
    
    // Log the monthlyGrowth array
    Logger.log(monthlyGrowth);
    
    // select the list growth output sheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName('List Growth');
    
    // calculate the number of rows and columns needed
    var numRows = monthlyGrowth.length;
    var numCols = monthlyGrowth[0].length;
    
    // output the numbers to the sheet
    sheet.getRange(4,1,numRows,numCols).setValues(monthlyGrowth.reverse());
    
    // adds formulas for absolute and relative growth
    for (var i = 0; i < numRows; i++) {
      sheet.getRange(4+i,5).setFormulaR1C1('=iferror(R[0]C[-3] - R[-1]C[-3],0)');  // absolute monthly change in list
      sheet.getRange(4+i,6).setFormulaR1C1('=iferror((R[0]C[-4] - R[-1]C[-4])/R[-1]C[-4],0)').setNumberFormat("0.00%");  // rate of change in list
    }
    
  }
  catch (error) {
    // deal with any errors
    Logger.log(error);
  };
}

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.

Resources

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.

65 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” (http://developer.mailchimp.com/documentation/mailchimp/reference/campaigns/), but if you instead returns “reports” (http://developer.mailchimp.com/documentation/mailchimp/reference/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!

      Cheers,Ben

  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. 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
            P

      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+'.api.mailchimp.com/3.0/';

        Found this solution from some old MC 2.0 API code.

      3. Hey Ben,

        Great stuff! Thanks for sharing your knowledge! I am having a similar issue as Reid when I implemented the scripts.

        TypeError: Cannot call method “forEach” of undefined.

        Any help advice would be appreciated.

  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 .

    Regards

  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: http://developer.mailchimp.com/documentation/mailchimp/reference/campaigns/

    2. If anyone is still reading this: I had the same problem. It is caused by campaigns count in line 8 being set to 100. Make it 1000 and the script should fetch all campaigns.

      var endpoint = ‘campaigns?count=100’;

  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?

    /Lucas

      1. Hi Ben
        is there a way to by pass the 1000 data row limit?

        My case has 65,000 + unsubscribers….

        Also is there a way to pull only status = “Unsubscribes”?

        Thanks,
        Ben

    1. Hi Lucas,

      google app script is a new language to me… did you happen to figure out how to pull email list or specifically only unsubscribe emails from Mailchimp without having to manually export.

      thanks,

  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’
    Thomas

  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!
    Darren

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

    Error
    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
    sheet.getRange(4,1,numRows,numCols).setValues(campaignData);

    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!

        1. Hi Mike (and all). I am also experiencing this issue. Very new to dealing with anything code related. I created the two internal tabs in this sheet labeled “Campaign Activity” and “List Growth” per your comment – however I am still receiving the same “TypeError: Cannot read property ‘getRange’ of null” message.

          I would be very grateful if you could explain what this means, and how to solve it.

  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.

    Thanks!

    1. Thank you Ben,
      This is yet another amazing find which I will deploy but I wanted to add a subscriber to mailchimp from our Google sheet and I found the actual coding a little confusing around the tags so I thought I will post this for others. This is based on your original item above plus a dollop of stackoverflow and trial and error.

      function addMe() {
      addMailChimpSubscriber(“test1.test@test.com”,”test”,”mcTestFace”,”TagToAssign”, );
      }

      function addMailChimpSubscriber(email, firstName,lastName, tag ) {
      var payload = {
      “apikey” : API_KEY,
      “email_address”: email,
      “first_name”: firstName,
      “last_name” : lastName,
      “status”: “subscribed”,
      “merge_fields”: {
      FNAME: firstName,
      LNAME: lastName
      },
      “tags”: [tag]
      };

      var headers = {
      “Authorization”: “apiKey ” + API_KEY
      };

      var data = {
      “method”: “POST”,
      “muteHttpExceptions”: true,
      “headers”: headers,
      “contentType”: “application/json”,
      “payload”: JSON.stringify(payload) // The payload should be stringified
      };
      var root = ‘https://{your MC server}.api.mailchimp.com/3.0/’;
      var endpoint = root+ ‘lists/’ + LIST_ID + ‘/members’;

      try {
      var response = UrlFetchApp.fetch(endpoint, data); // pass in data, not json

      if (response.getResponseCode() === 200) {
      // all good!
      Logger.log(“Success”);
      Logger.log(response);
      } else {
      Logger.log(“Issues”);
      Logger.log(response);
      }
      } catch (err) {
      // handle the error here
      Logger.log(“Error”);
      Logger.log(err);
      }
      }

  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.

      1. Yes, for me changing datacenter to our account datacenter resolved the issue. Our datacenter is us8, in the Ben’s code – us11
        So this URL should be adjusted accordingly to your datacenter ‘https://us1.api.mailchimp.com/3.0/
        It could be us20, us1, us8 etc. Just check the URL when you are logged in to the MailChimp account and you will see yours.

  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!
    -Emily

  20. Hi All!

    I followed all steps but when running script, it doesn´t shows information in my spreadsheet. When analyzing logs, the message shows:

    “no records found. Use the Logger API to add records to your project”

    Any help please? I only need to get info abour List Growth.

    Thaks a lot!!!!

  21. Hi Ben,

    You have got great set of posts on Google Scripts.

    However, I am kind of stuck on a point and unable to find guidance on that.

    I am trying to integrate Pocket APIs, but getting stuck in the way their OAuth works (https://getpocket.com/developer/docs/authentication)

    I have tried using OAuth library, but still not able to find way around this, which seem to be working bit differently than other portals.

    I have posted my questions and issue on this link, but haven’t got any response yet: https://github.com/gsuitedevs/apps-script-oauth2/issues/203

    It would be great if you could guide some.

    Thanks,
    Mukesh

  22. I wanted to grab more than 100 rows so I adjusted both instances of: campaigns?count=1000 <- to 1000, but still only get 100 rows returned. We have a lot of historic campaigns.

  23. Hi Ben,

    This was very helpful as I am new with app script. I was able to connect through mailchimp api to retrieve unsubscribed emails from mailchimp to google sheets!

    Out of curiosity, is it possible to send emails from google sheet to mailchimp automatically using app script. App script is so cool! 😛

    Thanks,
    Benjamin

  24. Hi Ben,

    This is very useful to extract the campaign data. Just wondering, if it is possible to get the data/stats from the Automation email?

    Thanks,

    Allan

    1. +1! Same issue here. The script is just giving me single-send email data and not sure how to get data on automation campaigns…

  25. Hi Ben. I’m trying to use your code but script editor is saying there’s an error on line 29 with “campaigns.length”. Would you know what the issue is? Thank you in advance for your help.

    Logger.log(‘Number of campaigns: ‘ + campaigns.length);

    TypeError: Cannot read property ‘length’ of undefined (line 29, file “Code”

  26. Hi Ben, very helpful script! Data from CampaignAnalysis is showing just fine, but ListGrowth is showing zeroes only, see below. Would you know what is the cause of that? Thanks! Ivan

    Informatie [[2021-03, 0.0, 0.0, 0.0], [2021-02, 0.0, 0.0, 0.0], [2021-01, 0.0, 0.0, 0.0], [2020-12, 0.0, 0.0, 0.0], [2020-11, 0.0, 0.0, 0.0], [2020-10, 0.0, 0.0, 0.0], [2020-09, 0.0, 0.0, 0.0], [2020-08, 0.0, 0.0, 0.0], [2020-07, 0.0, 0.0, 0.0], [2020-06, 0.0, 0.0, 0.0], [2020-05, 0.0, 0.0, 0.0], [2020-04, 0.0, 0.0, 0.0], [2020-03, 0.0, 0.0, 0.0], [2020-02, 0.0, 0.0, 0.0], [2020-01, 0.0, 0.0, 0.0], [2019-12, 0.0, 0.0, 0.0], [2019-11, 0.0, 0.0, 0.0], [2019-10, 0.0, 0.0, 0.0], [2019-09, 0.0, 0.0, 0.0]]

  27. Test Running The Script everything works Well.
    But the Data is not populating in Sheets……

    What am i doing Wrong? I have struggle with this for a While

  28. Hi Ben,

    Thanks for the script and tutorial. The campaign data works like a charm but I’m having an issue with List Growth. It just returns zeros. API Key and List ID are set correctly. Is there something else that needs to be adapted or do you know if there has been a change to the Mailchimp backend recently that’s preventing this script from working?

    Many thanks,
    M

    1. It’s to do with Mailchimp changing the list data they collect. In the script you should replace:

      // Add the list growth data to the array
      listGrowth.forEach(function(el) {
      monthlyGrowth.push([el.month, el.existing, el.optins, el.imports]);
      });

      With:

      // Add the list growth data to the array
      listGrowth.forEach(function(el) {
      monthlyGrowth.push([el.month, el.subscribed, el.unsubscribed, el.cleaned]);
      });

      And then switch the Optin heading on the sheet for Unsubscribed and Imported for Cleaned.

  29. Its an amazing tutorial, but I had a small doubt. I have everything as explained but am getting an error saying Info TypeError: Cannot read properties of undefined (reading ‘length’). Can you please help me with this.

    Thank you

  30. OMG I figured out how to get unsubscribes. You have to change the code to pull “reports” instead of “campaigns”. Change campaigns to reports in these lines of the code:

    // URL and params for the Mailchimp API
    var endpoint = ‘reports?count=50&status=sent&sort_field=send_time&sort_dir=DESC’;

    // get just campaign data
    var reports = json[‘reports’];

    // Add the campaign data to the array
    for (var i = 0; i < reports.length; i++) {

    // put the campaign data into a double array for Google Sheets
    if (reports[i]["emails_sent"] != 0) {
    campaignData.push([
    i,
    reports[i]["send_time"].substr(0,10),
    reports[i]["campaign_title"],
    reports[i]["subject_line"],
    reports[i]["list_name"],
    reports[i]["emails_sent"],
    reports[i]["opens"]["unique_opens"],
    reports[i]["clicks"]["unique_clicks"],
    reports[i]["unsubscribed"]
    ]);
    }

Leave a Reply

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