Export Google Analytics Accounts, Properties and Views Name & ID automatically – Google Appscript

Practitioner - Awanish Dubey 16-Jun-2019

Are you looking to track all your GA accounts, properties and views at one place, but don’t have any clue how to do it?

This post is going to help you then.

Let’s start with the final output and then get back to see how its implemented.

Final Output - Data studio report with accounts, properties & views name & id.

Additionally, I have included filter option in order to get the details of any specific account, property or view. I have embedded data studio report, which allows you to interact with the report.

OK, give it a try. In the account filter, select account name “Golden Triangle Tour India” only. You will get the filtered report with all the properties and views available under “Golden Triangle Tour India” account. Isn’t it nice?

So, how to get this output?

As the title states, solution is just a few lines of appscript code. Best source to learn appscript is google developer’s site - https://developers.google.com/apps-script/overview

So, instead of explaining, what is appscript and its usage, let's just move directly on the required code and setup to execute the code.

Also, I have shown you the output in data studio report, but there are many ways to get the result. I will show you the required data in three different ways.

  1. Data in Loggers.log
  2. Data (Account Name, ID | Property Name, ID | View Name, ID) in Google Spreadsheet
  3. Data (Account Name, ID | Property Name, ID | View Name, ID) in Google Data Studio Report

In this post, we will learn the starting two ways of getting data. I will write a separate post for the third one.

1) Data in Loggers.log

Basic requirement

  • You should have Gmail account with the admin access to your GA account.

Basic Setup

  • Login to your Gmail account and open https://script.google.com/home in a new tab.
  • Click on “New Script” to create new project. Once done, a project page will be opened in a new tab. Update the project name like GA data.
  • In the header menu, select “Advanced Google Services” under the “Resources” section & enable Google Analytics API.

Basic Terminologies

  • GA Account known as account
  • GA Properties known as webProperties
  • GA Views known as profiles

Now write a code. In code.gs file, copy paste the below code & save the file (CTRL + S).

Now execute the code. Under header menu, click on RUN option and select the function name getGAdata.Once the program starts executing/run, there will be couple of access pop-up’s which needs to be “allowed”. You just need to click on “Allow” option.

Once done, you can see the result in “Logs” under View section on the header menu.The purpose of above code was just to fetch account, properties and views list and display it in log. Logger.log() is similar to console.log().

2) Data (Account Name, ID | Property Name, ID | View Name, ID) in Google Spreadsheet

Now, what if you want to extract the same GA details in a spreadsheet using Appscript.

Basic Setup – Along with the setup done in the first solution, we just need to enable spreadsheet API.In the header menu, select “Advanced Google Services” under “Resources” section and enable Spreadsheet API.

Now, Let's try to understand this solution in steps.

• First, we will create a new spreadsheet named as “Google Analytics Data”
var createss = SpreadsheetApp.create("Google Analytics Data");

• Every spreadsheet’s have unique id. We will use this id to insert a new sheet in that spreadsheet. Let's name the sheet as "Google Analytics Schema"
var ssid = createss.getId(); var openss = SpreadsheetApp.openById(ssid); var insertsheet = openss.insertSheet('Google Analytics Schema');

• Then, freeze the first row of the spreadsheet
insertsheet.setFrozenRows(1);

• Now, set a first row as header by selecting the range and providing the names to each column. Suppose we want to extract below 6 items in 6 different columns i.e. Column A to F.
insertsheet.getRange("A1:F1").setValues([['Account Name', 'Account ID', 'Property Name', 'Property ID', 'View Name', 'View ID']]);

• Then, we will extract the GA accounts, properties and views info using the same code explained in the first section of the blog.

• In last, we will append the output in the spreadsheet using sheet.appendRow syntax.
sheet.appendRow([accounts.items[i].name,accounts.items[i].id, webProperties.items[j].name, webProperties.items[j].id, profiles.items[k].name, profiles.items[k].id]);

Final Code

Save the file & execute the code using “RUN” button in the header section.

Now, open https://docs.google.com/spreadsheets in a new tab. You will get “Google Analytics data” spreadsheet having sheet “Google Analytics Schema” with the account, property and views names and id.

This Spreadsheet can be used to create a data studio reports by selecting data source "Google Analytics Schema" sheet under "Google Analytics data" spreadsheet.

Also, we can include daily, weekly or monthly trigger condition to extract the GA Account details automatically.

Note - There is a scope to make these codes more optimized.

We will learn the third solution in the upcoming blog post. Hope, the post was helpful.

Please feel free to write us on connect@qaror.com , in case you need the code to be modified or optimized.