Google Analytics Spreadsheet Add-on

Philip Walton, Google Analytics Developer Programs Engineer – March 2014

The Google Analytics Spreadsheet add-on makes it easier for Google Analytics users to access, visualize, share, and manipulate their data in Google Spreadsheets.

Introduction

The Google Analytics spreadsheet add-on brings you the power of the Google Analytics API combined with the power of data manipulation in Google Spreadsheets. With this tool, you can:

  • Query and report data from multiple views.
  • Compute and display custom calculations.
  • Create visualizations and embed those visualizations on third-party websites.
  • Schedule your reports to run and update automatically.
  • Control who can see your data and visualizations by using Google Sheets' sharing and privacy features.
Screenshot of the Google Analytics Spreadsheet add-on

Video tutorials

The following two videos explain how to download and install the add-on as well as build a dashboard that updates automatically and can be embedded on a third-party website.

Introducing the Google Analytics Spreadsheet Add-on

Building a dashboard with the Google Analytics Spreadsheet Add-on

Installing the add-on

To use the Google Analytics add-on you must first add it to your spreadsheet. The following instructions will walk you through the process:

  1. Create a new Google Spreadsheet (or open an existing one).
  2. From the menu bar choose: Add-ons > Get Add-ons…
  3. Find the Google Analytics Add-on from the add-ons gallery and select it.
  4. From the add-on description page, click the "+" in the top right corner to add this add-on to your spreadsheet.
  5. A dialog should pop up requesting your permission for the add-on to access your Google Analytics data. Click "Accept".
  6. The add-on is now installed. A "Google Analytics" submenu should now appear in the Add-ons menu.

Creating reports

Reports can be created manually or with the help of the add-on's report creation tool. To use the tool, select "Add-ons" > "Google Analytics" > "Create a New Report" from the menu bar. This will display a sidebar on the right that allows you to easily look up your Google Analytics account information as well as choose what dimensions and metrics to query. When you're finished, click the "Create Report" button and your report information will appear in a sheet named "Report Configuration" (if the sheet doesn't already exist, it will be created).

To create additional reports, just repeat the steps above. Each additional report you create will add a new column of data to the right of the previous report.

If you look at the values the report creation tool enters into the report configuration sheet, you'll notice that many of the cells are left blank. This is intentional. The tool is meant to help get you started and provide you with the information you might not know off the top of your head. The rest of the fields will need to be entered by you. If you have any questions about what to put in each field, see the reference at the bottom of this page.

Running reports

To run all the reports you've created select "Add-ons" > "Google Analytics" > "Run Reports" from the menu bar.

Excluding a Report

By default, the add-on will run every report with a name, so if you want to temporarily exclude a report without deleting it, you can simply remove all text from the cell where the name value goes.

Choosing an Output Sheet

Running the reports will make a request to the Google Analytics API and print the results to a sheet of your choosing. It can be a sheet in the spreadsheet you're currently in, or a different spreadsheet altogether (as long as you have edit access to that spreadsheet). To print the results to a different spreadsheet copy the spreadsheet URL and paste it into the cell to the right of the "spreadsheet-url" parameter.

Scheduling reports to run automatically

You can enable your reports to run automatically by selecting "Add-ons" > "Google Analytics" > "Schedule Reports" from the menu bar. This opens a report scheduling dialog where you can turn scheduling on and off, and set how frequently your report will run.

To turn scheduling on, check the box labelled "Enable reports to run automatically." Once scheduling is enabled you can use the select dropdown to control the time and frequency. Reports can be scheduled to run every hour, day, week, or month.

To turn scheduling off, uncheck the box labelled "Enable reports to run automatically."

Common use cases

Querying data from multiple views

Through the Google Analytics web interface you can only see reports and visualization data from a single view at a time. With this add-on you can run reports on any number of views and use the generated data in whatever way you like. For example, if you have a view that tracks your iOS traffic and a separate view to track your Android traffic, you could use this add-on to run reports on both views and easily compare the results side by side, in the same spreadsheet.

Creating custom calculations with your Google Analytics data

The Google Analytics API collects a lot of different types of data and gives you a great deal of choice for how you want it reported but sometimes you require a specific calculation. When your data is in a Google Spreadsheet you can leverage the spreadsheet's built-in data manipulation features.

Creating dashboards with embedded data visualizations

When your data is in a Google Spreadsheet you can take advantage of all the visualization tools that Google gives you out of the box. Furthermore, Google Spreadsheet charts and graphs can be embedded into external sites. This means you can easily create your own dashboards with visualizations of your Google Analytics data, host them wherever you want, and they will automatically update as often as you run your reports.

Easily controlling who can see your Google Analytics data

The Google Analytics website provides great insight into your data at a glance, but sometimes you want to share your data with stakeholders without giving them access to your Google Analytics account.

With the Google Analytics Spreadsheet add-on you can easily solve this problem because Google spreadsheets have their own, built-in permissions system. All you have to do is create a new spreadsheet, grant access to whoever you want, and then have your reports output data to that spreadsheet.

Automatically updating your data and visualizations

Once you've created reports or embedded a visualization on a third party website, you can ensure your data is always current by scheduling your reports to run automatically.

FAQs

How do I know when my scheduled reports will run next?

The first run of your scheduled reports will occur randomly during the time period you specify. Subsequent runs will occur predictably at that same time, offset by the interval you've chosen. For example, if you schedule your reports to run daily between 4 a.m. and 5 a.m., and the first run occurs at 4:12 a.m., all subsequent runs will also occur at 4:12 a.m.

My scheduled reports didn't run when they were supposed to, is it broken?

When scheduling reports, make sure there is plenty of time between when you create the schedule and when the schedule is supposed to run. If it's too close to the first occurrence of the scheduled time, there's a chance those reports will be postponed until the next occurrence. It's usually best to leave at least a one-hour buffer.

Can I set different schedules for different reports?

Scheduling either applies to all reports or no reports. If you need to schedule different reports at different intervals, you can use multiple spreadsheets.

Why is my report data in the wrong format?

The Google Analytics Reporting APIs return all values in U.S. format. If your spreadsheet is set to a different locale, Google Sheets will attempt to do a conversion for you, which will sometimes yield unexpected results.

To avoid this issue, make sure the locale of your report spreadsheet is set to "United States" (instructions for changing your spreadsheet's locale can be found in the Docs editors Help.) To then convert your data into the format of your preferred locale, you can create a second spreadsheet with your preferred locale set and use the IMPORTRANGE function to import your report data into that new spreadsheet. Google Sheets will correctly convert formats between locales when importing from one spreadsheet to another.

Getting help

To ask a question about the add-on or to report a bug, post a message the google-analytics-spreadsheet-add-on Google group. Search the group before posting as your question may have already been answered.

Configuration parameter reference

The sections below outline the required, optional, and hidden parameters:

Required parameters

Name Description
Report Name This is the report name. It will also be the name of the sheet where the report data is written.
View ID The ID for your Google Analytics view. You can get this value using the report creation tool or find it in the Google Analytics account admin.
Start Date

The start date for fetching Analytics data. Requests can specify a start date formatted as YYYY-MM-DD, or as a relative date (e.g., today, yesterday, or NdaysAgo where N is a positive integer).

You can also use Sheets date functions to specify this value programmatically. For example, the following expression returns the last day of the previous month:

=EOMONTH(TODAY(), -1)
End Date

The end date for fetching Analytics data. Requests can specify an end date formatted as YYYY-MM-DD, or as a relative date (e.g., today, yesterday, or NdaysAgo where N is a positive integer).

You can also use Sheets date functions to specify this value programmatically. For example, the following expression returns the last day of the previous month:

=EOMONTH(TODAY(), -1)
Metrics

A list of metrics to query. Metrics can be specified in one of two formats:

For example, all of the following are valid values for the Metrics parameter.

ga:sessions,ga:bounces
ga:sessions
ga:bounces
[{
  "expression": "ga:sessions/ga:users",
  "alias": "Sessions per User",
  "formattingType": "FLOAT"
}, {
  "expression": "ga:totalEvents/ga:pageviews",
  "alias": "Events per Pageview",
  "formattingType": "FLOAT"
}]

For most use cases, a list of metric IDs is the easiest way to specify the Metrics parameter. However, if you want to leverage advanced features of the Analytics Reporting API such as metric expressions in the third example above, you have to specify the value as JSON.

The full list of dimensions and metrics and their valid combinations is available using the Dimensions and Metrics Explorer.

Optional parameters

Name Description
Dimensions

A list of dimensions to query. Dimensions can be specified in one of two formats:

For example, all of the following are valid values for the Metrics parameter.

ga:source,ga:deviceCategory
ga:source
ga:deviceCategory
[{
  "name": "ga:source"
}, {
  "name": "ga:deviceCategory"
}]

For most use cases, a list of dimension IDs is the easiest way to specify the Dimensions parameter.

The full list of dimensions and metrics and their valid combinations is available using the Dimensions and Metrics Explorer.

Order

The sorting order of the results by column (metric or dimension ID) and direction (ascending or descending). Order can be specified in one of two formats:

For example, all of the following are valid values for the Order parameter.

-ga:sessions,ga:eventLabel
-ga:sessions
ga:eventLabel
[{
  "fieldName": "ga:sessions",
  "sortOrder": "DESCENDING"
}, {
  "fieldName": "ga:eventLabel",
  "sortOrder": "ASCENDING",
  "orderType": "DIMENSION_AS_INTEGER"
}]

For most use cases, the legacy format is the easiest way to specify the Order parameter. However, if you want to use the orderType parameter, you must specify the value as JSON.

Filters

Conditions that let you restrict the set of results that are returned. Filters can be specified in one of two formats:

For example, any of the following are valid values for the Filters parameter.

ga:sessions>10;ga:country==United States
{
  "metricFilterClauses": [{
    "filters": [
      {
        "metricName": "ga:totalEvents",
        "operator": "GREATER_THAN",
        "comparisonValue": "100"
      }
    ]
  }],
  "dimensionFilterClauses": [{
    "operator": "AND",
    "filters": [
      {
        "dimensionName": "ga:eventCategory",
        "operator": "EXACT",
        "expressions": ["Outbound Link"]
      }
    ],
    "filters": [
      {
        "dimensionName": "ga:eventAction",
        "operator": "EXACT",
        "expressions": ["click"]
      }
    ]
  }]
}

For most use cases, the legacy format is the easiest way to specify the Filters parameter. However, if you need to do advanced filtering (e.g. use comparison operators on dimensions) then you must specify the value as JSON.

Segments

Conditions that let you restrict the set of sessions or users your query applies to. Segments can be specified in one of two formats:

For example, any of the following are valid values for the segments parameter.

gaid::-1,segment=sessions::condition::ga:city==New York
gaid::-1,
segment=sessions::condition::ga:city==New York
[{
  "dynamicSegment": {
    "name": "New York Sessions",
    "sessionSegment": {
      "segmentFilters": [{
        "simpleSegment": {
          "orFiltersForSegment": [{
            "segmentFilterClauses":[{
              "dimensionFilter": {
                "dimensionName": "ga:city",
                "expressions": ["New York"]
              }
            }]
          }]
        }
      }]
    }
  }
}, {
  "dynamicSegment": {
    "name": "Non-referral Sessions",
    "sessionSegment": {
      "segmentFilters": [{
        "simpleSegment": {
          "orFiltersForSegment": [{
            "segmentFilterClauses": [{
              "dimensionFilter": {
                "dimensionName": "ga:medium",
                "operator": "EXACT",
                "expressions": [ "referral" ]
              }
            }]
          }]
        },
        "not": "True"
      }]
    }
  }
}]

For most use cases, it's easiest to specify segment IDs or use the legacy definition format. However, if you need to do advanced segmentation (e.g. use comparison operators on dimensions) then you must specify the value as JSON.

Limit

The maximum number of results that will be returned for a report. If this parameter is left blank, all results are returned.

Spreadsheet URL The URL of an external Spreadsheet to output the result. By default, results are outputted to a sheet in the current spreadsheet. Note, you must have edit permissions on the spreadsheet to use this feature.
Skip Report

When TRUE the report in the corresponding column will not be run. This option is useful if experimenting with a new report and you don't want to run all your reports until you're finished. It can also be useful to if you want to stop running reports that already exist and the data isn't going to change.

Note, this field can be set programmatically using a formula. For example, the following expression will skip the report if its End Date value (cell "B5") is in the past:

=IF(B5 < TODAY(), TRUE, FALSE)

Hidden parameters

Name Summary
Report Type

By default (or when left blank) this report will query the Analytics Reporting API v4. To query the Multi-Channel Funnels Reporting API, you can set the value to "mcf".

Note that when using the MCF API, you must also use MCF dimensions and metrics. In addition, some of the other features (e.g. segments) are not available in MCF reports.

Sampling Level The desired report sample size. Possible values are "DEFAULT" (the same as leaving it blank), "SMALL", or "LARGE".
Use Resource Quotas The resource based quota system is a new quota system that will enable certain Analytics 360 (Premium) accounts to have higher sampling thresholds while using the Analytics Reporting API. If you have access to this feature, you can enable it by setting this parameter to TRUE.
Include Empty Rows

The Include Empty Rows parameter can be set to TRUE to have the report include rows where all the metric values for the respective dimensions in the row are zero. If FALSE or left blank these rows are not included.

The most common reason to use this parameter is when using date or time dimensions and you want your reports to include rows for all dates or times in the range regardless of whether there are metric values for these ranges.