Archer is releasing an update to a free tool for the web analytics community. The tool will help you create more actionable reports faster and more efficiently. We’ve updated the Google Docs Spreadsheet GA Data Grabber to be compatible with Google’s new API. It’s a Google Docs spreadsheet that automatically pulls GA data from the new API; we call it Analytics Docs 3.0.
We use Analytics Docs 3.0 internally because it saves us 10 to 15 hours per person per week, and because there are certain analyses that just aren’t effective to do with the standard reporting tool. Since it’s a spreadsheet, Excel power-users will find it really familiar. The tool supports all of Google’s charts and data layouts, making visualization or export into another tool (Excel, Tableau, etc.) almost instant. It’s also cross-platform, making it a great tool for GA users everywhere.
For instance, here’s what hourly activity on our website looks like, broken out by day of the week (we used data from April and May to create this chart – in about 15 minutes total).
This new version:
You’ll need to register for your own GA API Key to use it (the process is well-documented inside the tool itself).
Our update includes many new few features:
We learned a couple of lessons building this, too (developers, we’ve got a few hints for Google Scripts users here!):
OAuth 2.0 requires that the user authorize the request for the web service. We’ve discovered that authorization can’t be completed outside of the Script Editor. Google Documents appears to suppress the Authorize prompt (for security purposes). As a result, the spreadsheet can be clunky the first time, as opening the Script Editor is required to authorize the spreadsheet (complete instructions are included in the document).
Google Documents caches fetchurl requests to avoid pushing daily quotas. We added a timestamp to the requests to ensure that the data is always fresh. The onOpen function sets a cell’s value with the current date and time. A refresh button enables “on-demand” data refreshes by the user – without using the script editor.
When setting up the OAuth code, we discovered a few things. RedirectURI and scope were both unknowns and hard to determine based on currently available resources or documentation that we could find at the time. The RedirectURI should be set to https://docs.google.com, which makes sense since this will be the site that OAuth will be verifying on behalf of. The scope changes based on the Google API service that you are accessing. In the case of Google Analytics, the scope used is https://www.googleapis.com/auth/analytics.readonly. The code below will work for most Google APIs; the scope will change based on the service that you access:
var redirectURI = "https://docs.google.com"; var oAuthConfig = UrlFetchApp.addOAuthService("analytics"); oAuthConfig.setAccessTokenUrl("https://www.google.com/accounts/OAuthGetAccessToken"); oAuthConfig.setRequestTokenUrl("https://www.google.com/accounts/OAuthGetRequestToken?scope=https://www.googleapis.com/auth/analytics.readonly"); oAuthConfig.setAuthorizationUrl("https://www.google.com/accounts/OAuthAuthorizeToken"); oAuthConfig.setConsumerKey(clientId); oAuthConfig.setConsumerSecret(consumerSecret);