offset / while loop problem - using google apps script to download mailchimp 'members' data from list

Question

Hey I'm trying to write a google apps script that gets all Mailchimp email addresses and statuses into a google sheet. However, it stops after the first 1,000 and I can't figure out the off-set / get_all that I need to make this work.

function mailchimpMembers() {

  // URL and params for the Mailchimp API
  var root = 'https://us19.api.mailchimp.com/3.0/';
  var endpoint = 'lists/' + LIST_ID + '/members?offset=0&count=10000';

  var params = {
    'method': 'GET',
    'muteHttpExceptions': true,
    'headers': {
      'Authorization': 'apikey ' + API_KEY
    }
  };

  try {
    // call the Mailchimp API

    var keep_going = true;
    var offset = 0;
    var memberList = new Array();
    memberList.push(["email","status"]);


     while(keep_going) {
        var response = UrlFetchApp.fetch(root+endpoint+offset, params);
        var data = response.getContentText();
        var json = JSON.parse(data);
      keep_going = json["get_all"];
      offset = json.offset;
      var listGrowth = json['members'];
      listGrowth.forEach(function(el) {
      memberList.push([el.email_address, el.status]);
    });
    Logger.log(memberList);
    };

  writeResults("list",memberList);
  }
  catch (error) {

    Logger.log(error);
  };
}

Answer 1

Results from this API are paginated. See the Pagination section in MailChimp's Getting Started guide. This type of pagination is fairly typical for these types of APIs.

With each GET request, you'll need to set count for the number of results per page. (I see you've already done this, but I think you have a typo. You have '10000' in your code instead of '1000'.)

You'll also need to set offset to start the results of each page after the last result of the previous page. So if your count is 1000, you'll need to increment the offset by a thousand each time:

offset = offset + 1000

Finally, to set your keep_going boolean, you'll need to check if the next offset exceeds the total number of results. This particular endpoint returns a total_items property, which you can use for this comparison:

keep_going = offset > total_items

Make sure you're doing this comparison after you've updated the offset (opposite to what is currently in your code). Otherwise, you'll be checking the offset for the request you just made.

P.S. You'll need to re-write your original endpoint definition to not hard-code a value for the offset, since you'll need to re-write this value in your while-loop. I recommend:

var endpoint = 'lists/' + LIST_ID + '/members?count=10000&offset=';