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:
- Create a Google Sheets file. Give it a cool name.
- Create a new Apps Script.
- In the menu, go to Extensions > Apps Script.
- If a menu pops up, just click Blank Project.
- 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.
- In the script editor, select Resources > Advanced Google services...
- In the dialog that appears, click the on/off switch next to the Google Analytics API.
- At the bottom of the dialog, click the link for the Google Developers Console.
- 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.)
- 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
, andMidnight to 1am
.
- The
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:
- Reading Data from Spreadsheets – So you can specify your APIs queries in a spreadsheet instead of in JavaScript.
- Inserting Charts from Spreadsheets into a Google Site – So you can create dashboards in Google Sites with your Analytics Data.
- Custom Menus – To make it easy for other users in your company to use the scripts you write.