How to import Google Analytics data into Google Spreadsheets in 3 simple steps

Almost everybody that owns a website uses Google Analytics to analyze their visitor’s behaviour. While the Google Analytics web interface is useful, sometimes you just need a little more flexibility with your data by exporting it from Google Analytics into your spreadsheets.

In this post I will show you how you can set up a spreadsheet that allows you to import Google Analytics data with the push of a button and make custom graphs about your visitors like this:

Import Google Analytics data and make custom dashboards in Google Spreadsheets

1. Add the Analytics Google Apps Script

Go to Google Docs and open a new spreadsheet by pressing Create > Spreadsheet.
Don’t worry, you don’t need to be able to program for this to work, just follow these simple steps:

• In the new spreadsheet, click on Tools and then Script Gallery
• Search for “Google Analytics Report”
• Locate the script named “Google Analytics Report Automation (magic)” and press Install
• In the popup that follows press Authorize

2. Activate access to Google Analytics

You just installed a Google Apps Script that is able to import your Google Analytics data into your spreadsheet. For it to work, you need to enable access to your Google Analytics first. For this you need to add a API key that tells the script where it can get your data. Simply do this:

• In the top menu press Tools > Script editor
• Click Resources > Use Google APIs in the new window that has opened
• In the popup that opens, toggle the button after Analytics API to On

Authorizing the Goole Apps Script with you API key

• Press the Google APIs Console link at the bottom
• If you haven’t done already press “Create new project” in the next window
• Click Services on the left and toggle the button behind Google Analytics to On

You have now succesfully authorized the script to access you Google Analytics data!

3. Make a Google Analytics query configuration

Now go back to the spreadsheet and refresh your browser. Wait for a couple of seconds after the page has loaded and notice that a sheet named gaconfig has been added and an extra option in the top menu has been created called Google Analytics.
To get data from Google Analytics, you have to tell the script what data it should import. The gaconfig sheet is the place to do this.

• To add a query configuration press Google Analytics > Create Core Report in the top menu.

An empty configuration has now been added to the gaconfig sheet – don’t worry we only need to fill in a couple of fields. In this case, let’s make a query for the amount of visits and pageviews from Google Analytics for the last 30 days:

• In the first field ids, you specify the Google Analytics property ID you want to query. To get your ID, press Google Anaytics > Get Profile IDs in the top menu
• Press Grant Access to allow the script to run (you only have to do this once). Now press Get Profile IDs again
• Now select the Account, Web Property, and Profile you want to query and copy the id that starts with “ga:<some_number>”

Find you Google Analytics Profile ID

• Now paste that ID into the cell to the right of the ids cell
• Type 30 in the cell to the right of “last-n-days”
• Type ga:visits,ga:pageviews in the cell to the right of “metrics”
• Type ga:date in the cell to the right of “dimensions”
• Give the sheet on which the data will be imported a name in the cell next to “sheet-name”

We’re almost there! Your query configuration should look something like this now:

query1	value1
type
ids	ga:98456354
start-date
end-date
last-n-days	30
metrics	ga:visits,ga:pageviews
dimensions	ga:date
sort
filters
segment
start-index
max-result
sheet-name	Google Analytics Data

Tip: There are a lot of other metrics and dimensions you can make your query with. Also you can segment and filter your data just like you can in Google Analytics. For a full overview of functionality check the site from Google Analytics

Finally, press Google Analytics > Get Data in the top menu and watch the script import Google Analytics data into your spreadsheet!
Once you have closed the popup, you will have a new sheet with the amount of visits and pageviews to your site for the last 30 days. Additionally some extra information is included that tells you when the query was made, how many results there are and whether sampling was applied.

Your imported data from Google Analytics in Google Spreadsheets

That’s it! You have imported data from Google Analytics to Google Spreadsheets. You can now easily create graphs like this:

Import Google Analytics data and make custom dashboards in Google Spreadsheets

Think if all the cool stuff you can do with having your analytics data in Google Docs. As always, I’d love to hear what you have come up with in the comments.

Bonus Step: Automate the Google Analytics import

Using triggers to automate the script

Even though it’s pretty cool that you have set up your spreadsheet to import data by pressing Get Data, it can be very valuable to let your script automatically import data on specific times, or when you open the spreadsheet for example.
Luckily this is very easily done by creating trigger that can run the Get Data command:

• Go to Tools > Script editor again
• Press Resources > Current project's triggers

You can create triggers based on events in your spreadsheet or based on time.
To automatically import data when you open the spreadsheet:

• Press Add a new trigger and select the getData function under Run
• Make sure the two other fields shows from spreadsheet and On open

To automatically import data each day:

• Press Add a new trigger and select the getData function under Run
• Make sure the two other fields shows time-driven and Day timer

An event-driven and a time-driven trigger

Saving these trigger, the spreadsheet will now automatically import your Analytics data every day around midnight and when you open the spreadsheet.
Using these triggers, you can create cool dashboards that are always up to date