Google Sheets Archives - ryanpraski.com

February 18, 2019

Google Analytics Real-Time Data Studio Dashboard

This tutorial shows you how to create a real-time Google Analytics Data Studio dashboard. The real-time Google Analytics data is fed to Google Data Studio via a Google Sheet. The Google Sheet uses Google App Script to query the Google Analytics real-time API. The Google Data Studio dashboard is automatically updated using a Chrome browser extension.

How it Works Flow Chart

Google Analytics Real-Time API > Google App Script > Google Sheet > Google Data Studio Sheets Connector > Google Data Studio Dashboard > Refresh Automatically using Data Studio Auto Refresh Chrome Extension

September 22, 2016

Google Analytics Cost Data Import from Google Sheets – Automated

This tutorial shows how to import Google Analytics cost data automatically from Google Sheets with the click of a button. Here is the sample sheet. Go to the File – Make a Copy to edit the sheet in your Google Drive.

January 11, 2016

Google Sheets Remove Empty Columns & Rows Automatically

This is a tutorial on how to remove empty columns and rows in Google Sheets automatically with the click of a button. If you work with lots of data in Google Docs, this will help you stay under the 2 million cell limit in Google Sheets by removing unused cells. Here is the sample sheet. Go to the File – Make a Copy to edit the sheet.

December 16, 2015

Pull More than 10k rows Unsampled using Google Analytics Sheets Add-on

How to pull a Google Analytics report with more than 10,000 rows?
How to get around Google Analytics Sampling Limitations?

Sample Google Analytics Sheet used in this tutorial (file make a copy to edit)

Google Analytics Sheet Add-on 119,421 rows of data

How do you pull a Google Analytics report with more than 10,000 rows? How do you get around Google Analytics Sampling Limitations? These were the most common questions I was asked after my recent Google Analytics reporting API Python tutorial. This new tutorial will show you how to export more than 10,000 rows using the Google Analytics Spreadsheet Add-on and how avoid the sampling limitations of Google Analytics. I also have another post in the works on how to use Python and the Google Analytics API to avoid sampling and pull even more data. Keep an eye out for the new post!

June 12, 2015

Format Percent Change Red & Green- Excel & Google Sheets for Digital Analytics: Tips & Tricks

If an increase is good and a decrease is bad format percent change like this
[Green]▲ 0.00%;[Red]▼ -0.00%

If an increase is bad and a decrease is good format percent change like this
[Red]▲ 0.00%;[Green]▼ -0.00%

Download Excel Sample

Google Sheet Sample

You are putting together a report or dashboard and you want to make it simple to understand changes in metrics. By adding color and arrows to a percent change you can help better visualize the meaning of the data. This post shows you how to add green and red color and up and down arrows to highlight changes in metrics in Excel and Google Sheets.

April 16, 2015

Format Time on Page- Excel & Google Sheets for Digital Analytics: Tips & Tricks

• Download Excel Sample
• Google Sheet Sample

4.33 -> 04:20 Adobe Analytics
179.04 -> 02:59 Google Analytics

When you pull metrics like average time on page from digital analytics tools the number format looks like this: 4.33

I remember scratching my head the first time I saw this in Adobe Analytics (then Omniture). Was the other dot in the colon missing and did this mean 4:33 or 4 minutes 33 seconds? Or did this actually mean 4 minutes 0.33 * 60 seconds (0.33 multiplied by 60) which equals 4 minutes 19.8 seconds?

Average Time on Page in Adobe Analytics Report & Analytics

Drum roll please… it means 4 minutes 0.33 * 60 seconds or 04:20. For those of you who were able to convert 4.33 from an a number with a decimal to time in your head please pat yourself on the back and let me know in the comments. For the rest of us, this post will show how to convert a number with decimal time to minutes and seconds time formatting using Excel and Google Sheets.