Get Salesforce Report data into Google Spreadsheets

GETSALESFORCEREPORT.js

var setup = {
  // Add your keys
  consumerKey: "XXXXXXXXXXXXXXXXXXXXXXXXXXXX",
  consumerSecret: "XXXXXXXXXXXXXX"
};

function onOpen(e) {
  var menu = SpreadsheetApp.getUi().createAddonMenu();
  menu.addItem('Login to SF', 'menuAuth');
  menu.addItem('Update all reports', 'updateTrigger');
  menu.addToUi();
}

function menuAuth(){
  setupSalesforceAuth(setup.consumerKey, setup.consumerSecret);
}

function updateTrigger() {
  var triggerCell = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Trigger").getRange('B1');
  triggerCell.setValue(new Date());
}


/**
 * Gets report data from basic Salesforce report.
 *
 * @param {text} id The id of the Salesforce report. If the salesforce report URL is this "https://myorg.my.salesforce.com/00Oi0000005cJWl" then use "00Oi0000005c0kR".
 * @param {text} trigger Link this field to a trigger cell to update asynchronously.
 * @return report data.
 * @customfunction
 */
function GETSALESFORCEREPORT(id,trigger) {
  var reportEndpoint = "/services/data/v29.0/analytics/reports/" + id + "?includeDetails=true";
  var auth = setupSalesforceAuth(setup.consumerKey, setup.consumerSecret);
  
  var queryUrl = auth.instanceUrl + reportEndpoint;
  var response = UrlFetchApp.fetch(queryUrl, { method : "GET", headers : { "Authorization" : "OAuth " + auth.accessToken } });
  var queryResult = JSON.parse(response.getContentText());
  
  var report = getBodyRows(queryResult);
  report.splice(0,0,getHeaderRow(queryResult));
  return report; 
}

function getHeaderRow(response) {
  var columnTitles = [];
  for(column in response.reportExtendedMetadata.detailColumnInfo) {
    columnTitles.push(response.reportExtendedMetadata.detailColumnInfo[column]['label']);
  }
  return columnTitles;
};

function getBodyRows(response, headerRows) {
  var rows = [];
  var columnTypes = [];

  for(column in response.reportExtendedMetadata.detailColumnInfo) {
    columnTypes.push(response.reportExtendedMetadata.detailColumnInfo[column]['dataType']);
  }
  response.factMap['T!T'].rows.forEach(function(row,i){
    var newRow = [];
    row['dataCells'].forEach(function(cell,j){
      if (columnTypes[j]==="currency" && cell.value) {
         newRow.push(cell.value.amount);
      } else {
        newRow.push(cell.label)
      }
    })
    rows.push(newRow);
  })
  return rows;
};

function setupSalesforceAuth(sfConsumerKey,sfConsumerSecret) {
  var oauth = UrlFetchApp.addOAuthService("salesforce");
  oauth.setAccessTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/AccessTokenHandler");
  oauth.setRequestTokenUrl("https://login.salesforce.com/_nc_external/system/security/oauth/RequestTokenHandler");
  oauth.setAuthorizationUrl("https://login.salesforce.com/setup/secur/RemoteAccessAuthorizationPage.apexp?oauth_consumer_key="+encodeURIComponent(sfConsumerKey));
  oauth.setConsumerKey(sfConsumerKey);
  oauth.setConsumerSecret(sfConsumerSecret);
  
  var sessionLoginUrl = "https://login.salesforce.com/services/OAuth/u/21.0";
  var options = { method : "POST", oAuthServiceName : "salesforce", oAuthUseToken : "always" };
  var result = UrlFetchApp.fetch(sessionLoginUrl, options);
  var txt = result.getContentText();
  var accessToken = txt.match(/<sessionId>([^<]+)/)[1];
  var serverUrl = txt.match(/<serverUrl>([^<]+)/)[1];
  var instanceUrl = serverUrl.match(/^https?:\/\/[^\/]+/)[0];

  return {
    accessToken: accessToken,
    instanceUrl: instanceUrl
  }
}