I'm already a developer

How to automate Analytics reports with Google Spreadsheet and GoogleAppsScript

I manage data on Analytics on Google Spreadsheet. This time I tried to automate that work with GoogleAppsScript.

When I took over reporting work, it seems too troublesome. open analytics each time, select a period, copy and paste the result… This is daily, weekly, monthly,,,

So I decided to automate it using GoogleAppsScript.

How to automate this reporting work

There are some ways

  • Using add-on
  • Using GoogleAppsScript

I experienced to use GoogleAppsScript, So I chose the later.

A script for Google Analytics API

First, I enable GoogleAnalyticsAPI on script editor. Next, I wrote codes.

In the above code, if you specify path, we get the whole result and the results for each path and set the data in the spreadsheet.

A script for date

Since I often dealt with dates, I summarized the processing.

Execute by daily / weekly / monthly

After that, I wrote a script to execute by daily / weekly / monthly using the above method.

This is a script that is executed weekly, So I made something like this on a daily / monthly basis.

Trigger settings

We can set trigger for each script on GoogleAppsScript.

  • Run at 10 o’clock every day
  • Run at 10 o’clock every Monday
  • Run at 10 o’clock on the 1st of every month

https://gist.github.com/yukihirai0505