Get report data from Salesforce with Apps Script based on filter

Question

Trying to get data from Saleforce Analytics API into Google spreadsheet based on this handy script on Github. Except, instead of running entire report, I am trying to pass email filter based on the email list column on a different sheet of the same doc.

    // Use the Analytics API to get report data
    function runContactsReport() {
      var reportId = '';
      var sheetName = 'Report';
      var sfService = getSfService();
      var userProps = PropertiesService.getUserProperties();
      var props = userProps.getProperties();
      var name = getSfService().serviceName_;
      var obj = JSON.parse(props['oauth2.' + name]);
      var instanceUrl = obj.instance_url;
      var queryUrl = instanceUrl + "/services/data/v29.0/analytics/reports/" + reportId + "?includeDetails=true";  // Actual request for report Data
      var response = UrlFetchApp.fetch(queryUrl, { method : "GET", headers : { "Authorization" : "OAuth "+sfService.getAccessToken() } });
      var queryResult = JSON.parse(response.getContentText());

// sheet to insert reports      
      var ss = SpreadsheetApp.getActive();
      var sheet = ss.getSheetByName(sheetName);

//emails to filter on
      var dataSheet = ss1.getSheetByName('DATA');
      var emails = getColumnNrByName(dataSheet, 'Email')
      var emailFilters = dataSheet.getRange(2, emailds, dataSheet.getLastRow() -1).getValues();

      var answer = queryResult.factMap["T!T"].rows;  // assumes tabular report
      var headers = queryResult.reportExtendedMetadata.detailColumnInfo;
      var headname = queryResult.reportMetadata.detailColumns;
      var filters = queryResult.reportMetadata.reportFilters; //get filters

      var myArray = [];
      var tempArray = [];


      for (i = 0 ; i < headname.length ; i++) {

        tempArray.push(headers[headname[i]].label);

      }

      myArray.push(tempArray);

      for (i = 0 ; i < answer.length ; i++ ) {

        var tempArray = [];
        function getData(element,index,array) {
          tempArray.push(array[index].label)
        }

        answer[i].dataCells.forEach(getData);

        myArray.push(tempArray);

    } 

      var lastRow = sheet.getLastRow();
      if (lastRow < 1) lastRow = 1;
    sheet.getRange(1,1,lastRow, myArray[0].length).clearContent();
    sheet.getRange(1,1, myArray.length, myArray[0].length).setValues(myArray);

    }

To do this, I need to send back the report metadata object with edited filters. Here’s some typical metadata that is returned per Salesforce API:

'{reportMetadata":{"name":"MerchandiseReport","id":"00OD0000001ZbP7MAK","developerName":"MerchandiseReport",​"reportType":{"type":"MerchandiseList","label":"Merchandise"},"reportFormat":"MATRIX",​"reportBooleanFilter":null,"reportFilters":[{"column": "QUANTITY", "operator":"greaterThan",​ "value":"12"}],"detailColumns":["MERCHANDISE.NAME","CREATED_DATE","QUANTITY"],​"currency":null,"aggregates":["RowCount"],"groupingsDown":[{"name":"CONTACT2.COUNTRY_CODE",​"sortOrder":"Asc","dateGranularity":"None"}],"groupingsAcross":[{"name":​"OWNER","sortOrder":"Asc","dateGranularity":"None"}]}}'

Unfortunately, I don't know how to pass it in Apps Script. Any help is appreciated!

UPDATE: After battling with this code, here are the latest results. Per API instructions, I needed to pass POST request to pass filter parameters. Send a POST request to the Execute Async resource. If you’re passing filters, include them in the POST request metadata. The request returns the instance ID where results of the run are stored.

So here is what I have:

function filterReport(){
 var reportId = '00O33000004Dels';
 var ss = SpreadsheetApp.getActiveSpreadsheet();
 var dataSheet = ss.getSheetByName('DATA');
 var emailds = getColumnNrByName(dataSheet, 'Email');
 var emailFilters = dataSheet.getRange(2, emailds, dataSheet.getLastRow() -1).getValues();

  for (var i = 0; i < emailFilters.length; i++) {

    var payload = JSON.stringify({
      'reportMetadata': {
        'reportFilters': [{
      'value' : emailFilters[i][0],
      'column': 'EMAIL',
      'operator': 'equals' 
        }]
      }
    });

    filter(reportId, payload);
}
 }

function filter(reportId, payload) {

  var sfService = getSfService();
  var userProps = PropertiesService.getUserProperties();
  var props = userProps.getProperties();
  var name = getSfService().serviceName_;
  var obj = JSON.parse(props['oauth2.' + name]);
  var instanceUrl = obj.instance_url;
  var queryUrl = instanceUrl + "/services/data/v35.0/analytics/reports/" + reportId + "/instances";

  var options = {
    'headers': {
     'Authorization' : 'Bearer ' + sfService.getAccessToken()
    },
    'contentType' : 'application/json',
    'method' : 'POST',
    'payload' : payload
  }; 

  var response = UrlFetchApp.fetch(queryUrl, options);

  if (response.getResponseCode() == 200) {

    var params = JSON.parse(response.getContentText());
   //Logger.log(response.getContentText());
    var filterId = params.id;
    //Logger.log(filterId);
    Logger.log(response.getContentText())
  } 
}

This returns not one instance ID but 4, as I have in my sample spreadsheet... and I need one ID to pass 4 results (per this example).

After that, I need to send GET request with this instance ID, so I can get the data with filters into the spreadsheet.

I have this function, and it sort of works, but I don't know how to pass that Instance id into it from my POST request automatically...

// Use the Analytics API to get report data
// The try-catch can be removed if you do not need to track errors
function runContactsReport() {
  var reportId = '00O33000004Dels';
  var sheetName = 'Contact Email Validation Report';
  var sfService = getSfService();
  var userProps = PropertiesService.getUserProperties();
  var props = userProps.getProperties();
  var name = getSfService().serviceName_;
  var obj = JSON.parse(props['oauth2.' + name]);
  var instanceUrl = obj.instance_url;
  var queryUrl = instanceUrl + "/services/data/v35.0/analytics/reports/" + reportId + "/instances/0LG33000005ZJdqGAG";  // Actual request for report Data
  var response = UrlFetchApp.fetch(queryUrl, { method : "GET", headers : { "Authorization" : "OAuth "+sfService.getAccessToken() } });
  var queryResult = JSON.parse(response.getContentText());

  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName(sheetName);

  var answer = queryResult.factMap["T!T"].rows;  // assumes tabular report
  var headers = queryResult.reportExtendedMetadata.detailColumnInfo;
  var headname = queryResult.reportMetadata.detailColumns;   

  var myArray = [];
  var tempArray = [];

  for (i = 0 ; i < headname.length ; i++) {

    tempArray.push(headers[headname[i]].label);

  }

  myArray.push(tempArray);

  for (i = 0 ; i < answer.length ; i++ ) {

    var tempArray = [];
    function getData(element,index,array) {
      tempArray.push(array[index].label)

    }

    answer[i].dataCells.forEach(getData);

    myArray.push(tempArray);

} 

  var lastRow = sheet.getLastRow();
  if (lastRow < 1) lastRow = 1;
sheet.getRange(1,1,lastRow, myArray[0].length).clearContent();
sheet.getRange(1,1, myArray.length, myArray[0].length).setValues(myArray);

}

So, I am stuck again.. Please help!

Answer 1

I got it to work at last. Hopefully it will helpful for someone. The only issue I am having is that occasionally when I run runContactsReport(), it comes back with error -" TypeError: Cannot read property "T!T" from null." but then it runs fine - quite puzzling... here is what I got:

        //this is POST request to pass filters based on email column into report
function filterContacts() {

         var reportId = 'YOUR REPORT ID'; //SF Contacts Report ID
         var ss = SpreadsheetApp.getActiveSpreadsheet();
         var dataSheet = ss.getSheetByName('DATA');
         var emailds = getColumnNrByName(dataSheet, 'Email'); //custom function that finds header name and returns column umber
         var emailFilters = dataSheet.getRange(2, emailds, dataSheet.getLastRow() -1).getValues(); //column with emails

    //put all emails in one comma separated string, so we get one instance ID
    var emails = emailFilters.toString().split(",").join();

            var payload = JSON.stringify({
            'reportMetadata': {
            'reportFilters': [{  
              'value' : emails,
              'column': 'EMAIL',
              'operator': 'equals' 
           }]
           }
            });

          var sfService = getSfService();
          var userProps = PropertiesService.getUserProperties();
          var props = userProps.getProperties();
          var name = getSfService().serviceName_;
          var obj = JSON.parse(props['oauth2.' + name]);
          var instanceUrl = obj.instance_url;
          var queryUrl = instanceUrl + "/services/data/v35.0/analytics/reports/" + reportId + "/instances";

          var options = {
            'headers': {
             'Authorization' : 'Bearer ' + sfService.getAccessToken()
            },
            'contentType' : 'application/json',
            'method' : 'POST',
            'payload': payload
          }; 

          //Logger.log(options)


          var response = UrlFetchApp.fetch(queryUrl, options);

          if (response.getResponseCode() == 200) {

            var params = JSON.parse(response.getContentText());

          } 

            //Logger.log("my id is:" + params.id);
            return params.id; //return instance ID
        }


        // use returned instance ID to run report based on email filter
        function runContactsReport() {
          filterContacts();
          var reportId = 'YOUR REPORT ID'; //SF Contact Email Validation Report
          Utilities.sleep(200);// pause in the loop for 200 milliseconds
          var instanceId = filterContacts();
         // Logger.log("And my id is:" + instanceId);
          var sheetName = 'Contact Email Validation Report';
          var sfService = getSfService();
          var userProps = PropertiesService.getUserProperties();
          var props = userProps.getProperties();
          var name = getSfService().serviceName_;
          var obj = JSON.parse(props['oauth2.' + name]);
          var instanceUrl = obj.instance_url;
          var queryUrl = instanceUrl + "/services/data/v35.0/analytics/reports/" + reportId + "/instances/" + instanceId;  // Actual request for report Data
          var response = UrlFetchApp.fetch(queryUrl, { method : "GET", headers : { "Authorization" : "OAuth "+sfService.getAccessToken() } });
          var queryResult = JSON.parse(response.getContentText());

          var ss = SpreadsheetApp.getActive();
          var sheet = ss.getSheetByName(sheetName);

          var answer = queryResult.factMap["T!T"].rows;  // assumes tabular report
          var headers = queryResult.reportExtendedMetadata.detailColumnInfo;
          var headname = queryResult.reportMetadata.detailColumns; 

          var filters = queryResult.reportMetadata.reportFilters;

          //Logger.log(filters)

          var myArray = [];
          var tempArray = [];


          for (i = 0 ; i < headname.length ; i++) {

            tempArray.push(headers[headname[i]].label);

          }

          myArray.push(tempArray);

          for (i = 0 ; i < answer.length ; i++ ) {

            var tempArray = [];
            function getData(element,index,array) {
              tempArray.push(array[index].label)

            }

            answer[i].dataCells.forEach(getData);

            myArray.push(tempArray);

        } 

          var lastRow = sheet.getLastRow();
          if (lastRow < 1) lastRow = 1;
        sheet.getRange(1,1,lastRow, myArray[0].length).clearContent();
        sheet.getRange(1,1, myArray.length, myArray[0].length).setValues(myArray);
        }