Pagecounter with GA spreadsheets

Not really something usable, just an addendum to my answer to an stackoverflow question.Krzysztof wanted to know:

Am I able to push real time data from google analytics to google spreadsheet via google analytics add-on? (I know that it is possible via Google Analytics API)

To the best of my knowledge this is not possible, you’d need to use the Google Analytics Realtime API.

And not only that, realtime data in a spreadsheet might not even be terribly useful. The data from the realtime reports is not aggregated, you see individual hits. For one thing this might mean you might run out of space in your spreadsheet pretty quickly, since a Google Spreadsheet holds 2 millions rows at maximum. Also not everything displayed in Realtime view becomes part of the finished reports. In the content view a page path will show up for each event and transaction, but the final count will only use hits of the pageview type.  Realtime data will be attributed to a traffic channel and will have view filters applied, but I’m still not sure what anybody want’s with individual hits in a spreadsheet – if you want to collect raw data there are better ways.

However just because it’s fun here is a way, without involving Google Analytics, to capture hits in a Google Spreadsheet and print out a pageview counter based on the number of rows. This is not actually a feasible solution for anything, but might give you ideas to play around with.

The spreadsheet

First you need to set up a spreadsheet. Go to your Google Docs, create a new spreadsheet, give a name to the first worksheet (I used “RawData”, but use whatever you like) and then select the script editor from the “Tools” menu. Replace the contents of the editor window with the following script (replace the ID variable with the id from the url of your spreadsheet, and SHEET_NAME with, well, your sheet’s name):

var ID = "xxxxxxxxxxxxxxxx";
var SHEET_NAME = "RawData";

function doGet(e) {
 return handleResponse(e);
}

function handleResponse(e) {

prefix = e.parameters.prefix || "pageCounter";
 
 try {
 var doc = SpreadsheetApp.openById(ID);
 var sheet = doc.getSheetByName(SHEET_NAME);

var params = {};
 Object.keys(e.parameter).sort().forEach(function(key) {
 params[key] = e.parameter[key];
 });
 
var nextRow = sheet.getLastRow() + 1; // get next row
 var row = [];
 for (i in params) {
 row.push(params[i]);
 }
 sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
 // return jsonp success results
 return ContentService.createTextOutput(
 prefix + '(' + JSON.stringify({
 "result": "success",
 "row": nextRow
 }) + ')')
 .setMimeType(ContentService.MimeType.JAVASCRIPT);
 } catch (e) {

 return ContentService.createTextOutput(
 e.parameters.prefix + '(' + JSON.stringify({
 "result": "error",
 "error": e
 }) + ')')
 .setMimeType(ContentService.MimeType.JAVASCRIPT);
 
 }
}

Now publish the script as web app (you’ll need to authorize this; set app acccess to “everyone, even anonymous”).

The script, when called via it’s web app url, will write all GET parameters to a new row in the spreadsheet and will return the numeric index of the last row (so in effect the number of rows).  The output type is JSONP, so this will return a call to a  function that must be already defined in the page you are calling this from; the function receives the returned object with the row number as parameter.

The HTML

So next you need to set up a basic HTML page:

<html>
<head>
 <title>Pagecounter Test</title>
</head>
<body>

<div id="counter"></div>

 <script>
 function pageCounter(obj) {
 document.getElementById('counter').innerText = (obj.row + " Pageviews so far");
 }

 var head = document.getElementsByTagName("head")[0];
 var js = document.createElement("script");
 var baseurl = "https://script.google.com/macros/s/XXXXXXXXXX/exec?";
 var params = ["path="+document.location.href, "title="+document.title].join('&');
 js.type = "text/javascript";
 js.src = baseurl+params;
 head.appendChild(js);

 </script>
</body>
</html>

This will retrieve the document location and title, add it as parameter to the base url (the url of your published web app) and receive a JSONP request to the already defined pageCounter function in return (JSONP means something like “padded” JSON – the Javascript Notation Object is return as parameter to a function so you may avoid extended parsing or eval).

Thusly called the pageCounter function retrieves the row count from the returned object and writes the number of pageviews to a div in the HTML. You may look at code in action and the result here (at least until the spreadsheet is full), although it really looks a bit anticlimactic:

The parameters you passed in (url and title in this case) will be stored in your spreadsheet:

Google Spreadsheet

Of course you can add more parameters, or fire the pixel more selectively to store only data points of particular interest. As I’ve said before, this is not code that is very useful by itself, but you might get ideas to develop it into something interesting.

Leave a Reply

Your email address will not be published. Required fields are marked *