Takes 5 mins
We are well aware of the 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 your project.
- Create a new Google Spreadsheet.
- Click on Tools and from the drop-down menu choose Script Editor.
- The above operation will open a Script Editor in a new tab.
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.
6. You will be prompted for New Project Name. Give a desired name and then pop-up for Advanced Google Services will be listed.
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 that 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 Google Data Studio custom visualization to build more enhanced dashboards.
- Automate script execution by setting a simple time-based trigger.
- Set up a 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 the 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 queries in different ways to fetch data in sorting or in a particular range order just the way you wish to explore.
9 Comments. Leave new
Great article, but you should put the code snippet in a way where we could copy and paste
Code can’t be copy pasted. How do you add a date filter? Don’t wanna load all data since the beginning of time.
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.
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
Hi Thomas,
There is a “}” missing in the code which is provided in the code. Please recheck the code shared with the blog during implementing it.
Hi Sameer
Thanks for the code snippet. This has surely helped.
However, I would want to restrict the number of results I draw from the function Analytics.Data.Reatime.get, along with sorting all the results on a metrics and drawing only the top 20.
So, for instance I want to retrieve top 20 active pages on activeusers metric, for which I used
var ana = Analytics.Data.Realtime.get(‘GA-viewID’, ‘rt:activeUsers’, {“dimensions” : “rt:pagePath”}, {“sort” : “-rt:activeUsers”}, {“max-results” : 20})
But the script throws an error prompting to use only the allowed number of argu,ents with the said function, 2-3 arguments. I checked the syntax, it does allow 5 arguments.
Could you please help me here?
Read more at: https://tatvicstag.wpengine.com/blog/google-analytics-real-time-data-in-google-sheet/?utm_source=copytext&utm_medium=text&utm_campaign=textshare
Hi Sameer
Thanks for the code snippet, this surely helps.
However, I wish to limit the number of results I extract from GA and put them in google sheets, say upto 20. So, the idea is to draw the top 20 active pages (on activeUsers dimenisons).
I tried adding additional arguments to the function Analytics.Data.Realtime.get – sort and max-results.
But, it throws me an error prompting to use only 2-3 arguments with this function.
Could you please help me here?
Read more at: https://tatvicstag.wpengine.com/blog/google-analytics-real-time-data-in-google-sheet/?utm_source=copytext&utm_medium=text&utm_campaign=textshare
Hi Sameer
Thanks for the code snippet. This has surely helped.
However, I would want to restrict the number of results I draw from the function Analytics.Data.Reatime.get, along with sorting all the results on a metrics and drawing only the top 20.
So, for instance I want to retrieve top 20 active pages on activeusers metric, for which I used
var ana = Analytics.Data.Realtime.get(‘GA-viewID’, ‘rt:activeUsers’, {“dimensions” : “rt:pagePath”}, {“sort” : “-rt:activeUsers”}, {“max-results” : 20})
But the script throws an error prompting to use only the allowed number of argu,ents with the said function, 2-3 arguments. I checked the syntax, it does allow 5 arguments.
Could you please help me here?
Hi Sameer
Thanks for the code snippet. This has surely helped.
However, I would want to restrict the number of results I draw from the function Analytics.Data.Reatime.get, along with sorting all the results on a metrics and drawing only the top 20.
So, for instance I want to retrieve top 20 active pages on activeusers metric, for which I used
var ana = Analytics.Data.Realtime.get(‘GA-viewID’, ‘rt:activeUsers’, {“dimensions” : “rt:pagePath”}, {“sort” : “-rt:activeUsers”}, {“max-results” : 20})
But the script throws an error prompting to use only the allowed number of arguments with the said function, 2-3 arguments. I checked the syntax, it does allow 5 arguments.
Could you please help me here?
Read more at: https://tatvicstag.wpengine.com/blog/google-analytics-real-time-data-in-google-sheet/?utm_source=copytext&utm_medium=text&utm_campaign=textshare