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
|yes||The unique table ID of the form ga:XXXX, where XXXX is the Analytics view (profile) ID for which the query will retrieve the data.|
|yes||Start date for fetching Analytics data. Requests can specify a start date formatted as |
|yes||End date for fetching Analytics data. Request can specify an end date formatted as |
|yes||A list of comma-separated metrics, such as |
|no||A list of comma-separated dimensions for your Analytics data, such as |
|no||A list of comma-separated dimensions and metrics indicating the sorting order and sorting direction for the returned data.|
|no||Dimension or metric filters that restrict the data returned for your request. (e.g., |
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