Google Analytics

How to connect Google Analytics to google sheets

Google Analytics is the world’s most widely used digital analytics tool. It provides in-depth and accurate data about your website traffic, audience demographics, customer conversion rates, and much more. Google Analytics can help you understand how people engage with your site or app so that you can make informed decisions on content strategy, marketing campaign effectiveness, A/B testing and more.

Google Analytics
google sheet

Follow this step-by-step explanation to learn how to automatically retrieve data from your Google Analytics account and import it into Google Sheets. You’ll be building a script right into the Google Apps Script platform via Google Sheets’ Script Editor - no third-party software required.

If you don’t want to manage the script yourself, check out check out our list of Google Sheets Add-Ons that can do it for you.

Step 1

Create a Google Sheets script to import from Google Analytics

1. Create a new Google Sheet by typing sheet.new in your Chrome toolbar

2. Name your spreadsheet

Double click on the default name “Untitled spreadsheet” on the top left-hand corner and replace it with the name you want. For instance, we chose to name it “Test” here.

3. Click on Extensions > Apps Script; this will open a new tab with a url starting with https://script.google.com/.

4. Name your Google script

To name your script, click on the default name “Untitled project” on the top left-hand corner. A little window will pop up where you can write the name you choose. For instance, we chose to name it like our spreadsheet: Test. Then, click on the Rename button.

5. Get familiar with the API docs from Google Analytics

Review the API documentation from Google Analytics to see what data you might be able to pull into Google Sheets. You can use tools from Google Analytics like an API explorer or Postman collection to build a sense for what data you have access to. You’ll be referencing this documentation as you continue to write your code below.

6. Write code to write the header column of the data you picked into your spreadsheet: “updateData”

You can hardcode this. Let’s say you decided you’re going to make a sheet of all the users from the API, with columns Email and Name.

function updateData() {
   const HEADERS = ['Name', 'Email']
   const spreadsheet = SpreadsheetApp.getActive();
   const sheet = spreadsheet.getSheetByName('Sheet1');
   sheet.clear();
   sheet.appendRow(HEADERS);
}

You can run this code by clicking Save and then Run.

You’ll be required to Review Permissions to continue.

Look back at your spreadsheet: it’ll have your new header.

7. Write authentication code to start hitting the API: “readData"

Find the section on authentication in the API documentation. Generally, you’ll want to reference JavaScript or Node.js documentation: Apps Script code is written in JavaScript.

Write some code that uses a personal access token or performs an OAuth authentication process. You’ll probably need to create a personal access token on your Google Analytics account first.

Write this code as a new function (below your updateData function).

EXAMPLE

function readData() {
   const PERSONAL_ACCESS_TOKEN = "94e3c3fb-9570-48d2-bd44-028100b97879";
   const API_URL = "https://app.asana.com/api/1.0";
   const DATA_ENDPOINT = "/users?opt_fields=name,email&workspace=38823";
   const response = UrlFetchApp.fetch(API_URL + DATA_ENDPOINT, {
       headers: {
           "Authorization": "Bearer " + PERSONAL_ACCESS_TOKEN
       }
   });
   const content = JSON.parse(response.getContentText());
   console.log(content);
}

You can Save and run the readData code to make sure you’re getting the output you expect.

8. Connect “readData” to “updateData”

You want to create a new row of data for every result you get from “readData”.

EXAMPLE

function readData() {
   const PERSONAL_ACCESS_TOKEN = "94e3c3fb-9570-48d2-bd44-028100b97879";
   const API_URL = "https://app.asana.com/api/1.0";
   const DATA_ENDPOINT = "/users?opt_fields=name,email&workspace=38823";
   const response = UrlFetchApp.fetch(API_URL + DATA_ENDPOINT, {
       headers: {
           "Authorization": "Bearer " + PERSONAL_ACCESS_TOKEN
       }
   });
   const content = JSON.parse(response.getContentText());
   console.log(content);
}

function updateData() {
   const HEADERS = ['Name', 'Email']
   const spreadsheet = SpreadsheetApp.getActive();
   const sheet = spreadsheet.getSheetByName('Sheet1');
   sheet.clear();
   sheet.appendRow(HEADERS);
   const content = readData();
   const users = content.data;
   console.log(users);
   users.forEach(user => {
       const name = user.name;
       const email = user.email;
       sheet.appendRow([name, email]);
   })
}

9. Run your updateData function again

10. Check your sheet to view the updated data

If it didn’t work, try using console.log() statements to debug your code.

Don’t want to figure out writing your own code?

Use Actiondesk to pull live data from Google Analytics into a spreadsheet that your team can use to build reports.

Step 2

Run a Google Sheets script automatically every minute to retrieve Google Analytics data

Now we’ll show you the code to fetch and refresh your data every minute. Each time, it clears the existing content in the chosen sheet and replaces it with the database data while keeping the format you gave to the table in Google Sheets.

Go into Triggers and Add Trigger

Set it to run updateData, on Head, Time-driven, Minutes timer, Every minute. Click Save.

Now watch it run!

You can delete some data from spreadsheet. Wait one minute - and it will refill again.

That’s it! Now, you will import and automatically sync your data between Google Analytics and Google Sheets.

But isn’t there an easier way to do this?

You could use an already-built Google Sheets Add-On

But if you’re adding third party software to your spreadsheet anyway, there’s a better way

The reason you want to push Google Analytics data to Google Sheets is probably that you want to make this data accessible to team members who are comfortable in a spreadsheet

If that's the case, you should try out Actiondesk. Actiondesk is the easiest way for startup teams to make better decisions with their data. You can access your company key data without code or writing SQL, you'll just need basic spreadsheet skills.

Actiondesk looks like a spreadsheet and works like a spreadsheet. Users used to Google Sheets and Excel will be able to learn Actiondesk very quickly.

While writing a script or using a Google Sheets Add-On can be a good one-off solution, they're usually not durable. The overwhelming feedback we got from the market is that these solutions are not reliable and you always have to debug them yourself.

If you're looking for a more solid solution, check out Actiondesk, you can try it for free.

You can even easily get started with one of our many Actiondesk Google Analytics templates.