Google Sheets and Apps Script Blog Posts From Ben Collins

Repeatable Task: To Script Or Not To Script, That Is The Question

💡 Learn moreLearn how to write Apps Script and turbocharge your Google Workspace experience with the new Beginner Apps Script course

Script

This post was inspired by a question from a reader, who recently asked:

“When should I go to the trouble of writing a script versus doing the quick and easy approach?”

This is absolutely a valid question that you should ask before diving into your code editor.

Let’s create a framework for answering this question.

Question 1: Is It A Repeatable Task?

If you’re debating whether to create a script to automate a task, or simply do the task manually, the crucial question to ask is:

1) Is it a repeatable task?

If you answer YES, then ask yourself:

2) Do I have time right now to write a script or record a macro?

If you answer YES to this, then ask yourself:

3) Do I know how to do it?

If you answer YES, then go for it!

If you answer NO to any of those questions, it’s probably going to be quicker and easier to do it the usual (manual) way.

But I encourage you to learn a little Apps Script on the side, so you can answer YES next time!

Here’s a flow chart to help you:

Script

Question 2: How Long Does It Take?

The next crucial question to ask is: how long does it take to do the task?

The time it takes to do the task manually is how much time you’ll save each time if you can simply click a button to run a script.

So, if the answer is “the task takes lots of time”, then you should definitely consider automation.

Over the long run, it’s beneficial to turn repeatable tasks into automated scripts so the task can be done with a few button clicks.

Upfront Cost

Of course, there’s an upfront cost in the additional time it takes to record a macro or write a script. It will take longer than just doing the task manually.

Consider the following chart showing the time taken for a particular task:

Chart

The first time – when you write the script to do a task automatically – takes longer than doing it manually, but subsequent tasks are much, much quicker.

Eventually, the cumulative time doing the task manually will far surpass the cumulative time of using the script (assuming it doesn’t require any major updates).

Once the macro or script has been created, it takes minimal time (and effort) to perform future tasks, so the cumulative time effectively flat lines.

However, that manual process always takes about the same amount of time, so the cumulative time keeps increasing in a linear fashion…up, up, and up.

Repeatable Task versus Manual process

There’s an inflection point where the script starts saving you time. And it’s probably sooner than you think.

Consider a 30-minute repeatable task that you automate. Assuming it takes you 2 hours to create the script the first time, that’s only 4 repetitions until you “break-even” on time.

XKCD has a helpful matrix to show you how much time you should allow to automate a task:

Automation Matrix

Question 3: How Complex Is The Repeatable Task?

Don’t underestimate the time it takes to automate a task.

Sometimes automation looks more like this:

Automation gone wrong
(Source XKCD)

And you find yourself in this situation:

Never spend 6 minutes doing something by hand when you can spend 6 hours failing to automate it

— Zhuowei Zhang (@zhuowei) April 26, 2020

The best way to avoid this is to automate tasks that take a long time but are relatively simple.

For example, a set of data transformations is a good contender. An approvals pipeline involving 3 different people is not a good contender because the process is likely to be complex.

Benefits Of Automating A Repeatable Task

Saving Time

Years ago, I faced a dilemma:

Each month the client would send me their data. It took me a couple of hours to wrangle that data into the correct format, with formulas and pivot tables, so I could use it to update the dashboard.

Realizing this was a tedious and repeatable task, I wrote a script to automate the data transformation steps.

Now I click a button and it automatically does the task.

This frees me up to work on higher-value tasks, like thinking strategically about what insights the data shows or how to improve the model.

Avoiding Mistakes

When you keep doing a repeatable task manually, you stop learning. When you stop learning, you stop being engaged with the work.

After the 3rd or 4th repeat, you’re running on autopilot. You lose focus, get distracted, and make mistakes.

A script never gets bored, distracted, or tired!

How I Built A Glide App And Turned My Google Sheet Into A Simple No-Code App

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 Glide 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 apps with Glide App, the 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 the information included in the app, I made changes to 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 let 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 by 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 a Glide App 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 functional.

However, there are a few features available with the paid version that later prompted me to upgrade to their lowest-paid tier:

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
Pro app: $32/mo or $24/year
Private app: $40/mo + $2 per extra user

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.

Etch A Sheet In Google Sheets

I was playing with my children the other day when one of them grabbed our Etch A Sketch toy and started drawing a treasure map with it.

Sitting in my office later that day I had a crazy thought “Could I build a working Etch A Sketch in Google Sheets?”

Two days later and boom! Here it is:

Etch A Sheet Game In Google Sheets

🔗 Grab your own copy of the template at the bottom of this article.

The game works using four techniques:

Checkboxes as buttons
Self-referencing formulas with iterative calculations
Dynamic array, or spill, formulas to generate coordinates
A sparkline formula to draw the line

It doesn’t use any code. In fact, it’s created entirely with the native built-in functions of Google Sheets.

Before I dive in though, I want to acknowledge a fellow Google Sheets aficionado…

Hat Tip To Tyler Robertson

Tyler Robertson is a Google Sheets wizard who’s built an amazing portfolio of spreadsheet games (described by some as the Sistine Chapel for spreadsheets) using only built-in formulas.

Thankfully, he hasn’t built an Etch A Sketch clone yet 😉

This Etch A Sheet game uses Tyler’s checkboxes as a button technique, and has similar logic to his moving-a-character-around-a-Sheet game.

So thank you, Tyler, for your amazing work!

How Does Etch A Sheet Work?

Etch A Sheet Game In Google Sheets

Just like the real Etch A Sketch game, there are controls to move the stylus left or right and up or down, to create lineographic images.

Since you can’t “shake” a Google Sheet (although I bet you wish you could sometimes!), there’s an additional reset checkbox to clear out the image and put the stylus back to the bottom left corner.

Etch A Sheet Formulas

There’s a button to open up the right side of the Sheet and see the formulas that generate coordinates for the sparkline function:

Etch A Sheet formulas

The buttons are regular checkboxes, which toggle a TRUE/FALSE value in the cell.

The checkbox in cell H30 is the reset checkbox and I’ve called it “reset” in the named ranges box.

Named Range in Google Sheets

Left Button Formulas

In cell V3:

=IF(F30<>V3,F30,V3)

In cell U3:

=F30<>V3

I also named U3 “right”.

Then in cell W3:

=IF(reset,0,IF(right,W3+1,W3))

With iterative calculation switched on (see File > Settings > Calculation) and set to a maximum of 1 iteration, these formulas let the checkboxes function as buttons (see Tyler Robertson’s post for more details of how this works).

Starting Coordinates

I put the value 1 in cell U16 and named it “startX”.

Similarly, I put a 1 in cell U19 and named it “startY”.

Current X Formula

The main IFS function, which controls the horizontal X coordinate, is in cell V16:

=IFS(reset, startX, AND(right,currentX<50), currentX+1, AND(left,currentX>1), currentX-1, TRUE, currentX)

This formula cell is labeled as a named range “currentX”.

Firstly, it checks if the reset button is checked and if it is, resets the value of the cell to the starting X value.

If the reset button is unchecked, i.e. FALSE, then it checks if the right button is pressed and the current X is less than 50, using an AND function, and if it is, adds 1 to the current value.

Otherwise, if the left button is pressed and the current X is greater than 1, it subtracts 1 from the current value.

Finally, there is a TRUE condition to act as a catch-all when the previous conditions fail. It sets the value back to the start value.

X Path Formula

This self-referencing formula is put into the adjacent cell, W16, and called “xPath”.

It appends each new current value to itself to create a string of x values as the buttons are pressed, i.e. “1”, “1,2”, “1,2,3”, “1,2,3,4” etc.

=IF(reset,,xPath & "," & currentX)

For this to work, the spreadsheet needs to have iterative calculations enabled with a max of 1 iteration.

The settings are found under the File > Settings > Calculation:

Google Sheet settings

X Number Formula

This formula turns the xPath string of values into a column of numbers, which is fed into the sparkline function in the next step.

=IFERROR(TRANSPOSE(SPLIT(xPath,",")),"")

The SPLIT function breaks up the previous string of data, by the comma separator.

The TRANSPOSE function turns the array from a row vector to a column vector.

The IFERROR function wrapper hides the error message when the xPath variable is empty.

Y Formulas

The same formulas are replicated to create a column of Y coordinates.

Sparkline Formula

=IFERROR(SPARKLINE(Q3:R,{"linewidth",2 ; "xmin",1 ; "xmax",50 ; "ymin",1 ; "ymax",50}),"")

Here, the sparkline formula takes the X Number and Y Number coordinates (two columns of numbers) and simply plots them as a line.

In the sparkline options, I’ve set the linewidth to 2 so it stands out more. I also set min and max values for the canvas, so that the drawing always starts from the bottom left.

Using Groups To Show/Hide Content

This is another interesting technique, used here to show or hide content that doesn’t need to be on display all the time. I’ve used the same technique for the “Formulas” section, shown in the GIF above.

The grouped row button below the Etch A Sheet board shows and hides the instructions section when it is toggled:

Etch A Sheet instructions

Finishing Touches

There are a few other steps to complete the Etch A Sheet:

Merge a big section of cells for the sparkline area
Add a thick red background around the outside of the Etch A Sheet
Add a heading, in a playful gold-colored font
Remove gridlines (one of the best tips to make your Sheets look good!)

And there you have it!

Improvements

Alternative Controls

To stay true to the original game, I put the horizontal checkbox buttons on the left side and the vertical controls on the right side.

However, these are awkward to press because you have to jump back and forth between them with your cursor. Of course, it doesn’t matter with the physical Etch A Sketch because the dials are positioned for each hand and can be operated simultaneously.

Perhaps a better approach in the Sheet version is to put the checkbox buttons close together so that the cursor movement is minimized.

Alternative Controls

Starting From The Previous Position

Every Etch A Sheet game restarts from (1,1).

However, when you turn a real Etch A Sketch upside down, shake it, and then restart, the line is drawn from wherever it last finished. It does NOT revert back to the bottom left.

So I’ll leave this as a challenge for you! Can you modify the formulas to match this behavior?

Formula Bug

If you look really closely at the GIFs in this post, you’ll notice that the line is one step behind the button press. I.e. when I press a button, it has to draw the previous step still before registering the new button action. Obviously, this is not ideal.

Again, I leave this as a challenge for you to explore…

Unfortunately, I have to get back to my actual work now, so I’m going to leave this fix for another day. This project exceeded my expectations (It was a lot of fun! It was intellectually challenging! I learned some new techniques!) so I feel satisfied with this outcome. I don’t feel the need to make it perfect.

Etch A Sheet Template

Open the Etch A Sheet template here.

Make your own copy: File > Make a copy

(Note: If you are unable to open this file, it’s probably because it’s from an outside organization and my Google Workspace domain is not whitelisted at your organization. You may be able to ask your Google Workspace administrator about this. In the meantime, feel free to open it in an incognito window and you should be able to view it.)

If it does not appear to work, check you have the iterative calculations enabled.

Go to File > Settings > Calculation

Make sure Iterative Calculations is ON and set to 1 iteration. See the image under “X Path Formula” for more details.

If you do make a copy, I’d love to see what you draw with it!

Dot Plots In Google Sheets

Dot plots are simple charts consisting of dots plotted on a simple scale, typically used to show small counts or distributions.

Dot plots are one of the simplest statistical charts, only suitable for small-sized data sets. They’re helpful for understanding the “shape” of your data by highlighting clusters, gaps, and outliers. (A histogram is better suited to showing the data distribution of larger datasets, e.g. > 30 datapoints.)

Here’s a table using dot plots to show the hypothetical number of meetings per day for these five employees:

How To Create Dot Plots In Google Sheets

You create dot plots in Google Sheets with formulas!

Suppose we have this data in row 1 of a Google Sheet, in cells A1 to E1:

Step 1:

Create a basic REPT function next to the data, e.g. in cell F1:

=REPT("*",A1)

Step 2:

Next, turn this REPT formula into an array formula:

=ArrayFormula(REPT("*",A1:E1))

Step 3:

Then use the JOIN function and CHAR function to combine the array output. CHAR(10) creates a carriage return, which we use as the delimiter:

=ArrayFormula(JOIN(CHAR(10),REPT("*",A1:E1)))

Step 4 (optional):

Convert the * into circles with the CHAR function:

=ArrayFormula( JOIN(CHAR(10),REPT(CHAR(9679), A1:E1)))

Step 5 (optional):

Rotate the cell up:

Format > Rotation > Rotate up

Here’s an image showing the outputs for these 5 steps in column F:

How To Create Multi-Colored Dot Plots In Google Sheets

Taking this idea one step further, we can add colored symbols to indicate the relative counts.

Here’s an example with green dots for large counts, then orange, and then red dots for the smallest counts:

Multi-Colored Dot Plot in Google Sheets

The formula is more complex and uses the IFS Function to categorize the inputs by relative size:

=ArrayFormula(JOIN(CHAR(10),REPT(IFS(A1:E1/MAX(A1:E1)>0.85,"🟢",A1:E1/MAX(A1:E1)>0.5,"🟠",TRUE,"🔴"),A1:E1)))

How does this formula work?

It’s an array formula that takes an input of the five numbers in columns A to E.

Inside the IFS function, the number (e.g. 7) is divided by the maximum number in the range (10 in this example) and compared to see if it’s bigger than the first threshold (0.85 in this example). If this is true, then the green dot is plotted, otherwise, the threshold is checked (0.5 in this example) If that’s true, then orange dot is used. If that is not true, then the red dot is used as the default.

The REPT function and the JOIN function perform the same way as step 3 above for the simpler single color example.

You can also replace the colored dots in this formula with their CHAR function equivalents, to keep it entirely formula driven:

=ArrayFormula(JOIN(CHAR(10),REPT(IFS(A1:E1/MAX(A1:E1)>0.85,CHAR(128994),A1:E1/MAX(A1:E1)>0.5,CHAR(128992),TRUE,CHAR(128308)),A1:E1)))

As a final step, don’t forget to rotate the cell text up, to get the dots plotted as columns rather than bars.

Notes

This Dot Plot technique first appeared in issue 188 of my weekly Google Sheets Tips newsletter. Signup here if you’d like to receive it!.

Thanks to reader Marcel L. for his sharing his idea for the multi-colored dot plot.

Slow Google Sheets? Here are 27 Ideas to Try Today 🐢

(Updated Jan 2022 to reflect the increase in the cell limit of Google Sheets to 10 million.)

Slow Google Sheets?

We’ve all been there, stuck watching the little loading bar creep slowly, frustratingly to its conclusion:

Slow Google Sheets loading bar

How can you speed up a slow Google Sheet?

First off, this is a difficult question to answer because there are so many factors that may or may not be causing you to have a slow Google Sheet.

What follows in this article is some suggested optimization strategies and some research into what causes slow Google Sheets.

Strategies to speed up Google Sheets

Continue reading Slow Google Sheets? Here are 27 Ideas to Try Today 🐢