Google Analytics Data on Geckoboard Custom Widgets via Google Docs Spreadsheet

by Bibiano Wenceslao on 16 May 2012 Spreadsheets 30 Comments - Join the Conversation

About a month ago, I came across Bill French of iPadCTO (a really, really cool person by the way) who wrote on the Geckoboard Blog, April last year, about how Google Docs can be used to provide real-time data for Geckoboard custom widgets. With further modifications, the approach really proved its use in monitoring site metrics at a glance through Geckoboard dashboards though back then we had to manually update or “hardcode” the metric values from Google Analytics daily.

Enter Jamie Steven of SEOmoz who wrote on their blog, October last year, about using GA Data Feed API to fetch data from Google Analytics to Google Docs. To quote him: “Realtime Google Analytics data inside a Google Doc—a panacea!” Indeed. AutomateAnalytics.com’s Mikael Thuneberg was the person behind the nifty app script that drives the whole fetching process.

With the two methods now at hand, I thought to myself: hey, how about a combination of both? Aha! Automation.

The Process

I’ll intently skip on the details of the process flow. Everything should be self-explanatory anyways if you’ve gone through the two awesome aforementioned blog posts which I highly recommend that you do before or after reading everything here. Simply put, here’s how things work:

Through the Data Feed API, Google Analytics data are…

Google Analytics Traffic Data

…fetched right inside a Google Docs spreadsheet using a particular app script that runs in the background, and another one that creates a feed URL based on the pulled data that is then used…

Using Google Analytics Data Feed API at Google Docs

…to present specific data sets in custom widgets on a Geckoboard dashboard. Ta daaaaa! Real-time data!

Google Analytics Data on Geckoboard Dashboard

The Tool

Google Analytics Data on Geckoboard Custom Widgets via Google Docs Spreadsheet
(Click to open the spreadsheet in a new tab/window)

Everything’s pretty much understandable once you see the document. Make sure to make a copy of it before proceeding. I used the same Settings sheet Jamie created because it already looks great. Functionality-wise, it has everything I needed. He even made a screencast at the source blog post on how to connect Google Analytics to Google Docs through this sheet. Just make sure you got all the required information correct. As Jamie points out on the 6th step in the Instructions section on the sheet, “Once the area below the Profile ID shows an Auth Token (a very long alphanumeric string) you are ready to access your Google Analytics data…”

Settings Sheet on Google Docs

I’ve also created three-row sets as one GeckoBoard custom widget only supports up to three metric values maximum, particularly the Rag numbers only and the Rag column and numbers widgets. I’ll just discuss further on the details of some fields and a minor portion of the app script that needs to be altered and/or populated manually. By the way, I’m sincerely not well-versed with scripts and the terms involved so I might have misused or mixed up some of them in this post e.g. macros and functions. My apologies in advance for these mistakes.

Metric and Filter

Again, reading the aforesaid posts will really get you a solid foundation in understanding how things work. You’ve probably set everything right in the Settings sheet by now. When using the  geckoData sheet, make sure first that you’ve already configured the End Date, Date Range, and most importantly the Spreadsheet Key (right after making a copy of the original public spreadsheet, you’ll have to use the generated spreadsheet key from your copy) before you proceed with anything else.

Copying the Spreadsheet Key

In my example (an old, unattended Blogger blog by the way, hence the very low traffic values you’ve probably noticed above), I’ve defined visits as a Metric, then paired it with Filters such as ga:medium==organic to name one. For more information on other Metrics and Filters, check out Google’s Dimensions & Metrics Reference page.

Configuring Dimensions, Metrics and Filters - Google Analytics API

Custom Label

This is one segment where you can freely choose how a specific metric should be labeled (see first column below) which gets paired with its corresponding value and shows up on the custom widget in the Geckoboard dashboard.

Using Basic Spreadsheet Functions on Google Docs

Supported Custom Charts & Widgets

Before moving forward, I’d just like to make some things clear. This approach has its own limitations when it comes to the types of custom Geckoboard charts and widgets it supports. There’s actually just four of them but you can always get creative with your data using native Google Docs spreadsheet functions and still be able to present data well visually even when just using the four of those.

The script that generates the XML code also relies on the Widget Type input (case-sensitive) hence it’s important to know what to put in there so you don’t end up wondering what when wrong in the end without knowing that the error started at this stage of the process.

Funnel Chart

On the spreadsheet: Input “Funnel” under Widget Type
On the Geckoboard dashboard: Add widget > Custom Charts > Funnel chart

Funnel Chart

Geck-O-Meter

On the spreadsheet: Input “GeckoMeter” under Widget Type
On the Geckoboard dashboard: Add widget > Custom Charts > Geck-O-Meter

Geck-O-Meter

RAG Column & Numbers

On the spreadsheet: Input “Rag” or “RagPercent” (if your data is in percentage) under Widget Type
On the Geckoboard dashboard: Add widget > Custom Widgets > RAG Column & Numbers

RAG Column & Numbers

RAG Numbers

On the spreadsheet: Input “Rag” or “RagPercent” (if your data is in percentage) under Widget Type
On the Geckoboard dashboard: Add widget > Custom Widgets > RAG Numbers

RAG Numbers

Update: Added new supported custom widgets. Credit goes to Patrick Linthorst for the Pie Chart widget XML-generating script.

Number Only

On the spreadsheet: Input “Number” under Widget Type
On the Geckoboard dashboard: Add widget > Custom Widgets > Number & Secondary Stat

Number Only

Pie Chart

On the spreadsheet: Input “PieChart” under Widget Type
On the Geckoboard dashboard: Add widget > Custom Charts > Pie Chart

Pulling in Values using the getGAdata() Macro Function

This is where Thuneberg’s script works its magic. After setting the Metrics (and Filters if necessary), values should automatically show up here, pulled straight from Google Analytics as shown below. You can also use basic spreadsheet functions to create a new set of values (i.e. percentage) from the ones that are already fetched as shown on the image above.

getGAdata() Function Fetches Data from Google Analytics Into Google Docs

Here’s the whole data fetching script:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
function getGAauthenticationToken(email, password) {
//Fetches GA authentication token, which can then be used to fetch data with the getGAdata function
//Created by Mikael Thuneberg
    try {
        if (typeof email == "undefined") {
            return "Email address missing";
        }
        if (typeof password == "undefined") {
            return "Password missing";
        }
        if (email.length == 0) {
            return "Email address missing";
        }
        if (password.length == 0) {
            return "Password missing";
        }
        password = encodeURIComponent(password);
        var responseStr
        var response = UrlFetchApp.fetch("https://www.google.com/accounts/ClientLogin", {
            method: "post",
            payload: "accountType=GOOGLE&Email=" + email + "&Passwd=" + password + "&service=analytics&Source=Mikael Thuneberg-GA Google Docs functions-1.0"
        });
        responseStr = response.getContentText();
        responseStr = responseStr.slice(responseStr.search("Auth=") + 5, responseStr.length);
        return responseStr;
    } catch (e) {
        if (e.message.indexOf("CaptchaRequired") != -1) {
            return "Complete CAPTCHA at http://www.google.com/accounts/" + e.message.slice(e.message.indexOf("CaptchaUrl=") + 11, e.message.length);
        } else {
            return "Authentication failed (" + e.message + ")";
        }
    }
}

function getGAaccountData(authToken, dataType, includeHeaders, maxRows, startFromRow) {
//Fetches account data for the authenticated user
//Input authentication token produced by the getGAauthenticationToken function
//If dataType parameter is omitted, the functions fetches a list profiles to which the user has access
//By specifying the dataType parameters as "goals", the functions will fetch a list of goals by profile
//By specifying the dataType parameters as "segments", the functions will fetch a list of advanced segments
//Created by Mikael Thuneberg
  if (typeof authToken == "undefined") {
        return "Authentication token missing";
    }
    dataType = (typeof dataType == "undefined") ? "profiles" : dataType;
    maxRows = (typeof maxRows == "undefined") ? 200 : maxRows;
    maxRows = (typeof maxRows == "string") ? 200 : maxRows;
    startFromRow = (typeof startFromRow == "undefined") ? 1 : startFromRow;
    startFromRow = (typeof startFromRow == "string") ? 1 : startFromRow;
    if (authToken.length == 0) {
        return "Authentication token missing";
    }
    if (dataType.length == 0) {
        dataType = "profiles";
    }
    if (authToken.indexOf("Authentication failed") != -1) {
        return "Authentication failed";
    }
    try {
        authToken = authToken.replace(/\n/g, "");
        dataType = dataType.toLowerCase();
        var URL = "https://www.google.com/analytics/feeds/accounts/default?max-results=" + maxRows + "&start-index=" + startFromRow
        var responseStr;
        var response = UrlFetchApp.fetch(URL, {
            method: "get",
            headers: {
                "Authorization": "GoogleLogin auth=" + authToken,
                "GData-Version": "2"
            }
        });
        responseStr = response.getContentText();
        var XMLdoc = Xml.parse(responseStr);
        var lapset2;
        var TempArray = [];
        var RowArray = [];
        var HeaderArray = [];
        if (includeHeaders == true) {
            var rivi = 1;
            if (dataType == "segments") {
                HeaderArray[0] = "Segment ID";
                HeaderArray[1] = "Segment Name";
                HeaderArray[2] = "Segment Definition";
            } else {
                HeaderArray[0] = "Account Name";
                HeaderArray[1] = "Profile Title";
                HeaderArray[2] = "Profile Number";
            }
            TempArray[0] = HeaderArray;
        } else {
            var rivi = 0;
        }
        var sar = 0;
        var lapset;
        var dataFound = false;
        if (dataType == "segments") {
            lapset = XMLdoc.getElement().getElements();
            for (i = 0; i < lapset.length; i++) {
                if (lapset[i].getName().getLocalName() == "segment") {
                    sar = 0;
                    RowArray[0] = lapset[i].getAttribute("id").getValue();
                    RowArray[1] = lapset[i].getAttribute("name").getValue();
                    lapset2 = lapset[i].getElements();
                    for (j = 0; j < lapset2.length; j++) {
                        if (lapset2[j].getName().getLocalName() == "definition") {
                            RowArray[2] = lapset2[j].getText();
                        }
                    }
                    TempArray[rivi] = RowArray;
                    RowArray = [];
                    dataFound = true;
                    rivi++;
                    if (rivi == maxRows) {
                        return TempArray;
                    }
                } else {
                    if (lapset[i].getName().getLocalName() == "entry") {
                        break;
                    }
                }
            }
        } else { // datatype = profiles
            lapset = XMLdoc.getElement().getElements("entry");
            for (i = 0; i < lapset.length; i++) {
                sar = 0;
                lapset2 = lapset[i].getElements();
                for (j = 0; j < lapset2.length; j++) {
                    if (lapset2[j].getName().getLocalName() == "title") {
                        RowArray[1] = " " + lapset2[j].getText();
                        dataFound = true;
                    } else {
                        if (lapset2[j].getName().getLocalName() == "property") {
                            if (lapset2[j].getAttribute("name").getValue() == "ga:accountName") {
                                RowArray[0] = lapset2[j].getAttribute("value").getValue();
                            }
                            if (lapset2[j].getAttribute("name").getValue() == "ga:profileId") {
                                RowArray[2] = lapset2[j].getAttribute("value").getValue();
                                break;
                            }
                        }
                    }
                }
                TempArray[rivi] = RowArray;
                RowArray = [];
                dataFound = true;
                rivi++;
                if (rivi == maxRows) {
                    return TempArray;
                }
            }
        }
        if (dataFound == false) {
            return "No data found";
        }
        return TempArray;
    } catch (e) {
        return "Fetching account data failed (" + e.message + ")";
    }
}

function getGAdata(authToken, profileNumber, metrics, startDate, endDate, filters, dimensions, segment, sort, includeHeaders, maxRows, startFromRow) {
//Fetches data from the GA profile specified, using the authentication token generated by the getGAauthenticationToken function
//For instructions on the parameters, see http://bit.ly/bUYMDs
//Created by Mikael Thuneberg
  try {
        startDate.getYear();
    } catch (e) {
        return "Date Required";
    }
    try {
        endDate.getYear();
    } catch (e) {
        return "Invalid end date";
    }
    try {
        if (typeof authToken == "undefined") {
            return "Authentication token missing";
        }
        if (typeof profileNumber == "undefined") {
            return "Profile number missing";
        }
        if (typeof metrics == "undefined") {
            return "Specify a metric";
        }
        if (profileNumber != parseInt(profileNumber)) {
            return "Invalid profile number";
        }
        filters = (typeof filters == "undefined") ? "" : filters;
        dimensions = (typeof dimensions == "undefined") ? "" : dimensions;
        segment = (typeof segment == "undefined") ? "" : segment;
        maxRows = (typeof maxRows == "undefined") ? 100 : maxRows;
        maxRows = (typeof maxRows == "string") ? 100 : maxRows;
        startFromRow = (typeof startFromRow == "undefined") ? 1 : startFromRow;
        startFromRow = (typeof startFromRow == "string") ? 1 : startFromRow;
        if (authToken.length == 0) {
            return "Authentication token missing";
        }
        if (profileNumber.length == 0) {
            return "Profile number missing";
        }
        if (metrics.length == 0) {
            return "Specify a metric";
        }
        if (authToken.indexOf("Authentication failed") != -1) {
            return "Authentication failed";
        }
        authToken = authToken.replace(/\n/g, "");
        var startDateString
        var endDateString
        var dMonth
        var dDay
        if (startDate.getMonth() + 1 < 10) {
            dMonth = "0" + (startDate.getMonth() + 1);
        } else {
            dMonth = startDate.getMonth() + 1;
        }
        if (startDate.getDate() < 10) {
            dDay = "0" + startDate.getDate();
        } else {
            dDay = startDate.getDate();
        }
        startDateString = startDate.getYear() + "-" + dMonth + "-" + dDay
        if (endDate.getMonth() + 1 < 10) {
            dMonth = "0" + (endDate.getMonth() + 1);
        } else {
            dMonth = endDate.getMonth() + 1;
        }
        if (endDate.getDate() < 10) {
            dDay = "0" + endDate.getDate();
        } else {
            dDay = endDate.getDate();
        }
        endDateString = endDate.getYear() + "-" + dMonth + "-" + dDay
        if (startDateString > endDateString) {
            return "Start date should be before end date";
        }
        var URL = "https://www.google.com/analytics/feeds/data?ids=ga:" + profileNumber + "&start-date=" + startDateString + "&end-date=" + endDateString + "&max-results=" + maxRows + "&start-index=" + startFromRow;
        if (metrics.slice(0, 3) != "ga:") {
            metrics = "ga:" + metrics;
        }
        metrics = metrics.replace(/&/g, "&ga:");
        metrics = metrics.replace(/ga:ga:/g, "ga:");
        metrics = metrics.replace(/&/g, "%2C");
        URL = URL + "&metrics=" + metrics
        if (dimensions.length > 0) {
            if (dimensions.slice(0, 3) != "ga:") {
                dimensions = "ga:" + dimensions;
            }
            dimensions = dimensions.replace(/&/g, "&ga:");
            dimensions = dimensions.replace(/ga:ga:/g, "ga:");
            dimensions = dimensions.replace(/&/g, "%2C");
            URL = URL + "&dimensions=" + dimensions;
        }
        if (filters.length > 0) {
            if (filters.slice(0, 3) != "ga:") {
                filters = "ga:" + filters;
            }
            filters = filters.replace(/,/g, ",ga:");
            filters = filters.replace(/;/g, ";ga:");
            filters = filters.replace(/ga:ga:/g, "ga:");
            filters = encodeURIComponent(filters);
            URL = URL + "&filters=" + filters;
        }
        if (typeof(segment) == "number") {
            segment = "gaid::" + segment;
        }
        if (segment.length > 0) {
            if (segment.indexOf("gaid::") == -1 && segment.indexOf("dynamic::") == -1) {
                if (segment.slice(0, 3) != "ga:") {
                    segment = "ga:" + segment;
                }
                segment = "dynamic::" + segment;
            }
            segment = encodeURIComponent(segment);
            URL = URL + "&segment=" + segment;
        }
        if (sort == true) {
            URL = URL + "&sort=-" + metrics;
        }
    }
    catch (e) {
        return "Fetching data failed (" + e.message + ")";
    }
    try {
        var randnumber = Math.random()*5000;
            Utilities.sleep(randnumber);
            Utilities.sleep(randnumber);
            Utilities.sleep(randnumber);
        var response = UrlFetchApp.fetch(URL, {
            method: "get",
            headers: {
                "Authorization": "GoogleLogin auth=" + authToken,
                "GData-Version": "2"
            }
        });
    } catch (e) {
        if (e.message.indexOf("Timeout") != -1) {
            response = UrlFetchApp.fetch(URL, {
                method: "get",
                headers: {
                    "Authorization": "GoogleLogin auth=" + authToken,
                    "GData-Version": "2"
                }
            });
        } else {
            return "Fetching data failed (" + e.message + ")";
        }
    }
    try {
        var responseStr = response.getContentText();
        var XMLdoc = Xml.parse(responseStr);
        var lapset = XMLdoc.getElement().getElements("entry");
        var lapset2;
        var TempArray = [];
        var RowArray = [];
        var HeaderArray = [];
        if (includeHeaders == true) {
            var rivi = 1;
        } else {
            var rivi = 0;
        }
        var sar = 0;
        var dataFound = false;
        for (i = 0; i < lapset.length; i++) {
            sar = 0;
            lapset2 = lapset[i].getElements();
            for (j = 0; j < lapset2.length; j++) {
                if (lapset2[j].getName().getLocalName() == "dimension") {
                    RowArray[sar] = lapset2[j].getAttribute("value").getValue();
                    if (rivi == 1) {
                        HeaderArray[sar] = lapset2[j].getAttribute("name").getValue();
                    }
                    sar++;
                }
                if (lapset2[j].getName().getLocalName() == "metric") {
                    RowArray[sar] = Number(lapset2[j].getAttribute("value").getValue());
                    if (rivi == 1) {
                        HeaderArray[sar] = lapset2[j].getAttribute("name").getValue();
                    }
                    sar++;
                }
            }
            TempArray[rivi] = RowArray;
            RowArray = [];
            dataFound = true;
            rivi++;
        }
        if (dataFound == false) {
            return 0; //changed from "No data found" to 0 (zero);
        }
        if (includeHeaders == true) {
            TempArray[0] = HeaderArray;
        }
        return TempArray;
    } catch (e) {
        return "Fetching data failed (" + e.message + ")";
    }
}

Now that the script is set, click on Select function on the toolbar and choose geckoDocs.

Selecting the Macro to Run - Script Editor Page

Hit the Run button and wait for the macro function to finish running.

Running the geckoDocs() Macro Function

After the successful run and before you leave the the Script Editor page, I just want you to take note that when you go to Resources on the menu and and select Current script’s triggers, you’ll see that I’ve already set both macro functions to run at a set time. Feel free to modify them as you see fit.

Current Script's Triggers Settings in Google Docs

Now going back to the spreadsheet, you’ll see that the XML and Feed URL sections have been populated already.

Generated XML Scripts and Feed URLs on the Spreadsheet

Below is Bill French’s script that generates the widget-specific XML codes (I’ve added the Number Widget part):

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
// ***
// *** Copyright (c) 2011 - Bill French, iPadCTO, ALL RIGHTS RESERVED
// ***

// ***
// Configure the spreadsheet ID
// ***
var spreadsheetID = SpreadsheetApp.getActiveSpreadsheet().getId();

function geckoDocs()
{
  var sheet = SpreadsheetApp.openById(spreadsheetID).getSheetByName("geckoData");

  // process all the widgets
  for (var i = 12; i <= 17; i++) // var i = [firstDataRow]; i <= [lastDataRow]
  {
    var _i = i.toString();
    var geckoType = "E" + _i;
    var dataRange = "F" + _i;
    var xmlTarget = "G" + _i;
    sheet.getRange(xmlTarget).setValue(genXML(sheet.getRange(geckoType).getValues(),
      sheet.getRange(dataRange).getValues(), sheet));
  }

}

function genXML(_type, _range, _sheet)
{

    // setup the sheet objects
    var _range = _range.toString();

    // test for multi-column non-continguous ranges
    if(_range.indexOf(",") > 0)
    {

      // parse the rages and grab the values
      _range1 = _range.substr(0, _range.indexOf(","));
      _range2 = _range.substr(_range.indexOf(",") + 1);

      // grab the range values
      var range1 = _sheet.getRange(_range1);
      range1 = range1.getValues();
      var range2 = _sheet.getRange(_range2);
      range2 = range2.getValues();

      // merge the non-contiguous range values into one value set

      // establish value array
      var values = new Array();
      for (i = 0; i < 3; ++ i)
    values [i] = new Array();

      // assemble data
      for (var i = 0; i < range1.length; i++)
      {
          values[0 + i][0] = range1[0 + i][0];
          values[0 + i][1] = range2[0 + i][0];
      }

    } else {
      var range = _sheet.getRange(_range);
      var values = range.getValues();
    }

    // establish the XML header
    var xml = "<?xml version='1.0' encoding='UTF-8'?>";
   
    // Rag Widget
    if(_type == "Rag")
    {
      xml = xml + " \
        <root>"
;
            for (var i = 0; i < values.length; i++)
            {
                xml = xml + "<item>"
                xml = xml + "<value>" + values[0 + i][1] + "</value>";
                xml = xml + "<text>" + values[0 + i][0] + "</text>";
                xml = xml + "</item>"
            }
        xml = xml + "</root>";
    }
 
    // RagPercent Widget
    if(_type == "RagPercent")
    {
      xml = xml + " \
        <root>"
;
            for (var i = 0; i < values.length; i++)
            {
                xml = xml + "<item>"
                xml = xml + "<value>" + values[0 + i][1] + "</value>";
                xml = xml + "<text>" + values[0 + i][0] + "</text>";
                xml = xml + "<prefix><![CDATA[%]]></prefix>"
                xml = xml + "</item>"
            }
        xml = xml + "</root>";
    }
   
    // Funnel
    if(_type == "Funnel")
    {
      xml = xml + " \
        <root> \
            <type>standard</type> \
            <percentage>hide</percentage>"

            for (var i = 0; i < values.length; i++)
            {
                xml = xml + "<item>"
                xml = xml + "<value>" + values[0 + i][1] + "</value>";
                xml = xml + "<label><![CDATA[" + values[0 + i][0] + "]]></label>";
                xml = xml + "</item>"
            }
        xml = xml + "</root>";
    }

    // GeckoMeter Widget
    if(_type == "GeckoMeter")
    {
      xml = xml + " \
        <root> \
          <item>"
+ values + "</item> \
          <type>reverse</type> \
          <min> \
            <value>10</value> \
            <text>Min Score</text> \
          </min> \
          <max> \
            <value>100</value> \
            <text>Max Score</text> \
          </max> \
        </root>"
;
    }
 
  // Number Widget --- Added by Bibiano Wenceslao
    if(_type == "Number")
    {
      xml = xml + " \
        <root> \
          <item> \
             <value>"
+ values + "</value> \
             <text></text> \
          </item> \
        </root>"
;
    }
 
  // Pie Chart --- Contributed by Patrick Linthorst
  if(_type == "PieChart")
  {
    xml = xml + " \
    <root> \
    "

    for (var i = 0; i < values.length; i++)
    {
        xml = xml + "<item>"
        xml = xml + "<value>" + values[0 + i][1] + "</value>";
        xml = xml + "<label></label>";
        xml = xml + "</item>"
    }
    xml = xml + "</root>";
  }

    return xml;
}

Just to make sure that the sheet has already been published (or has to be published), on the menu go to File > Publish to the Web and hit the Start Publishing or Republish now button.

Publishing a Google Docs Spreadsheet to the Web

All you’ll have to do now is to use each Feed URL on the proper custom widget or chart that you need to create…

Using the XML Feed URL on a Geckoboard Custom Widget

…and you’ll have yourself a custom Geckoboard Dashboard with data pulled straight from Google Analytics!

Google Analytics Data on Geckoboard Custom Widgets and Charts

Related Links

If you’re encountering any issues regarding the process, you may leave a comment below. Or maybe you just want to drop by and say “hi”. Either way, I’d be happy to respond. (Hurray! My first post!) :D

If you liked this post, you can subscribe to my feed. I’m also on Twitter and if you feel like asking something. I also secretly want to be your friend.

I have a newsletter. No ads. No spam. Just the good stuff. Subscribe below to get started.

You can also connect with me and get quick updates through the following:

Tags:   

This post was last modified on January 16, 2014.

Next Post: How to Create Copies of a Google Docs Collection/Folder, Even Ones Just Shared With You

  • Arvid

    Great tutorial and very useful! I used the default setup to see if I could get the data shown in Geckoboard. I entered my Google ID, Password, Profile ID and ran the scripts as suggested. Yet in Geckoboard I get the notification that there is an ‘invalid profile number’. Any idea what might cause this?

    • https://twitter.com/intent/follow?original_referer=http%3A%2F%2Fbibianowenceslao.com%2F&region=follow_link&screen_name=bibianowency&source=followbutton&variant=2.0 Bibiano Wenceslao

      Hi Arvid,

      First off, thanks for dropping by. I’ve updated the post to make it clearer on how to set up the spreadsheets right.

      To answer your query directly, on the Settings sheet, were you able to generate an Auth Code right after you entered the credentials? If so, then you should have a Settings sheet similar to this setup.

      You may have missed updating the Spreadsheet Key field on the geckoData sheet though I should have made in clearer in my post in the first place. When you make a copy of the original doc, the new doc gets a unique spreadsheet key. That’s why it needs one-time updating as shown in this screenshot.

      Also if things went right this time, don’t forget to re-run the script and publish/re-publish the doc on the first time before using the Feed URLs on Geckoboard.

      Hope that helps. :)

      Cheers!

  • Arvid

    Hi Bibiano,

    That did the trick, I did now update the Spreadsheet Key. Everything is working fine now… Thanks!

    • https://twitter.com/intent/follow?original_referer=http%3A%2F%2Fbibianowenceslao.com%2F&region=follow_link&screen_name=bibianowency&source=followbutton&variant=2.0 Bibiano Wenceslao

      Wow, it’s good to hear that! No problem at all. ;)

  • @Williamdrew57

    Hi Bibiano,

    Great article! Really clever idea. Thanks for making it easy to follow!

    • http://bibianowenceslao.com/ Bibiano Wenceslao

      No problems, sir. Glad it helped you. :)

  • Henry

    Hey, The link provided to the template for the Google Analytics to Google Spreadsheets that was show in video doesn’t seem to work anymore http://xy.cm/vS2wI3

    • http://bibianowenceslao.com/ Bibiano Wenceslao

      Hi Henry,

      I just checked on Jamie’s post (http://goo.gl/GYRj9) and you’re right, the link doesn’t seem to direct to the original spreadsheet anymore. Mine’s (http://goo.gl/5z4hZ) a working copy though but a bit modified.

      Let me know if there’s anything I could help you figure out. Would be glad. :)

      Cheers!

      • Henry

        I’ve used the script in conjunction with my own google spreadsheet that pulls data from a different source. I’m having trouble finding resources on the web that explain what geckoboard widgets are available, so I can figure out what would suit my data and the appropriate changes I need to make to the google script.

        • http://bibianowenceslao.com/ Bibiano Wenceslao

          Hi Henry,

          It indeed appears that the last two links that points out to Geckoboard-related pages I’ve provided then in the Resources section has been closed down for reasons I myself don’t know why they had to. I’ve removed them as they now just point to a 404 page (http://goo.gl/41Kt4) and one that requires the user to be logged in (http://goo.gl/r6eHC) to be able to view it. You might want to check out Geckoboard Help & Support (http://support.geckoboard.com/home) for answers to your questions. The Knowledge Base also contains good information about the widgets and other things you can do in Geckoboard.

          Cheers!

  • Brian

    Hi Bibiano,

    Quick question, I got everything setup in terms of the spreadsheet and its grabbing the data and executing perfectly. However, when I copy the link over into Geckoboard, the widgets don’t seem to be pulling any data. Got any ideas?

    • http://bibianowenceslao.com/ Bibiano Wenceslao

      Hi Brian,

      Seems like you’re having the same problem as Arvid (first commenter of this post). To quote myself:

      You may have missed updating the Spreadsheet Key field on the geckoData sheet though I should have made in clearer in my post in the first place. When you make a copy of the original doc, the new doc gets a unique spreadsheet key. That’s why it needs one-time updating as shown in this screenshot.

      Also if things went right this time, don’t forget to re-run the script and publish/re-publish the doc on the first time before using the Feed URLs on Geckoboard.

      The unique spreadsheet key affects the Feed URL output (it tells GeckoBoard which specific spreadsheet contains your data). Also, you can check if the generated XML script for every particular metric is rendered well (item value and text are OK).

      Hope that helped. Please feel free to let me know on how it turns out.

      Cheers!

      • Brian

        Hi Bibiano,

        I have entered the Spreadsheet Key and I do see data showing up the spreadsheet. However, the Feed URLs aren’t feeding any data to Geckoboard.

        I have tried re-publishing the spreadsheet, but that doesn’t seem like it did anything either.

        Should I just try re-creating the spreadsheet from the beginning?

        • http://bibianowenceslao.com/ Bibiano Wenceslao

          Hi Brian,

          Is this the first time that you’ve encountered the error? Did it work before and just suddenly went off somehow?

          Have you tried an initial run of the geckoDocs script as I pointed out in the instructions on this post? I’ve set the triggers to an interval of 4 hours being the most frequent (you may change this trigger as you see fit) so when starting out I highly suggest you run the script first.

          After doing that, you can then publish the sheet (if it’s a fresh copy) or republish it (just to make sure).

          Did you set up everything right on GeckoBoard when you added the widget e.g. Custom Charts for Funnel (for Funnel chart) & GeckoMeter (for Geck-O-Meter), or Custom Widgets for Rag and RagPercent (both works for RAG Column & Numbers, and RAG Numbers)? I realized I forgot to be more detailed about those as well in this post.

          I followed along my instructions here and re-created a RAG Column & Numbers widget and it appears to be still working fine on my side.

          Hope that helps. Let me know how it turns out. I’ll edit this post to include additional details about the specific Geckoboard widgets supported by this approach.

          Cheers!

  • http://talkweb.eu Bogomil Shopov

    Oh, great tutorial man. I am using my custom (PHP) scripts to retrieve most of the data I need and maybe using GDocs is better :)

    Thanks!
    //Bogo

    • http://bibianowenceslao.com/ Bibiano Wenceslao

      Hi Bogomil,

      Thanks for the kind words. Glad this opened up ideas to you about automating data using gDocs. I just checked out your website. Great, great stuff you’ve created. I’m particularly interested with the browser extensions. How I wish I could code well too. I’ve been signed up to http://www.codecademy.com/ and am learning through the modules when I have some spare time (which I barely have). I’d love to learn more about PHP to get to understand more about WordPress.

      You might want to check out my Resources section through the link below for more gDocs stuff:
      http://bibianowenceslao.com/resources/#rSpreadsheets

      All the best,
      Bibiano

  • http://www.satchelsuk.co.uk/ Berta Lei

    Thanks for sharing this tutorial. I’m never really good at following steps but your pictures helped me a lot. I can’t wait for your next updates.

  • http://www.facebook.com/patricklinthorst Patrick Linthorst

    Hi Bibiano,

    Great article! It’s working fine for me, except for 1 thing. I’d like to see the number of goal completions for goal4 for this entire year, so I use the formula =(MINUS(DATE(2012,01,01),TODAY())*-1)-1 in the Date range cell to get the number of days that have passed this year, and goal4Completions as a metric. However, the number I get is not the correct number for this year. It looks like every result is the same for Daterange>108.

    Is there a fix for this? Or maybe the Google Analytics API does not allow for date ranges above 108 days?

    Greets Patrick

    • http://bibianowenceslao.com/ Bibiano Wenceslao

      Hi Patrick,

      Thanks for checking this out. I recently have no access to the analytics data of a site tracking conversions for more than a hundred days yet so I can’t replicate your process (fetching goal values) at this point. I tried it with the basic values like organic visits though and it seems to update fairly well even with a date range beyond an entire year.

      I’m not sure if this could answer your question but have you confirmed that there already were goal4Completions before the 108 previous days mark? Also, I haven’t heard or read about the GA API limiting data by date ranges.

      Hope that helps.

      All the best,
      Bibiano

      • http://www.facebook.com/patricklinthorst Patrick Linthorst

        Hi,

        It is working now, the profile ID was from the wrong account which had no goal4completions before 108 days ago. I have entered the correct profile ID and it’s working!

        • http://www.facebook.com/patricklinthorst Patrick Linthorst

          I have another question though: is it also possible to display a Number widget? I was able to add a Piechart by adding the following code:

          // pie chart
          if(_type == “PieChart”)
          {
          xml = xml + ”


          for (var i = 0; i < values.length; i++)
          {
          xml = xml + "”
          xml = xml + “” + values[0 + i][1] + “”;
          xml = xml + “”;
          xml = xml + “”
          }
          xml = xml + “”;
          }

          However, I have not figures out how to add a Number (without Secondary stat) widget.

          • http://bibianowenceslao.com/ Bibiano Wenceslao

            That is awesome Pat! I’ll add that to the list of supported custom widgets.

            For the “Number Only” widget, I added the following piece of code:

            if(_type == “Number”)
            {
            xml = xml + ”

            ” + values + ”

            “;
            }

            I’ve already updated the spreadsheet and the info on this post as well.

            Hope that helps.

          • http://www.facebook.com/patricklinthorst Patrick Linthorst

            Cool! Just one remark: the number is showed as a percentage, but it should be just a number (so without the % sign). Is this possible?

          • http://bibianowenceslao.com/ Bibiano Wenceslao

            Hmmm. It doesn’t show the percent sign on my end when I tested it (whole value of 12 as shown in the image on post). Is the value in your cell reference a whole number or in decimal format?

            For more details, you may refer to this Custom Widgets documentation from Geckoboard: http://docs.geckoboard.com/custom-widgets/number.html

          • http://www.facebook.com/patricklinthorst Patrick Linthorst

            Got it fixed now, works fine. Nice!

          • http://www.facebook.com/patricklinthorst Patrick Linthorst

            Do you think it is also possible to change the colours of a chart by adding a and tag? It hasn’t been succesful for me yet.

  • http://twitter.com/radrew radrew

    Wow, very helpful tutorial. One quick question though… Is it possible to make the data real time? I’ve setup the triggers for getGAdata and geckoDocs to run every minute, but the spreadsheet data doesn’t update. Is it possible to have the data refresh regularly?

    • http://bibianowenceslao.com/ Bibiano Wenceslao

      Hi Drew,

      I think gDocs sets limits on the number of times you can auto-re-run scripts in a day or how close the intervals are for the auto-re-run schedules. At times, I’d receive email notifications from them titled “Summary of failures for Google Apps Script: Not found”, telling me that at some point the time-based re-run didn’t trigger.

      Sorry for this delayed reply. I’ve been away from my site for a while due to a lot of projects.

      I hope I made sense. :)

      – Bibiano

  • http://www.facebook.com/patricklinthorst Patrick Linthorst

    Hi Bibiano,

    I’m eperiencing some issues with my spreadsheet. After a while, the value item in the XML feed becomes N/A ( #N/A), an no data is being fetched to Geckoboard. When I manually rerun the GeckoDocs script and republish, it works again. Any thoughts on how to fix this? I tried to play around with the triggers but this didn’t help.

    • http://bibianowenceslao.com/ Bibiano Wenceslao

      Hi Patrick,

      Sorry for this very late reply.It’s been a while since I’ve gotten back into attending my site. For your query, at times I’ve been receiving notifications from gDocs as well about “Summary of failures for Google Apps Script: Not found” as they titled it on their email. I don’t know much about gAnalytics API but they’re probably setting limits for data pulls.

      If you’re seeing data being pulled though and that it’s only the generated XML scripts’ that are messed up, the problem’s likely on the spreadsheet’s part and not on the GA API. Maybe gDocs sets limits for the number of times the geckoDocs script is run hence the email notifications at times?

      I’m having the same problem as well sometimes.

      – Bibiano

UX + Social + Customers + Workflows

Hey! Bibiano here. I write about a range of stuff - user experience, social media, web marketing, CRO, customer support, workflows, productivity hacks and life - and this blog is a repository of my thoughts on all those.

I have a newsletter, but I'm not just hurling everything I write into your inbox. I pick the good ones as soon as they're published (rants and scrawls stay here), plus relevant tips and materials I got from outside sources. No ads. No spam. Subscribe below to get started.

You can also connect with me and get quick updates through the following: