Google Analytics data to Google Docs

UPDATE March 2014: We’ve published a new Google Docs add-on that makes it very simple to get your metrics from Google Analytics and other sources: Supermetrics for Google Docs
UPDATE: Due to changes by Google, this no longer works. We have a new version available here.

The functions for importing Google Analytics data to a spreadsheet are now also available for Google Docs! With this solution, you can create automatically updating Analytics reports in a GD spreadsheet – these can contain analyses and visualizations that are not available through the GA user interface. You can combine data from several GA profiles, and merge it with data from other systems, such as internal sales data. And having the reports in Google Docs makes it easy to share them within your company or with clients.
Try it now here!  (If you get a “We’re sorry, your spreadsheet cannot be copied at this time” error, open your Google Docs file list, click Create new-> From template -> Public templates, and search for “Google Analytics Functions Examples”)

You can use the functions following the instructions here (they are for Excel but usage is practically the same) and the parameter reference here. There’s also a nice tutorial made by someone else here.

The Google Docs functions also work on a Mac, as well as any other systems from which Google Docs can be accessed – you can even check the latest stats with your mobile.

There are three ways to get started using the functions in Google Docs:

  1. Do it yourself: Copy the function code from here and paste it to a Google Docs spreadsheet file (open the Script editor from the Tools: Scripts menu, paste the code there, and save).  
  2. A customized solution: Put an email to us at info@supermetrics.com describing the kind of report you’d like to see in Google Docs, and I’ll give you price for creating it.

Using the functions in a Google Docs spreadsheet is almost identical to how they are used in Excel: there are the same three functions (getGAauthenticationToken, getGAaccountData and getGAdata), and they take the same parameters as the Excel versions (with a few minor exceptions, which are explained below). The function code is of course different (I’ve converted it from VBA to Google Apps Script), but for the end user this doesn’t make a difference.

Having the GA functions available in Google Docs opens up some nice possibilities. See for example this chart I created using the functions in Google Docs – it’s embedded here to always show the distribution of visits during the last 30 days.

The few differences between the GD and Excel versions of the functions are that in the GD versions, you can specify the maximum number of rows fetched (‘maxRows’ parameter). It seems that GD currently gives an “all our servers are currently busy” error when trying to parse a large XML response, and this can be avoided by setting a limit on the rows fetched. My experience is that this error starts to appear when the result set is over 600 rows. You can also specify a ‘startFromRow’ parameter, to work around the row limit by combining several instances of the function.

Also in other ways, the GD version is less stable than the Excel one – every now and then the authentication doesn’t work, or fetching data gives the “servers busy” error. These are caused by problems in Google’s end, so unfortunately there isn’t anything we can do currently to fix them – but Google Docs is developing rapidly so I’m sure the stability will improve.

The other difference is that the GD version doesn’t yet support fetching conversion goals with the getGAaccountData function – I just didn’t have the time to implement that yet.

If you get an error complaining about “rate limit exceeded”, this is due to too many functions being updated simultaneosly (the GA API only allows 10 queries per second). You can avoid this by staggering the queries so that they are not all fired at the same second; to do this, go to Tools: Scripts: Script editor, and in the gatGAdata function, above the line which has the UrlFetchApp, insert these two rows:

var randnumber = Math.random()*5000;

Utilities.sleep(randnumber);

With this in place, the queries will wait for a random time of between zero and five seconds before being updated, thus it’s less likely that you would hit Google’s limitation of 10 queries per second. If you still have trouble, you can duplicate the second row, this will cause a wait of between zero and ten seconds (and of course, you can put this line there as many times as needed, if you’re still hitting the limit).

The code for these functions is now also available in the Google Docs script library.

In case you have any questions regarding the usage of this solution, please visit our help forum.

 

65 Responses to “Google Analytics data to Google Docs”

  1. Art Wilbur

    This is amazing. I would love to learn how you embedded the dynamic chart – if I can just wrap my head around it. Great discovery, and great work!

    Reply
  2. Johan Myrberger

    Look really nice.

    At the moment I am not interested in Google Analytics per se, but in the authentication method – are you using OAuth to access GA?

    If so, care to share some details? I’m interested in accessing other sites using OAuth from Google Spreadsheets…

    Reply
  3. Johan Myrberger

    Hi Mikael, thanks for the reply.

    As many services from where it would be interesting to bring data into a Google spreadsheet is using OAuth I do hope there’s a way to do this…

    I haven’t yet seen any such implementation, and as you say – there seems to be some limitations.

    However – I wouldn’t be surprised if anyone finds a way around this… I’ll keep on looking..

    Reply
  4. Mikael Thuneberg

    Yes, I’m sure it’s possible to do this with scripts – just not with custom functions, as they are limited to interacting with the user through inputs in the spreadsheet cells, and thus can’t open the login window necessary for OAuth

    Reply
  5. systemaddict

    Hi. Greats scripts.

    One problem. It seems to me that the columns are pushed one column to the left after the third row.

    E.g.

    Date ga:visits ga:pageviews
    20100511 100 200
    20100611 200 300
    20100711 400

    And so on.

    Reply
  6. Mikael Thuneberg

    Hi Systemaddict,

    I haven’t noticed this kind of behaviour, and no one else has complained about it to me. Does it happen in the example spreadsheet I’m sharing, or in one made by you?

    In Google Docs, array functions really only reside in one cell (the upper left cell of the range) and the other cells have “=CONTINUE(x,r,c)” functions, where x is the address of the array function cell, r is row number of the data to be displayed in that specific cell, and c is the column number for the data in that cell. The problem you’re experiencing may be caused by your CONTINUE functions having somehow gotten mixed up.

    Reply
  7. Systemaddict

    Hi Mikael

    Thanks for reporting back so fast. Strangely, this happened only on one specific account, and the problem has disappered today. I wonder if analytics returned a null value or something. I didn’t change anything in your demo sheet apart from the username / password fields.

    Anyway, it’s gone. I will report back if I see it again.

    Reply
  8. Anonymous

    This is awesome. I’m testing out the complex data query and wondering if there is a way to add more rows to the array.

    Reply
  9. Katya de palma

    Hey Mikeal,so… great work!

    I found it today, and I’m out from the demo version limited.
    But my question is, this grabber works with proxy active, isn’t it??
    Because I’m testing the demo version, and my results are 0.
    thanks a lot

    Katy

    Reply
  10. Anonymous

    is it possible to get the pageviews for a spezific page by the dimension ga:pageTitle

    example:

    =getGAdata(B4;B6;D2;F4;F5;”pageTitle==example”

    Reply
  11. Tomas

    Hello,
    I wanted to ask whether someone experienced discrepancies in the data shown in GA and the GA Data Fetch Tool for Google Doc? For example the income per day shown in the GA Data Fetch Doc is higher/lower by almost 1%. Thanks.

    Reply
  12. Mikael Thuneberg

    Hi Andrew,

    I hope to find time to do it at some point, but right now I’m really busy with all kinds of other projects, and unless someone’s willing to pay for this development, I can’t prioritize it very high.

    Reply
  13. Kutitap

    Sorry for a seemingly stupid question but how do I get the Absolute Unique Visitors and the direct traffic or search engine traffic values? Hope you can give me an answer.

    Reply
  14. Mikael Thuneberg

    There’s a metrics called “visitors” that you should use. To get direct traffic, use the filter “medium==direct”, for organic search use “medium==organic”

    Reply
  15. Justin

    Everything was working, i closed the docs, opened them on a new computer, and now i am getting the error, UrlFetchApp is not defined.

    Reply
  16. Grishamist

    I’m trying your code and get this incorrect data on the fetch data coz’ I’ve compared it to the analytics and it didn’t matched. Can you tell me what’s the problem on this one?

    Reply
  17. Mikael Thuneberg

    If you let me know what you’re querying for, then I may be able to tell you. Optimal would be if you can share the file and give me temporary access to your account.

    Reply
  18. Grishamist

    I think on the code of start and end date perhaps. I’m using this http://code.google.com/apis/analytics/docs/gdata/gdataExplorer.html just to check. The data on this query and on the analytics matched. I can’t give you the data coz’ I’m also having a temporary account. As of the moment, I’m dealing with the start and end date lines, maybe that’s the action item for that code.

    But just to be honest, this is a great work. It maybe a big blast on the market if it’s 100% accurate to fetch data.

    Reply
  19. Mikael Thuneberg

    I think it’s improbable that there would be a bug in the code with the dates – I haven’t noticed any incorrect data, and if there really was a bug, I would assume many people would have notified me of it months ago. If you can send me screenshots by email of the Google docs report and the Analytics report with differing numbers, I can take a look.

    Reply
  20. Tomas

    Hi,
    I am using your GD spreadsheet for a longer time and now I experienced a little problem. I have a function, that is returning a series of keywords, but it seems that the amount of keywords is limited to 100. I dont get more results, but I also dont get any error message.
    Is there any solution for this?
    Thank you.
    Tomas

    Reply
  21. Mikael Thuneberg

    Hi Margie,

    Would it be possible for you to contact me by email? If you can share the document and your GA data temporarily with me then I can investigate.

    Thanks,
    Mikael

    Reply
  22. Margarita

    Sure thing, shooting you an email right now.

    just figured out why the numbers are off-
    if I want the data from october (1-31) the script fetches data from Sept.30th to October 31; so that’s why the data doesn’t match.

    Margie

    Reply
  23. Julio Alonso

    Mikael, first and foremost thanks for the script, it’s awesome. I have been using it for a very long time now with a very complex GA account.

    I have never been able to get unique users info with anyother filter. I get unique users alone fine, but if I want to get them filtered by location or by source it doesn’t work. I was assuming that was normal behaviour, but I just read a comment you made to the contrary. Also today Google is announcing a new GA api that provides unique visitors.

    Any help on this unique users filtered issue greatly appreciated. Thanks

    Reply
  24. j0n0

    These functions are great, thanks! Support for goal conversions would be an awesome addition! I sent you a small donation but I recognize it’s not going to adequately fund this development effort, lol. 😉

    Reply
  25. Tomas

    Hi Mikael,

    I am using your Google Docs Sheet and it works like a charm. But lately I am getting an error message – rateLimitExceeded. After that I have to change the dates and ther error disappears. Is there a way to avoid this error? I do not want to change the dates each time I open my tables.
    Thank you for your answer.

    Reply
  26. Mikael Thuneberg

    Hi Tomas,

    This is because many people are using the script, and Google Analytics sees all these connections originating from the same place (the servers hosting Google Docs), and thus the Analytics API limit of 10 simultaneous queries is often exceeded. I’m working on a way to overcome this.

    Reply
  27. Rimbaud

    Hi Mikael

    Looking at the UrlFetchApp I stumbled on the useIntranet-feature, which let’s you use Googles SDC – http://code.google.com/securedataconnector/. Which, if I understand this correctly – can function as a proxy.

    If one managed to set this up, and thereby resolving external calls via your own router/firewall – wouldn’t this resolve the issue of multiple requests violating the 10 qps limit?

    Whats your take on this?

    Reply
  28. Jens

    Hi Mikael,

    Thanks for a fantastic script. I’ve already had much use of it.

    I’ve got a session token (rather than single-use token) and I’m trying to use Authsub to authenticate. I changed:
    “Authorization”: “GoogleLogin auth=” + authToken
    to
    “Authorization”: “Authsub token=” + authToken
    … and use the session token, but I either get “invalid token” or “internal error” (the “internal error” only happens when I use a valid session token).

    Am I missing something?

    Reply
  29. Johan Myrberger

    As many have said – really great GDocs script!

    A question – when I pull visits and visitors as metrics the number of visitors are always higher than the number of visits. This seems odd to me.

    Do you have any insight? (I guess this is more related to GA than your script, but I guess more people might have noticed).

    Reply
  30. Johan Myrberger

    Ah, found some related details that makes this seem a script issue…

    The problem with visits>vistors happens when I put multiple filters in.

    In my case I have defined the filter as “pagePAth==/xxx/yyy/,pagePath==”/zzz/qqq/” – then I get this mixup (metrics set to “visits&visitors”)

    Reply
  31. Julio Alonso

    I am back from holidays and when trying to get into the file, I get problems authenticating. It will just not recognize the user+password. It happens with any user I try and only happens with this file. I can both access analytics directly or any other google doc just fine.

    Is it just me or someone else is having this problem?

    Reply
  32. Mikael Thuneberg

    Johan: To check whether this is related to my script, can you check with Google’s own API tool (http://code.google.com/apis/analytics/docs/gdata/gdataExplorer.html) using the same filters and metrics? The problem may be that visitors and visits are not metrics that should be fetched per page, you should use pageviews and unique pageviews instead – I’ve seen some strange results with those type of queries too.

    Julio: Perhaps it was just a temporary problem, I can log in fine

    Reply
  33. jim

    getting returned:

    Error: rateLimitExceeded; Insufficent quota to proceed.

    how can i fix this?

    thank you so much.

    Reply
  34. Mikael Thuneberg

    Jim: Do you use the latest version? These errors should be more rare with that. But if you have a lot of queries, they may still happen, as the number of queries running will exceed Google’s limits. You should open the script editor and add this to getGAdata:

    var randnumber = Math.random()*5000;
    Utilities.sleep(randnumber);

    If you still get the errors, increase the number from 5000 – it will be slower but the errors should occur more rarely

    Reply
  35. Gabriel B

    Hello,
    thanks a lot for your work, it is really very useful!!
    I want to use a filter to get only the visits of people who have spent at least 15min on one specific page.
    I have tried “timeonpage>900” and “timeonpage%3E900”, but it doesn’t work.. the data are not filtered at all.

    do you have any idea to fix it?
    thanks a lot!

    Reply
  36. Tim Leighton-Boyce

    Mikael, this is brilliant, thank you.

    But I am having a problem with getting data from an account with lots of profiles.

    I’ve tried adding the two extra ‘sleep’ lines, which seemed to reduce the errors, but not eliminate them. So I’ve tried increasing the 5000 but now I get a different error — Fetching data failed (Specified sleep period exceeds maximum.)

    I wonder if I am doing something wrong?

    Tim

    Reply
  37. Philip Read

    Hi

    I keep getting an error “error:Service timed out: Apps Script” in Google Docs. Any ideas? Help!

    thanks

    Philip

    Reply
  38. Mikael Thuneberg

    Apparently the queries hit a time limit if it takes too long for Analytics to return the data. Unfortunately it’s not possible to extend the time limit in Google Docs like it is in Excel..

    Reply
  39. Tim

    Hi Mikael,

    I’m getting the error “Error: rateLimitExceeded; Insufficient quota to proceed”. I’m getting this error from a single cell (manually calculating one cell in the worksheet). Each cell I try returns the same error. I have looked for the UrlFetchApp line to add the fix you posted but I can’t find it. I’m using the developer ribbon in Office 2010 as there is no Tools > Script > Script Editor menu. Any pointers?

    Thanks for making this available too, been very helpful!

    Reply
  40. Tim

    Sorry Mikael, just realised that 🙁 Thanks for your feedback though, I’ll go and check that out at once.

    Reply
  41. Victor Pereyra

    It’s not working now, after the Google Analytics update it’s showing the following error:

    404. That’s an error.

    The requested URL /analytics/feeds/accounts/ was not found on this server. That’s all we know.

    Reply
  42. Julio Alonso

    Is this now broken with the new Google Analytics API? Doesn’t seem to work today when I came back from holidays.

    BTW the problem I had with Google Apps accounts persisted, I bypassed it using a regular gmail account.

    Reply

Leave a Comment to Philip Read

  • (will not be published)