Automated Access to Google Analytics Data in Google Sheets

Nick Mihailovski, Google Analytics API Team – August 2012

This tutorial describes how to access the Management and Core Reporting APIs inside Google Sheets using Apps Script.


Introduction

You can use the Google Analytics API and Google Apps Script to access your Google Analytics data from Google Sheets. This is powerful because it allows you to utilize all the great features of Google Sheets with your analytics data, such as easy sharing, collaboration, charting and visualization tools.

This tutorial will walk you through the code required to access your Google Analytics data in Google Sheets using Google Apps Script.

Overview

This tutorial will show you how register, and configure the Apps Script environment to use the Google Analytics API. Once configured, the tutorial walks you through how to retrieve a view (profile) ID for the authorized user using the Management API. Then how to use the view (profile) ID to query the Core Reporting API to retrieve the top 250 mobile search keywords from Google. Finally the results will be inserted into a Google Spreadsheet. Once you have data, the tutorial also discusses how to automate retrieving data.

When you build an application using the Google Analytics API and Apps Script, you will generally go through the following steps:

  • Enable the Google Analytics APIs in Google Sheets
  • Work with the Google Analytics APIs

Let's go through each step in detail.

Enable Google Analytics API in Apps Script

To enable access to your Google Analytics data from within Google Sheets, follow these steps:

  1. Create a Google Sheets file. Give it a cool name.
  2. Create a new Apps Script.
    1. In the menu, go to Extensions > Apps Script.
    2. If a menu pops up, just click Blank Project.
    3. Give the project a name. Make sure it has a cool name.

Once you have a new script, you need to enable the Google Analytics Service.

  1. In the script editor, select Resources > Advanced Google services...
  2. In the dialog that appears, click the on/off switch next to the Google Analytics API.
  3. At the bottom of the dialog, click the link for the Google Developers Console.
  4. In the new console, again click the on/off switch next to the Google Analytics API. (Once enabled, it will jump to the top of the page.)
  5. Return to the script editor and click OK in the dialog.

A little yellow dialogue box should popup that says that you have successfully added a new Google APIs service to your script. Now you are ready to start writing your first script.

Work with the Google Analytics API

This script in this tutorial will query the Google Analytics API for the top 250 Google mobile search keywords, then output the results into Google Sheets. To accomplish this, the script will go through the following steps:

  • Retrieve the authorized user's first view (profile).
  • Query the Core Reporting API for data.
  • Insert data into a spreadsheet.

Add the following function to the blank project.

function runDemo() {
  try {

    var firstProfile = getFirstProfile();
    var results = getReportDataForProfile(firstProfile);
    outputToSpreadsheet(results);

  } catch(error) {
    Browser.msgBox(error.message);
  }
}

In the code above, a try...catch block is used to handle any API errors. If any errors occur, the program execution will halt and the error will be displayed in a message box. In the try block, a function is used to perform each of the steps the script will go through. Let's now add the code for each of these functions.

Retrieve the Authorized User's First View (Profile)

In Google Analytics, each report belongs to a view (profile) and is identified by a view (profile) ID. So when you specify a query for report data, you must also specify the view (profile) ID of the view (profile) from which you want to retrieve data.

The Google Analytics Management API provides access to all the accounts, webproperties, and view (profile) entities that belong to a user. Each of these entities belong in a hierarchy, and you can programmatically traverse this hierarchy to retrieve a view (profile) ID for the authorized user.

The second function we will write will traverse down the Management API hierarchy and return the user's first view (profile). Copy and paste the following code to your Apps Script project:

function getFirstProfile() {
  var accounts = Analytics.Management.Accounts.list();
  if (accounts.getItems()) {
    var firstAccountId = accounts.getItems()[0].getId();

    var webProperties = Analytics.Management.Webproperties.list(firstAccountId);
    if (webProperties.getItems()) {

      var firstWebPropertyId = webProperties.getItems()[0].getId();
      var profiles = Analytics.Management.Profiles.list(firstAccountId, firstWebPropertyId);

      if (profiles.getItems()) {
        var firstProfile = profiles.getItems()[0];
        return firstProfile;

      } else {
        throw new Error('No views (profiles) found.');
      }
    } else {
      throw new Error('No webproperties found.');
    }
  } else {
    throw new Error('No accounts found.');
  }
}

In this function, the Accounts collection is first queried, using the Analytics.Management.Accounts.list method. If the authorized user has Google Analytics accounts, the ID of the first account is retrieved. Next, the web properties collection is queried by calling the Analytics.Management.Webproperties.list method and passing the method the account ID retrieved in the previous step. If web properties exist, the view (profile) collection is finally queried using the Analytics.Management.Profiles.list method. Both account ID and a web property IDs are passed as parameters to this method. If views (profiles) exist, the first view (profile) is returned.

If at any time an API error occurs, or if the API response contains no results, an error is thrown with a message describing that no results were found. The catch block in the runDemo function above will catch this error and print the message to the user.

After the script returns, it can now query for reporting data.

Query the Core Reporting API for data.

Once you have a view (profile) ID, you use the Core Reporting API to query for Google Analytics report data. In this section you will learn how to query this API using Apps Script.

Add the following code to your Apps Script project:

function getReportDataForProfile(firstProfile) {

  var profileId = firstProfile.getId();
  var tableId = 'ga:' + profileId;
  var startDate = getLastNdays(14);   // 2 weeks (a fortnight) ago.
  var endDate = getLastNdays(0);      // Today.

  var optArgs = {
    'dimensions': 'ga:keyword',              // Comma separated list of dimensions.
    'sort': '-ga:sessions,ga:keyword',       // Sort by sessions descending, then keyword.
    'segment': 'dynamic::ga:isMobile==Yes',  // Process only mobile traffic.
    'filters': 'ga:source==google',          // Display only google traffic.
    'start-index': '1',
    'max-results': '250'                     // Display the first 250 results.
  };

  // Make a request to the API.
  var results = Analytics.Data.Ga.get(
      tableId,                    // Table id (format ga:xxxxxx).
      startDate,                  // Start-date (format yyyy-MM-dd).
      endDate,                    // End-date (format yyyy-MM-dd).
      'ga:sessions,ga:pageviews', // Comma seperated list of metrics.
      optArgs);

  if (results.getRows()) {
    return results;

  } else {
    throw new Error('No views (profiles) found');
  }
}

function getLastNdays(nDaysAgo) {
  var today = new Date();
  var before = new Date();
  before.setDate(today.getDate() - nDaysAgo);
  return Utilities.formatDate(before, 'GMT', 'yyyy-MM-dd');
}

The first part of the code constructs a Core Reporting API query using the Analytics.Data.Ga.get method. The method accepts a bunch of parameters that specify the type of report to retrieve. Each Core Reporting API query consists of a set of required and optional parameters. The required parameters are passed to the method as parameters while the optional parameters are passed as an object.

The table ID parameter is required and is formed by combing the prefix ga: to the view (profile) ID. The code creates the table ID using the view (profile) ID retrieved in the previous step. The start and end date are also required and specify the date range of the data to retrieve. Both are calculated based on today's date using the getLastNdays function. Finally all the optional parameters are passed to the function using the optArgs object.

When the Analytics.Data.Ga.get method runs, a request is made to the Core Reporting API. If an error occurs, it is caught in the try...catch block defined in the outer runDemo method. If the request was successful, the results are returned.

Insert Data Into A Spreadsheet

The final step in our script is to output the results from the Core Reporting API into Google Sheets. The outputToSpreadsheet method does this work. Add the following code to your project:

function outputToSpreadsheet(results) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().insertSheet();

  // Print the headers.
  var headerNames = [];
  for (var i = 0, header; header = results.getColumnHeaders()[i]; ++i) {
    headerNames.push(header.getName());
  }
  sheet.getRange(1, 1, 1, headerNames.length)
      .setValues([headerNames]);

  // Print the rows of data.
  sheet.getRange(2, 1, results.getRows().length, headerNames.length)
      .setValues(results.getRows());
}

This function first inserts a new sheet into the active spreadsheet. It then inserts all the header and reporting data to the sheet. For more tips on how to insert data into Google Sheets, read Writing Data from JavaScript Objects to a Spreadsheet in the Storing Data in Spreadsheets tutorial.

Run The Script

Once you have added all the code to the project, you can now run it.

  • In the script editor toolbar, in the select function dropdown, select, runDemo.
  • Next, click the play button.

The first time you run this, a pop-up box will appear that will require you to authorize this script to access your Google Analytics account data.

Click authorize.

Once clicked, a new page hosted on google.com will open and prompt you to grant this script access to your data. Once you click on allow, you will be redirected to a confirmation page. At this point you the script will now be able to access your Google Analytics data and it can continue to execute.

After the script runs, click over to the window with Google Sheets. You should see all the keywords data returned from the API, or a message box with an error message.

Automate the Script

At this point you should have a script that queries the Google Analytics API. You might now want to automate this script to retrieve new data every night. Apps Script makes automation very easy using the triggers feature.

To automate this script go though the following steps:

  • In the script editor toolbar, click Resources -> All your triggers...
  • Click Add a new trigger. The triggers dialogue box will appear.
  • Configure the trigger to execute the runDemo method every night
    • The Run dropdown should be set to: runDemo
    • The Events dropdown should be set to: Time-driven, Day timer, and Midnight to 1am.

Once configured this script will run every night, giving you fresh data in the morning.

If any errors occur at night, you will want to be notified. Apps Script also allows you to send an email alerting if any failures occur. To configure this, in the triggers dialogue box, click the notifications link. A new dialogue box will pop-up and allow you to configure to which email you want errors to be sent.

Conclusion

you have successfully registered and authorized access to your script. You have queried the Management API multiple times to retrieve a view (profile) ID. Then you used the view (profile) ID to query the Core Reporting API to retrieve data and output it into Google Sheets.

Using the techniques described in the tutorial will allow you to do more complex analysis, gain greater insight, build custom dashboards and reduce a lot of time running manual reports.

A couple of other cool tutorials you might find useful to help you get more out of the Google Analytics API and Google Apps Script are: