function chimpCampaigns() { var API_KEY = 'VOSTRO API KEY'; // MailChimp API Key var REPORT_START_DATE = '2014-11-30 20:30:00'; // Report Start Date (Da quanto vuoi far iniziare ad estrarre i dati) var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("CampagneMailChimp"); var dc = API_KEY.split('-')[1]; var api = 'https://'+ dc +'.api.mailchimp.com/3.0'; var count = 100; // Max numero di righe restituite 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 // Generazione titoli colonne sheet.appendRow(["Sent Time", "Campaign ID", "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 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; // report รจ un array degli elementi che verranno inseriti nel foglio di Google var report = [send_time, cid, campaign_title, subject_line, emails_sent, 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); } } } }