Get report data from Salesforce with Apps Script based on filter
<p>Trying to get data from Saleforce Analytics API into Google spreadsheet based on this <a href="https://github.com/yzhilova/sfconnect/blob/master/getReports.gs" rel="nofollow">handy script</a> 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. </p>

<pre><code>    // 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 &lt; headname.length ; i++) {

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

      }

      myArray.push(tempArray);

      for (i = 0 ; i &lt; 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 &lt; 1) lastRow = 1;
    sheet.getRange(1,1,lastRow, myArray[0].length).clearContent();
    sheet.getRange(1,1, myArray.length, myArray[0].length).setValues(myArray);

    }
</code></pre>

<p>To do this, I need to send back the report metadata object with edited filters.
Here’s some typical metadata that is returned per <a href="https://developer.salesforce.com/docs/atlas.en-us.api_analytics.meta/api_analytics/sforce_analytics_rest_api_filter_reportdata.htm" rel="nofollow">Salesforce API</a>:</p>

<pre><code>'{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"}]}}'
</code></pre>

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

<p>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.</p>

<p>So here is what I have:</p>

<pre><code>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 &lt; 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())
  } 
}
</code></pre>

<p>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).</p>

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

<p>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...</p>

<pre><code>// 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 &lt; headname.length ; i++) {

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

  }

  myArray.push(tempArray);

  for (i = 0 ; i &lt; 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 &lt; 1) lastRow = 1;
sheet.getRange(1,1,lastRow, myArray[0].length).clearContent();
sheet.getRange(1,1, myArray.length, myArray[0].length).setValues(myArray);

}
</code></pre>

<p>So, I am stuck again.. Please help!</p>