Using Google Sheets as a basic web scraper

Want to build a web scraper in Google Sheets? Turns out, basic web scraping, automatically grabbing data from websites, is possible right in your Google Sheet, without needing to write any code.

You can extract specific information from a website and show it in your Google Sheet using some of Sheets’ special formulas.

For example, recently I needed to find out the authors for a long list of blog posts from a Google Analytics report, to identify the star authors pulling in the page views. It would have been extremely tedious to open each link and manually enter each author’s name. Thankfully, there are some techniques available in Google Sheets to do this for us.

Continue reading Using Google Sheets as a basic web scraper

Hang on a Median, whaddya Mean there’s no Mode function in MySQL?

Mean Median and Mode

Calculating median and mode values for a dataset in MySQL is more complicated than you might expect.

Sure, there is the avg function to calculate mean, which is simple to use, but what about finding the median or mode average values? There are no equivalent functions in MySQL, so we have to work a bit harder to compute these values.

There are many ways to do this. Below I show the approach I take to calculate mean, median and mode values, along with sharing some resources at the end of this post that continue the discussion on the merits of different approaches.
Continue reading Hang on a Median, whaddya Mean there’s no Mode function in MySQL?

10 techniques for building a Google Sheets dashboard

You’re probably familiar with using Google Sheets to organize and analyze your data. But did you know you can build a dynamic Google Sheets dashboard to really understand your data?

With a handful of powerful techniques, you can add some pizzazz and dynamism to the presentation of your data. Here are ten tricks to try next time you’re building a Google Sheets dashboard.

Google Sheets dashboard for digital marketing
Google Sheets dashboard for digital marketing, covered in my online course

Continue reading 10 techniques for building a Google Sheets dashboard

How to create CSS pie charts in Geckoboard dashboards with data from Google sheets

I’ve been working on an interesting project for a client recently, creating a custom Geckoboard dashboard for sales and financial data from Google spreadsheets. Geckoboard pairs well with Google sheets; it’s a fast and cost-effective way of visualizing your data.

One of the client requests on this project was a pie chart to display the data for sales sources. At the moment, Geckoboard doesn’t come with a pre-built pie chart option for data that comes from Google sheets (full list of Google sheets widgets here). So, unless you build a custom back-end widget to get data from Google’s API, which is a fairly complex project, you’re out of luck. Hang on a minute though, there is a way!

Geckoboard pie chart with legend
A Geckoboard Text Widget displaying a pie chart with legend

There’s a crafty workaround you can use to create a pie chart in your Geckoboard using a text-widget, which pulls data from a Google spreadsheet and updates dynamically along with the rest of your charts.

Continue reading How to create CSS pie charts in Geckoboard dashboards with data from Google sheets