google_apps_script_close_io.js

var APIKEY = "<INSERT CLOSE.IO API KEY HERE>";

function cioUpdateActivity() {
  var activitySheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Activity")
  var sellerRange = activitySheet.getDataRange().getValues();
  sellerRange.shift();
  var sellers = []

  for(var i = 0; i < sellerRange.length; i++)
    sellers.push(sellerRange[i].slice(0,2));
    
  // SITs
  var sits = [];
  for(i = 0; i < sellers.length; i++)
    sits.push(cioGetSITs(sellers[i][0])); 
  var rangestr = "C2:C"+parseInt(sits.length + 1)
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Activity").getRange(rangestr).setValues(transpose(sits));
  
   // Offers
  var offers = [];
  for(i = 0; i < sellers.length; i++)
    offers.push(cioGetOffers(sellers[i][0])); 
  var rangestr = "I2:I"+parseInt(sits.length + 1)
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Activity").getRange(rangestr).setValues(transpose(offers));

  // Emails & calls
  var calls = [], duration = [], emails = [], act;
  for(i = 0; i < sellers.length; i++) {
    act = cioGetActivity(sellers[i][1]);
    calls.push(act.calls); duration.push(act.duration); emails.push(act.emails_sent);
  }
  var rangestr = "D2:D"+parseInt(sits.length + 1)
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Activity").getRange(rangestr).setValues(transpose(duration));
  var rangestr = "E2:E"+parseInt(sits.length + 1)
  SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Activity").getRange(rangestr).setValues(transpose(emails));
}
 
  
function cioGetOrgID() {
  var encodedAuth = "Basic " + Utilities.base64Encode(APIKEY + ":");
  var url = "https://app.close.io/api/v1/me";
  var options = {
        "method" : "get",
        "headers" : {"Accept" : "application/json", "Authorization": encodedAuth}
  };  
  var response = Utilities.jsonParse(UrlFetchApp.fetch(url, options).getContentText());
  var id = response.organizations[0].id;
  return (id)
}

function cioGetActivity(email) {
  var activity = new Object()
  var d = getMonday(new Date());
  var encodedAuth = "Basic " + Utilities.base64Encode(APIKEY + ":");
  var orgId = cioGetOrgID();
  var userId = cioGetUserID(email);
  var url = "https://app.close.io/api/v1/report/" + orgId + "/?type=activity&date_start=" + cioDateString(d) + "&user_id="+userId;
  var options = {
        "method" : "get",
        "headers" : {"Accept" : "application/json", "Authorization": encodedAuth}
  };  
  var response = Utilities.jsonParse(UrlFetchApp.fetch(url, options).getContentText()); 
  activity.calls = response.calls; activity.duration = response.calls_duration_total/3600; activity.emails_sent = response.emails_sent
  return (activity)
}

function cioGetSITs(name) {
  var d = getMonday(new Date());
  var encodedAuth = "Basic " + Utilities.base64Encode(APIKEY + ":");
  var url = 'https://app.close.io/api/v1/lead?query=' + encodeURIComponent('custom.Source:SIT custom.Owner:"' + name + '" created > ' + cioDateString(d));

 var options = {
        "method" : "get",
        "headers" : {"Accept" : "application/json", "Authorization": encodedAuth}
  };  
  var response = Utilities.jsonParse(UrlFetchApp.fetch(url, options).getContentText()); 
  return response.total_results;
}

function cioGetOffers(name) {
  var d = getMonday(new Date());
  var encodedAuth = "Basic " + Utilities.base64Encode(APIKEY + ":");
  var url = 'https://app.close.io/api/v1/lead?query=' + encodeURIComponent('email_template:Tilbud email_user:"' + name + '" email_date > ' + cioDateString(d));

 var options = {
        "method" : "get",
        "headers" : {"Accept" : "application/json", "Authorization": encodedAuth}
  };  
  var response = Utilities.jsonParse(UrlFetchApp.fetch(url, options).getContentText()); 
  return response.total_results;
}

function cioGetUserID(email) {
  var org = cioGetOrgID();
  var encodedAuth = "Basic " + Utilities.base64Encode(APIKEY + ":");
  var url = "https://app.close.io/api/v1/organization/" + org;
  var options = {
        "method" : "get",
        "headers" : {"Accept" : "application/json", "Authorization": encodedAuth}
  };  
  var response = Utilities.jsonParse(UrlFetchApp.fetch(url, options).getContentText());  
  for(var i = 0; i < response.memberships.length; i++)
    if (response.memberships[i].user_email === email) return(response.memberships[i].user_id)  
  
  return ("<unknown email>");
}

function cioDateString(dt) {
  var year = dt.getFullYear();
  var month = dt.getMonth() + 1;
  if (month < 10) month = "0"+month;
  var day = dt.getDate();
  if (day < 10) day = "0"+day;
  return(year + "-" + month + "-" + day);
}

function transpose(data) {
   var t = [];
  
  for(var i=0; i < data.length; i++) {
    t[i] = new Array(1);
    t[i][0] = data[i];
  }
 return t;
}

function getMonday(d) {
  d = new Date(d);
  d.setHours(0,0,0,0);
  var day = d.getDay(),
      diff = d.getDate() - day + (day == 0 ? -6:1); // adjust when day is sunday
  return new Date(d.setDate(diff));
}