How to pull data from multiple Mailchimp endpoints?

Question

The code below pulls data from the Mailchimp API Reports endpoint and adding it to Sheets.

I would like to add some more data from other endpoints (like fields from the "List/Audience" endpoint: member_count, total_contacts i.e.) but don't have a slick solution to this.

What's the best practice/solution here? Can this task be kept in the same function or is a separate function preferable?

I'm new in this area so bear with me :)

function chimpCampaigns() {
var API_KEY = 'X'; // MailChimp API Key
var REPORT_START_DATE = '2018-01-01 15:54:00'; // Report Start Date (ex. when you sent your first MailChimp Newsletter)

var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("CampaignData");

var dc = API_KEY.split('-')[1];
var api = 'https://'+ dc +'.api.mailchimp.com/3.0';
var count = 100; // Max rows to return

var campaignList = '/campaigns?&count='+count+'&since_send_time='+REPORT_START_DATE
var options = {"headers": {"authorization": 'apikey '+API_KEY}};
    var apiCall = function(endpoint){
        apiResponseCampaigns = UrlFetchApp.fetch(api+endpoint,options);
        json = JSON.parse(apiResponseCampaigns);
        return json
    }

var campaigns = apiCall(campaignList);
var total = campaigns.total_items;
var campaignData = campaigns.campaigns;

 if (campaignData) {
    sheet.clear(); // Clear MailChimp data in Spreadsheet
   // Append Column Headers
    sheet.appendRow(["Sent Time", "Campaign ID", "Audience", "Campaign Title", "Subject Line", "Emails Sent", "Abuse Reports", "Unsubscribed", "Unsubscribe Rate", "Hard Bounces", "Soft Bounces", "Bounces Total", "Syntax Errors", "Forwards Count", "Forwards Opens", "Opens Total", "Unique Opens", "Open Rate", "Last Open", "Clicks Total", "Unique Clicks","Unique Subscriber Clicks", "Click Rate", "Last Click"]);
  }  

for (i=0; i< campaignData.length; i++){
    var c = campaignData[i];
    var cid = c.id;
    var title = c.title;
    var subject = c.subject;
    var send_time = c.send_time;

    if (send_time){
        apiResponseReports = UrlFetchApp.fetch('https://'+ dc +'.api.mailchimp.com/3.0/reports/'+cid,options);
        reports = JSON.parse(apiResponseReports);
        reportsSendTime = reports.send_time;
        if(reportsSendTime){

          var campaign_title = c.settings.title;
          var subject_line = c.settings.subject_line;
          var emails_sent = reports.emails_sent;
          var list_name = reports.list_name;            
          var fields = reports.fields;           
          var abuse_reports = reports.abuse_reports;
          var unsubscribed = reports.unsubscribed;       
          var unsubscribe_rate = unsubscribed/emails_sent;
          var hard_bounces = reports.bounces.hard_bounces;
          var soft_bounces = reports.bounces.soft_bounces;
          var bounces = hard_bounces+soft_bounces;
          var syntax_errors = reports.bounces.syntax_errors;
          var forwards_count = reports.forwards.forwards_count;
          var forwards_opens = reports.forwards.forwards_opens;
          var opens_total = reports.opens.opens_total;
          var unique_opens = reports.opens.unique_opens;
          var open_rate = reports.opens.open_rate;
          var last_open = reports.opens.last_open;
          var clicks_total = reports.clicks.clicks_total;
          var unique_clicks = reports.clicks.unique_clicks;
          var unique_subscriber_clicks = reports.clicks.unique_subscriber_clicks;
          var click_rate = reports.clicks.click_rate;
          var last_click = reports.clicks.last_click;

          // the report array is how each row will appear on the spreadsheet
          var report = [send_time, fields, cid, list_name, campaign_title, emails_sent, subject_line, abuse_reports, unsubscribed, unsubscribe_rate, hard_bounces, soft_bounces, bounces, syntax_errors, forwards_count, forwards_opens, opens_total, unique_opens, open_rate, last_open, clicks_total, unique_clicks, unique_subscriber_clicks, click_rate, last_click];

      sheet.appendRow(report);
      }
    }
  }
}

Answer 1

You can call each endpoint in succession using the error-first pattern. More on this here. If your previous call returns data and doesn't error out, you pass the next function as a callback, etc. In the example below, I've omitted the logic that builds URL endpoint, query-string, and the 'options' object as these can simply be borrowed from your code. Basically, you define a function with a callback parameter for each API endpoint. Whenever you need to call multiple endpoints, you create a 3rd function that calls them in succession, passing each new function call as a parameter to the previous one. The inner functions will still have access to the outer scope so you can combine data from multiple endpoints after the last call is executed (provided you assign unique names to the returned data - 'campaigns', 'reports', etc)

  //function for the 'campaings' endpoint
  function getCampaings(options, callback) {

       //API call
       var response = UrlFetchApp.fetch(campaignsEndpoint, options);
      if (res.getStatusCode() == 200) {

              var campaigns = JSON.parse(res.getContentText());
              callback(false, campaigns);

       } else {
             callback("Error: Server responded with the status code of " + res.getStatusCode());
           }             
     }

After creating the function for calling the 'reports' endpoint using the same approach, combine calls in the 3rd function.

    function getCampaignsAndReports(){
      var combinedData = {};
      getCampaigns(options, function(err, campaigns){
        if (!err && campaigns) {
             //Call is successful - proceed to the next call
            getReports(options, function(err, reports){

               //Call successful
               if (!err && reports) {
                    //Proceed to the next call or combine data from
                    //multiple endpoints
                    combinedData.campaigns = campaigns.campaigns;
                    combinedData.reports = reports.reports;
                    //write to sheet
                    //...
                } else {
                 //Error calling reports endpoint
                   throw err;
                 }

             });
        } else {
            //Error calling 'campaigns' endpoint. Throw error or write 
            //another function to show it to the user
            throw err;
        }
    });
    }

This may vary depending on how the MailChimp API data is structured so please change the code accordingly. Also, if you need to call the 'reports' endpoint multiple times for each entry in the 'campaings' endpoint, you can change your function to handle multiple request (options) object using UrlFetchApp.fetchAll(request[]). More on this here. Calling this method will return multiple response objects that you can iterate over.