Guest User

Get MailChimp Subscribers (Member Count) in Google Sheets

a guest
Apr 22nd, 2019
515
0
Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function mailchimpListInfo() {
  2.   var API_KEY = 'XXXXX-us1'; // MailChimp API Key
  3.   var LIST_ID = 'XXXXX'; // List ID
  4.  
  5.   var ss = SpreadsheetApp.getActiveSpreadsheet();
  6.   var sheet = ss.getSheetByName("MemberData");
  7.  
  8.   var dc = API_KEY.split('-')[1];
  9.   var api = 'https://'+ dc +'.api.mailchimp.com/3.0';
  10.  
  11.   var memberList = '/lists/'+LIST_ID;
  12.   var options = {"headers": {"authorization": 'apikey '+API_KEY}};
  13.   var apiCall = function(endpoint){
  14.     apiResponseMembers = UrlFetchApp.fetch(api+endpoint,options);
  15.     var json = JSON.parse(apiResponseMembers);
  16.     return json
  17.   }
  18.  
  19.   var members = apiCall(memberList);
  20.   if (members) {
  21.     var listInfos = {
  22.       "Date": new Date(), /* Now */
  23.       "Subscribers": members.stats.member_count,
  24.       "Unsubscribe Count": members.stats.unsubscribe_count,
  25.       "Cleaned Count": members.stats.cleaned_count,
  26.       "Member Count Since Send": members.stats.member_count_since_send,
  27.       "Unsubscribe Count Since Send": members.stats.unsubscribe_count_since_send,
  28.       "Cleaned Count Since Send": members.stats.cleaned_count_since_send,
  29.       "Campaign Count": members.stats.campaign_count,
  30.       "Campaign Last Sent": members.stats.campaign_last_sent,
  31.       "Merge Var Count": members.stats.merge_field_count,
  32.       "Average Subscription Rate": members.stats.avg_sub_rate,
  33.       "Average Unsubscription Rate": members.stats.avg_unsub_rate,
  34.       "Average Target Subscription Rate": members.stats.target_sub_rat,
  35.       "Open Rate": members.stats.open_rate,
  36.       "Click Rate": members.stats.click_rate,
  37.       "Date of Last List Subscribe": members.stats.last_sub_date,
  38.       "Date of Last List Unsubscribe": members.stats.last_unsub_date,
  39.       "List Rating": members.list_rating
  40.     };
  41.     var listInfosKeys   = Object.keys(listInfos);
  42.     var listInfosValues = Object.keys(listInfos).map(function(key){return listInfos[key]});
  43.        
  44.     // Append MailChimp data to Spreadsheet
  45.     if(sheet.getLastRow() === 0) {
  46.       sheet.appendRow(listInfosKeys);
  47.     }
  48.     sheet.appendRow(listInfosValues);
  49.   }
  50. }
Add Comment
Please, Sign In to add comment