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:
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:
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.
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:
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.
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:
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:
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
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.
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!