Google Analytics needs no introduction. Definitely the most popular web analytics service on the Internet, helping businesses to collect and analyze data and use them to grow and enhance their services or products. The most common way to access Google Analytics data is through the main dashboard and predefined reports. In order to manipulate the data and extract more valuable insights, you can create a standard or custom report to present the data you need and then export them usually to a spreadsheet. The way to do this is by using the export capability (PDF, Google Sheets, Excel or CSV format) offered in the GA console and then import the file to Google Spreadsheets or any other spreadsheet software.

Export Google Analytics report

But what if you need to do this for multiple files or do it periodically during a time period (e.g to generate periodic reports). It’s a pain isn’t it? Well, Google Apps Scripts to the rescue! 💪💪

Google Apps Script

So, what is Google Apps Script? Google Apps Script is a JavaScript cloud scripting language that provides easy ways to automate tasks across G-Suite products and third party services and build web applications. In simple words, using JavaScript we can increase the power of our favourite and already powerful Google Apps like Spreadsheets, Docs, Calendar, Mail etc. In our case we will focus on the integration between Google Spreadsheets and Google Apps Scripts. To access the Script Editor, where we can build our application, we can create a new spreadsheet, and from the top menu we can click at Tools > Script Editor.

Connecting Google Spreadsheets and Google APIs

The above option will open a new tab with the Script Editor Console. Using this console we can build our application, create script files, connect resources etc. In our case we will use Google Analytics API as a resource to fetch the data we need. To enable the connection between Google Spreadsheets and Google Analytics API we must follow the below steps:

Click the “Resources” option on top menu. Click on “Advanced Google Services…” option. Give a name to our project (in this case we will name it “Test Project”). Search for Google Analytics API and click the toggle to turn it on.

5. Don’t hurry up to close the window yet. In order to gain full access we must also enable our service of choice in the Google API Console. If we click the link provided we will be redirected directly to the GCP console and by searching “Analytics API” on the top search bar we will find our service of choice.

6. Click Enable and you are good to go! 👏

Query Explorer

When dealing with data and reports, the top priority is our data to be consistent and trustworthy. It takes a long to build someone’s trust on your data but just a minute to lose it. Our best friend in this process is a very useful tool by Google called Query Explorer. Query Explorer helps us play with Google Analytics data by querying with various parameters and test the responses. We can choose the Account — Property — View we want to query the data and just add the parameters (start-date, end-date, metrics, dimensions, filters, segments e.t.c). We can then download the data in TSV format (tab-delimited file), or save the direct API Query URI to this report. Querying the data through Google Apps Script is a way to simulate Query Explorer data and personally I always have the Query Explorer open in a tab to validate the queries.

Building the script

Enough talk. Let’s dive into action. After we created our project and enabled the Analytics API, the next step is to actually build the script to fetch the data we need. We will need 2 tabs constantly within a click away.

Query Explorer (to build and validate the queries) Google Apps Script Spreadsheet reference (a very detailed and well written documentation)

In order to query the data from Google Analytics we need the below function. The function takes 5 arguments as an input and returns a report as an output.

function gaGet(tableId, startDate, endDate, metrics, options)

The arguments are:

tableId: Is actually the id of the view we want to query. In Query Explorer, when we choose the appropriate Account — Property — View, the table id is automatically set (format: “ga:xxxxxxx”). startDate: the start date of the query (format: “yyyy-MM-dd” or NdaysAgo). endDate: the end date of the query (format: “yyyy-MM-dd” or NdaysAgo). metrics: a table that holds the metrics of the query (e.g ga:users or ga:sessions) options: an object that contains all the other necessary information (e.g dimensions, filters, segments, sort, e.t.c)

*Note: For a complete reference to these arguments you can check out the documentation here.

function gaGet

The core command from the above script that we should care the most is:

return Analytics.Data.Ga.get(tableId, startDate, endDate, metrics, options);

This is the command that actually query the data from Google Analytics and returns the report. All the other part of the snippet is just for handling any possible error.

Use Case

So in our use case we are going to fetch some core metrics like pageviews, avg. time on page and bounce rate for the top 5 pages of our website and write it on a sheet of our spreadsheet. To do this you need to copy the below script and paste it into the Apps Script Console.

*Note: You need to specify your own values for the name of the sheet (not the Spreadsheet), the table id to query from (you can get that from Query Explorer by selecting Account — Property — View) and the start and end date of the query.

After you pasted the above script and added your own parameters, choose Run > Run function > main. Authenticate if needed and voilà! Your data are available on the spreadsheet. In the first column we see the page and the next three columns are Pageviews, Average Time On Page and Bounce Rate respectively as we declared them in our ‘metrics’ array.

Conclusion

In this little post we’ve seen a simple way to query data from Google Analytics and write it in Google Spreadsheets using Apps Scripts. Of course the power of Apps Scripts is limitless and can do much more than fetch simple data. Get your hands dirty and as you get more and more comfortable to use Apps Script you will realise how much more you can achieve and how many tasks and reports you can automate.

P.S: The guide is updated to match recent changes.