Last active
March 21, 2022 11:30
-
-
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/
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
// 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