Skip to content

Instantly share code, notes, and snippets.

@ryanpraski
Last active March 21, 2022 11:30
Show Gist options
  • Save ryanpraski/e7488d0800257f175ae2dafbff45db0c to your computer and use it in GitHub Desktop.
Save ryanpraski/e7488d0800257f175ae2dafbff45db0c to your computer and use it in GitHub Desktop.
Google Analytics Real-Time App Script Query- data is written to a Google Sheet then used in a Google Data Studio Dashboard by using the data studio data connector. See the full tutorial here: http://www.ryanpraski.com/google-analytics-real-time-data-studio-dashboard/
// get time stamp of query run
function setTimeStamp(sheetName) {
SpreadsheetApp.getActive().getSheetByName(sheetName)
.getRange('C2').setValue(new Date())
}
// gaGet data
function gaGet(tableId, metrics, options) {
// Apply standard options
options = options || {};
options['max-results'] = options['max-results'] || '10000';
// If errors persist up to 5 times then terminate the program.
for (var i = 0; i < 5; i++) {
try {
return Analytics.Data.Realtime.get(tableId, metrics, options); // 503
} catch (err) {
// https://developers.google.com/analytics/devguides/reporting/core/v3/coreErrors
if (err.message.indexOf('a server error occurred') > -1) {
Logger.log('Backend Error');
// Note: Don't listen to Google's reply and retry request after 2 minutes
Utilities.sleep(2 * 60 * 1000);
} else if (err.message.indexOf('User Rate') > -1) {
Logger.log('Rate Limit Error');
// Exponential Backoff
Utilities.sleep(1000 * Math.pow((i + 1), 2));
} else if (err.message.indexOf('too many concurrent connections') > -1) {
Logger.log('Concurrent Connections Error');
// Exponential Backoff
Utilities.sleep(1000 * Math.pow((i + 1), 2));
} else {
Logger.log(err);
throw err;
}
}
}
throw 'Error. Max retries reached';
}
// rt pages query
function getRtPages(){
// set up the parameters and variables
var sheetName = 'web'; // The name of the sheet (not the Spreadsheet) we want to write the data e.g Sheet1
var tableId = 'ga:94579701'; // The id of the view to query the data from e.g ga:123456
//var startDate = 'yyyy-MM-dd'; // The start date of the query with the appropriate format e.g 2018-04-01 (1 April 2018)
//var endDate = 'yyyy-MM-dd'; // The end date of the query with the appropriate format e.g 2018-04-30 (30 April 2018)
var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
var sheet = spreadsheet.getSheetByName(sheetName);
// set Up the query arguments
var metrics = ['rt:pageviews'];
var options = {
'dimensions': 'rt:pagePath',
//'filters': '',
'sort': '-rt:pageviews',
//'segment': '',
'samplingLevel': 'HIGHER_PRECISION',
'max-results': '100' // To limit the results to 100. Maximum number of results: 10000
}
// fetch the report
var report = gaGet(tableId, metrics, options);
// clear current sheet data
var range = sheet.getRange("A2:D101");
range.clear();
// set timestamp of query run
setTimeStamp(sheetName)
var data = report.rows;
// get the range to write and write the results
try {
var writeRange = sheet.getRange(2, 1, data.length, data[0].length) // Read reference for getRange arguments
writeRange.setValues(data);
}
catch(err) {
logger.log(err);
}
}
// test ga query to return json to log
function test(){
var x = Analytics.Data.Realtime.get('ga:94579701', 'rt:pageviews')
Logger.log(x)
}
//adapted from this script thanks!: https://gist.github.com/dcvogi/8b31358becaa717a5e8a97d8e4a26df7
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment