Welcome to issue 27 of the Sheets Insiders membership.
You can see the full archives here.
Last week I shared some useful undocumented functions hidden in Google Sheets (link in case you missed it).
In the course of doing that research, I bumped into two thorny problems involving URLs and links in my Google Sheets. Unable to solve them with formulas, I turned to Apps Script for a scalable solution.
Today, I’m going to share this solution and Apps Script template with you.
It’s a great example of automating a manual task with a small utility script.
And with the rise of AI tools, it’s easier and quicker than ever to build these kinds of tools.
Template
Download the URL Utilities Template
Click on “Use Template” in the top right corner to make your own copy.
To view the Apps Script, open the menu:
Extensions > Apps Script
Video Tutorial
Problem 1: Extract URLs from rich text
I needed to extract URLs from these rich text cells in my Sheet, hidden behind the “Learn more” text:
There are no formulas or built-in tools that can solve this problem.
And, as I had over 500 cells to deal with, doing it manually was out of the question.
Enter Apps Script…
Apps Script Solution 1
The key is to get hold of the rich text in the cell and then extract the URL from there.
We use these Apps Script methods:
- getRichTextValues()
This returns the Rich Text values for the cells in the range.
- getRuns()
This returns the Rich Text string split into an array of runs, which are substrings with similar properties (e.g. a URL).
- getLinkUrl()
This returns the link URL for this value.
The rest of the code is the scaffolding to hang these methods on, and loop over our data.
Find the full code, with additional explanatory comments, in the template file (under Extensions > Apps Script).
Problem 2: Activate plain text links
In this situation, we want to convert plain text into clickable links in our Sheet. I.e. from this:
to this:
There are a few steps involved:
- Check the cell does indeed have a URL
- Build a new rich text value using the method newRichTextValue()
- Set the link URL to the URL text string in the cell using the method setLinkUrl(string)
As before, that’s essentially it. The remaining code just handles getting the data and looping over it.
Find the full code, with additional explanatory comments, in the template file (under Extensions > Apps Script).
When should you automate?
“When should we go to the trouble of writing a script versus doing the quick and easy approach?”
This is a great question!
Previously, I would have said it depends mostly on your skill/experience level with Apps Script.
Suppose a task takes 1 hour to do manually but it takes 2 hours to write a script. After 2 weeks you break even and start saving time.
Our decision flowchart would look something like this:
But now, things have changed.
The current generative AI tools are so good at writing short scripts that the time to automate a process is much, much shorter than previously.
The flowchart now looks more like this:
If you find yourself doing a task manually and repeatedly, then it’s a strong contender for automation.
And skill is no longer a barrier. Yes, you need to understand the basics of Apps Script (i.e. where to find it, how to run it) but you can rely on the AI tool to write the actual code.
To caveat, I’m talking about writing ~20 or ~50 or ~100 lines of code to automate a singular, discrete process like the ones I showcased above. I’m not talking about relying on AI to write 100,000+ lines of code for a complex app (you’ll want engineers for that still 😉).
Read more: