Extract URLs from Google Sheets

Today’s post is going to go into extracting URLs, which is a pretty interesting thing you might be frustrated doing by hand.

What if you’re a VA, you have a VA, or you have other people working for you in Google Sheets? We want to get links in there because we want to make our Sheets interactive.

We want to include a link. And that’s what I’m here for, to help you make better Google Sheets.  Now let’s get started!

In your Google Sheet, what if you’re getting a bunch of text and websites in, but we want to keep our information dense. So you might have a column for name or title, and another column for website (a URL). But sometimes, we want to kill that URL column because we’re just going to end up with a list of links all the way down. We don’t really want to see that. What we want is this: Give the title a URL.

Skip the Command K. This Google Sheet tutorial will make it easier for you to extract URLS from Google Sheets.

This is a typical thing we do, right? When we use the Command K, we paste a URL there but we can’t get it. Once we delete the cell, we can’t programmatically get it. But what if we want to output this to something else? Maybe we create a CSV file that contains all those URLs. So what do we do, then? This URL wasn’t programmatically available until our friend Tyler just wrote a code. He found the answer to “How can you get the URL of a hyperlink that wasn’t made with the hyperlink function?”

Now, if we were diligent, we would do something like this:

=HYPERLINK(https://bettersheets.co”, “Better Sheets”)

Great! We can get this. It’s easy to grab, BUT! But if we do Command K, it’s not easy to grab. How do we get this? By creating a function with Google Script. I love Google Script, these very simple functions that you can literally copy and paste. So let’s do that now. Copy this Google Script function. Just hit Command C:

function GETLINK(input){
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = sheet.getRange(input);
  var value = range.getRichTextValue();
  var url = value.getLinkUrl();
  return url;
}

Now go up to Extensions and click on Apps Script.

By the way, make sure you check out Tyler Robinson’s work. He does awesome stuff in Google Sheets. I wish there was more people like this who love Google Sheets.

All right. Back to our Apps Script. Just delete what you see there and paste our Google Script function.

Make sure you save it next. We gotta save that. That orange button there? It tells us whether we saved it or not. When you no longer see the orange button, it means you’ve successfully saved it.

Now, we have this URL. You can enter it in any cell you want. Following Tyler’s advice, we have to use the quotes. This is very important, because it’s not going to work if you don’t use the quotes. Now enter the code below to a cell:

=GETLINK(“D2”)

We got it! The GETLINK in D2 got the URL for bettersheets.co in E2.

We can keep adding. Let’s try CNN and add the URL. We get the URL by doing the same thing, except that we put D3 instead of D2:

=GETLINK(“D3”)

There it is. That’s the link there. But as Tyler says this is great if we only need to do one. But if we’re doing lists of things, we might not want to only do one. And Tyler gives us the answer. Let’s go ahead and delete the URLs you entered in E2 and E3. Let’s do something cool by entering this code:

=GETLINK(CELL("Address",D2))

And we have the link we wanted!

We can copy and paste this down. So now, instead of having to do these – right click, copy link address, paste it – we now have this function or this formula inside of our Google sheet. We can copy and paste anywhere we want to grab the URL. This is pretty cool. This is going to save us a lot of time. Or our VAs a lot of time!

So get this function. Put into App script and use it. You can extract your URLs from Google Sheets.

Let me tell you about Better Sheets

If you enjoyed this tutorial, I have more that you might like as well. Check out more tutorials, templates and tools for Google Sheets at Bettersheets.co

For $19/m you can access over 200 Better Sheets tutorials. Learn Apps Script in under 40 minutes. Design better dashboards. Make your sheets faster and yourself more confident in sheets.

Don’t make any sheets. Make Better Sheets.

Related Video

You can watch the whole youtube video: