do something useful with Apps Script in 5 minutes

5. Analytics pageviews to a sheetBruce McPhersonwww.mcpher.com

Snippet objectives● Use the lessons learned in ‘using a spreadsheet as a

database’ ● Use the lessons learned in ‘getting analytics properties

to a sheet’, and get the analytics id you want to analyze● Flattens analytics data and writes it all to a sheet

Libraries used

● database abstraction● driver sheet● useful stuff

Add libraries to script

● create a spreadsheet● get its id● create a script● Open resources● Add references to librariesMrckbr9_w7PCphJtOzhzA_Cz3TLx7pV4j

MHfCjPQlweartW45xYs6hFai_d-phDA33

Mcbr-v4SsYKJP7JMohttAZyz3TLx7pV4j

Add analytics service

in advanced services, enable analytics

Enable analytics on cloud console

layout what you are going to dofunction myFunction() {

// this is the propertyID we want to get the analytics for - it is the field named ‘id’ if you did the previous tutorial

// open spreadsheet as database

// get the analytics for all time

// write to the sheet after deleting current contents

}

write function to get pageviewsFirst step - Create a function to get the pageviews from the Analytics service. The analytics service is rate limited, so we have to handle retries using exponential backoff. We’re only doing one call so its probably not needed, but no harm to get in the habit and create a reusable function.

/**

* get pageviews

* @param {string} propertyId the analytics property id

* @param {Date} start start date

* @param {Date} finish end date

* @return {object} Analytics response data

**/

function pageViews (propertyId, start , finish) {

return cUseful.rateLimitExpBackoff(function () {

return Analytics.Data.Ga.get('ga:' + propertyId , gaDate(start), gaDate(finish), 'ga:pageViews', {dimensions:'ga:pagePath'} );

});

}

write function to format dateThe analytics service expects a specific date format for date range filtering. Create a reusable function for that.

/**

* convert date format for analytics filtering

* @param {Date} a date

* @return {string} a formatted date

*/

function gaDate (dt) {

return Utilities.formatDate(dt, Session.getScriptTimeZone(), 'yyyy-MM-dd');

}

write wrapper to get data This fills in some default dates and gets the analytics data. However the data is not yet in a very usable format.

/**

* get analytics data

* @param {string} propertyId the analytics property id

* @param {Date} optStart start date

* @param {Date} optEnd end date

* @return {Array.objects} array of objects showing url and pageviews

**/

function getAnalytics(propertyId,optStart,optEnd) {

// get data for this property filtered by optional dates

var data = pageViews (propertyId, optStart || new Date(2010,0 ,1 ), optEnd || new Date());

}

clean analytics dataClean up property names and convert to a usable JavaScript array

function getAnalytics(propertyId,optStart,optEnd) {

// get data for this property filtered by optional dates

var data = pageViews (propertyId, optStart || new Date(2010,0 ,1 ), optEnd || new Date());

// clean up into a json object

return data.rows.map ( function (row) {

var i =0;

return row.reduce(function (p,c) {

p[data.columnHeaders[i++]['name'].replace("ga:","")] = c;

return p;

},{});

})

}

sort analytics dataMay as well also sort it by URL

function getAnalytics(propertyId,optStart,optEnd) {

// get data for this property filtered by optional dates

var data = pageViews (propertyId, optStart || new Date(2010,0 ,1 ), optEnd || new Date());

// clean up into a json object

return data.rows.map ( function (row) {

var i =0;

return row.reduce(function (p,c) {

p[data.columnHeaders[i++]['name'].replace("ga:","")] = c;

return p;

},{});

})

.sort ( function (a,b) {

return (a.pagePath > b.pagePath ? 1 : (a.pagePath === b.pagePath ? 0 : -1)) ;

});

}

write everything to a sheet

// write to the sheet after deleting current contents

var result = sheetHandle.remove();

if (result.handleCode < 0 ) throw JSON.stringify(result);

var result = sheetHandle.save(data);

if (result.handleCode < 0 ) throw JSON.stringify(result);

We’ve cleaned up the data so it can just be written as is

Here’s the whole thingfunction myFunction() {

// this is the propertyID we want to get the analytics for

var propertyId ='4xxx7';

// open spreadsheet as database

var sheetHandle = new cDbAbstraction.DbAbstraction (cDriverSheet, {

siloid:'analytics' + propertyId,

dbid:'19tZRW5CxA4V0kjJX8yAXAGGjzvVZ1433vz-NmBIBt7U'

});

if (!sheetHandle.isHappy()) throw 'unable to open sheet';

// get the analytics

var data = getAnalytics (propertyId, new Date(2010,0,1), new Date());

// write to the sheet after deleting current contents

var result = sheetHandle.remove();

if (result.handleCode < 0 ) throw JSON.stringify(result);

var result = sheetHandle.save(data);

if (result.handleCode < 0 ) throw JSON.stringify(result);

}

take a look at the sheetYou’ll find a row for each url in your site

Further homework

● The problem is that Analytics treats each url variation as different URL - see below. How about modifying to combine ‘like’ urls

Follow up materials

Take a copy of this scriptTake a copy of these slidesJoin me on G+, or the G+ communityMore on desktop liberationMore on database abstractionMore on Analytics instrumentationMore 5 minute things