How to Import Data from Google Analytics into Google Sheets withApps Script

At some point I got curious about how to get data from different API, such Google Analytics API, into Google Sheets. (not)Surprisingly there was a ready solution for that called Google Apps Script. So if you are tired of regularly exporting data from Google Analytics API, importing it into Excel and making the same report over and over again, then this post is for you! In this blog post I will explain :

1. How to get started with Google Apps Scripts
2. How to get data from Google Analytics Core Reporting API and insert it into Google Sheet
3. How to use cell values to specify parameters for Google Analytics API request

At the end of this post you should have Google Sheet that a) captures parameters (i.e. metrics that you want to get from Google Analytics API) from one sheet, b) gets the data using those parameters from Google Analytics API, and c) insert the result into a new sheet.

Hint: if reading the rest of the post doesn’t sound like fun, just open this shared Google Sheet to see the end result. Feel free to make a copy of it and use it as you wish.

Getting Started with Google Apps Scripts

1. Create a new Google Sheet
2. Navigate to Tools -> Script Editor

3. Select Blank Project in the popup window

4. Paste the following code on the Script Editor window and click Save:

function onOpen() {
 var ui = SpreadsheetApp.getUi();
 
 ui.createMenu(‘Get external data’)
 .addItem(‘Google Analytics’, ‘getGoogleAnalyticsData’)
 .addToUi();
}
function getGoogleAnalyticsData() {
 SpreadsheetApp.getUi().alert(‘You clicked Get external data -> Google Analytics menu item!’);
}

5. If you go back to the Google Sheet that you started with and click refresh (Script editor tab will close, but don’t worry about that), you will be able to see a new menu item called “Get External Data”.

Try clicking on the “Google Analytics” item in the menu and you will see a small popup:

Now let’s walk through what we just did to understand how Apps Script work (in a simple, marketer to marketer, terms):

We created an Apps Script that is associated with the Google spreadsheet. The Apps Script (for now) has two functions (select Tools -> Script Editor to get back to the Editor and see the code.

function onOpen() {
 var ui = SpreadsheetApp.getUi();
 
 ui.createMenu(‘Get external data’)
 .addItem(‘Google Analytics’, ‘getGoogleAnalyticsData’)
 .addToUi();
}

function onOpen() {} is fired automatically when the subject Google spreadsheet is opened (here’s why the name). For the future, the easiest way to run some code each time a document is opened is to put it into onOpen() function.

In this case we create a new Menu named “Get external data”, createMenu(‘Get external data’), as well as one Menu Item named “Google Analytics, addItem(‘Google Analytics’.‘getGoogleAnalyticsData’).

The most important part here, is that we define what happens when a user clicks the Menu item “Google Analytics” and we do that be specifying a function name, getGoogleAnalyticsData, to execute once this menu item is selected.

addItem(‘Google Analytics’.‘getGoogleAnalyticsData’)

Simple, right? Now, all is left is to write that getGoogleAnalyticsData function, which we do in the rest of the code:

function getGoogleAnalyticsData() {
 SpreadsheetApp.getUi().alert(‘You clicked Get external data -> Google Analytics menu item!’);
}

The code between the brackets {} just creates a popup for now, but in the next part of the post we will write a script that will get data from Google Analytics.

Getting Data from Google Analytics API

Google offer a great example on querying Google Analytics API using Apps Script, so let’s use it! (and my two cents will be in explaining what this code does.

Open Script Editor and replace function getGoogleAnalyticsData() {…} with this code:

function getGoogleAnalyticsData() {
var today = new Date();
var oneWeekAgo = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);

var startDate = Utilities.formatDate(oneWeekAgo, Session.getTimeZone(),'yyyy-MM-dd');
var endDate = Utilities.formatDate(today, Session.getTimeZone(),'yyyy-MM-dd');

var tableId  = 'ga:83819930';
var metric = 'ga:visits';
var options = {
    'dimensions': 'ga:source,ga:keyword',
    'sort': '-ga:visits,ga:source',
    'filters': 'ga:medium==organic',
    'max-results': 25
};
var report = Analytics.Data.Ga.get(tableId, startDate, endDate, metric,options);

if (report.rows) {
  var spreadsheet = SpreadsheetApp.getActive();
  var sheet = spreadsheet.getActiveSheet();

  var headers = report.columnHeaders.map(function(columnHeader) {
      return columnHeader.name;
  });
  sheet.appendRow(headers);

  sheet.getRange(2, 1, report.rows.length, headers.length)
        .setValues(report.rows);
} else {
    Logger.log('No rows returned.');
}
}

Now let’s go through what is happening in this code:

var today = new Date();
var oneWeekAgo = new Date(today.getTime() - 7 * 24 * 60 * 60 * 1000);

var startDate = Utilities.formatDate(oneWeekAgo, Session.getTimeZone(),'yyyy-MM-dd');
var endDate = Utilities.formatDate(today, Session.getTimeZone(),'yyyy-MM-dd');

You can ignore most of this part, what is important is that it defines a variable today and gives it a value of now (meaning the day and time you run this script), as well as defines a variable oneWeekAgo and gives it a value of one week ago from now (or rather one week ago from the day and time you run this script).

As you can see to get to one week ago you do simple calculus: (days) * (hours in a day) * (minutes in an hour) * (seconds in a minute) * 1000. Last multiplication by 1000 is needed as the value of the variable is in milliseconds. So to get 365 days back you do:

var oneYearAgo = new Date(today.getTime() - 365 * 24 * 60 * 60 * 1000);

The rest of the code in this part just converts the dates of today and oneWeekAgo into a format that is acceptable by Google Analytics API, so let’s move on:

var tableId = 'ga:83819930';
var metric = 'ga:visits';
var options = {
 'dimensions': 'ga:source,ga:keyword',
 'sort': '-ga:visits,ga:source',
 'filters': 'ga:medium==organic',
 'max-results': 25
};

This part of the code defines the parameters of the request we are going to make to Google Analytics API. If you know all of those, lucky you, for the rest of us I suggest Google Analytics Query Explorer (which is especially helpful in finding tableId).

var tableId = 'ga:83819930';

This the the ID of the Google Analytics View (not Account, not Property, but View) from which we want to get the data. In the above code you need to write tableId for one of the Google Analytics Views that you have access to. You can find it either using Query Explorer like in the picture above, or in Google Analytics -> Admin -> View Settings (just don’t forget to add “ga:” before the actual ID):

var metric = 'ga:visits';
var options = {
 'dimensions': 'ga:source,ga:keyword',
 'sort': '-ga:visits,ga:source',
 'filters': 'ga:medium==organic',
 'max-results': 25
};

The above defines the metrics, dimensions, filter and sorting options we are going to request from Google Analytics API. The “max-results” parameter can be set to 10,000, which is the absolute limit per Google Analytics API request. Thus, if you plan to retrieve more data, you will need to break down your query into parts (which Apps Script can easily help with!).

Finally we make a request to Google Analytics API:

var report = Analytics.Data.Ga.get(tableId, startDate, endDate, metric,options);

The response from the API is now store in the variable report; thus, the first thing we do is check if that variable has any data, and if it doesn’t we write to the log a message “No rows returned”:

if (report.rows) {
 ...
} else { 
  Logger.log('No rows returned.');
}

Log is extremely helpful tool, as it allows to you detect the problems that might be happening during the code execution. For instance, in this case if you run a script and nothing happens, you should check the log. If the log has the message “No rows returned” then most likely nothing happens because you formulated the API query parameters incorrectly.

You can access the log from Script Editor: View -> Log:

But let’s see what happens if the API does return some data:

var spreadsheet = SpreadsheetApp.getActive();
var sheet = spreadsheet.getActiveSheet();

The above code represents the very beauty of Google Apps Script: in just two simple lines you got hold of the active sheet, spreadsheet.getActiveSheet() in the active Google spreadsheet file, SpreadsheetApp.getActive() , and now can write data directly to the spreadsheet!

var headers = report.columnHeaders.map(function(columnHeader) {
 return columnHeader.name;
 });
sheet.appendRow(headers);

The first line is some fancy Javascript code (ok, not so fancy, but fully understanding it is beyond the scope of this post) that get the headers from the API response, which are ga:source, ga:keyword, ga:visits (remember what we specified in the API query? exactly those!).

The second line appends (meaning adding to the last non-empty row) a row with header values in the active sheet in the active spreadsheet, because earlier we define sheet as active sheet:

var sheet = spreadsheet.getActiveSheet();

in the active spreadsheet:

var spreadsheet = SpreadsheetApp.getActive();

Finally, we also add data we got back from the Google Analytics API to the same sheet:

sheet.getRange(2, 1, report.rows.length, headers.length)
 .setValues(report.rows);

A couple of important things here to understand what this line of code does:

getRange(row, column, numRows, numColumns)
gets a range, or selects several cells in the active sheet

In this example, row = 2, column = 1, numRows = number of rows in the the API response (report.rows.length), numColums = number of headers returned by the API call (headers.length).

.setValues()
sets the value of the range that we got by calling getRange

In this example we set the values of this range to the values of the Google Analytics API response, which are stored in report.rows.

Apps Script will do the magic and put value into respective cells, despite the fact that report.rows is actually a table, or array, not a single value.

Let’s finally run the code! Menu -> Get external data -> Google Analytics! The below part is super important to actually be able to run your code.

Here comes the first authorization that we need to make to run the code. Click continue and grant requested permissions (don’t worry this is your app, and I hope you should be safe by giving your own app access to your data).

Did you get that red alert “ReferenceError: “Analytics” is not defined.DetailsDismiss”? That’s good, it means we’re on the right track!

Navigate to Tools -> Script Editor and in the Editor navigate to Resources -> Advanced Google Services:

Turn on Google Analytics API (DO NOT click OK just yet!):

Now click the link Google Developers Console, which will open Developers Console. Now find Google Analytics API in the list and turn it on:

Now you can finally click OK in one the popup window in Script Editor. Navigate back to the spreadsheet and run the code again.

There will be one more Access request, this time for accessing your Google Analytics data (again, don’t worry, this is your app and you give access only to this app).

If you didn’t forget to change tableId to the Google Analytics View ID, which you have access to then you should see the result in the spreadsheet:

Awesome, isn’t it? Now let’s make it a bit more usefull!

Use Cell Values to Specify Parameters for Google Analytics API Request

We all probably were (or are) in situations where we needed to send regular reports to bosses or clients. We’d get the same data from Google Analytics, put into Excel and send the report. Let’s try to make this task easier with Google Apps Script!

First, I will add another menu item to our Get external data menu:

function onOpen() {
 var ui = SpreadsheetApp.getUi();
 
 ui.createMenu('Get external data')
 .addItem('Google Analytics', 'getGoogleAnalyticsData')
 .addItem('Create report', 'createReport')
 .addToUi();
}

I will also create a separate sheet with parameters for the query (as you can see I used VLOOKUP function to lookup tableId from the list at the bottom; collecting those IDs once will simplify the work in the future), as well as the name of the sheet that the report will be stored in:

Here is the code I wrote for the function createReport:

function createReport() {
 var spreadsheet = SpreadsheetApp.getActive();
 var sheet = spreadsheet.getSheetByName('params');
 
 var start = new Date(sheet.getRange(2, 3, 1, 1).getValues());
 var end = new Date(sheet.getRange(3, 3, 1, 1).getValues());
 
 var startDate = Utilities.formatDate(start, Session.getTimeZone(),'yyyy-MM-dd');
 var endDate = Utilities.formatDate(end, Session.getTimeZone(),'yyyy-MM-dd');
 
 var tableId = String(sheet.getRange(5, 3, 1, 1).getValues());
 var metric = String(sheet.getRange(6, 3, 1, 1).getValues());
 var options = {
 'dimensions': String(sheet.getRange(7, 3, 1, 1).getValues()),
 'sort': String(sheet.getRange(8, 3, 1, 1).getValues()),
 'filters': String(sheet.getRange(9, 3, 1, 1).getValues()),
 'max-results': String(sheet.getRange(10, 3, 1, 1).getValues())
 };
 Logger.log(tableId);
 var report = Analytics.Data.Ga.get(tableId, startDate, endDate, metric,options);
 
 if (report.rows) {
 var reportsheet = spreadsheet.getSheetByName(String(sheet.getRange(11, 3, 1, 1).getValues()));
 if(reportsheet) {
 spreadsheet.deleteSheet(reportsheet);
 }
 
 var reportsheet = spreadsheet.insertSheet(String(sheet.getRange(11, 3, 1, 1).getValues()));
 var headers = report.columnHeaders.map(function(columnHeader) {
 return columnHeader.name;
 });
 reportsheet.appendRow(headers);
 
 reportsheet.getRange(2, 1, report.rows.length, headers.length)
 .setValues(report.rows);
 
 Logger.log('Report spreadsheet created: %s',
 spreadsheet.getUrl());
 } else {
 Logger.log('No rows returned.');
 }
}

As you can see, I didn’t add many new things. Just few simple ones:

var sheet = spreadsheet.getSheetByName('params');

Previously, we selected an active sheet, but in this example I am selecting a sheet by name (“params”). I later get value for the API query from this sheet:

sheet.getRange(5, 3, 1, 1).getValues()

We discussed getRange, but getValues() is as simple as getting the values from that range. I later convert it into String(), so Google Analytics API can accept it:

String(sheet.getRange(5, 3, 1, 1).getValues())

Finally, I create a new sheet based on the name indicted in the “params”:

spreadsheet.insertSheet(String(sheet.getRange(11, 3, 1, 1).getValues()));

That’s it! Open the final Google Sheet, make a copy and feel free to use it. Hope this helps!

p.s. the same can be done to get data from Facebook API, Twitter API, YouTube API, Mailchimp API and AnyOtherAPI! ;) Ping me on Twitter (@jevgenijs) if you want me two write a blog post on using other APIs with Google Apps Script.

p.s.s. Thanks to brillian folks at Google for creating Apps Script and the great tutorial on how to get started.

How to Use Query Explorer to Get Any Data Out of Google Analytics

10 cool things you can do with Google Tag Manager

Introduction to JQuery for Marketers

Image: http://wallpoper.com/