There are regular reporting features built in to Google Analytics but what if you want to do customised reporting or other regular data collection and integration techniques? One answer is Google Apps Script. Apps Script is a Google Drive feature that makes custom automation so much easier. The big advantage of Apps Script is you are already in a Google authenticated environment. So while anyone can use the existing Google Analytics APIs using Apps Script you don’t need to worry about the authentication handshake to get your data. This means that you can access Google Analytics data in one line of code. There is a couple of tickboxes to get to that one line of code but in this tutorial I’ll walk you through the process.
Note: You can also get Google Analytics (and other analytics data) in a Google Sheet with zero lines of code using add-ons. The current limitation of these is they cannot currently be automatically run on a scheduled basis.
Setting up our project
Google Apps Script lives in a couple of places but for this example we are going to use a Google Sheet so step one is:
- From your Google Drive account create a new Spreadsheet
- In the new spreadsheet open Tools > Script editor…
- If this is your first script you might see a window popup with options to ‘Create projects for’, if so click ‘Close’
- You should now see the Script editor
- The connection to Google Analytics is a Google Apps Script ‘Advanced Service’ and to enable we need to turn it on. To do this in the Script Editor select Resources > Advanced Google services
- At this point you will be prompted to create a project name. Enter a name for your project and click ‘OK’.
- You should now see a list of available advanced services. For this project we want to just turn on Google Analytics API by clicking the on/off toggle. So far we have enabled Analytics in our project but we also need to click on the Google Developers Console link highlighted to enable this at the console end.
- Similarly to the ‘Advanced Google Services’ we need to enable the Analytics API by toggling the on/off (at this point you may be prompted to review and accept terms of service)
- Once enabled in the Developer Console you can close this tab/window and click OK in the ‘Advanced Google Services’ box in your Script Editor.
To recap our project is now configured to use the Google Analytics API. You can use multiple advanced services in the same project as needed. Remember ‘Advanced Services’ need to be enabled for each project you create but this only needs to be done once for each project.
Getting Google Analytics data (and some basic Script Editor tips)
For those familiar with coding environments the Script Editor comes with a code autocomplete. This is useful when starting rather than remembering a long list of classes and methods. To use this:
In the Script Editor click on line 2 between the moustaches (‘{‘ and ‘}’) and for PC press CTRL + SPACE or Mac ⌘ + SPACE, from this you can select Analytics from the list. To continue the autocomplete follow it with a dot ‘.’ which brings the associated options. The line we want to get is Analytics.Data.Ga.get(ids, start-date, end-date, metrics, optionalArgs);
So you’ll see there are a couple of parameters we need to provide defined in the GA Core Reporting Query Parameters Summary:
ids - Unique table ID for retrieving Analytics data. Table ID is of the form ga:XXXX, where XXXX is the Analytics view (profile) ID.startDate - 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 7daysAgo). The default value is 7daysAgo.endDate - End date for fetching Analytics data. Request can should specify an end date formatted as YYYY-MM- DD, or as a relative date (e.g., today, yesterday, or 7daysAgo). The default value is yesterday.metrics - A comma-separated list of Analytics metrics. E.g., 'ga:sessions,ga:pageviews'. At least one metric must be specified.optionalArgs – is an object array of the optional query parameters like dimensions, segment, filters, sort etc. An example from this Google Analytics Apps Script tutorial is:
var optionalArgs = {
'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.
};Building a Google Analytics Query with Query Explorer
If you are unfamiliar with the Google Analytics Core Reporting API building a query can be quite daunting. Fortunately the Google Analytics team have made the Google Analytics Query Explorer which gives you an interactive interface to build and test queries. Here’s an example to query your top referring sites, which should give you a page like this:
If you haven’t used the Query Explorer before you’ll need to click on the ‘Authorize Access’ button, which will enable a ‘Get Data’ button. You can test and tweak your query as much as you like using the ‘Get Data’ button to see what is returned from your Google Analytics accounts. The Query Explorer is a great starting point but remember it only contains a few of the optional arguments.
If you are using the Query Explorer then below is a helper script for converting your Query URI into an Apps Script snippet. To use this build your query in the Query Explorer and then press the Query URI button 
to get a URI to paste in the textfield and then click ‘Submit’
The little helper script gives us some extra code to store/pass the parameters we need. If you are scheduling this script to run on a regular basis you’ll need to modify the start/end date in the query. The are two main ways you can do this: either building the date by manipulating a Date instance formatted as YYYY-MM-DD; or as a relative date (e.g., today, yesterday, or NdaysAgo where N is a positive integer). Below is an example of a modified query made with the Query Explorer and exported to generate the script we need which gets data from the last 7 days. In this example I’ve wrapped it in a new function name fetchMyQuery. Note: In your example you need your own ids value to return results.
function fetchMyQuery() {
var query = {
"optionalArgs": {
"dimensions": "ga:source",
"filters": "ga:medium==referral",
"sort": "-ga:pageviews",
"max-results": "50"
},
"ids": "ga:82426939",
"metrics": "ga:pageviews,ga:sessionDuration,ga:exits",
"start-date": "7daysAgo",
"end-date": "yesterday"
};
var results = Analytics.Data.Ga.get(query.ids, query['start-date'], query['end-date'], query.metrics, query.optionalArgs);
Logger.log(results);
}Testing/Debugging Apps Script
With your script saved we can test the code. When developing scripts there is a debug feature demonstrated in the video below (the first time you run a new script you need to authorise it. This only needs to be done the first time the script runs or when new permissions are required.
Insert Data Into A Spreadsheet
The final step is to output the results from our query into Google Sheets. For this example let reuse a modified version of the outputToSpreadsheet method in the Automated Access to Google Analytics Data in Google Sheets tutorial:
function outputToSpreadsheet(results, sheet) {
// 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 inserts all the header and reporting data to the sheet. For more information on how to insert data into Google Sheets with Apps Script there is a Storing Data in Spreadsheets tutorial.
The outputToSpreadsheet method in our example is expecting two objects to be passed in. To do this your fetchMyQuery method needs to include getting Sheet object and passing it to will need to outputToSpreadsheet. The entire project should look like this:
When you now Run > fetchMyQuery you should see the data written in the sheet you specified.
Automate the Script
This project has been about automated data collection so let look at how we set this up. Google Apps Script makes automation very easy using the triggers feature. To set this up
- In the Script Editor click Resources > Current project’s triggers
- Click ‘No triggers set up. Click here to add one now’
- Lets configure the
fetchMyQuery to run once a week by setting: - The
Run dropdown to: fetchMyQuery - The
Events dropdown to: Time-driven, and selecting Week timer to run Every Monday between 7:00 a.m. to 8:00 a.m.
Once saved this script will run as scheduled with no need for your to have the sheet open. If you would like to be told if the script fails whilst unattended click the notifications link which opens a new dialogue box to allow you to configure to which email you want errors to be sent and when.
Summary
This post has introduced you to using Google Apps Script to automatically collect and write Google Analytics data to a Google Sheet. Once you get started with Apps Script you’ll start discovering many more opportunities to automate tasks. For example, as part of our triggered event we could notify people of an update using the MailApp service or write an entire document using Document Service. For some ideas you might want to read about Analytics reporting with Google Apps Script at the UK Cabinet Office. If you are just starting to use Google Apps Script the Google Developers has extensive documentation and tutorials and if you get stuck there is a dedicated tag in Stackoverflow and an active community on Google+.
