Google Analytics Real Time Data in Google Sheet

Google Analytics Real Time Data in Google Sheet

 

We are well aware of a fact that Google Analytics 360 has real-time data available. But not everyone is aware that if you want to export this data, Google Analytics 360 does not allow its users to export all rows. The maximum number of rows that you can export is limited to only the top 10 values of the dimension that you’re looking at.

Google Analytics has also provided Real Time Reporting API which you can use to pull all the data, and if you are a developer, you can do so with any scripting language and create a dashboard. But, in this blog, we are going to see how you can pull all your data into a single Google Sheet with Google Apps Script with minimal effort. One of the biggest advantages of working with Apps Script is that you are already in a Google Authenticated environment. So, you need not worry about the authentication handshake since all the users will be authenticated by Google itself. This means you can access Real Time data with just a few lines of code. There are only a couple of configurations that you first need to achieve and that’s it, you are done.

Setting up you project.

  1. Create a new Google Spreadsheet.
  2. Click on Tools and from the drop down menu choose Script Editor.
  3. The above operation will open a Script Editor in new tab.

 

Script Edior

4. Our task is to fetch the Google Analytics real-time data for which we have to enable Analytics API.

5. To enable Analytics API, click on Resources -> Advanced Google Services.

Adv.Google Services

6. You will be prompted for New Project Name. Give a desired name and then pop up for Advanced Google Services will be listed.

Name

7. Choose Google Analytics API and enable it.

8. You need to enable this API also in your Google Developers Console.

9. Click on Google API Console.

10. Click on ENABLE APIS AND SERVICES.

11. Search for Analytics API and enable it.

12. Here we shall pull dimensions Source & Medium, and Active user as Metric. Go back to your Script Editor and use the below snippet of code with a couple of changes as listed below:

a. Change your Google Analytics View ID

b. Change your Google Spreadsheet ID

Code Snippet:

 

13. Now, click on Run to run the script check in your Spreadsheet your data would be populated.

Below are reference documents which will help you to pull different dimensions:

 

Benefits of using Apps Script for fetching Google Analytics Real Time Data

  • No need to write code for user authentication as the same will be handled by Google.
  • You can start taking decisions in the meantime you have access to real time data.
  • Utilize this as a data source in Data Studio to build more enhanced dashboards.
  • Automate script execution by setting a simple time based trigger.
  • Set up custom mail notification with App Script to get notified if something is found wrong with the existing data.

Note: Do check the quota limits of Real Time API and set trigger to refresh data accordingly.

Concluding Thoughts

We are now familiar with using Real Time API with App Script to fetch real time data from Google Analytics. But this is not just an end yet. You can also build more enhanced dashboards or even a product with this script. You can run API query in different ways to fetch data in sorting or in a particular range order just the way you wish to explore.

The following two tabs change content below.
Sameer Sanghavi

Sameer Sanghavi

Sameer is a Partner & VP of Customer Development at Tatvic Analytics, heading the operations for the company in North America. He has been working across a global footprint, in creating strategies & building a synergistic portfolio of businesses for the firm, whose philosophy is to help enterprises make better data-driven decisions. Sameer’s background has been in running successful technology businesses and leading large initiatives.
,
Previous Post
How to Get Rid of (not set) in Landing Pages of Your Google Analytics Report
Next Post
Google Analytics Setting variable in Google Tag Manager: One for all, All for one

5 Comments. Leave new

  • Great article, but you should put the code snippet in a way where we could copy and paste

    Reply
  • Code can’t be copy pasted. How do you add a date filter? Don’t wanna load all data since the beginning of time.

    Reply
    • Hi Charlotte,

      Thank you for writing for us. Here we are extracting real time report data. So, it will show Last 30 minutes data only, which is available in real time reports of Google Analytics. We don’t need to use date filter for this data extraction.

      Reply
  • hi rahul, for me the code is not working…
    for(var i = 0;<ana.rows.length; i++){
    sheet.appendRow([ana.rows[i][0],ana.rows[i][1],ana.rows[i][2]]);

    is the picture missing a closing "}" for ending the for function? but even with the closing bracket I always get a "Syntax error. (line 22, file "Code")" error message. any idea why?

    any tipp or hint would be appreciated!!!!!
    cheers tomek

    Reply

Leave a Reply

Your email address will not be published.

Fill out this field
Fill out this field
Please enter a valid email address.
You need to agree with the terms to proceed

Menu