Google Sheets and Apps Script Blog Posts From Ben Collins

2024 In Review And A Look Forward To 2025

Welcome to Annual Review number 10! Ten! TEN!!

Sunrise over the Potomac River from a hike in October 2024

Ten years in business. Ten years working for myself. As I write this, I still can’t quite believe that it’s been ten years.

In some ways it feels like only yesterday that I was wearing a suit to an office on K-Street in Washington DC, and yet, also, it feels like I’ve been doing this for a really long time now.

I want to go back and give my naive 2015 self a quick pep talk and say “it’s hard but it works out ok!”, when I was flailing around after quitting my accounting job.

Here’s a look at 2024:

2024 Highlights

The work highlight of 2024 was launching my new membership:

⭐️ Sheets Insiders ⭐️

I worked hard to launch this in September and feel like the work over the past few months has been some of the best I’ve ever done.

We’re up to 414 members now and have covered data visualization techniques, drop-down menus in detail, analytical functions, the new Google Sheet Tables in detail, user interface with Sheets elements, and, of course, AI.

And, we have a 3-part Apps Script beginner workshop coming this January (it’s not too late to join!).

In addition to live workshops, members enjoy an exclusive weekly newsletter, packed with Sheets tutorials, news, and templates.

Tables Template for Sheets Insiders — Template #6 for Sheets Insiders on the new Tables feature

Advanced chart tutorial for Sheets Insiders members

Interested? Join today and get instant access to the full archives!

Google Sheets Tips Newsletter

I sent 44 Google Sheets Tips newsletters this year, starting with #285 in January and ending with #328 in December. I really enjoy putting the weekly newsletter together and I’m still blown away that it goes out to nearly 55,000 people every week. 🤯

If you’re one of those folks, thank you for your support!

And if you’re not, and you’d like to level up your Sheets skills with a bite-size tutorial each week, you can sign up here.

Website

This year I published 14 new and updated posts on this website. My favorite posts to research and write were:

Play-It-Through Chess Game in a Single Google Sheets Formula

Play It Through Chess Game in Google Sheets Formula

Formula Pie Charts in Google Sheets

Sparkline Pie Charts in Google Sheets

Website traffic declined again this year, following the trend from last year.

I think there are a combination of factors at play, including: 1) more competition from other Google Sheets sites, 2) competition for search traffic generally from AI chatbots, and 3) younger audiences consuming content in video form, via YouTube or social media.

Website Traffic 2024

YouTube

I focused on my YouTube channel for the first few months of 2024 and saw good results, finally reaching 10k subscribers! After this initial burst of energy at the beginning of the year, things slowed down and I’ve only had minimal growth since then.

Google Partnership

One of the highlights of every year is working closely with the Google Sheets team to provide feedback on new features and this year was no exception.

I was also honored to continue as a Google Developer Expert and be part of the Champions Innovator program for another year. 🙏

Google Workspace Expert 2024

Craft & Commerce Conference

This was so fun!

Working with AI Tools

It’s crazy how fast these tools are improving, although there are signs those gains are slowing down. But what’s really crazy is how good these tools are getting at doing my job! Yikes 😉

From generating formulas, explaining concepts, writing code, or even drafting blog posts, gen AI can seemingly do it all now.

It’s very clear that what has worked incredibly well for me the past 10 years will not carry me forward for another 10. I’m okay with this though.

One of the things I love most about my job is all the learning I get to do. I’ve always considered adaptability one of my strengths and I’m excited to jump into new technologies this year.

Obviously, AI will be a big focus of my work for 2025 (mostly in a spreadsheet context of course).

Non-Work Highlights

Although a foot injury slowed me down for the second half of 2024, I have many wonderful memories of adventures with family, friends, and solo, to savor from last year.

The 2024 adventure highlights included:

A 4-day trek in the Julian Alps of Slovenia with my wife
A 5-day solo hike on the Appalachian Trail in March
Progressing the sport of bike SUP! I ride to the river put-in, paddleboard downriver, then ride home 😉
A backpack along the AT with Lexi and our boys to celebrate our 9-yo’s birthday
Wonderful walks in the UK with my parents and brother, when our trips home lined up
An awesome 3-day packrafting course in Colorado with Four Corners Guides
A 23 mile day hike along the Appalachian Trail with friends over summer
Doing the Harpers Ferry 3-peaks again (~24 miles, 4.5k ft vert), via bike-and-hike with a friend
Paddleboard adventures with the family on a new lake (Smith Mountain Lake, Virginia)
Getting started on my birding journey and participating in an amazing bird + coding course with Jer Thorp
Picking up regular swimming again, when I take my son to his team practice

Hiking in Slovenia — Hiking in Julian Alps of Slovenia

A wet camp on the Appalachian Trail — A wet camp during my 5 days on the Appalachian Trail in March

Bike strapped to the front of a paddleboard — 2024 featured many bike & paddleboard adventures!!

2024 Challenges

Another lap around the sun and 2024 is in the rear view mirror.

The challenges of 2023 — creative burnout, feeling like I’m running on a treadmill balancing work, parenting and life — continued to affect me in 2024.

I realized in the spring that I didn’t have the enthusiasm for another Google Sheets course, and it took me a while to figure out what my next step was. I was sick for a while in the spring too (a rough cold and then a cough that rumbled on for months), which didn’t help.

A break, participating as a student in an online course myself, and a change of approach (shifting from courses to membership has helped).

I’m looking forward to starting afresh this year and delivering consistent, high-quality teaching through the Sheets Insiders membership.

Looking Forward To 2025

2025 Work Goals

Similar to the past few years, I have a couple of annual goals that I strive for:

Send my weekly Google Sheets newsletter every week, except for holidays and a short summer break
Hit 60k newsletter subscribers

I also have internal (non-public) goals for my business, around revenue and subscriber numbers for the Sheets Insiders membership. I want to grow the membership in 2025.

Other 2025 Goals

My 2025 non-work goals are similar to my 2024 ones, mostly to get out for lots of outdoor adventures with friends and family:

Have another healthy year
A C&O canal bike tour with my boys
Get fit on the bike again (and do a 200 mile double century ride!)
Join a hiking or biking club to meet more people to go adventuring with
An adventure trip with my brother
A trip home to see family in the UK
Weekly brainstorming hike with my wife
Read 20 books

Thank You!

Thank you for your support on this journey.

A huge thank you if you read my newsletter, have joined Sheets Insiders, or taken one of my online courses. I hope they’re helpful to you!

Best wishes for 2025!

Previous years

How to Use Google Sheets in Your Google Sites

This is a guest post from Kyle Horst of Kirksville Web Design.

Introduction

Google Sites is an amazing app for bringing together information across an organization’s Google Workspace domain. However, Google Sites doesn’t natively have a CMS, databases or even a decent option for basic tables. Therefore, Google Sheets is hugely important for building a Google Sites hub that serves up fresh data to teams.

Let’s discuss key use cases for Google Sheets in Google Sites and how to implement them. Also, we’ll learn about how to effectively utilize Google Sheets for organizing, sharing and managing information. There are some limitations when integrating Sheets into a Google Site, which we’ll also cover.

How to Embed a Sheet and Adjust Settings

The process for inserting a Sheet into Google Sites is streamlined. You won’t have to deliberately publish the Sheet(s) or any special iframe code.

Here’s how easy it is:

Enter editing mode on your site
Select “Insert” from the right panel
Choose “Drive” from the top four primary options
Select the Google Sheets file from your Drive or Shared Items
Drag and drop the sheet into place on the page and confirm sharing settings
Check embedded widget settings to select the default displayed sheet tab

Your embedded Sheet may look different than what you natively see in the full Sheets version. For instance, not all custom fonts are supported for your embedded Sheet. So a tip is to stick to basic universal fonts like Arial, Georgia, or Trebuchet.

In addition to this in-page widget, you can use the Google Sites full page embed for a more full screen sheets experience. This user experience is very useful for comprehensive project trackers because the scrolling is easier to manage.

Charts from sites can also be embedded into Google Sites if you’ve already generated them within your Google Sheet. The sheets and charts stay synced (at least upon page load), so you only need to manage your data in one location.

Google Sheets for Workspace Intranets Examples

1. Project Management and Task Tracking

A Google Sites intranet is designed to be a knowledge hub and a source of truth, so it makes sense that important information be accessible. Checklists and gantt charts created with Sheets can be embedded directly into intranet pages. There’s no need to launch Drive, dig in various folders or even use search.

Here’s an example of an intranet developed by Kirksville Web Design using Google Sites. In this case, you’ll see an onboarding portal’s checklist using emoji, dropdowns, checkboxes and images. Convert this type of sheet to a table for an even more polished look.

2. People Directories and Org Charts

You can maintain an up-to-date employee directly really easily with Sheets. Common data points include phone numbers, email addresses, roles and departments. Employ smart chips in sheets for a more advanced directory.

Google Sheets itself also supports org charts, which rely on a role hierarchy to be generated. Once you have everything prepared in Sheets, drop it right into the Workspace company intranet powered by Google Sites. Power users can go a step further by utilizing Apps Script to import active directory contacts.

3. Deploy a Simple CMS (Content Management System)

Build a project tracker, requests tracker or other CMS using Sheets connected to Google Forms. Reorganize and reformat user submitted data sent to Sheets.

You can embed both the form and the resulting CMS sheet into a private Google Site. Use cases include survey results, job applications or event registrations.

You can even create a dashboard that integrates directly with a Sites company wiki.

Caveats

Embedded Google Sheets don’t behave exactly like the in-app counterpart. For instance, tables won’t have the same easy filters installed on column headings. You can’t live edit spreadsheet data from the embedded version. Likewise, you won’t see a fully live, collaborative sheet inserted into your website or wiki.

Conclusion

Sheets + Sites is an intuitive low-cost option for businesses to consider for intranet solutions.

Gemini AI within Sheets also makes the whole experience that much easier to get off the ground.

Written By

Kyle Horst is a developer specializing in the Google Sites apps. He builds custom intranet solutions for enterprises to help increase employee knowledge, engagement, and performance.

Learn more about using Google Sites for intranet applications and explore Kyle’s professional templates here: Kirksville Web Design

Introducing The Sheets Insiders Membership Program

Sheets Insiders Graphic

DENIZENS OF THE SHEETS UNIVERSE:

I’m launching something special.

I’ve been thinking about it for over a year.

And today I’m excited to invite you to join the Sheets Insiders membership program.

In this post, I want to explain what Sheets Insiders is and why I’m launching a membership program.

What Is Sheets Insiders?

Sheets Insiders is a paid membership program for folks like you that rely on Google Sheets as part of their daily work.

I created it to help you do your job better.

Through weekly content, you’ll expand your skill set, keep up-to-date with the latest and greatest features of Google Sheets, flex your formula muscles with regular challenges, keep abreast of the AI + Sheets world, and continue to enjoy in-depth tutorials on key topics.

In a little more detail, here’s what you’ll get as a member:

Weekly members-only newsletter
Regular formula challenges and solution tutorials
Exclusive content such as templates and deep-dives
AI + Sheets updates and tutorials to stay on top of the AI revolution
Access to archives of all past Sheets Insiders issues and content
Behind-the-scenes insights
And more!

Become a Sheets Insiders today

What Does Sheets Insiders Cost?

Sheets Insiders will cost $199/yr.

During this launch week, Sheets Insiders will be available for $149 for the first year.

Why Sheets Insiders?

I published my first Google Sheets tutorial 10 years ago, in October 2014. Since then, I’ve written over 250 tutorials or 300,000 words and published them all for free on this site.

And since April 2017, I’ve been publishing my free, weekly Google Sheets Tips newsletter. Each week I share a tutorial and the latest news from the Sheets world. In the intervening 6 years, I’ve sent it every week (except holidays) and we’re now over 300 editions and 55,000 readers.

I’ve also launched 3 free online courses with over 70,000 students and ran a popular online Google Sheets conference with thousands of attendees.

We live in an amazing age where it’s possible for an individual contributor to reach a global audience with their ideas. I’m incredibly fortunate to be in that position.

Suffice to say, teaching has been the most fulfilling work of my career and I’m proud of what I’ve achieved.

I want to continue doing this for the next five years, then the next five after that.

This membership is a way you can support me.

So if you enjoy my work and want to see more of it, please consider becoming a Sheets Insiders Member.

Why A Membership Program Instead Of A Course?

For years I’ve created in-depth online courses, teaching folks how to use Google Sheets to analyze their data.

But when I sat down to create another new course earlier this year, I realized a few fundamental truths:

Most online courses aren’t completed, so folks miss out on amazing content. I’ve heard from many of you, with busy work and family lives, how difficult it is to find the time to work though 50+ videos. It feels overwhelming. This new format is much more digestible so that you’ll learn something new and actionable each week.
Technology, particularly AI, advances so quickly that courses that take months to create are out–of-date before they’re published. This membership format ensures that you’re getting new material, as soon as it’s readily available.
My core mission is to help as many folks as possible with Google Sheets, data analysis, and automation. Over time, the courses have become increasingly specialized, which appeal to a smaller and smaller audience. This new format is much more flexible, so we can cover more topics that are relevant to you.

For these reasons, I set out to find a different model to best serve you. One that would deliver fresh content in a more timely and manageable way.

A business model that will be sustainable for me in the long run.

So I’m taking everything I’ve learned over the past 10 years of content creation and putting it to use in this new membership.

You’ll receive content in a weekly newsletter with exclusive, new resources. It’s a format that you can digest and implement quickly.

These weekly, discrete units of content will let me move quickly, jumping on new features and techniques to bring the latest ideas to you. Plus, I can more easily incorporate your feedback and suggestions, ensuring the content stays relevant.

Everything from the membership program will be included in the archive, which will become increasingly valuable over time.

Thank you for reading and supporting my work.

Much love Sheets amigos,
Ben ❤️

Multiple Selections in Drop Downs in Google Sheets

The drop down menu feature in Google Sheets is useful for efficient data entry. It can also be used to create dynamic spreadsheets, for example, dashboards that change based on choices a user makes.

At the end of July 2024, Google announced multiple selections in drop downs (scheduled to roll out in late-August/early-September). This is great news that will give us even more flexibility in our spreadsheets.

In this post, we’re going to take a look at this new drop down feature:

Multiple Dropdowns in Google Sheets

How to Enable Multiple Selections in Drop Downs

To enable multiple selections, create a regular dropdown and then check the box that says “Allow multiple selections”:

Allow Multiple Selections in Google Sheets dropdown

Boom! 💥 That’s it.

Working with Drop Down Multiple Selections in Formulas

If you select multiple options from a drop down menu, the output is a comma separated list:

Multiple Selections in Drop Downs in Google Sheets

To work with them in formulas, we use the SPLIT function to separate the choices into their separate parts.

Note the space after the comma. We need to be mindful of this in our formulas. Split out the comma-separated lists with this formula, which accounts for the space too:

=SPLIT(B2,", ",FALSE)

It looks like this in our Sheet:

Split formula for multiple Dropdowns

We can use a BYROW function to expand this SPLIT formula to work with a range of multi dropdowns. This single formula in cell C2 splits out all the cells in the range B2:B6 and outputs all the data in C2:E6 range:

=BYROW(B2:B6,LAMBDA(r,SPLIT(r,", ",FALSE)))

In our Sheet:

BYROW and SPLIT with multiple dropdowns

Finally, we could wrap this with a TOCOL function and a QUERY function to count our choices:

=QUERY(TOCOL(BYROW(B2:B6,LAMBDA(r,SPLIT(r,", ",FALSE)))),

"select Col1, count(Col1) where Col1<>'' group by Col1 order by count(Col1) desc label Col1 'Option', count(Col1) 'Count'",0)

In our Sheet:

Multiple Selections in Drop Downs with QUERY

GETPIVOTDATA Function in Google Sheets: How To Extract Data From Pivot Tables

The GETPIVOTDATA function is used to extract data from a pivot table.

Pivot tables are one of the most powerful and useful features in spreadsheets. We use them to summarize our data, by grouping, sorting and filtering it.

But pivot tables are dynamic elements. That means they can change size and shape when data is added or removed from the underlying dataset (e.g. when a new category is added). This makes it tricky to extract data consistently.

When a pivot table changes size, a regular cell reference (e.g. A12 or F34) might not point to the correct value in the pivot table anymore. However, the GETPIVOTDATA function will still extract the correct data.

The downside of GETPIVOTDATA is that it’s fiendishly difficult to use. It’s something of a dark art to identify the correct rows and columns inside the function.

In this post, we’re going to learn how this function works.

👇 Feel free to grab the template from the bottom of this article to follow along.
Continue reading GETPIVOTDATA Function in Google Sheets: How To Extract Data From Pivot Tables