Create a gist now

Instantly share code, notes, and snippets.

What would you like to do?
Automatically updating a Google Sheet with data from Google Analytics for regular reporting
function fetchMyQuery() {
// our query object
var query = {
"optionalArgs": {
"dimensions": "ga:source",
"filters": "ga:medium==referral",
"sort": "-ga:pageviews",
"max-results": "50"
},
"ids": "ga:82426939",
"metrics": "ga:pageviews,ga:sessionDuration,ga:exits",
"start-date": "7daysAgo",
"end-date": "yesterday"
};
// one line! get GA results
var results = Analytics.Data.Ga.get(query.ids, query['start-date'], query['end-date'], query.metrics, query.optionalArgs);
// get a Sheet object - you can replace the sheet name with any other named sheet in your spreadsheet
var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
outputToSpreadsheet(results, sheet);
}
function outputToSpreadsheet(results, sheet) {
// Print the headers.
var headerNames = [];
for (var i = 0, header; header = results.getColumnHeaders()[i]; ++i) {
headerNames.push(header.getName());
}
sheet.getRange(1, 1, 1, headerNames.length)
.setValues([headerNames]);
// Print the rows of data.
sheet.getRange(2, 1, results.getRows().length, headerNames.length)
.setValues(results.getRows());
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment