Script to Export Google Analytics Custom Dimensions from all the properties & accounts in one go

Practitioner - Awanish Dubey 29-Sep-2019

Listing down all the custom dimensions from different properties is a very tedious task. It can take hours to copy and paste each and every custom dimension from all the properties.

But with the script, it can be done within few seconds. This script will help you to export all the custom dimensions from different properties and accounts & and will insert it in a spreadsheet in a proper format.

The output will be in google spreadsheet and it will look like this.

Final Output - Google spreadsheet - list of all the exported custom dimensions.

Hope you found it interesting. To achieve this output, we will execute few lines of Appscript code. Best source to learn appscript is google developer’s site - https://developers.google.com/apps-script/overview.

Lets move to the basic requirement, basic setup and code creation section.

Basic requirement

  • You should have Gmail account with the "Admin" or "Read & Analyze" 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 a new project. Once done, a project page will be opened in a new tab. Update the project name like "Export Google Analytics Custom Dimensions".
  • In the header menu, select “Advanced Google Services” under the “Resources” section & enable Google Analytics & Spreadsheet API .

Basic Terminologies

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

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

Once the file is saved, click the RUN option under header menu 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.

Now, open https://docs.google.com/spreadsheets in a new tab. You will get a spreadsheet with the name “GA Custom Dimensions – Date & Time” having a sheet Custom Dimension with the below fields.

  • Account name
  • Account ID
  • Property Name
  • Property ID
  • Dimension ID
  • Dimension Name
  • Dimension Scope
  • Dimension Index
  • Dimension Active Status
  • Dimension Creation date
  • Dimension Updated Date

Now, Let's try to understand this solution in steps. Basically, the code is divided into 3 different parts to perform 3 different tasks.

1. Spreadsheet creation

In this step, we will create a new spreadsheet named as “GA Custom Dimensions”. You can also append the date and time to the spreadsheet name. It will help you to get the latest exported file.
var now = new Date();
var createss = SpreadsheetApp.create("GA Custom Dimensions - " + now );

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 "Custom Dimensions".
var ssid = createss.getId(); var openss = SpreadsheetApp.openById(ssid); var insertsheet = openss.insertSheet('Custom Dimensions');

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 11 items in 11 different columns, i.e. Column A to K.
insertsheet.getRange("A1:K1").setValues([['Account Name', 'Account ID', 'Property Name', 'Property ID', 'Dimension ID', 'Dimension Name','Dimension Scope','Dimension Index','Dimension Active Status','Dimension Created Date','Dimension Updated Date']]);

Then we will get the sheet id of the sheet in which we wants to append the custom dimension data. In our case, we will get the id of newly inserted sheet, i.e. "Custom Dimension".
var sheet = SpreadsheetApp.openById(createss.getId()).getSheetByName("Custom Dimensions")

2. Export Custom Dimensions from Google Analytics

In this step, we will extract custom dimensions from all the accounts & properties using below code.
var accounts = Analytics.Management.Accounts.list();
if (accounts.items && accounts.items.length) {
for (var i = 0; i < accounts.items.length; i++) {
var webProperties = Analytics.Management.Webproperties.list(accounts.items[i].id);
for (var j = 0; j < webProperties.items.length; j++) {
var customdimensions = Analytics.Management.CustomDimensions.list(accounts.items[i].id, webProperties.items[j].id) }}}

3. Insert Custom Dimensions to spreadsheet

In this step, we will insert the extracted custom dimensions and its details 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, customdimensions.items[k].id, customdimensions.items[k].name, customdimensions.items[k].scope, customdimensions.items[k].index, customdimensions.items[k].active, customdimensions.items[k].created, customdimensions.items[k].updated]);

That's all. Hope you enjoyed this learning. Let us know, if you have any doubt in any of the above steps.

Note - There is a scope to make this code more optimized.

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