First Off! To give credit where it’s due, I got this idea (and some of the base code) from one of the Apps Script GDE, Martin Hawksey’s (who is also big in GA) blog post: Automatically updating a Google Sheet with data from Google Analytics for regular reporting
This uses Google Apps Script to pull data from the google analytics core reporting API in batches.
It’s actually really similar to the Google Analytics sheets addon, this is only used for a specific case.
Let’s say you want a report to update everyday with new data, you could use the sheets addon, set it to auto update, and make the end date cell in the report config tab relative with =TODAY()
BUT every day the data gets pulled, the data set is going to get larger. Since it expands everyday you could hit sample data with complicated queries with lots of data. And who wants sample data? Not I!
The App Script code below can solve that problem!
It pulls the recent data and appends it to the report. So if you have set this to update every day it will only pull the 1 days worth of data and add it to the bottom of the report data. That will reduce the possibility of sampling for queries that might hit the sampling max.
Create a new Google Sheet
In the new Google Sheet open the script editor (Tools > Script editor…)
If this is your first script you might see a window popup with options to ‘Create projects for’, if so click ‘Close’
In the Script Editor select Resources > Advanced Google services and turn on Google Analytics API.
Go to Google Dev Console and enable the Analytics API
Enable the Analytics API by toggling the on/off
getGAdata(reportName,sheetName,gaid,start,end,dims,mets,sort,opt_filters)
Parameter Name |
type |
Description |
reportName |
string |
The name of the report that you are pulling |
sheetName |
string |
The name of the sheet that you want the data to dump into |
gaid |
string |
The view ID of the view that you want to pull data from |
start |
string |
Start date for fetching Analytics data. Requests can specify a start date formatted as YYYY-MM-DD, or as a relative date (e.g., today, yesterday, or NdaysAgo where N is a positive integer). |
end |
string |
End date for fetching Analytics data. Request can specify an end date formatted as YYYY-MM-DD, or as a relative date (e.g., today, yesterday, or NdaysAgo where N is a positive integer). |
dims |
string |
A list of comma-separated dimensions for your Analytics data, such as ga:browser,ga:city. Full list & syntax of dimensions |
mets |
string |
A list of comma-separated metrics, such as ga:sessions,ga:bounces. Full list & syntax of metrics |
sort |
string |
A list of comma-separated dimensions and metrics indicating the sorting order and sorting direction for the returned data. |
opt_filters |
string |
Dimension or metric filters that restrict the data returned for your request. |
Create a trigger, go to Resources > Current Project’s Triggers
Click create a new trigger
Set the pullDataDaily() function to update in whatever time interval you want.
Make sure the time interval matches your date range of the data pull.
That’s it! If you want to to read more about Apps Script check out the documentation for tutorials , examples, and other resources. Also, check out the Google Dev Experts for Apps Script, they blog and share lots of great content on Apps Script.