The Challenge: Merge Columns With Single Range Reference
Question: How can you merge n number of columns and get the unique values, without typing each one out?
In other words, can you create a single formula that gives the same output as this one:
=SORT( UNIQUE( {A:A;B:B;C:C;...;XX:XX} ))
but without having to write out A:A, B:B, C:C, D:D etc. and instead just write A:XX as in the input?
Use this simple dataset example, where your formula will be in cell E1 (in green):
Your answer should:
be a formula in a single cell
work with the input range in the form A:XX (e.g. A:C in this example)
work with numbers and/or text values
return only the unique values, in an ascending order in a SINGLE COLUMN.
Solutions To Sort A Column By Last Name
I received 67 replies to this formula challenge with two different methods for solving it. Congratulations to everyone who took part!
I learnt so much from the different replies, many of which proffered a shorter and more elegant second solution than my own original formula.
Here I present the two solutions.
There’s a lot to learn by looking through them.
1. FLATTEN method
=SORT(UNIQUE(FLATTEN(A:C)))
The formula uses the FLATTEN function to collect data from the input ranges into a single column before the UNIQUE function selects the unique ones before they are finally sorted.
Note 1: you can have multiple inputs (arguments) to the FLATTEN function. Data is ordered by the order of the inputs, then row and then column.
Note 2: at the moment the FLATTEN function doesn’t show up in the auto-complete when you start typing it out. You can still use it, but you’ll have to type it out fully yourself.
Thanks to the handful of you that shared this neat solution with me. Great work!
2. TEXTJOIN method
Join all the values in A:C with TEXTJOIN, using a unique character as the delimiter (in this case, the King and Queen chess pieces!)
You want to use an identifier that is not in columns A to C.
=TEXTJOIN("♔♕",TRUE,A:C)
Split on this unique delimiter using the SPLIT function:
=SPLIT(TEXTJOIN("♔♕",TRUE,A:C),"♔♕")
Use the TRANSPOSE function to switch to a column, select the unique values only and finally wrap with a sort function to get the result:
Google Sheets sort by color and filter by color are useful techniques to organize your data based on the color of text or cells within the data.
For example, you might highlight rows of data relating to an important customer. Google Sheets sort by color and filter by color let you bring those highlighted rows to the top of your dataset, or even only show those rows.
As a bonus, they’re really easy to use. Let’s see how:
Google Sheets Sort By Color
Suppose you have a dataset with highlighted rows, for example all the apartments in this dataset:
Add a filter (the funnel icon in the toolbar, shown in red in the above image).
On any of the columns, click the filter and choose the “Sort by color” option.
You can sort by the background color of the cell (like the yellow in this example) or by the color of the text.
The result of applying this sort is all the colored rows will be brought to the top of your dataset.
This is super helpful if you want to review all items at the same time. Another reason might be if they’re duplicate rows you’ve highlighted which you can now delete.
Google Sheets Filter By Color
The Google Sheets filter by color method is very similar to the sort by color method.
With the filters added to your dataset, click one to bring up the menu. Select “Filter by color” and then select to filter on the background cell color or the text color.
In this example, I’ve used the Google Sheets filter by color to only display the yellow highlighted rows, which makes it really easy to review them.
There’s an option to remove the filter by color by setting it to none, found under the filter by color menu. This option is not found for the sort by color method.
Apps Script Solution
When I originally published this article, sort by color and filter by color were not available natively in Google Sheets, so I created a small script to add this functionality to a Sheet.
Here is my original Apps Script solution, left here for general interest.
With a few simple lines of Apps Script, we can implement our own version.
This article will show you how to implement that same feature in Google Sheets.
It’s a pretty basic idea.
We need to know the background color of the cell we want to sort or filter with (user input 1). Then we need to know which column to use to do the sorting or filtering (user input 2). Finally we need to do the sort or filter.
So step one is to to prompt the user to input the cell and columns.
I’ve implemented this Google Sheets sort by color using a modeless dialog box, which allows the user to click on cells in the Google Sheet independent of the prompt box. When the user has selected the cell or column, we store this using the Properties Service for retrieval when we come to sort or filter the data.
Apps Script Sort By Color
At a high level, our program has the following components:
Custom menu to run the Google Sheets sort by color program
Prompt to ask user for the color cell
Save the color cell using the Properties Service
Second prompt to ask the user for the sort/filter column
Save the sort/filter column using the Properties Service
Show the color and column choices and confirm
Retrieve the background colors of the sort/filter column
Add helper column to data in Sheet with these background colors
Sort/Filter this helper column, based on the color cell
Clear out the values in the document Properties store
Let’s look at each of these sections in turn.
Add A Custom Menu (1)
This is simply boilerplate Apps Script code to add a custom menu to your Google Sheet:
/**
* Create custom menu
*/
function onOpen() {
var ui = SpreadsheetApp.getUi();
ui.createMenu('Color Tool')
.addItem('Sort by color...', 'sortByColorSetupUi')
.addItem('Clear Ranges','clearProperties')
.addToUi();
}
Prompt The User For Cell And Column Choices (2, 4 and 6 above)
I use modeless dialog boxes for the prompts, which allows the user to still interact with the Sheet and click directly on the cells they want to select.
/**
* Sort By Color Setup Program Flow
* Check whether color cell and sort columnn have been selected
* If both selected, move to sort the data by color
*/
function sortByColorSetupUi() {
var colorProperties = PropertiesService.getDocumentProperties();
var colorCellRange = colorProperties.getProperty('colorCellRange');
var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
var title='No Title';
var msg = 'No Text';
//if !colorCellRange
if(!colorCellRange) {
title = 'Select Color Cell';
msg = '<p>Please click on cell with the background color you want to sort on and then click OK</p>';
msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(1); google.script.host.close();" />';
dispStatus(title, msg);
}
//if colorCellRange and !sortColumnLetter
if (colorCellRange && !sortColumnLetter) {
title = 'Select Sort Column';
msg = '<p>Please highlight the column you want to sort on, or click on a cell in that column. Click OK when you are ready.</p>';
msg += '<input type="button" value="OK" onclick="google.script.run.sortByColorHelper(2); google.script.host.close();" />';
dispStatus(title, msg);
}
// both color cell and sort column selected
if(colorCellRange && sortColumnLetter) {
title= 'Displaying Color Cell and Sort Column Ranges';
msg = '<p>Confirm ranges before sorting:</p>';
msg += 'Color Cell Range: ' + colorCellRange + '<br />Sort Column: ' + sortColumnLetter + '<br />';
msg += '<br /><input type="button" value="Sort By Color" onclick="google.script.run.sortData(); google.script.host.close();" />';
msg += '<br /><br /><input type="button" value="Clear Choices and Exit" onclick="google.script.run.clearProperties(); google.script.host.close();" />';
dispStatus(title,msg);
}
}
/**
* display the modeless dialog box
*/
function dispStatus(title,html) {
var title = typeof(title) !== 'undefined' ? title : 'No Title Provided';
var html = typeof(html) !== 'undefined' ? html : '<p>No html provided.</p>';
var htmlOutput = HtmlService
.createHtmlOutput(html)
.setWidth(350)
.setHeight(200);
SpreadsheetApp.getUi().showModelessDialog(htmlOutput, title);
}
/**
* helper function to switch between dialog box 1 (to select color cell) and 2 (to select sort column)
*/
function sortByColorHelper(mode) {
var mode = (typeof(mode) !== 'undefined')? mode : 0;
switch(mode)
{
case 1:
setColorCell();
sortByColorSetupUi();
break;
case 2:
setSortColumn();
sortByColorSetupUi();
break;
default:
clearProperties();
}
}
The buttons on the dialog boxes use the client-side google.script.run API to call server-side Apps Script functions.
Following this, the google.script.host.close() is also a client-side JavaScript API that closes the current dialog box.
Save The Cell And Column Choices In The Property Store (3 and 5)
These two functions save the cell and column ranges that the user highlights into the Sheet’s property store:
/**
* saves the color cell range to properties
*/
function setColorCell() {
var sheet = SpreadsheetApp.getActiveSheet();
var colorCell = SpreadsheetApp.getActiveRange().getA1Notation();
var colorProperties = PropertiesService.getDocumentProperties();
colorProperties.setProperty('colorCellRange', colorCell);
}
/**
* saves the sort column range in properties
*/
function setSortColumn() {
var sheet = SpreadsheetApp.getActiveSheet();
var sortColumn = SpreadsheetApp.getActiveRange().getA1Notation();
var sortColumnLetter = sortColumn.split(':')[0].replace(/\d/g,'').toUpperCase(); // find the column letter
var colorProperties = PropertiesService.getDocumentProperties();
colorProperties.setProperty('sortColumnLetter', sortColumnLetter);
}
As a result of running these functions, we have the color cell address (in A1 notation) and the sort/filter column letter saved in the Property store for future access.
Sorting The Data (7, 8 and 9 above)
Once we’ve selected both the color cell and sort column, the program flow directs us to actually go ahead and sort the data. This is the button in the third dialog box, which, when clicked, runs this call google.script.run.sortData();.
The sortData function is defined as follows:
/**
* sort the data based on color cell and chosen column
*/
function sortData() {
// get the properties
var colorProperties = PropertiesService.getDocumentProperties();
var colorCell = colorProperties.getProperty('colorCellRange');
var sortColumnLetter = colorProperties.getProperty('sortColumnLetter');
// extracts column letter from whatever range has been highlighted for the sort column
// get the sheet
var sheet = SpreadsheetApp.getActiveSheet();
var lastRow = sheet.getLastRow();
var lastCol = sheet.getLastColumn();
// get an array of background colors from the sort column
var sortColBackgrounds = sheet.getRange(sortColumnLetter + 2 + ":" + sortColumnLetter + lastRow).getBackgrounds(); // assumes header in row 1
// get the background color of the sort cell
var sortColor = sheet.getRange(colorCell).getBackground();
// map background colors to 1 if they match the sort cell color, 2 otherwise
var sortCodes = sortColBackgrounds.map(function(val) {
return (val[0] === sortColor) ? [1] : [2];
});
// add a column heading to the array of background colors
sortCodes.unshift(['Sort Column']);
// paste the background colors array as a helper column on right side of data
sheet.getRange(1,lastCol+1,lastRow,1).setValues(sortCodes);
sheet.getRange(1,lastCol+1,1,1).setHorizontalAlignment('center').setFontWeight('bold').setWrap(true);
// sort the data
var dataRange = sheet.getRange(2,1,lastRow,lastCol+1);
dataRange.sort(lastCol+1);
// add new filter across whole data table
sheet.getDataRange().createFilter();
// clear out the properties so it's ready to run again
clearProperties();
}
And finally, we want a way to clear the properties store so we can start over.
Clear The Property Store (10 above)
This simple function will delete all the key/value pairs stored in the Sheet’s property store:
/**
* clear the properties
*/
function clearProperties() {
PropertiesService.getDocumentProperties().deleteAllProperties();
}
Run The Google Sheets Sort By Color Script
If you put all these code snippets together in your Code.gs file, you should be able to run onOpen, authorize your script and then run the sort by color tool from the new custom menu.
Here’s the sort by color tool in action in Google Sheets:
You can see how all of the green shaded rows are sorted to the top of my dataset.
Note that this sort by color feature is setup to work with datasets that begin in cell A1 (because it relies on the getDataRange() method, which does the same).
Some improvements would be to make it more generalized (or prompt the user to highlight the dataset initially). I also have not included any error handling, intentionally to keep the script as simple as possible to aid understanding. However, this is something you’d want to consider if you want to make this solution more robust.
(If you’re prompted for permission to open this, it’s because my Google Workspace domain, benlcollins.com, is not whitelisted with your organization. You can talk to your Google Workspace administrator about that. Alternatively, if you open this link in incognito mode, you’ll be able to view the Sheet and copy the script direct from the Script Editor.)
The program flow is virtually identical, except that we filter the data rather than sort it. The code is almost exactly the same too, other than variable names being different and implementing a filter instead of a sort.
Rather than sorting the data, we create and add a filter to the dataset to show only the rows shaded with the matching colors:
The filter portion of the code looks like this:
// remove existing filter to the data range
if (sheet.getFilter() !== null) {
sheet.getFilter().remove();
}
// add new filter across whole data table
var newFilter = sheet.getDataRange().createFilter();
// create new filter criteria
var filterCriteria = SpreadsheetApp.newFilterCriteria();
filterCriteria.whenTextEqualTo(filterColor);
// apply the filter color as the filter value
newFilter.setColumnFilterCriteria(lastCol + 1, filterCriteria);
If you want a challenge, see if you can modify the sort code to work with the filter example.
Apps Script Filter By Color Template
Feel free to copy the Google Sheets filter by color template here.
(If you’re prompted for permission to open this, it’s because my Google Workspace domain, benlcollins.com, is not whitelisted with your organization. You can talk to your Google Workspace administrator about that. Alternatively, if you open this link in incognito mode, you’ll be able to view the Sheet and copy the script direct from the Script Editor.)
Let’s hope for a brighter, happier, safer lap around the sun this time.
We had a December snowstorm! Lots of fun with the young ‘uns 🙂
This is annual review number 6!
As always, I’m super grateful when I sit down to write this because it means I’m still working for myself and building this business.
2020 was a difficult year for the world.
I’m fortunate to have my health and so do those close to me. I can’t imagine how difficult 2020 has been for those who have lost someone. My heart goes out to you.
My wife and I have taken the virus seriously. Given my history of pneumonia in the last two years (see challenges of 2018 and 2019) I can’t afford to take this virus lightly.
We’re extremely fortunate that we already work from home, so that didn’t present a significant challenge when the whole world went remote. However, going from full time childcare to no childcare was certainly a challenge.
I’m looking forward to 2021 and the promise of a vaccine. I haven’t seen my UK family since January 2020 and I miss them (and the UK) terribly.
I’m cautiously optimistic that 2021 will be better, and make up for the annus horribilis that was 2020.
With that, let me present my review of the year:
Did I Meet My 2020 Goals?
Overall, given the circumstances – I probably had 50% fewer working hours this year because I spent that time with my kids – I’m really happy with what I achieved and feel positive about how the year went from a work perspective.
Publish more high-quality tutorials than in 2019 (target > 17) – Yes! I wrote 26 new tutorials this year.
Hit 50k newsletter subscribers and send out a tip every Monday – Yes and no. I sent a newsletter every Monday and hit 40k subs, which I’m super happy with. This is after removing 8k inactive subs, so I actually got pretty close to my original goal.
Update my existing Google Sheets courses – Yes! I re-recorded all of the Google Sheet course videos. I’m updating the Automation with Apps Script course at the moment, which will complete the update process.
Create one new Google Sheets course – Yes! I launched the Google Sheets Essentials course this year.
Run 10 in-person workshops – No. Obviously not 😉
Re-brand my digital assets – Yes! I was thrilled with how it turned out. Details below.
Find a VA to help with the business – Yes! And she’s been an enormous help. Thanks, Jo!
Live-blog Google Next 2020 again – No 🙁 Obviously, this didn’t happen since the conference was cancelled.
Work through this book: Data Science on the Google Cloud Platform – Sort of. I started the book and worked through another BigQuery book, but it’s still early in that journey.
My overall number 1 goal for 2020 is to be healthy – Yes! Apart from my whole family having the flu in February and a grotty headcold in August, I’ve been healthy this year.
Fitness goals: be active 5 times/week (a mix of spin classes, runs and at least 1 run/hike up the mountain) – Sort of… my R knee is still not healed from the running injuries last year, so I’ve been confined to hiking and occasional yoga classes.
Keep up the weekly brainstorming hike with my wife – The pandemic put a dampener on this. We’ve managed a few hikes together but since childcare is limited in the current circumstances, we haven’t had the opportunity to do this weekly as we’d hoped.
Read 30 books – No. I read ~20 books, but the last one I read was 650 pages of small print, all about life in Stalin’s Russia of the 1930s, 40s and 50s. That counts for at least 3 or 4 normal books by my reckoning 😉
2020 Highlights
2020 felt like a long year. Events from the start of this year feel like they happened years ago. I feel like I aged 10 years!
But despite the terrible toll the pandemic exacted on us all, there were plenty of highlights throughout the year.
In no particular order:
1) New Brand
I hired the super talented team at Left Hand Design to do a rebrand for my business and courses.
I wanted something simple, bold and geometric, and I think Left Hand Design did an outstanding job.
Over the course of a couple of months, they created new family of logos, new color scheme, fonts and styles for my entire online presence. They created new images for my courses and a new slide deck template for the lessons.
I also need to credit my wife, Alexis Grant, for the green dot over the “i”, a wonderful addition!
This new brand represents a huge leap forward for my business.
2) SheetsCon
In March this year I ran my most ambitious project to date: SheetsCon, a 2-day online conference for all things Google Sheets.
When I planned the conference in late 2019, way before any of us had heard of Coronavirus, I envisioned an online conference so that people from all over the world could participate, free of charge.
SheetsCon ran on Wednesday 11th and Thursday 12th March. My sons had their last day at preschool on the 13th March, because it shut down the following week. We all went into lockdown that weekend.
The timing of an online conference in March might have looked prescient from the outside, but I can promise you it wasn’t planned that way because of Covid.
The event was a massive success; we had almost 7,000 registered attendees, 3,800 of whom attended live, and 89.5% of whom said they’ll return in 2021.
My email list has grown from around 30,000 at the beginning of the year to over 38,000 by year end, after removing over 8,000 inactive subscribers part way through the year (the steep drop).
Email continues to be my main marketing channel, and the list grew steadily throughout the year. I get about 40 – 50 daily signups for the Google Sheets Tips newsletter, which goes out at 11am every Monday.
I sent 51 Google Sheets Tips newsletters this year, only skipping the Christmas week.
I’m grateful to all of you who read this website, open my Google Sheets tips newsletters or learn from one of my online courses. It’s a great privilege to share my teachings with the world. I love my work and hope to serve you for years to come. Thank you! 🙏
I’m also extremely grateful to the Google Developer Expert Workspace group and the Googlers I’ve gotten to know over the past few years. It’s been a real pleasure to learn from you all and I’m humbled to be included in such a wonderful and knowledgeable group. Cheers to future collaborations!
Spending lots of time with my two young sons this year and watching them blossom, despite the difficult circumstances. Yes, it’s been frustrating and challenging at times, but it’s impossible to put into words how much I love these two little guys and want to do my best for them.
We had a wonderful week at Deep Creek Lake with my wife’s family in August. It was relaxing and we got to be mostly normal for a week, and socialize with more than just my immediate family four. We enjoyed time on the lake, some great hikes, fires and BBQs!
2020 was an incredibly challenging year for everyone. I’m grateful that I, and those close to me, have remained healthy this year.
Aside from staying healthy and isolating, the biggest challenge for my wife and me was the lack of childcare.
We had no childcare in April or May, some in June to August, and then about 28 hours/week since September-ish. Since we both have our own businesses and are ambitious, it’s been a tricky balancing act.
Looking Forward To 2021
I’m super focussed on doing just a few things as well as I can, so I condensed my entire 2021 plan onto a single whiteboard.
Obviously, this only covers the big ticket items, and not things like the blog posts. I find it incredibly helpful to have it written down though. I look at every day to keep me focussed.
New Initiatives
My big initiative for 2021 is to create a cohort-based course for Google Sheets and data analysis, tentatively called ProSheets.
It’ll consist of two live classes and office hours each week for 5 weeks, with a project to finish. You’ll be in a cohort with other students going through the same transformation, so you’ll have a peer group to be accountable with. You’ll leave the course as a pro with Google Sheets, how to solve business and data analysis problems from end-to-end, and have an amazing group of peers to continue learning with. More details to come in early 2021!
To make this new course as successful as possible for students, I’m joining two training programs myself in early 2021. They are: 1) the Keystone Accelerator course, a course/mastermind with other ambitious creators looking to start cohort courses, and 2) the Scaling Intimacy workshop, all about how to create memorable online experiences. I’m super excited about both and can’t wait to put these lessons into practice.
2021 Work Goals
Run 3 cohorts of this new live cohort based course
Publish a comprehensive guide to REGEX in Google Sheets
Hit 60k newsletter subscribers
Send a Google Sheets tip email every week for the next year
Create one new on-demand video course
One technical project, related to Sheets/Apps Script/Data in some way. This is partly for my own intellectual curiosity and learning but will also lay the foundations for future blog posts and courses.
Other 2021 Goals
See my UK family!
Have another healthy year
Exercise regularly: 4 hike or bikes each week, 2 yoga/strength
Go camping again! I used to do a lot of camping but it’s been a few years since I last went 🙁
Take my boys out on lots of adventures and camping trips.
Read 30 books (same target as 2020)
Thank You
Finally, my biggest thanks are reserved for you, dear reader.
It’s an extreme honor and privilege for me to help you through my writing and teaching.
My work to create the world’s best resources for learning Google Sheets and data analysis is just getting started.
Growing up, I vividly remember sitting in my dad’s home office after school, waiting for him to get home from work.
The office had a tall ceiling and a single window at the back that opened into a tiny access courtyard between our house and the neighbor’s house (it was a semi-detached Victorian).
My dad sat behind a heavy wooden desk, with a big, boxy desktop computer sitting atop. On one wall was a bookshelf, full of computer books and boxes of floppy disks for illustrious programs like Microsoft Windows, Lotus 1-2-3, Borland Quattro Pro, and many others I’ve forgotten.
I would pull the thickest manual off the shelf and ask dad to explain it to me the minute he got home from work. I’m sure it’s just what he wanted to do at the end of a long work day. Sorry (but not sorry) dad!
I’ve wanted my own work space, reflecting my personality and overflowing with books, ever since.
Working From Home
I’ve worked for myself for 5 years now, so I’m used to working from home.
For the first couple of years, I worked from a small desk in the living room and then the basement of where I lived at the time.
When my wife and I moved to Florida in 2017, I rented a 1-person office in downtown St. Petersburg. My youngest son was only a few months old so I needed a quiet space to record videos. (I launched my first online course in 2017.)
I customized that rental office to make it my own. The first investment was a Fully Jarvis standing desk, which I still use and love today.
Last year, we moved to Harpers Ferry, WV, and it was a chance to set up a new office. The only change was the better scenery out my window and a couple of pieces of artwork on the walls.
This year, 2020, we moved out of the rental house and into our own home, so it was finally time to build the dream office. This is iteration three of my home office.
An Investment In You And Your Business
I’ve come to realize that the environment in which you do your work is important.
To do my best work I need to clear my mind out first. If there’s clutter everywhere, which is most days since I have young kids, then my mind is using energy to think about it. In my head, I’m doing a virtual Maire Kondo where I sweep it all away and out of sight.
My office is one space I have control over though. I can set it up to be clean and minimal.
Today, I’m much more sure of who I am and what I do than at any previous stage in life. And that translates into being able to create a workspace that facilitates the work I do now.
Global HQ for Collins Analytics LLC
My 2014 MacBook Pro is 6 years old and showing its age.
I don’t do a lot of heavy-duty computing, but I do work with large video files. And of course, I have a lot of Chrome tabs open at any given time.
The time from deciding I needed a new computer to actually purchasing one was about 12 months!
I spent a LOT of time researching options and looking at other’s setups.
I’m using the new Apple Mac Mini with the M1 chip, powering 2 monitors: an ultrawide Dell U3419W (supported by a Fully Jarvis monitor arm) and an Acer R240HY.
The microphone is a Blue Yeti on a Blue Compass arm, and the light is an Elgato Key light.
Everything sits on Fully’s Jarvis standing desk, which I’ve had for years and love.
So far, it’s a fantastic combination! Super fast, quiet and tons of real estate.
That’s a Lego Saturn V rocket on the window ledge, one of the greatest Lego models of all time.
This tutorial is written for Google Sheets users who have datasets that are too big or too slow to use in Google Sheets. It’s written to help you get started with Google BigQuery.
If you’re experiencing slow Google Sheets that no amount of clever tricks will fix, or you work with datasets that are outgrowing the 10 million cell limit of Google Sheets, then you need to think about moving your data into a database.
As a Google user, probably the best and most logical next step is to get started with Google BigQuery and move your data out of Google Sheets and into BigQuery.
By the end of this tutorial, you will have created a BigQuery account, uploaded a dataset from Google Sheets, written some queries to analyze the data and exported the results back to Google Sheets to create a chart.
You’ll also do the same analysis side-by-side in a Google Sheet, so you can understand exactly what’s happening in BigQuery.
I’ve highlighted the action steps throughout the tutorial, to make it super easy for you to follow along:
Google BigQuery exercise steps are shown in blue.
Actions for you to do in Google BigQuery.
Google Sheet exercise steps are shown in green.
Actions for you to do in Google Sheets.
Section 1: What is BigQuery?
Google BigQuery is a data warehouse for storing and analyzing huge amounts of data.
Officially, BigQuery is a serverless, highly-scalable, and cost-effective cloud data warehouse with an in-memory BI Engine and machine learning built in.
This is a formal way of saying that it’s:
Works with any size data (thousands, millions, billions of rows…)
Easy to set up because Google handles the infrastructure
Grows as your data grows
Good value for money, with a generous free tier and pay-as-you-go beyond that
Lightning fast
Seamlessly integrated with other Google tools, like Sheets and Data Studio
Can import and export data from and to many sources
Has Built-in machine learning, so predictive modeling can be set up quickly
What’s the difference between BigQuery and a “regular” database?
BigQuery is a database optimized for storing and analyzing data, not for updating or deleting data.
It’s ideal for data that’s generated by e-commerce, operations, digital marketing, engineering sensors etc. Basically, transactional data that you want to analyze to gain insights.
A regular database is suitable for data that is stored, but also updated or deleted. Think of your social media profile or customer database. Names, emails, addresses, etc. are stored in a relational database. They frequently need to be updated as details change.
Section 2: Google BigQuery Setup
It’s super easy to get started wit Google BigQuery!
There are two ways to get started: 1) use the free sandbox account (no billing details required), or 2) use the free tier (requires you to enter billing details, but you’ll also get $300 free Cloud credits).
In either case, this tutorial won’t cost you anything in BigQuery, since the volume of data is so tiny.
We’ll proceed using the sandbox account, so that you don’t have to enter any billing details.
A new project called “My First Project” is automatically created
In the left side pane, scroll down until you see BigQuery and click it
Here’s that process shown as a GIF:
You’re ready for Step 2 below.
BigQuery Console
(click to enlarge)
Here’s what you can see in the console:
The SANDBOX tag to tell you you’re in the sandbox environment
Message to upgrade to the free trial and $300 credit (may or may not show)
UPGRADE button to upgrade out of the Sandbox account
ACTIVATE button to claim the free $300 credit
The current project and where to create new projects
The Query editor window where you type your SQL code
Current project resource
Button to create a new dataset for this project (see below)
Query outputs and table information window
What is the free Sandbox Account?
The sandbox account is an option that lets you use BigQuery without having to enter any credit card information. There are limits to what you can do, but it gives you peace of mind that you won’t run up any charges whilst you’re learning.
In the sandbox account:
Tables or views last 60 days
You get 10 Gb of storage per month for free
And 1 Tb data processing each month
It’s more than enough to do everything in this tutorial today.
Unlike Google Sheets, you have to pay to use BigQuery based on your storage and processing needs.
However, there is a sandbox account for free experimentation (see below) and then a generous free tier to continue using BigQuery.
In fact, if you’re working with datasets that are only just too big for Sheets, it’ll probably be free to use BigQuery or very cheap.
BigQuery charges for data storage, streaming inserts, and for querying data, but loading and exporting data are free of charge.
Your first 1 TB (1,000 GB) per month is free.
Full BigQuery pricing information can be found here.
Clicking on the blue “Try BigQuery free” button on the BigQuery homepage will let you register your account with billing details and claim the free $300 cloud credits.
Section 3: How to get your data into BigQuery
Extracting, loading and transforming (ELT) is sometimes the most challenging and time consuming part of a data analysis project. It’s the most engineering-heavy stage, where the heavy lifting happens.
You can load data into BigQuery in a number of ways:
From a readable data source (such as your local machine)
From Google Sheets
From other Google services, such as Google Ad Manager and Google Ads
Use a third-party data integration tool, e.g. Supermetrics, Stitch
You might want to make a SECOND copy in your Drive folder too, so you can keep one copy untouched for the upload to BigQuery and use the second copy for doing the follow-along analysis in Google Sheets.
The first dataset is a record of pedestrian traffic crossing Brooklyn Bridge in New York city (source).
It’s only 7,000 rows, so it could be easily analyzed in Sheets of course, but we’ll use it here so that you can do the same steps in BigQuery and in Sheets.
The second dataset is a daily total of bike counts for New York’s East River bridges (source).
There’s nothing inherently wrong with putting “small” data into BigQuery. Yes, it’s designed for truly gigantic datasets (billions of rows+) but it works equally well on data of any size.
Back in the BigQuery Console, you need to set up a project before you can add data to it.
Get started with Google BigQuery: Loading data From A Google Sheet
Think of the Project as a folder in Google Drive, the Dataset as a Google Sheet and the Table as individual Sheet within that Google Sheet.
The first step to get started with Google BigQuery is to create a project.
In step 1, BigQuery will have automatically generated a new project for you, called “My First Project”.
If it didn’t, or you want to create another new project, here’s how.
Step 3: Create a new Project
In the top bar, to the right of where it says “Google Cloud Platform”, click on Project drop-down menu.
In the popup window, click NEW PROJECT.
Give it a name, organization (your domain) and location (parent organization or folder).
Optionally, you can choose to bookmark this project in the Resources section of the sidebar. Click “PIN PROJECT” to do this.
Step 4: Create a new Dataset
Next you need to create a dataset by clicking “CREATE DATASET“.
Name it “start_bigquery”. You’re not allowed to have any spaces or special characters apart from the underscore.
Set the data location to your locale, leave the other settings alone and then click “Create dataset”
This new dataset will show up underneath your project name in the sidebar.
Step 5: Create a new Table
With the dataset selected, click on the “+ CREATE TABLE” or big blue plus button.
You want to select “Drive”, add the URL and set the file format to Google Sheets.
Name your table “brooklyn_bridge_pedestrians”.
Choose Auto detect schema.
Under Advanced settings, tell BigQuery you have a single header row to skip by entering the value 1.
Your settings should look like this:
If you make a mistake, you can simply delete the table and start again.
Section 4: Analyzing Data in BigQuery
Google BigQuery uses Structure Query Language (SQL) to analyze data.
The Google Sheets Query function uses a similar SQL style syntax to parse data. So if you know how to use the Query function then you basically know enough SQL to get started with Google BigQuery!
Basic SQL Syntax for BigQuery
The basic SQL syntax to write queries looks like this:
SELECT these columns
FROM this table
WHERE these filter conditions are true
GROUP BY these aggregate conditions
HAVING these filters on aggregates
ORDER BY i.e. sort by these columns
LIMIT restrict answer to X number of rows
You’ll see all of these keywords and more in the exercises below.
Get started with Google BigQuery: First Query
The BigQuery console provides a button that gives you a starter query.
Step 6: Write your first query
Click on “QUERY TABLE” and this query shows up in your editor window:
SELECT FROM `start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians` LIMIT 1000
Modify it by adding a * between the SELECT and FROM, and reducing the number after LIMIT to 10:
SELECT * FROM `start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians` LIMIT 10
Then format your query across multiple lines with through the menu: More > Format
SELECT
*
FROM
`start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians`
LIMIT
10
Click “▶️ Run” to execute the query.
The output of this query will be 10 rows of data showing under the query editor:
(click to enlarge)
Woohoo!
You just wrote your first query in Google BigQuery.
Let’s continue and analyze the dataset:
Exercise 2: Analyzing Data In BigQuery
Run through the following steps:
Step 7: tell the story of one row
I always advocate doing this with any new dataset.
Write a query that selects all the columns (SELECT *) and a limited number of rows (e.g. LIMIT 10), as you did in step 6 above.
Run that query and look at the output. Scan across one whole row. Look at every column and think about what data is stored there.
Think about doing the equivalent step in Google Sheets. Look at your dataset and scroll to the right, telling the story of a single row.
We do this step to understand our data, before getting too immersed in the weeds.
Select Specific Columns
Step 8: Select specific columns
Select specific columns by writing the column names into your query.
You can also click on column names in the schema view (click on the table name in the left sidebar to access this) to add them to the query directly.
SELECT
hour_beginning,
location,
Pedestrians,
weather_summary
FROM
`start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians`
LIMIT
10
Math Operations
Let’s find out the total number of pedestrians that crossed the Brooklyn Bridge across the whole time period.
Step 9: Calculate total in Google Sheets
Open the Google Sheet you copied in Step 2, called “Copy of Brooklyn Bridge pedestrian count dataset”
Add this simple SUM function to cell C7298 to calculate the total:
=SUM(C2:C7297)
This gives an answer of 5,021,692
Let’s see how to do that in BigQuery:
Step 10: Math operations in BigQuery
Write a query with the pedestrians column and wrap it with a SUM function:
SELECT
SUM(Pedestrians) AS total_pedestrians
FROM
`start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians`
This gives the same answer of 5,021,692
You’ll notice that I gave the output a new column name using the code “AS total_pedestrians“. This is similar to using the LABEL clause in the QUERY function in Google Sheets
Filtering Data
In SQL, the WHERE clause is used to filter rows of data.
It acts in the same way as the filter operation on a dataset in Google Sheets.
Step 11: Filtering data in Google Sheets
Back in your Google Sheet with the pedestrian data, add a filter to the dataset: Data > Create a filter
Click on the filter on the weather_summary column to open the filter menu.
Click “Clear” to deselect all the items.
Then choose “sleet” and “snow” as your filter values.
Hit OK to implement the filter.
You end up with 61 rows of data showing only the “sleet” or “snow” rows.
Now let’s see that same filter in BigQuery.
Step 12: WHERE filter keyword
Add the WHERE clause after the FROM line, and use the OR statement to filter on two conditions.
SELECT
*
FROM
`start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians`
WHERE
weather_summary = 'snow' OR weather_summary = 'sleet'
Check the count of the rows outputted by the this query. It’s 61, which matches the row count from your Google Sheet.
Ordering Data
Another common operation we want to do to understand our data is sort it. In Sheets we can either sort through the filter menu options or through the Data menu.
Step 13: Sorting data in Google Sheets
Remove the sleet and snow filter you applied above.
On the temperature column, click the Sort A → Z option, to sort the lowest temperature records to the top.
(Quick aside: it’s amazing to still see so many people walking across the bridge in sub-zero temps!)
Let’s recreate this sort in BigQuery.
Step 14: ORDER BY sort keyword
Add the ORDER BY clause to your query, after the FROM clause:
SELECT
*
FROM
`start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians`
ORDER BY
temperature ASC;
Use the keyword ASC to sort ascending (A – Z) or the keyword DESC to sort descending (Z – A).
You might notice that the first two records that show up have “null” in the temperature column, which means that no temperature value was recorded for those rows or it’s missing.
Let’s filter them out with the WHERE clause, so you can see how the WHERE and ORDER BY fit together.
Step 15: Filter out null values
The WHERE clause comes after the FROM clause but before the ORDER BY.
Remove the nulls by using the keyword phrase “IS NOT NULL”.
SELECT
*
FROM
`start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians`
WHERE
temperature IS NOT NULL
ORDER BY
temperature ASC;
Aggregating Data
In Google Sheets, we group data with a pivot table.
Typically you choose a category for the rows and aggregate (summarize) the data into each category.
In this dataset, we have a row of data for each hour of each day. We want to group all 24 rows into a single summary row for each day.
Step 16: Pivot tables in Google Sheets
With your cursor somewhere in the pedestrian dataset, click Data < Pivot table
In the pivot table, add hour_beginning to the Rows.
Uncheck the “Show totals” checkbox.
Right click on one of the dates in the pivot table and choose “Create pivot date group“.
Select “Day of the month” from the list of options.
Add hour_beginning to Rows again, and move it so it’s the top category in Rows.
Check the “Repeat row labels” checkbox.
Right click on one of the dates in the pivot table and choose “Year-Month” from the list of options.
Add Pedestrians field to the Values section, and leave it set to the default SUM.
Your pivot table should look like this, with the total pedestrian counts for each day:
Now let’s recreate this in BigQuery.
If you’ve ever used the QUERY function in Google Sheets then you’re probably familiar with the GROUP BY keyword. It does exactly what the pivot table in Sheets does and “rolls up” the data into the summary categories.
Step 17: GROUP BY in BigQuery to aggregate data
First off, you need to use the EXTRACT function to extract the date from the timestamp in BigQuery.
This query selects the extracted date and the original timestamp, so you can see them side-by-side:
SELECT
EXTRACT(DATE FROM hour_beginning) AS bb_date,
hour_beginning
FROM
`start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians`
The EXTRACT DATE function turns “2017-10-01 00:00:00 UTC” into “2017-10-01”, which lets us aggregate by the date.
Modify the query above to add the SUM(Pedestrians) column, remove the “hour_beginning” column you no longer need and add the GROUP BY clause, referencing the grouping column by the alias name you gave it “bb_date”
SELECT
EXTRACT(DATE FROM hour_beginning) AS bb_date,
SUM(Pedestrians) AS bb_pedestrians
FROM
`start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians`
GROUP BY
bb_date
The output of this query will be a table that matches the data in your pivot table in Google Sheet. Great work!
Functions in BigQuery
You’ll notice we used a special function (EXTRACT) in that previous query.
Like Google Sheets, BigQuery has a huge library of built-in functions. As you make progress on your BigQuery journey, you’ll find more and more of these functions to use.
For more information on functions in BigQuery, have a look at the function reference.
We saw the WHERE clause earlier, which lets you filter rows in your dataset.
However, if you aggregate your data with a GROUP BY clause and you want to filter this grouped data, you need to use the HAVING keyword.
Remember:
WHERE = filter original rows of data in dataset
HAVING = filter aggregated data after a GROUP BY operation
To conceptualize this, let’s apply the filter to our aggregate data in the Google Sheet pivot table.
Step 18: Pivot table filter in Google Sheets
Add hour_beginning to the filter section of your pivot table in Google Sheets.
Filter by condition and set it to Date is before > exact date > 11/01/2017
This filter removes rows of data in your Pivot Table where the data is on or after 1 November 2017. It leaves just the October 2017 data.
By now, I think you know what’s coming next.
Let’s apply that same filter condition in BigQuery using the HAVING keyword.
Step 19: HAVING filter keyword
Add the HAVING clause to your existing query, to filter out data on or after 1 November 2017.
Only data that satisfies the HAVING condition (less than 2017-11-01) is included.
SELECT
EXTRACT(DATE FROM hour_beginning) AS bb_date,
SUM(Pedestrians) AS bb_pedestrians
FROM
`start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians`
GROUP BY
bb_date
HAVING
bb_date < '2017-11-01'
The output of this query is 31 rows of data, for each day of the month of October.
Get started with Google BigQuery: Joining Data
A SQL Query walks into a bar.
In one corner of the bar are two tables.
The Query walks up to the tables and asks:
Mind if I join you?
JOIN pulls multiple tables together, like the VLOOKUP function in Google Sheets. Let's start in your Google Sheet.
Step 20: Vlookup to join data tables in Google Sheets
Create a new blank Sheet inside your Google Sheet.
Drag the formula down the rows to complete the dataset.
The data in your Sheet now looks like this:
That's great!
We summarized the pedestrian data by day and joined the bicycle data to it, so you can compare the two numbers.
As you can see, there's around 10k - 20k pedestrian crossings/day and about 2k - 3k bike crossings/day.
Joining tables in BigQuery
Let's recreate this table in BigQuery, using a JOIN.
Step 21: Upload bicycle data to BigQuery
Following step 5 above, create a new table in your start_bigquery dataset and upload the second dataset, of bike data for NYC bridges from October 2017.
Name your table "nyc_bridges_bikes"
Your project should now look like this in the Resources pane in the left sidebar:
What we want to do now is take the table the you created above, with pedestrian data per day, and add the bike counts for each day to it.
To do that we use an INNER JOIN.
There are several different types of JOIN available in SQL, but we'll only look at the INNER JOIN in this article. It creates a new table with only the rows from each of the constituent tables that meet the join condition.
In our case the join condition is matching dates from the pedestrian table and the bike table.
We'll end up with a table consisting of the date, the pedestrian data and the bike data.
Ready? Let's go.
Step 22: JOIN the datasets in BigQuery
First, wrap the query you wrote above with the WITH clause, so you can refer to the temporary table that's created by the name "pedestrian_table".
WITH pedestrian_table AS (
SELECT
EXTRACT(DATE FROM hour_beginning) AS bb_date,
SUM(Pedestrians) AS bb_pedestrians
FROM
`start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians`
GROUP BY
bb_date
HAVING
bb_date < '2017-11-01'
)
Next, select both columns from the pedestrian table and one column from the bike table:
SELECT
pedestrian_table.bb_date,
pedestrian_table.bb_pedestrians,
bike_table.Brooklyn_Bridge AS bb_bikes
FROM
pedestrian_table
Of course, you need to add in the bike table to the query so the bike data can be retrieved:
INNER JOIN
`start-bigquery-294922.start_bigquery.nyc_bridges_bikes` AS bike_table
Finally, specify the join condition, which tells the query what columns to match:
ON
pedestrian_table.bb_date = bike_table.Date
Phew, that's a lot!
Here's the full query:
WITH pedestrian_table AS (
SELECT
EXTRACT(DATE FROM hour_beginning) AS bb_date,
SUM(Pedestrians) AS bb_pedestrians
FROM
`start-bigquery-294922.start_bigquery.brooklyn_bridge_pedestrians`
GROUP BY
bb_date
HAVING
bb_date < '2017-11-01'
)
SELECT
pedestrian_table.bb_date,
pedestrian_table.bb_pedestrians,
bike_table.Brooklyn_Bridge AS bb_bikes
FROM
pedestrian_table
INNER JOIN
`start-bigquery-294922.start_bigquery.nyc_bridges_bikes` AS bike_table
ON
pedestrian_table.bb_date = bike_table.Date
You'll notice that the names of the columns in our SELECT clause are preceded by the table name, e.g. "pedestrian_table.bb_date".
This ensures there is no confusion over which columns from which tables are being requested. It’s also necessary when you join tables that have common column headings.
The output of this query is the same as the table you created in your Google Sheet step 20 (using the pivot table and VLOOKUP).
Formatting Your Queries
Last couple of things to mention with the SQL syntax is how to add comments and format your queries.
Step 23: Formatting Your Queries
You can add comments in SQL two ways, with a double dash "--" or forward slash and star combination "/*...*/".
-- single line comment, ignored when the program is run
or
/* multi-line comment
everything between the slash-stars
is ignored by the program when it's run */
It's also a good habit to put SQL keywords on separate lines, to make it more readable.
Use the menu More > Format to do this automatically.
Section 5: Export Data Out Of BigQuery
You have a few options to export data out of BigQuery.
In the Query results section of the editor, click on the "SAVE RESULTS" button to:
Save as a CSV file
Save as a JSON file
Export query results to Google Sheets (up to 16,000 rows)
Copy to Clipboard
In this tutorial, we're going to export the data out of BigQuery and back into a Google Sheet, to create a chart. We're able to do this because the summary dataset we've created is small (it's aggregated data we want to use to create a chart, not the row-by-row data).
Explore BigQuery Data in Sheets or Data Studio
If you want to create a chart based on hundreds of thousands or millions of rows of data, then you can explore the data in Google Sheets or Data Studio directly, without taking it out of BigQuery.
Click on the "EXPLORE DATA" option in the Query results section of the editor:
Explore in Google Sheets using Connected Sheets (Enterprise customers only)
Explore directly in Data Studio
Get started with Google BigQuery: Export to Google Sheets
In this tutorial, the output table is easily small enough to fit in Google Sheets, so let's export the data out of BigQuery and into Sheets.
There, we'll create chart a chart showing the pedestrian and bike traffic across the Brooklyn Bridge.
Step 24: Export Data Out Of BigQuery
Run your query from step 22 above, which outputs a table with date, pedestrian count and bike count.
Click on the "SAVE RESULTS" and select Google Sheets.
Hit Save.
Select Open in the toast popup that tells you a new Sheet has been created, or find it in your Drive root folder (the top folder).
The data now looks like this in the new Sheet:
Yay! Back on familiar territory!
From here, you can do whatever you want with your data.
I chose to create a simple line chart to compare the daily foot and bike traffic across Brooklyn Bridge:
Step 25: Display the data in a chart in Google Sheets
Highlight your dataset and go to Insert > Chart
Select the line chart (if it isn't selected as the default).
Fix the title and change the column names to display better in chart.
Under the Horizontal Axis option, check the "Treat labels as text" checkbox.
See how much information this chart gives you, compared to thousands of rows of raw data.
It tells you the story of the pedestrian and bike traffic crossing the Brooklyn Bridge.
Congratulations!
You've completed your first end-to-end BigQuery + Google Sheets data analysis project.
