Pulling Google Analytics Data in Sheets with Google Apps Script to Prevent Sampling

29 Jun

You think it's good?

Pulling Google Analytics Data in Sheets with Google Apps Script to Prevent Sampling
Buffer

First Off! To give credit where it’s due, I got this idea (and some of the base code) from one of the Apps Script GDE, Martin Hawksey’s (who is also big in GA) blog post: Automatically updating a Google Sheet with data from Google Analytics for regular reporting

TL;DR What does this script do?

This uses Google Apps Script to pull data from the google analytics core reporting API in batches.

But…how Is different than the GA sheets addon?

skeptical

It’s actually really similar to the Google Analytics sheets addon, this is only used for a specific case.

Let’s say you want a report to update everyday with new data, you could use the sheets addon, set it to auto update, and make the end date cell in the report config tab relative with =TODAY()

BUT every day the data gets pulled, the data set is going to get larger. Since it expands everyday you could hit sample data with complicated queries with lots of data. And who wants sample data? Not I!

The App Script code below can solve that problem!

It pulls the recent data and appends it to the report. So if you have set this to update every day it will only pull the 1 days worth of data and add it to the bottom of the report data. That will reduce the possibility of sampling for queries that might hit the sampling max.

Setting Up the Google Sheets and Google Cloud Dev Console

  1. Create a new Google Sheet

  2. In the new Google Sheet open the script editor (Tools > Script editor…)

  1. If this is your first script you might see a window popup with options to ‘Create projects for’, if so click ‘Close’

  2. In the Script Editor select Resources > Advanced Google services and turn on Google Analytics API.

  1. Go to Google Dev Console and enable the Analytics API

  2. Enable the Analytics API by toggling the on/off

Apps Script Code

Setting up the getGAdata Function

getGAdata(reportName,sheetName,gaid,start,end,dims,mets,sort,opt_filters)

Parameter Name

type

Description

reportName

string

The name of the report that you are pulling

sheetName

string

The name of the sheet that you want the data to dump into

gaid

string

The view ID of the view that you want to pull data from

start

string

Start date for fetching Analytics data. Requests can specify a start date formatted as YYYY-MM-DD, or as a relative date (e.g., today, yesterday, or NdaysAgo where N is a positive integer).

end

string

End date for fetching Analytics data. Request can specify an end date formatted as YYYY-MM-DD, or as a relative date (e.g., today, yesterday, or NdaysAgo where N is a positive integer).

dims

string

A list of comma-separated dimensions for your Analytics data, such as ga:browser,ga:city. Full list & syntax of dimensions

mets

string

A list of comma-separated metrics, such as ga:sessions,ga:bounces. Full list & syntax of metrics

sort

string

A list of comma-separated dimensions and metrics indicating the sorting order and sorting direction for the returned data.

opt_filters

string

Dimension or metric filters that restrict the data returned for your request.

Automated update

  1. Create a trigger, go to Resources > Current Project’s Triggers

  1. Click create a new trigger

  1. Set the pullDataDaily() function to update in whatever time interval you want.

    1. Make sure the time interval matches your date range of the data pull.

That’s it! If you want to to read more about Apps Script check out the documentation for tutorials , examples, and other resources. Also, check out the Google Dev Experts for Apps Script, they blog and share lots of great content on Apps Script.

Hi, thanks for reading! If you liked this post and saw value in it please consider sharing it. There are share buttons at the top.
15138 views
Loading Facebook Comments ...