Leverage Mailchimp data and sort based on sent time

Leverage Mailchimp data

function chimpCampaigns() {
var API_KEY = '95610c4b1779a8a5852f4f7c8dfab953-us1'; // MailChimp API Key
var REPORT_START_DATE = '2020-01-01 07:00:00';
  
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", 
      "Campaign Title", 
      "Subject Line", 
      "Emails Sent", 
      "Opens Total", 
      "Unique Opens", 
      "Open Rate", 
      "Last Open", 
      "Clicks Total", 
      "Unique Clicks",
      "Unique Subscriber Clicks", 
      "Click Rate", 
      "Last Click", 
      "Unsubscribed", 
      "Unsubscribe Rate", 
      "Abuse Reports", 
      "Hard Bounces", 
      "Soft Bounces", 
      "Bounces Total", 
      "Syntax Errors",
      "Forwards Count", 
      "Forwards Opens"]);
  }  

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 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 = [
            parseISOString(send_time), 
            cid, 
            campaign_title, 
            subject_line, 
            emails_sent, 
            opens_total, 
            unique_opens, 
            open_rate, 
            last_open, 
            clicks_total, 
            unique_clicks, 
            unique_subscriber_clicks, 
            click_rate, 
            last_click,
            unsubscribed, 
            unsubscribe_rate,
            abuse_reports, 
            hard_bounces, 
            soft_bounces, 
            bounces, 
            syntax_errors,
            forwards_count, 
            forwards_opens
          ];

      sheet.appendRow(report);
         
      }
    }
  }
  
  // Sorting sent_time Z -> A
  var range = sheet.getRange("A2:A");
  SORT_ORDER = [
    {column: 1, ascending: false}
  ];

  range.sort(SORT_ORDER);
}

function parseISOString(s) {
  var b = s.split(/\D+/);
  return new Date(Date.UTC(b[0], --b[1], b[2], b[3], b[4], b[5], b[6]));
}

Original: https://www.savio.no/analytics/get-mailchimp-api-3-0-campaign-data-google-sheets