Extract URLs from Google Sheets

About this Tutorial

When you can't get the url of a hyperlinked cell, do this instead.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

[00:00:00] Hi, Andrew can be here with better sheets, helping you make better Google Sheets every single day. This video's gonna go into extracting URLs, which is a pretty interesting thing because what happens is if you are a VA or you have a VA, or you have some other people that are working for you in Google Sheets, we want to get links in there because we wanna make our sheets like interactive and we wanna. [00:00:25] Include a link. If we're getting a bunch of text in and a bunch of websites and we're trying to keep our information dense, you might have, you know, a name column. You might have a website column, a url. You might have name, you might try to say title and url, but sometimes we want to kill that URL column cuz it's just gonna be a bunch of. [00:00:47] Right all the way down and we're like, ah, we don't really wanna see that, but we want this like title to have a url. So what we'll do, and this is a typical thing we do, right, is we do command K and we paste a URL there, but we can't get it. Once we delete this, we can't programmatically get it. Maybe we wanna output this to something else, create a CSV file with all those URLs and we can't get this. [00:01:13] This URL is not programmatically available until our friend Tyler just released this, just wrote this. He found the answer, how can you get the URL of a hyperlink that wasn't made with the hyperlink function? Right. We could potentially, right? If we were diligent, we would do something like this. We would do hyperlink and we would say HT dps better sheets.co. [00:01:39] And then we. Put any text here. Better sheets. Great. We can get this. This is easy to grab, but if we do command K, it is not easy. It's to grab what Tyler's saying here. How do we get this? And we, we get this by creating a function Google Script. Tyler doesn't like Google Script, but I love Google Script. I love these very simple Google Script functions that you can literally copy and paste. [00:02:04] So I'll do that right now. So command C and I'll show you how to implement this. So go up to Extensions app script. By the way, make sure you check out Tyler Robinson. Work. He does awesome stuff in Google Sheets. I wish there was more people like this who love Google Sheets. Just check out. There he is Spreadsheets. [00:02:25] All of these great Google Sheet stuff. All right, back to our app script. This is our base function. We're gonna just delete that and we're gonna paste this GI link and input and I'll show you what to do next. We do command S to save. We gotta save that, that orange button over there. It's gonna tell us we didn't save. [00:02:43] Now it's gone. We saved it. Now we have this url. Go equals get link and follow. Tyler's advice. We have to use the quotes. All right. This is very important because immediately you're gonna be like, oh, okay, I'm gonna do D two. I know how to use custom functions and it's not gonna work. Should we do D two in quotes? [00:03:08] We got it. Get linked. D two over here. Better sheets. Got the url. Better sheets here. We can keep adding, you know, anything we want, we can do CNN if we add. Now we can get it with this Git link. Again, we can't copy and paste this, but we can go here D three and we'll get that. There it is. That's the link there. [00:03:32] All right. But as Tyler says, this is great if we only need to do one, but we most definitely, if we're doing lists of things, we might not want to only do one. We, this is sort of a little even more chaotic than just going to this, oh, we can do this copy. Link address. You're gonna like hire a VA to do that, right? [00:03:53] We can do better. We can do way, way, way better. And Ty gives us the answer. We'll delete this. We'll delete this, we'll do equals get link, do sell. And we'll go back here and we'll see address paste that. But it's not a two, it is d. Let's see that work. There it is. There's a link. And now we can copy paste this down. [00:04:17] There's a link. So now instead of having to do 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 gonna save us a lot of time or our VAs a lot of time. So get this function. [00:04:39] Put into App script and use it. You can extract your URLs from Google Sheets. Make sure you check out Tyler's work. I love it. It's amazing. He's done great work. Cheatle. He created, this is the guy who created Cheatle. If you haven't watched that video, go check out Cheatle. It's amazing. Don't make any sheets. [00:05:00] Make better sheet.