getCalculatedRanges

This function allows you to call to another report, calculate it in the background without rendering it to the user and return the values from a given set of ranges.

Syntax

getCalculatedRange (reportname, ranges, queryString, isPrivate, publisher)

Arguments

reportName

(Required)

The name of the report which you want to perform a background calculation on

ranges

(Required)

This argument is an array of ranges to return. A range can be specified in any of the following formats:

  • A1 reference
  • R1C1 reference
  • A1:B6 reference
  • Named range

The format of this argument is a JavaScript string array:

 ["A1:B2", "G3:H7"]

queryString

Any additional query string arguments you want to pass in for the calculation of the report

Default value: ”“

isPrivate

Is this a private report

Default value: false

publisher

If it is a private report, specify the login id of the user that published it.

Default value: ”“

Return

The result of this will be a JSON object that has the following sample structure:

{
  "A1:B2" : [ {
        "R1C1" : "A1",
        "R1C2" : "B1",
        "R2C1" : "A2",
        "R2C2" : "B2"
      } ],
  "D1:E2" : [ {
        "R1C4" : "D1",
        "R1C5" : "E1",
        "R2C4" : "D2",
        "R2C5" : "E2"
      } ]
}

Examples

The following code will call back to the server and calculate the named report and retrieve data from it, without ever rendering that report.

//
// Example 1 - Calculate the named public report and retrieve ranges A1:B5 and the Named Range 'MyNamedRange'
//
var values1 = report.api.getCalculatedRanges("MyTestReport", ["A1:B5", "MyNamedRange"]);

//
// Example 2 - Calculate the named public report with some query string parameters and
//         ranges A1:B5 and the Named Range 'MyNamedRange'.
//
var queryString = "[Date].[Date].%26[2]&R1C3=Yesterday"
var values2 = report.api.getCalculatedRanges("MyTestReport", ["A1:B5", "MyNamedRange"], queryString);

//
// Example 3 - Calculate the named private report and retrieve ranges A1:B5 and the Named Range 'MyNamedRange'
//
var values3 = report.api.getCalculatedRange("MyTestReport", ["A1:B5", "MyNamedRange"], "", true, "sgibbs");