The Import Cookbook: How to import social media data into Google Sheets with formulas

Updated for 2017!

Google Sheets has a powerful and versatile set of IMPORT formulas that can import data from other websites.

This article looks at importing data from popular social media channels into a Google sheet, for social network analysis or social media management. If you manage a lot of different channels then you could use these techniques to set up a master view (dashboard) to display all your metrics in one place.

The formulas below are generally set up to return the number of followers (or page likes) for a given channel, but you could adapt them to return other metrics (such as follows) with a little extra work.

Caveats: the websites these formulas retrieve data from frequently change, which means the formulas stop working. I will try to keep this post updated with working versions.

Example workbooks: Each example has a link to an associated Google Sheet workbook, so feel free to make your own copy: File > Make a copy....

Continue reading The Import Cookbook: How to import social media data into Google Sheets with formulas

Help, my formula doesn’t work!

Admit it, we’ve all been there.

Whether you’re just starting out with Google Sheets or you’re a seasoned pro, sooner or later one of your formulas will give you an error message rather than the result you want.

It can be frustrating, especially if it’s a longer formula where the error may not be obvious.

Auditing and Debugging Formula Errors in Google Sheets

Match the error message in your Google Sheet to the sections below, and find out what might be causing your error.

  1. An error message popup prevents me entering my formula
  2. I’m getting an #N/A error message
  3. I’m getting an #DIV/0! error message
  4. I’m getting an #VALUE! error message
  5. I’m getting an #REF! error message
  6. I’m getting an #NAME? error message
  7. I’m getting an #NUM! error message
  8. I’m getting an #ERROR! error message
  9. I’m getting an #NULL! error message
  10. Other strategies for dealing with errors
  11. Functions to help deal with formula errors in Google Sheets
  12. Help! My formula is STILL not working

Continue reading Help, my formula doesn’t work!

How to add a hyperlinked index sheet to your large Google Sheet workbooks

Admit it, we’ve all been there. Getting frustrated trying to find a specific sheet inside a huge workbook, and not being able to see it amongst all the other sheets.

Well, here’s a quick Apps Script to create a hyperlinked index page at the start of your workbook.

Quick caveat first: There’s one drawback – the hyperlinks open the sheets in new tabs, which unfortunately I can’t do anything about. However, it should still be useful for anyone working with workbooks with 10+ sheets.

Here it is in action:

Create hyperlinked index sheet

What if we already have a sheet by the name ‘Index’?

Well, you’ll be prompted to enter a different index name or to cancel the operation:

Create hyperlinked index sheet

Steps to create your own hyperlinked index sheet in Google Sheets

Step 1: Open up the workbook you want to add the index sheet to and open the apps script editor (Tools > Script editor...).

Continue reading How to add a hyperlinked index sheet to your large Google Sheet workbooks

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

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

Vlookup in Google Sheets using wildcards for partial matches

VLOOKUP with a single wildcard

Imagine we have this table of employee information and we’ve been given a partial name (in this example: “Mye“) to find and retrieve information about.

To do this we use a standard VLOOKUP formula with the wildcard asterisk character: *

Continue reading Vlookup in Google Sheets using wildcards for partial matches

Web traffic chart with dynamic banding in Google Sheets

This is a simple but effective technique for adding dynamic bands to your charts, which are useful to highlight specific parts of your chart.

For example, in this chart of website pageviews, I’ve added bands to show weekdays or weekends and make it easier to see the changing trends.

Chart with dynamic banding

Continue reading Web traffic chart with dynamic banding in Google Sheets