Blog

Formula Challenge #5: Merge Columns in Google Sheets With Single Range Reference

This Formula Challenge originally appeared as Tip #131 of my weekly Google Sheets Tips newsletter, on 21 December 2020. Sign up so you don’t miss future Formula Challenges!

Find all the Formula Challenges archived here.

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):

Formula challenge 5 Data

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)))

Flatten collects all the data from the ranges into a single column, before the unique ones are selected and then sorted.

The FLATTEN function is a new function that popped up in early 2020, but was only recently documented by Google.

It’s a powerful formula, and one that can be used to unpivot data.

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:

=SPLIT(TEXTJOIN("♔♕",TRUE,A:C),"♔♕")

Transpose to a column, select the unique values only and finally wrap with a sort function to get the result:

=SORT(UNIQUE(TRANSPOSE(SPLIT(TEXTJOIN("♔♕",TRUE,A:C),"♔♕"))))

There we go!

Two brilliant solutions to an interesting formula challenge.

Please leave comments if you have anything you wish to add.

And don’t forget to sign up to my Google Sheets Tips newsletter so you don’t miss future formula challenges!

Google Sheets Sort By Color And Google Sheets Filter By Color

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.

They’re really helpful for removing duplicates in Google Sheets too.

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:

Google Sheets 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 filter by the background color of the cell (like the yellow in this example) or by the color of the text.

Google Sheets Sort By Color

The result of applying this sort is all the colored rows will be brought to the top of your dataset.

Data sorted by color in Google Sheets

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.

Google Sheets filter by 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.

Data filtered by color in Google Sheets

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.

They were added on 11th March 2020. Read more here in the Google Workspace update blog.

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.

Filter By Color in Google Sheets

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:

  1. Custom menu to run the Google Sheets sort by color program
  2. Prompt to ask user for the color cell
  3. Save the color cell using the Properties Service
  4. Second prompt to ask the user for the sort/filter column
  5. Save the sort/filter column using the Properties Service
  6. Show the color and column choices and confirm
  7. Retrieve the background colors of the sort/filter column
  8. Add helper column to data in Sheet with these background colors
  9. Sort/Filter this helper column, based on the color cell
  10. 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:

Google Sheets sort by color

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.

Apps Script Sort By Color Template

Here’s the Google Sheet template for you to copy.

(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.)

If GitHub is your thing, here’s the sort by color code in my Apps Script repo on GitHub.

Apps Script Filter By Color

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:

Filter By Color in Google Sheets

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.)

Or pull the code directly from the GitHub repo here.

2020 In Review And A Look Forward To 2021

Best wishes to all of you for 2021!

Let’s hope for a brighter, happier, safer lap around the sun this time.

Winter snow
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 PlatformSort 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.

benlcollins logos

benlcollins logo

benlcollins social logos

benlcollins typography

benlcollins colors

Google Sheets Essentials

2) SheetsCon

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.

SheetsCon stats

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.

Watch the 2020 SheetsCon replays for free here.

Read about what happened behind-the-scenes to make SheetsCon happen.

It was a huge amount of work. I’m glad I didn’t realize that when I embarked because I might not have done it!

It’ll be easier in some ways in 2021 as I have a blueprint to follow. However, I’m looking to make it bigger and better.

See you there, on 24th and 25th March 2021!

SheetsCon Swag Bags

3) Online Courses

I only added one new course this year: Google Sheets Essentials.

Google Sheets Essentials Course

But I also updated every course in my catalog*, to reflect the changes in the Google Sheets and Apps Script environments.

This was a huge project to re-record over 300 videos and create new templates. It occupied me for most of the year!

* at the time of writing, I’m in the process of updating the Automation with Apps Script course, which is the final one to update.

4) Website

benlcollins site traffic

  • I published 26 new posts this year, which was really pleasing. I had a burst of creative energy in November and December.
  • The traffic to benlcollins.com continues to grow and now reaches around 200k+ users each month for about 350k+ pageviews. Wow!
  • Across 2020, the site saw over 2 million users and nearly 4 million page views 🤯
  • Traffic has increased steadily across the year, although it’s mostly plateaued in the second half of the year
  • I want to keep growing this traffic in 2021!

My favorite posts of the year are the ones that nourish me intellectually.

This year, my favorite ones to research and write were:

5) Google Sheets Tips Newsletter

2020 email growth

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.

2020 saw formula challenges #3 and #4, with formula challenge #5 straddling the Christmas holiday break.

As a surprise, my dad printed me a physical copy of tips 1 to 100! Thank you!

6) Community

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! 🙌

7) Dream Office

I set up my dream office in 2020.

New Office

Office door decal

Lego Saturn V rocket

8) Non-Work Highlights

Being a dad!

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 even made spreadsheets together 😉)

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!

Locally, I’ve done tons of hiking on our local trails. I can walk miles in either direction along the Appalachian Trail from my doorstep, so I’m really fortunate in that regard. Most recently, I had a great hike up the mountain during the December snowstorm and rounded out the year with a 19-mile hike along the Appalachian Trail with my wife.

Challenges In 2020

Oh boy!

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

  1. Run 3 cohorts of this new live cohort based course
  2. Run SheetsCon 2021 in March
  3. Improve the SEO and site speed of benlcollins
  4. Publish 30 long-form blog posts
  5. Publish a comprehensive guide to REGEX in Google Sheets
  6. Hit 60k newsletter subscribers
  7. Send a Google Sheets tip email every week for the next year
  8. Create one new on-demand video course
  9. 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

  1. See my UK family!
  2. Have another healthy year
  3. Exercise regularly: 4 hike or bikes each week, 2 yoga/strength
  4. Go camping again! I used to do a lot of camping but it’s been a few years since I last went 🙁
  5. Take my boys out on lots of adventures and camping trips.
  6. 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.

Best wishes to all of you for 2021!

Cheers,
Ben

Happy Holidays!

christmas tree

Previous years

How I Built a Simple App Using No-Code App Builder Glide and Google Sheets

This is a guest post from my wife Alexis Grant.

We use Google Sheets to solve all sorts of challenges in our family.

We combine Sheets with Tiller to track our family finances. We rely on a Google spreadsheet to stay on top of our home renovation project. And whenever one of us launches a digital product for one of our businesses, we build a dashboard in Google Sheets so we can watch sales in real time.

Usually when we build something fancy with Google Sheets, I need Ben’s help. But over the last few months, I used Google Sheets and a third-party tool to solve a problem I’d noticed in our town… and I didn’t need Ben’s help at all.

For this project, I used a no-code app builder called Glide. This was my first time using a no-code tool to build an app, and the first time I organized an app via Google Sheets. Here’s how I did it.

My no-code app in Google Sheets: Hiking in Harpers Ferry, WV

Our family moved to Harpers Ferry, West Virginia, a year and a half ago. We decided to live here for access to hiking trails, and wow, has the town delivered on that promise. Several days a week, one or both of us leave our home at dawn for a walk in the woods or up the mountain.

Maryland Heights

Lots of tourists come to Harpers Ferry to hike, too. Yet there’s no one resource that outlines all the hiking options. The Harpers Ferry National Historical Park shares trails in the park. The Appalachian Trail Conservancy features local walks on the AT, and the C&O Canal Trust gives visitors local options, too.

But we couldn’t find a resource to share with visitors that combined all of these walks, plus others in town that don’t fall into any of these categories.

So I decided to build it.

I picked Glide because I’d heard about them at SheetsCon, Ben’s Google Sheets conference, where the company was a sponsor. I’d thought the concept was cool — an easy-to-use, web-based, drag-n-drop app builder that pulls data from Google Sheets. And it was free to get started.

In the end, Glide was as easy to use as I’d hoped, and the app I put together achieved just what I’d hoped.

You can access it at HarpersFerryTrails.com.

You can use it in a laptop web browser, but it’s really designed for mobile. Since it’s browser-based, you don’t have to use a lot of data to download the app; it simply pops up in your browser.

Below I’ll review more details on how Glide works and which features I used.

How to create an app using Glide, a no-code app builder

Glide App Screenshot Hikes

You can see in this screenshot of the app that it covers hiking Harpers Ferry, WV, as well as eating and parking.

While it took some practice to learn how to use the drag-and-drop interface, Glide was easy to use even for a first-timer. Here are the steps I followed to make an app with this no-code app builder.

1. Created the Google Sheets that power the app

All the information displayed in the app is housed in Google Sheets, so pulling that information together was the first step.

I created several tabs within my sheet, one each for hikes, restaurants and parking. Then I added rows for each option and columns for details about those options.

For example, here’s what this looks like for the hikes tab:

Harpers Ferry App

This was the most time-consuming part of making the app: gathering all the information and photos. Yet it’s also what makes the app truly useful.

2. Learned how to use Glide’s drag-and-drop interface

Next, I pulled that spreadsheet into my Glide account and fiddled with the settings to get each screen to look the way I wanted.

To adjust information included in the app, I made changes in the spreadsheet. To adjust the presentation of that information, I changed items within the app itself, using different settings to choose various icons, titles, links, etc.

There was a bit of a learning curve since it was my first time using Glide, but the tool is generally user-friendly. In a few spots I had trouble figuring out how to do what I wanted, but with some poking around, I eventually accomplished it. I would have appreciated more tutorials or how-to instructions in Glide’s help community on how to accomplish specific tasks within the app; hopefully they’ll build that out over time.

I liked how there were different ways to showcase the information, so I could pick the option that worked best for each screen. The restaurant screen below, for example, has shorter rows, while the hiking screen above shows a photo for each hike. Information can also be presented as a checklist, tiles, a calendar and more.

Glide App Screenshot Eats

Once I was happy with the Hike, Eat and Park tabs, I added an About tab to share some background on how the app came about and help users easily share it with their friends.

3. Built a landing page for my no-code app

Glide makes it possible to send new users directly to the app’s home screen. But I wanted a slightly different experience, a landing page that explained what to expect from the app.

As far as I can see, there’s no option to create this type of landing page within Glide itself, so I used Carrd.co to build a simple one-pager.

This particular landing page was incredibly easy to create; it only took a few minutes. The hardest part was getting my web host (MediaTemple) to point the URL, HarpersFerryTrails.com, which I already owned, to the Carrd.co site.

I love how simple and modern it turned out:

Carrd Landing Page

Carrd.co is not only easy to use, it’s also cheap. I upgraded to a $19/year pro account to use a custom URL.

4. Published the Glide app and asked for feedback

Once I published the app, I asked for beta users on Twitter and also had a few friends try the app.

I wanted feedback on whether the app was intuitive. Generally the answer was yes, and I also received some helpful feedback that prompted me to make some tweaks.

(If you have any feedback you want to share, I’m all ears! Contact me here.)

Step 5: Help users find the app

Now I need people to use the app to navigate hiking, eating and parking in Harpers Ferry! This is always the hardest part of creating anything online: helping the right people know about it.

While I’ve mentioned it to some residents already, I want to dedicate time and energy in 2021 to letting people know this resource is available, and that it’s free.

One way I began to attract new users near the end of 2020 was working with our tourism board to add a temporary Holiday Lights tab. They organized a driving tour that showcased holiday decorations, a socially-distanced way for visitors to experience the town this winter.

Since I’d already built this app, it was easy to add an additional tab showcasing the holiday lights driving tour. This was a win-win: the app made it easy for people to follow the route on their phone as they enjoy the lights, and people who tried the app for the lights tour might return to it later for hiking or restaurant information, and maybe share it with their friends.

The holiday lights tour screen defaults to a map view:

Glide App Screenshot Map

I wish there was a way to make this work more like a Google Map, with a route that shows the users’ location. I couldn’t figure out how to do that, so for this year, I kept it simple.

Now that the holiday lights tour is over, I can easily remove that tab from the app.

How to make apps for free: How much does Glide cost?

Because this isn’t a revenue-generating project and I plan to keep the app free for users, I didn’t want to spend much money to create it.

Glide made that possible: I used their free tier initially to create this no-code app. I was pleased from the get-go that the free tier provided everything I needed to make it functionable.

However, there are a few features available with the paid version that later prompted me to upgrade to their lowest paid tier of $12/month:

  • Map functionality. The free tier allows for a map, but only up to 10 locations. If you want to show more than 10 locations on a map, you have to upgrade to the paid version of Glide. Because we ended up adding more than 10 locations for the holiday lights tour, for example, I upgraded to the paid version to accommodate.
  • Vanity URL. On the free tier, my app’s URL looks like this: https://ibo7l.glideapp.io/. That’s not easy to share or remember. With a paid upgrade, I could nab a custom, more memorable domain. That doesn’t really matter right now since I’m sending new users to the landing page at HarpersFerryTrails.com, but it could be a nice-to-have feature in the future.
  • Creative icons. Glide’s free tier gives you access to a selection of basic icons, but you get more choices if you upgrade. Before I upgraded, for example, I had a peace sign for the Eat tab, because I didn’t have access to icons that were more food-related. Now all the icons feel intuitive.

When I wrote this post, here’s what the pricing tiers looked like:

  • Personal app: Free
  • Basic app: $12/mo or $9/year
  • Pro app: $32/mo or $24/year

With paid tiers, you get more storage, more data rows pulled from your Google Sheets, and more features. The company also offers business plans that charge according to how many users you have.

I’d really like to use their Google Analytics integration to see how many people use the app, but that feature isn’t included in the Basic plan, only in the Pro version. Since I didn’t have this feature during the holiday lights tour, I wasn’t able to see how many people actually used the app. (Another data point: We allowed people who drove the tour to vote for the best-decorated house through a Google Form, and we had more than 200 responses, which I suspect represents only a fraction of people who took the tour.) It would be fun to use Google Analytics to watch the user base for this app grow, so I might splurge for an upgrade in the future.

Glide is still just a few years old (founded by former Microsoft employees), so I won’t be surprised if their pricing options morph over time.

Conclusion

I never thought of myself as someone who would create an app or join the no-code movement. Yet this was enjoyable to build! Using Google Sheets as an organizational method felt natural and satisfying, and I appreciate that Glide allows me to iterate as I build, so the app is always improving.

Most of all, I’m happy to see the information I wanted to share available in a format that’s visual and easy to digest, which increases the benefit to users. I’m already scheming other ways to use Glide to create fun and useful apps, including an app that shows the schedule of events for a series of workcations I’m organizing called Retreat & Create.

These types of informational apps are really just the tip of the iceberg; you can also use Glide to build tools for your business, for example an employee directory, applicant tracker or inventory tracker.

Have you tried Glide or another no-code product that’s built on Google Sheets? We’d be keen to hear about your experience in the comments.

This post includes affiliate links, which means I get a small commission if you purchase a subscription via one of the links in this post.

Dream Home Office Setup

New Office

How it began

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.

But it wasn’t until I saw this Mac Mini and Ultra widescreen setup that I found what I was looking for. This was the perfect setup for me.

Here’s my current home office setup:

New Office

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.

Apple Mac Mini

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.

Lego Saturn V rocket

Office door decal