If you are using Google Analytics, you might want to retrieve automated reports. Google Sheets allow you to automate reports using Google App Script. If you want to retrieve data on a repeated basis and upload a Google Sheet automatically, then this is article is for you. Within this article, I will explain to you how to get Google Analytics Automated Reports using Google Sheet, what the Google Analytics API reporting is, how you can authenticate, and how you can retrieve some basic metrics.
Google Analytics API
As mentioned in of my other articles about the Google Analytics API, Google Analytics has two different types of APIs:
The Management API allows you to change settings and create accounts, properties, and views.The reporting API allows you to retrieve data from your Google Analytics views, which you can then use to create custom dashboards, automate reporting, and integrate your Google Analytics data with any of your business applications. Luckily for us, Google Sheet allows you to call both types of APIs with the same class.
Authorize your Google Analytics API
Authorizing in Google Sheet is really easy, as it is just a matter of logging in with your Google Credentials after running your script. If you want to read data from your Google Analytics views, you will need to tell your script that you are allowed to see the data that you are requesting. Therefore, you will need to “authorize” the Google Analytics API. Without this process, anyone would be able to retrieve data from anyone.
We do have to enable to API by clicking on the “+” icon next to services (on the left side of the screen). There you should click on “Google Analytics API” and click add. I like to use this service, as it also includes the Google Analytics Management API. Now you are able to call the service by typing “Analytics” in your code. If you have clicked on the Google Analytics Reporting API service, then you will need to call the service by typing “AnalyticsReporting”. Both of these services should work the same for what we are going to do in this article
Google Analytics Automated Reports (Google App Script)
If this is your first time working with Google App Script, I will recommend you to first read my Google App Script Tutorial for Beginners. In that article, I discuss more in-depth the different elements of Google App Script and how it works fundamentally. For this article, I will split up our tasks in to the following steps:
- Get list of accounts
- Get list of properties (web properties)
- Get list of views (profiles)
- Get relevant metrics
Please get a copy of the following sheet before you start writing along:
Get list of accounts
So I am assuming here that you are interested in getting data from multiple views, which perhaps are in different properties, and even in different accounts. Therefore, we are first going to get a list of relevant accounts.
var ss = SpreadsheetApp.getActiveSpreadsheet(); //Get reference to active spreadsheet for when setting values in certain sheets;
var ui = SpreadsheetApp.getUi();
var settingSheet = ss.getSheetByName('Settings')
var accountSheet = ss.getSheetByName('Account List')
var propertySheet = ss.getSheetByName('Property List')
var viewSheet = ss.getSheetByName('View List')
var outputSheet = ss.getSheetByName('Output')
function getAccountList(){
var searchName = settingSheet.getRange("B4").getValue();
accountList = Analytics.Management.Accounts.list().items;
for (var x = 0; x < accountList.length; x++)
{
if (accountList[x].name.includes(searchName)){
var accountName = accountList[x].name
var accountId = accountList[x].id
accountSheet.appendRow([accountName, accountId]);
}
}
}In the code above we are globally defining all the sheets, hence they are not defined specifically in a function. That’s because we will need these variables across different functions. I assume the accounts you are looking for, all have a similar naming, and thus we are looking for accounts with a certain substring in their title. If we have found an account with that conditions, we append the name and the ID to the sheet called “Account List”
Get list of properties
Now that we have a list of accounts, we can now get all properties in those accounts with the following function.
function getPropertyList(){
var lastRow = accountSheet.getLastRow()
var accountList = accountSheet.getRange("B2:B"+lastRow).getValues()[0]
for (var i = 0 ; i < accountList.length; i++){
console.log(accountList[i])
var propertyList = Analytics.Management.Webproperties.list(accountList[i]).items
console.log(propertyList)
for (var x = 0; x < propertyList.length; x++) {
var propertyName = propertyList[x].name
var propertyId = propertyList[x].id
propertySheet.appendRow([accountList[i], propertyName, propertyId]);
}
}
}The code above reads all the account IDs that we retrieved with our previous getAccountList function (in the sheet called “Account List”). Then we use these IDs to retrieve the properties. These properties are then we append the account ID, property name, and the property ID to the sheet called “Properties List”.
Get list of views
Now that we have a list of properties, we can now get all views in those properties with the following function
function getViewList(){
var rangeData = propertySheet.getDataRange();
var lastColumn = rangeData.getLastColumn();
var lastRow = rangeData.getLastRow();
var accountIdColumn = 0
var propertyNameColumn = 1
var propertyIdColumn = 2
var rangeValues = rangeData.getValues();
// Loop through array
for ( j = 0; j < lastRow; j++){
if (j == 0){
continue} // skip first row
for ( i = 0 ; i < lastColumn; i++){
if (i == accountIdColumn){
var accountId = rangeValues[j][i]
} else if (i == propertyNameColumn) {
var propertyName = rangeValues[j][i]
} else if (i == propertyIdColumn) {
var propertyId = rangeValues[j][i]
}
if (i == (lastColumn - 1)){ // We execute on last column
var viewList = Analytics.Management.Profiles.list(accountId, propertyId).items
console.log(viewList)
for (var x = 0; x < viewList.length; x++) {
var viewName = viewList[x].name
var viewId = viewList[x].id
viewSheet.appendRow([accountId, propertyId, viewName, viewId]);
}
}
}
}
}The code above is slightly more complex than the previous two, as we now suddenly need to retrieve multiple values within one row. So we are looping over each row, and then retrieving value from multiple columns. The relevant columns are defined at the beginning of the function, and specify the position of that column. The account ID for example, can be found in the “Property List” sheet as the first column (index = 0). The property name, in the second column (index = 1), and the property ID in the third column (index = 3). Note that the column position starts counting at 0, instead of 1.
We are skipping the first row (j === 0) as that contains the heading. Then when reach the column of each row, we do an API call to retrieve all views based on the account ID and property ID. The results are appended to the sheet called “View List” and contains the account ID, property ID, view name, and view ID.
If you want to better understand how we are looping over each row here, you can check this article by Yagisanatode.
Get results
Now that we have a list of all the views for which we want to retrieve data, we can start calling the Google Analytics Reporting API.
function runReport() {
var createList = settingSheet.getRange("B3").getValue()
if (createList == true){
getAccountList()
getPropertyList()
getViewList()
}
var now = new Date();
var MILLIS_PER_DAY = 1000 * 60 * 60 * 24;
var yesterday = new Date(now.getTime() - MILLIS_PER_DAY);
var startDate = Utilities.formatDate(yesterday, Session.getScriptTimeZone(),
'yyyy-MM-dd');
var endDate = Utilities.formatDate(yesterday, Session.getScriptTimeZone(),
'yyyy-MM-dd');
var rangeData = settingSheet.getDataRange();
var lastRow = rangeData.getLastRow();
var metricsRange = settingSheet.getRange("B7:B"+lastRow.toString())
var metrics = metricsRange.getValues().join(",");
var options = {
'max-results': 100
};
var rangeData = viewSheet.getDataRange();
var lastRow = rangeData.getLastRow();
console.log("lastrow: ", lastRow)
var viewList = viewSheet.getRange("D2:D"+lastRow).getValues()
var counter = 1
for (var i = 0 ; i < viewList.length; i++){
var profileId = viewList[i]
var tableId = 'ga:' + profileId;
var report = Analytics.Data.Ga.get(tableId, startDate, endDate, metrics,
options);
console.log(report)
if (report.rows) {
//Adds the values to the correct rows
var row = counter + 1
var headers = report.columnHeaders.map(function(columnHeader) {
return columnHeader.name;
});
var range = outputSheet.getRange(row, 1, report.rows.length, 4)
range.setValues([[report.profileInfo.accountId.toString(), report.profileInfo.webPropertyId.toString(), report.profileInfo.profileId.toString(), report.profileInfo.profileName.toString()]])
var range2 = outputSheet.getRange(row, 5, report.rows.length, headers.length)
range2.setValues(report.rows)
counter = counter + report.rows.length
} else {
Logger.log('No rows returned.');
}
}
}Looks like a lot of code above, but we are basically doing the following
- Reading the “Settings” sheet to retrieve the settings for our API call, such as whether to create a list of accounts/properties/views, the relevant account name, and metrics.
- If Create Account/Property/View List (“B3”) is TRUE, then we populate the account-, property-, and view- list sheet.
- Reading the “Views” sheet to retrieve all the view IDs for which we want to retrieve data.
- Calling the Google Analytics Reporting API to retrieve the relevant metrics for each of these views for yesterday.
- Appending the results to the “Output” sheet
Create Automation
To really get Google Analytics Automated Reports using Google Sheet, we will need to add some automation. This is just a matter of adding a time trigger. In the code above we assume you want to get data daily for the past day (yesterday). You can click on the clock icon on the right, and then click on add trigger in the bottom right corner. You will then see a pop-up similar to the one below. I have set the options to running the script every day around 9am to 10am. If you save these settings, you do not have to worry about it anymore and each day you will see the metrics for the past day in this Google Sheet.
I hope you have a better understanding of how to getGoogle Analytics Automated Reports using Google Sheet (Google App Script). Do not hesitate to ask me any questions by adding a comment below.
Pingback: Google Analytics Data API (GA4) with Python [Extensive Guide] - Automation Help