Google Sheets, Apps Script and Data Studio Resources: The Ultimate List for 2017

Want tons of great Google Sheets, Apps Script and Data Studio resources in one place?

Then you’ll love this list.

These are my go-to resources when I’m building spreadsheet applications for clients or developing content for this blog. I have hundreds of bookmarks on the subject but here I’ve whittled it down to just the very best.

Feel free to suggest any other resources in the comments below!


Google Sheet Resources

Google Help Resources

Starting this list off is the official Google Sheets documentation. This guide covers everything about sheets, including all 346 formulas.

Google spreadsheets function list – all 346 of them! A good place to start when you want to find out the syntax details or how formula X works.

The Google Docs blog – the official source for product announcements, updates and tips from Google.

Forums and G+ communities

Aka, good places to ask questions!

Google Sheets Help Forum. The place to go to get your Google Sheet questions answered. Read this getting started guide if you’re new to it. There are also forums for the other Google Docs.

Google Spreadsheets Tips & Tricks. Another great community for asking and answering questions on Google Sheets. It’s full of experts willing to give their time to answer questions.

Blogs

Continue reading Google Sheets, Apps Script and Data Studio Resources: The Ultimate List for 2017

2016 in review and a look forward to 2017

Happy New Year to you all!

DC fireworks
Photo credit: Architect of the Capitol

If 2015 was a year of huge change, then 2016 was more incremental, building on the foundations of 2015. I still tried lots of different things – different gigs, different projects, different tools – but I’ve found creating apps and solving data problems on the Google platform is my sweet spot of skills, experience and enjoyment.

2016 highlights:

On this site

Investing so much time and effort into this site really started to pay off over the course of 2016.

Web traffic chart

Over 2,500 of you have subscribed to my email list and enjoyed a free copy of my ebook: Spice Up Your Sheet Life.

Midway through the year, I passed 100k pageviews, then 150k pageviews and am now close to 200k.

Several posts have garnered some really great commentary from readers, adding tons of value to the original post. This one in particular has 112 comments and counting!

Continue reading 2016 in review and a look forward to 2017

Funnel charts in Google Sheets using the chart tool, formulas and Apps Script

Let’s talk about funnel charts in Google Sheets.

The charts themselves are a bit of a novelty. Yes, they’re aesthetically pleasing because of that resemblance to a real-world, tapering funnel, which reinforces their message, but a plain ole’ bar chart would be equally suitable and actually easier to read data from (because the bars have a common baseline).

However, they throw up some interesting techniques in Google Sheets and for that reason, merit this long article.

We’ll build them using tricks with the chart builder tool, then with two different types of funky formula and finally, and best of all, we’ll build a tool using Apps Script, as shown in this image:

Funnel charts with apps script in Google Sheets

As with the waterfall charts in Google Sheets, they’re not one of the out-the-box charts available to us, so we have to manually create them with a crafty workaround. Thankfully, they’re relatively simple to create, certainly simpler than the waterfall chart.

For all of these examples, we’ll use this fictitious real-estate dataset:

Google Sheets funnel chart data

Here, I’m imagining the real estate agency collects data relating to their sales funnel, and they want to display it in a funnel chart format.

Click here to open up the Google Sheet template and make your own copy (File > Make a copy...).

Continue reading Funnel charts in Google Sheets using the chart tool, formulas and Apps Script

How to create waterfall charts in Google Sheets

Waterfall charts, eh?

Ok, waterfall charts are real. And useful. They don’t actually involve falling water in any capacity, but they do show the cumulative effect of a series of positive and/or negative values on an initial starting value.

The following waterfall chart shows the headcount changes for a department, visually depicting the cumulative effect of the additions and deletions to the start value:

Headcount Waterfall Chart

It shows the number of staff in our department at the start of the year (left grey bar), the number of people added from other departments or as new hires (green bars), the number of people who left (red bars) and finally the balance which is the headcount at the end of the year (right grey bar).

The waterfall chart above is relatively easy to create in Google Sheets but does still require some data wrangling to set it up. Notice that all of the bars are above the x-axis (Case 1), which makes the data set up vastly simpler than the case when we have a mix of bars above and below the x-axis, or spanning the x-axis (see Case 2 below).

I’ll show you how to create both of these cases, starting with the easier, positive-bar case.

After creating the simple and complex versions manually with formulas, I’ll show you some Apps Script code to automate the majority of the process and massively speed up creating complex waterfall charts.

Templates are available for all three methods, with links at the end of each section and at the end of this post.
Continue reading How to create waterfall charts in Google Sheets

Animated Data Visualization of Washington D.C.’s warming temperatures

I love animated charts where the animation is central to the storytelling; where it adds value, rather than being simply fancy decoration.

When it’s done well, it can be extremely powerful, mesmerizing and compelling.

One of the best examples I’ve seen is Bloomberg’s animated temperature visualization. I love how powerfully this chart conveys its message, and I’ve been semi-obsessed with it ever since I saw it.

So, over the past few weeks and since my last experiment with the Google Visualization API, I’ve been chipping away at this project to create an animated temperature chart within the Google environment. It’s finally in a state I can share here, however it’s what you might call an MVP still…

Here’s my animated temperature chart built with the Google Visualization API connecting to Washington D.C. data in a Google Sheet:

Animated temperature chart using Google Chart API

You can see this animated chart in all its glory here.

Continue reading Animated Data Visualization of Washington D.C.’s warming temperatures

Recreating Tufte’s famous weather chart with Google Sheets and Charts API

My Twitter feed is mostly populated with data-related resources and people, so I almost always find something new and shiny going on there to distract me.

Sometimes this can be a good thing though (and sometimes, it can be the best thing ever).

Take last Friday. I found myself procrastinating, browsing my home feed when I saw this tweet for the upcoming Microsoft Data Science summit:

Tufte temp chart tweet

What caught my eye was not the actual tweet, but the sumptuous weather visualization that I recognized as one of Edward Tufte’s famous creations.

I clicked through to the article, which linked to Tufte’s original chart (here) and a fantastic tutorial about re-creating the chart using R, from Brad Boehmke (which is actually the chart shown in the tweet above).

It looks at daily average temperature over a period of time and captures the historic min/max range, the normal range (95% confidence interval), the 2014 temperature line and picks out the highs and lows. It renders all of this information in a single, beautiful column/line combination chart.

Curiousity piqued, I wondered whether this would be possible with Google Sheets?

The short answer is….. Continue reading Recreating Tufte’s famous weather chart with Google Sheets and Charts API

Build time-based triggers in Apps Script projects to automate programs

Ever find yourself needing to repeat an action in your Google Sheets?

For example, on a recent client project I wanted to cycle through my spreadsheet data, compare it to another dataset and apply a matching algorithm. To do this and stay within the 6 minute execution limit for Apps Script, I split my data into “blocks” of 10 rows, counted how many blocks I had and then ran the matching algorithm once for each block until they’d all been processed.

By adding time-based triggers, I setup my Google sheet to do this automatically, rather than having me click a button each time.

Using Apps Script (read my getting started guide here), you can add these time-based triggers programmatically, so they can run and stop automatically.

In this post I’m going to show a skeleton example of this time-based architecture, to which you can add your own functionality.

The entire code file is here on GitHub and the spreadsheet is available here (feel free to make a copy: File > Make a copy… and then you can open the script editor to modify the code).

Time-based triggers with Google Apps Script

In this simple example, I’m going to output a random number in a new line of my spreadsheet every minute until I’ve looped through a set number of times (2 in this case). It’s deliberately basic as the focus is on the trigger/timing architecture.

Here’s a screencast of this simple program in action (speeded up):

Auto trigger loop

Behind the scenes, everything is running on autopilot once I’ve clicked that first Run button.

Continue reading Build time-based triggers in Apps Script projects to automate programs

Friday reading: resources and inspiration for spreadsheet buffs

Here’s a collection of resources for you: a strange mix of advanced, esoteric and inspirational spreadsheet uses, showing how far some (mad)men and women push their spreadsheets. The list gets more light-hearted towards the bottom of the post!

Somehow I doubt Dan Bricklin would have envisioned all this when he invented the spreadsheet…

Spreadsheet programming

Functional programming in Excel from Felienne Hermans all about her research into treating Excel as programming language. Well worth a watch.

Continue reading Friday reading: resources and inspiration for spreadsheet buffs

6 advanced techniques to master in Google Data Studio

Google launched a new business intelligence tool called Data Studio in May 2016. It’s a really smart reporting tool for quickly creating powerful, stunning dashboards from multiple Google data sources.

It’s a great option for small/medium businesses already using Google tools, who want to build bespoke dashboards for that 40,000ft view of their business.

Google Data Studio Example Reports

Here are two example reports for a mid-size website (~500k pageviews a month).

Firstly, a mobile performance dashboard:

Data Studio Mobile Performance Dashboard

Continue reading 6 advanced techniques to master in Google Data Studio