Can I Automate Google Analytics export into a MisoData?

Google Apps Script is a scripting language based on JavaScript that lets you do new and cool things with Google Apps like Docs, Sheets, and Forms. There's nothing to install — Google provide a code editor right in your browser, and your scripts run on Google's servers.


Google Analytics Report Automation (Magic Script) is an integration that reduces the work required to put Google Analytics data into any Apps Script supported product, such as Google Docs, Sites, or Spreadsheets.

Important: The new Google spreadsheets have replaced the script gallery with add-ons however, it’s missing an important factor: the ability to trigger API calls on schedule. In order to regain the trigger options, we must use the former Magic Script. Since, you won’t be able to install the Magic script from Google Docs directly, you’ll need to copy paste the original script which can be found here into your script editor. 

The first step is to create a new spreadsheet in Google Docs and give it a name (You'll need to be signed in to your Google account.)

Then 
go to Tools –> Script Editor   If this is the first time you've been to script.google.com, you'll be redirected to a page that introduces Apps Script. Click Start Scripting to proceed to the script editor.

Copy and paste the entirety of Google Analytics Report Automation Script into Code.gs, give the project a name and save it



Go to Resources –> Advanced Google Services and turn on the Google Analytics API 



Click on Google Developers Console link and turn on the Analytics API for your new project 



Go back and refresh your document and a Google Analytics tab should appear. From here, go to Google Analytics –> Create Core Report



This will place a bunch of text into your spreadsheet. Each row is a component of the query. You can define the start and end date for your query (or skip that and list number of days in “last-n-days”), and then you can define your metrics, dimensions, and more. I’ll show you how.

Open the Query Explorer and select the metrics you’d like to analyze then copy and paste the metrics, dimensions, etc. into gaconfig sheet.

Query Parameters Summary

NameValueRequiredSummary
idsstringyesThe unique table ID of the form ga:XXXX, where XXXX is the Analytics view (profile) ID for which the query will retrieve the data.
start-datestringyesStart date for fetching Analytics data. Requests can specify a start date formatted as YYYY-MM-DD, or as a relative date (e.g., todayyesterday, or NdaysAgo where N is a positive integer).
end-datestringyesEnd date for fetching Analytics data. Request can specify an end date formatted as YYYY-MM-DD, or as a relative date (e.g., todayyesterday, or NdaysAgo where N is a positive integer).
metricsstringyesA list of comma-separated metrics, such as ga:sessions,ga:users,ga:pageviews
dimensionsstringnoA list of comma-separated dimensions for your Analytics data, such as ga:browser,ga:city.
sortstringnoA list of comma-separated dimensions and metrics indicating the sorting order and sorting direction for the returned data.
filtersstringnoDimension or metric filters that restrict the data returned for your request.  (e.g., ga:pagePath=~landing-page for any page that contained ”landing-page“ in URL)
If you don’t know the GA id, you can use Google Analytics –> Find Profile/ids option

Once you have your queries, go to Google Analytics –> Run Report

If result sheet is not formatted at your convenience, you can easily replicate data and copy it from one sheet to another by entering the sheet name and an exclamation mark before the cell being copied. Here are an examples: =results!A1

Google Spreadsheet formulas allow also to clean up your data


In order to enable triggers, go back to the Script Editor, go to Resources –> Current project’s triggers, then add a getData Time-driven trigger. Here I set the API calls to execute between 5 and 6am. 

Following tutorial will show you how to create a MisoData app based on this spreadsheet
http://faq.netvibes.com/knowledgebase/articles/370100

Others resources:
https://developers.google.com/analytics/solutions/report-automation-magic
https://www.distilled.net/blog/seo/google-analytics-api-made-easy-google-docs-magic-tutorial
https://developers.google.com/analytics/solutions/google-analytics-spreadsheet-add-on

Feedback and Knowledge Base