Google Analytics Report Automation (Magic Script)

Nick Mihailovski, Google Analytics Developer Programs Engineer – August 2012

Update: Google Analytics has released an Add-on for Google Spreadsheets built on top of magic script. It's easy to install, requires no coding, and takes care of the authorization piece for you. For more information check out the add-on usage guide.

Google Analytics Report Automation (Magic) is an integration that reduces the work required to put Google Analytics data into any Apps Script supported product, such as Google Docs, Sites, or Spreadsheets.

Introduction

The Google Analytics API allows you to export data so that you can perform analysis and create custom reports to answer questions important to your business. However, many users are looking for a simple way to put Google Analytics data into a Google Spreadsheet. Once the data is inside a Google Spreadsheet, users can easily manipulate Google Analytics data, create new visualizations, and build internal dashboards. Google Analytics Report Automation (Magic Script) simplifies this process and makes it easy to get the data you want so that you can focus on analysis and reporting.

An example of Google Analytics data accessed through Apps Script and displayed in a Google Spreadsheet.

Custom API Dashboards - No Code Required

A popular use case for report automation is to create dashboards that automatically update. The Google Analytics Report Automation (Magic) script is available in the Google Spreadsheets script gallery and can handle all of this work for you - no code required.

This script is a great template for starting your own project. You can find this script by opening or creating a Google Spreadsheet, clicking Tools -> Script Gallery and searching for analytics. Look for Google Analytics Report Automation (magic).

The following video shows you how to build a dashboard using this script:

API Setup Process

Before you can use Magic Script, you must configure the script to use the Google Analytics API. The API configuration has recently changed from the video above. Go through the following steps to enable the Google Analytics API:

1. In the script editor go to: Resources > Use Google APIs
2. Turn 'Google Analytics API' to ON
3. Click the link to Google APIs Console
4. Turn 'Google Analytics API' to ON
5. Accept the terms of service
6. Close the 'Google APIs Console' window
7. Click OK in the script editor window and close that window
8. Back in the spreadsheet, the 'Google Analytics' menu should now be working. (You may need to re-authenticate.)

Writing Your Own Script

It is also possible to write your own code and develop custom scripts. For example, with the Analytics - Apps Script integration, you can request the total users, sessions, and pageviews over time and put this data into a spreadsheet with just the following code:

// Get Data.
var results = Analytics.Data.Ga.get(
    tableId,
    startDate,
    endDate,
    'ga:users,ga:sessions,ga:pageviews',
    {'dimensions': 'ga:date'});

// Output to spreadsheet.
var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();
sheet.getRange(2, 1, results.getRows().length, headerNames.length).setValues(results.getRows());

// Make Sandwich.

To get started now, read our Automated Access to Google Analytics Data in Google Spreadsheets tutorial. Also check out the Google Analytics Apps Script reference docs.