The Spreadsheet Functions tab in Grow can be super helpful if you know what to use it for. One thing you can do in Spreadsheet Functions is reference other reports. Here's how to do that.
How to use Spreadsheet Functions
The syntax is
=FUNCTION(report!location), where the
function is the expression, the
report is where you want to pull it from, and
location is the column or cell that you want to reference.
- The report numbers are listed above your data, but below the metric visual. (You can see this in the example below.) When referencing a report, you will write
report#!It is always followed by an exclamation mark. For example, you will reference
report3!, etc. based on the reports you have.
- Previously, to reference the Master SQL report, you would enter
masterreport. Now, to reference the Master SQL report or Joined reports, use the corresponding
- If you are referencing a cell or column from spreadsheet functions, use
- In the location field, you can reference a specific cell or a column in a report. This location always comes immediately after the exclamation mark denoting which report is being referenced.
Please note that you cannot reference spreadsheet data anywhere outside of spreadsheet functions. This includes joined reports or master reports. Spreadsheet data can only be referenced as
spreadsheet!A1 inside spreadsheet functions.
Examples: Referencing other reports in Spreadsheet Functions
Here are a few simple examples of referencing a report:
=report1!B3 + report3!B5
=SUM(spreadsheet!A1, B1) This example would return the same figure as the example directly above it.
Additionally, we will use the following Google Analytics example.
The metric is the Visitor Overview prebuilt metric. It has two Google Analytics reports and a Master SQL report.
Let’s say we want to pull column B from Report 1 into our spreadsheet. First, go to the Spreadsheet Functions tab. You can give the column a title, if you want.
In the cell below your title, enter the following function:
Hit enter, and watch your data pull in to your spreadsheet.
Next, we’re going to reference something from Report 2. Again, you can label the cell if you want. We are going to pull cell D2 from report 2.
Enter the following function into your cell:
This will return the value that is in Report 2, cell D2.
For the Master SQL report, we will pull in column D, which, in our metric, is Total Pageviews.
In order to reference this, use the following function:
Press enter, and the column will fill in to your spreadsheet.
If you want to know what functions the Spreadsheet Functions tab supports, check out this article. It walks through each of the functions available in Grow.
You can find this list in Grow on the Edit Metric page under Spreadsheet Functions.
Questions? Email us at firstname.lastname@example.org or chat in on the site.