The SUBTOTAL function in Google Sheets is a powerful function for working with data.
There are three principal uses for the SUBTOTAL function:
Calculating Subtotals for lists of data
Calculating metrics with filtered and/or hidden rows of data
As a dynamic function selector
It’s versatile, as you’ll see in this tutorial.
However, it’s not well known and I suspect it’s vastly under utilized. It’s not an easy function for beginners to use, because it involves using a function code to control how it operates, as you’ll see below.
In this tutorial, you’ll use Apps Script to build a tool called Sheet Sizer, to measure the size of your Google Sheets!
Google Sheets has a limit of 10 million cells, but it’s hard to know how much of this space you’ve used.
Sheet Sizer will calculate the size of your Sheet and compare it to the 10 million cell limit in Google Sheets.
Sheet Sizer: Build a sidebar to display information
Step 1:
Open a blank sheet and rename it to “Sheet Sizer”
Step 2:
Open the IDE. Go to Tools > Script editor
Step 3:
Rename your script file “Sheet Sizer Code”
Step 4:
In the Code.gs file, delete the existing “myFunction” code and copy in this code:
/**
* Add custom menu to sheet
*/
function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Sheet Sizer')
.addItem('Open Sheet Sizer', 'showSidebar')
.addToUi();
}
/**
* function to show sidebar
*/
function showSidebar() {
// create sidebar with HTML Service
const html = HtmlService.createHtmlOutputFromFile('Sidebar').setTitle('Sheet Sizer');
// add sidebar to spreadsheet UI
SpreadsheetApp.getUi().showSidebar(html);
}
There are two functions: onOpen, which will add the custom menu to your Sheet, and showSidebar, which will open a sidebar.
The text between the /* ... */ or lines starting with // are comments.
Step 5:
Click the + next to Files in the left menu, just above the Code.gs filename.
Add an HTML file and call it “Sidebar”. It should look like this:
Step 6:
In the Sidebar file, on line 7, between the two BODY tags of the existing code, copy in the following code:
Step 8:
Select the Code.gs file, then select the onOpen function in the menu bar (from the drop down next to the word Debug). Then hit Run.
Step 9:
When you run for the first time, you have to accept the script permissions. If you see an “App isn’t verified” screen, click on Advanced, then “Go to…” and follow the prompts. (More info here.)
Step 10:
After authorizing the app in step 8, jump back to your Google Sheet. You should see a new custom menu “Sheet Sizer” in the menu bar, to the right of the Help menu.
Click the menu to open the sidebar.
Step 11:
Close the menu using the button.
Here’s what you’ve built so far:
Sheet Sizer: Add a new button and functionality to the sidebar
Step 12:
In the Sidebar file, after the first BODY tag, on line 6, and before the INPUT tag on line 7, add a new line.
When clicked, this will run a function called getSheetSize.
Step 13:
Add the getSheetSize function into the Sidebar file with the following code.
Copy and paste this after the two INPUT tags but before the final BODY tag, on line 9.
<script>
function getSheetSize() {
google.script.run.auditSheet();
}
</script>
When the button is clicked to run the getSheetSize function (client side, in the sidebar), it will now run a function called auditSheet in our Apps Script (server side).
Step 14:
Go to the Code.gs file
Step 15:
Copy and paste this new function underneath the rest of your code:
/**
* Get size data for a given sheet url
*/
function auditSheet() {
// get Sheet
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getActiveSheet();
// get sheet name
const name = sheet.getName();
// get current sheet dimensions
const maxRows = sheet.getMaxRows();
const maxCols = sheet.getMaxColumns();
const totalCells = maxRows * maxCols;
// output
SpreadsheetApp.getUi().alert(totalCells);
}
This gets the active Sheet of your spreadsheet and calculates the total number of cells as max rows multiplied by max columns.
Finally, the last line displays an alert popup to show the total number.
Step 16:
Back in your Google Sheet, run Sheet Sizer from the custom Sheet Sizer menu.
When you click on the “Get Sheet Size” button, you should see a popup that shows the number of cells in your Sheet:
Sheet Sizer: Display the Sheet size in the sidebar
Step 17:
Delete this line of code in the auditSheet function:
SpreadsheetApp.getUi().alert(totalCells);
Step 18:
Paste in this new code, to replace the code you deleted in Step 4:
// put variables into object
const sheetSize = 'Sheet: ' + name +
'<br>Row count: ' + maxRows +
'<br>Column count: ' + maxCols +
'<br>Total cells: ' + totalCells +
'<br><br>You have used ' + ((totalCells / 5000000)*100).toFixed(2) + '% of your 10 million cell limit.';
return sheetSize;
Now, instead of showing the total number of cells in an alert popup, it sends the result back to the sidebar.
Let’s see how to display this result in your sidebar.
Step 19:
Go to the Sidebar file and copy this code after the two INPUT tags but before the first SCRIPT tag:
<div id="results"></div>
This is a DIV tag that we’ll use to display the output.
Step 20:
Staying in the Sidebar file, replace this line of code:
It runs when the Apps Script function auditSheet successfully executes on the server side. The return value of that auditSheet function is passed to a new function called displayResults, which we’ll create now.
Step 21:
Underneath the getSheetSize function, add this function:
function displayResults(results) {
// display results in sidebar
document.getElementById("results").innerHTML = results;
}
When this function runs, it adds the results value (our total cells count) to that DIV tag of the sidebar you added in step 7.
Step 22:
Back in your Google Sheet, run Sheet Sizer from the custom Sheet Sizer menu.
When you click on the “Get Sheet Size” button, you should see a popup that shows the number of cells in your Sheet:
Sheet Sizer: Handle multiple sheets
Step 23:
Modify your auditSheet code to this:
/**
* Get size data for a given sheet url
*/
function auditSheet(sheet) {
// get spreadsheet object
const ss = SpreadsheetApp.getActiveSpreadsheet();
// get sheet name
const name = sheet.getName();
// get current sheet dimensions
const maxRows = sheet.getMaxRows();
const maxCols = sheet.getMaxColumns();
const totalCells = maxRows * maxCols;
// put variables into object
const sheetSize = {
name: name,
rows: maxRows,
cols: maxCols,
total: totalCells
}
// return object to function that called it
return sheetSize;
}
Step 24:
In your Code.gs file, copy and paste the following code underneath the existing code:
/**
* Audits all Sheets and passes full data back to sidebar
*/
function auditAllSheets() {
// get spreadsheet object
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheets = ss.getSheets();
// declare variables
let output = '';
let grandTotal = 0;
// loop over sheets and get data for each
sheets.forEach(sheet => {
// get sheet results for the sheet
const results = auditSheet(sheet);
// create output string from results
output = output + '<br><hr><br>Sheet: ' + results.name +
'<br>Row count: ' + results.rows +
'<br>Column count: ' + results.cols +
'<br>Total cells: ' + results.total + '<br>';
// add results to grand total
grandTotal = grandTotal + results.total;
});
// add grand total calculation to the output string
output = output + '<br><hr><br>' +
'You have used ' + ((grandTotal / 5000000)*100).toFixed(2) + '% of your 10 million cell limit.';
// pass results back to sidebar
return output;
}
This adds a new function, auditAllSheets, which loops over all the sheets in your Google Sheet and calls the auditSheet function for each one. The results for each Sheet are joined together into a result string, called output.
The callback function is the general auditAllSheets function, not the specific individual sheet function.
Step 26:
Back in your Google Sheet, add another sheet to your Google Sheet (if you haven’t already) and run Sheet Sizer.
It will now display the results for all the sheets within your Google Sheet!
Sheet Sizer: Add CSS styles
This step is purely cosmetic to make the sidebar more aesthetically pleasing.
Step 27:
Add these CSS lines inside the HEAD tags of the sidebar file:
<!-- Add CSS code to format the sidebar from google stylesheet -->
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<style>
body {
padding: 20px;
}
</style>
One final improvement from me is to move the 10 million cell limit number into a global variable.
Currently, it’s buried in your script so it’s difficult to find and update if and when Google updates the Sheets cell limit.
It’s not good practice to hard-code variables in your code for this reason.
The solution is to move it into a global variable at the top of your Code.gs file, with the following code:
/**
* Global Variable
*/
const MAX_SHEET_CELLS = 10000000;
And then change the output line by replacing the 5000000 number with the new global variable MAX_SHEET_CELLS:
// add grand total calculation to the output string
output = output + '<br><hr><br>' +
'You have used ' + ((grandTotal / MAX_SHEET_CELLS)*100).toFixed(2) + '% of your 10 million cell limit.';
Here is your final Sheet Sizer tool in action:
Click here to see the full code for the Sheet Sizer tool on GitHub.
Next steps: have a go at formatting the numbers shown in the sidebar, by adding thousand separators so that 1000 shows as 1,000 for example.
Several times I walked towards the corner office but turned around, on the pretense of needing to do something else first.
Finally, there was nothing left but to do it.
As I walked, robotically, towards my boss’s office, my pulse quickened and heat rose through my head.
I was so focused on knocking on the door, on willing myself past that point of no return, that when I entered the boss’s office, my carefully rehearsed words spilled out my mouth in a nervous jumble. Words I’d confidently spoken a hundred times in my head.
In my head I had pictured a triumphant scene – the “I quit!” scene – but the reality was a somewhat awkward, anticlimactic conversation.
The news came as a surprise to my boss. He wanted to understand.
“No, it’s not the work…”
“That’s a generous offer to stay, but I’ve made up my mind…”
“I want to focus on getting a job in the tech industry…”
That last statement sounded hollow and vague, even to me.
Did I believe it myself? What did it even mean? I felt the heat rising in my head again.
What will I do next?
Truthfully, I didn’t know.
News traveled fast along the corridors and soon the whole office knew.
Most were incredulous that I would leave a job without a new job to go to. I projected confidence and recited my spiel about wanting to break into the tech industry because it’s my passion.
But in reality, I felt like a vertigo sufferer on a cliff edge, my head swimming.
I was saved by my innermost kernel: my determination to succeed and make a dent in the world.
It’s my most powerful ally, always there to fight my cause. All I had to do was learn how to channel it.
Two weeks later, the change was real and permanent. I woke up and didn’t go to the office. It was a disorientating feeling that took me years to acclimate to.
I spent that summer cycling around the US with my brother, then I got married in the fall. Yet through those joyous events, I carried anxiety, not knowing exactly where my career would go next.
The Wilderness Years: 2014 – 2015
Act 1: A Forensic Accounting Newsletter
My first entrepreneurial idea was to start a paid newsletter for the Forensic Accounting industry, the industry where I’d worked for 8 years and just left.
It didn’t exist, it was specialist knowledge that people would pay for, and my experience qualified me to deliver it.
Paid newsletters are in vogue right now, but not back in 2014. But that was attractive. It added a technology angle to this project: I had to figure out how to deliver and charge for the newsletter.
I spent a month working hard on this idea.
I interviewed people in the industry. I wrote many draft editions of the email. I created a business plan and put together a list of 100 contacts in the industry who would receive the first newsletter.
Mockup of Issue 1 of the White Wire, a Forensic Accounting Industry Newsletter
I was just a few days shy of sending the first email when I knew something wasn’t right.
I wasn’t excited by this idea. At all.
In fact, I wanted to get away from the corporate accounting world and blaze my own trail. This wasn’t the solution I was looking for.
I clearly remember the conversation with my wife:
“I can’t do this. I’m not passionate about this industry so I won’t be successful. I’m passionate about the technology around the newsletter and the design of it, but not the contents.”
(By the way, I still think this newsletter is a good idea. Just not a good idea for me.)
It was back to the drawing board.
Act 2: Wannabe Web Developer
I didn’t have another entrepreneurial dream at this stage. But I loved the VBA and SQL coding parts of my old job, so I knew what I wanted to do next.
I wanted to become a developer.
I signed up for a part-time web development course with the education startup General Assembly.
I poured my heart and soul into coding for the next six months.
I built a social learning application called UpLearn:
Crucially during this time, I did what every tech job seeker is advised to do and started this blog to share my knowledge.
Version 1 of benlcollins.com
I started the blog as a way to showcase my skills to future employers, not expecting it to become a business in its own right.
I started applying for junior web developer roles in the spring of 2015. I applied to 10 positions but didn’t hear back from a single one. Nada. Zip. Not even an acknowledgment of my application.
It was hard to take. I was frustrated. I knew I could do it. Why couldn’t they see this?
I became more and more despondent with each passing day. Act 2 of my own career had yielded nothing either.
Looking back now, this period was full of doubt, as strong as any other time in my life.
Maybe I wasn’t cut out for this. Maybe my destiny was to be a lifetime accountant.
Maybe I could find a way to be happy doing that.
Maybe I should call up my old work again.
But before that happened, the universe sent a lifeline my way…
I got my first inbound client request.
Gaining Traction: 2016 – 2017
Act 3: Freelance Data Analyst
My first client messaged me after reading my Google Sheets dashboard post. He asked if I could build something similar for his real estate business. “Yes, of course!” I said.
Soon a trickle of inbound client work was coming from my website. Projects were all across the board: from Google Sheets dashboards to SQL data analysis to corporate training with Tableau.
It was just enough work to postpone the job hunt and call myself a freelancer.
When General Assembly announced they were looking for instructors for their new Data Analysis course, I applied.
Hardly anyone else was writing about Google Sheets at the time, so it was a huge opportunity.
I was intimately familiar with the cottage industry built on the back of Microsoft Excel – consultants, course creators, bloggers – so I reasoned there was a good chance something similar would exist for Google Sheets years from now.
And I intended to be at the forefront of that industry.
Act 5: Course Creator
For years, I’d contemplated the idea of creating an online course, inspired by the Excel gurus like Chandoo, who made their living teaching spreadsheets.
In mid-2016 I committed to creating a Google Sheets course. Just one. I would give it a try and see what happened. If it was successful, then I could do it again. If not, then no problem, I could keep going with my freelance consulting.
I chose Dashboards as the first-course topic. I’d seen good traction on those posts on my website so I knew it was something people wanted to learn.
It took me about six months – alongside my existing consulting work – to create the dashboards and record the lessons, making many mistakes along the way.
I made $7,000 in the week I launched the course, to an email list of around 2,000.
To me, it was a huge success. Certainly enough to convince me to create a second course.
I recorded and launched a Data Cleaning course – now part of the Data Analysis course – between March and May 2017. I was incentivized to finish this one because my second son was born at the end of May.
Building a Business: 2018 – 2020
By the end of 2017, I was making a modest salary from my business. But it was still some way short of my old corporate earnings.
I still struggled with the process of working for myself. In my previous jobs, I’d always had a boss (or drill sergeant) telling me what to do next.
Now I was on my own.
And I clearly needed help building good work habits so I could scale my business. I felt like I started from scratch with each new client project. I had no systems in place. I didn’t have a consistent pricing methodology.
She taught me how to evaluate opportunities. How to sell my work. How to write convincing marketing copy.
And she helped me start a weekly Google Sheets tips newsletter in April 2018, which has grown to be one of the fundamental pillars of my business.
If you’re thinking about hiring a business coach, do it. It’s one of the best investments you can make for your business and yourself.
In 2019, Google invited me to be part of the Google Developer Expert program, as recognition of my work on Google Sheets and Apps Script. This has been an amazing community to be a part of. I’ve enjoyed working closely with other members and Google team members.
Act 6: Growing The Online School
Between 2018 and 2020, I released a whole series of online courses. They sold well.
Most importantly, I’ve kept up my weekly newsletter habit, started in 2017, so that my audience is engaged and familiar with me and my course offerings.
But my courses looked amateur because I’d created all the graphics myself. And I knew the recording quality could be improved.
So my big goal for 2020 was to update the entire catalog.
At the beginning of 2020 I hired a design firm to create a new brand for this site and my courses.
I then embarked on a journey to overhaul every course in my catalog. I thought it would take me six months.
But the pandemic hit…
Work hours were reduced as I shared child care with my wife, who also has a career to sustain.
In the end, it took me a year to complete the course updates (not full-time). I uploaded the last video for the Automation With Apps Script course in late February 2021, complete with professional branding.
The Future: 2021 onwards
Now, in March 2021, my business is at an inflection point.
These are always fascinating times for an entrepreneur.
You’re reinventing yourself again. Starting over. Shedding your skin.
My existing online courses generate a six-figure profit annually, more than I used to make at my corporate job at a law firm.
But it’s time for me to grow again. Online education is evolving and some students crave more than on-demand courses alone can provide.
With the shift to zoom-first work culture in the past year, live training courses, often called Cohort-Based-Courses (CBCs), have exploded in popularity.
It’s not hard to understand why. Traditional on-demand online courses only work for the most motivated students, ones who have the discipline (and time!) to sit down and work through the content.
What the on-demand courses lack is accountability and community, two aspects that deliver huge benefits to students.
And that’s where these next-gen cohort courses come in. They offer you that accountability and community, as well as direct access to the expert teachers behind the videos. They’re a step closer to in-person training, combined with the convenience of remote learning.
I’m working on a new cohort course, called Pro Sheets Accelerator. It’s a five-week course, meeting twice a week for a 90-minute live session, plus office hours, community and a capstone project.
I’m really excited to bring this to life and share more details over the coming weeks.
My online, on-demand course library, now fully updated, remains available for students who prefer to learn that way. But this new live format course offers more opportunities for students to learn, grow and support each other.
Shifting my energy to this live cohort course model feels like a tipping point that will propel me forward to bigger things.
It took me years to get to this point, to find that sweet spot where my skills and experience matches what the market is looking for. Along the way, I’ve built an engaged audience of folks who can benefit from my work.
The first cohort of my live training course, Pro Sheets Accelerator, is just the beginning of this next Act.
My mission is to create a world-class online school, teaching productivity, data analysis, and workflow automation, using Google tools.
If you have a Nest thermostat at home, you can access it from your Google Sheet by using Google Apps Script to connect to the Smart Device Management API.
It means you can do some cool stuff like build a virtual, working Nest thermostat in your Google Sheet:
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:
