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.
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.
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:
- Create a new Google Spreadsheet (or open an existing one).
- From the menu bar choose: Add-ons > Get Add-ons…
- Find the Google Analytics Add-on from the add-ons gallery and select it.
- From the add-on description page, click the „+” in the top right corner to add this add-on to your spreadsheet.
- A dialog should pop up requesting your permission for the add-on to access your Google Analytics data. Click „Accept”.
- The add-on is now installed. A „Google Analytics” submenu should now appear in the Add-ons menu.
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.
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 (profiles)
Through the Google Analytics web interface you can only see reports and visualization data from a single view (profile) at a time. With this add-on you can run reports on any number of views (profiles) 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.
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.
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
|Report Name||no||This is the report name. It will also be the name of the sheet where the report data is written. If you want to skip a report from running without deleting all of its configuration data, simply delete the value from the report name field and that report column will be ignored.|
|Type||no||Possible values are „core” or „mcf”. The default value is „core”. These represent the Core Reporting API and the Multi-Channel Funnels Reporting API respectfully.|
View (Profile) ID / ids
|yes||The identifier for your Google Analytics view (profile). If you don’t know this value, you can get it using the report creation tool or the Account Explorer.|
|Start Date||yes *||Start date for fetching Analytics data. Requests can specify a start date formatted as YYYY-MM-DD, or as a relative date (e.g.,
|End Date||yes *||End date for fetching Analytics data. Request can specify an end date formatted as YYYY-MM-DD, or as a relative date (e.g.,
|Last N Days||no||For convenience, you can specify this value instead of setting start and end dates. This will query the last N full days, means from N days ago up until yesterday. Note: This value defaults to „7” when using the report creation tool.|
|Metrics||yes||A list of metrics to query, such as
|Dimensions||no||A list of dimensions to query, such as
|Sort||no||A list of dimensions and metrics indicating the sorting order and sorting direction for the returned data. Multiple sort values can be separated with a comma or new line. To sort in reverse, prefix the value with a minus. For example:
|Filters||no||Dimension or metric filters that restrict the data returned for your request.|
|Segment||no||Segments the data returned for your request.|
|Sampling Level||no||The desired sampling level. Allowed Values: „DEFAULT”, „FASTER”, or „HIGHER_PRECISION”. For more information see sampling.|
|Start Index||no||Only show results starting from this index. By default it starts from the first result.|
|Max Results||no||The maximum number of results to show. The default value is 1000, the maximum is 10000.|
|Spreadsheet URL||no||You may specify a spreadsheet other than the current one to output your report data. Simply enter the full URL of that spreadsheet. Note that you must have edit permissions to do this.|
* Start and end date are not required if you’re using „Last N Days”, which gets automatically translated to start and end date parameters behind the scenes.