Creating a Custom URL Link for Your Spreadsheet

About this Tutorial

Create an apps script function that gets the url of your spreadsheet into a cell.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

CONCAT() HYPERLINK()

Video Transcript

<div>0:00 Alright, you may know that you can use hyperlink , and this will create a hyperlink of any URL you have. And that's pretty cool, but what if you want to have the URL of the spreadsheet itself inside of this hyperlink , or inside of any formula at all? 0:21 Uh, there is no sheet URL.

There is no this sheet formula , but let's go and create it ourselves, so that we can link to this sheet. 0:33 Because we can do some really cool things, like if we have the URL of the sheet, we could paste it in here, go to the end of it, and add copy, and now we can have a link that copies this spreadsheet . 0:47 sheet, but we can also write it wrap it in a formula like equals hyperlink , and then put a label , copy. 0:57 And now we have a button that says copy, and once we click this, it'll make a copy of this spreadsheet for someone. 1:05 So that's pretty cool.

We just need to get the URL of this sheet. We don't You want to have to copy-paste the URL every time. 1:12 So let's go do that. Let's go to extensions, Apps Script , and even though this is scripting and coding, it's going to be very simple. 1:24 Once this loads, we have a few things. We have an untitled project. We can type URL up here. We have a function called my function . 1:33 It has a, uh, parentheses and then brackets. We need to name this function . We're going to call this thisSheet in all caps. 1:44 And inside of this function , we're going to do one thing.

We're going to return URL. Well, what is the URL? 1:53 We're going to call it variable URL equals Capital S Spreadsheet app with a capital S. Capital A as well. .git active spreadsheet . 2:07 Add the two parentheses. .git URL. Command S or there's a button here called Save Project to Drive. Now, we need to use this inside of our sheet. 2:23 So we need to do one extra thing. That will allow it to basically auto-complete as if it were a native spreadsheet . 2:30 So we're going to do slash and then two, uh, two asterisks. And we're going to write at custom function .

That's all. 2:41 Now we're going to click Save again. And now let's go back to our sheet and anywhere we want to type this sheet, there it is. 2:52 And let's see if it works. It's going to load. And there it is. That is the, the URL of the spreadsheet . 2:58 But one weird thing is it has edit at the end, which we want to be able to use this sort of without that edit. 3:07 So let's do one more thing. So we're going to go back to our code and add one thing to our return URL. 3:11 And we're going to say .replace.

And here, in quotes, we're going to put in edit and then a comma after the quote. 3:18 And we're going to replace it with nothing. So that's just two single quotes here. So we're saying, hey, in this URL, take the word edit and replace it with literally nothing. 3:30 So command S, save, and let's go back and get this sheet again. And there we go. We have our blank URL. 3:41 So we can add things to it, like ampersand, copy, or rather, concatenate . hat, together, this and the word copy. There we go. 4:08 So we've added, now, copy to the end of the URL.

You can click it and see, it works totally fine. 4:16 We can wrap this with hyperlink and put a label , copy this sheet, pretty cool. Cool, right? We now have the URL of this sheet. 4:28 So now, if we actually made a copy of this sheet, this particular URL would change with this copy. That's pretty cool, I think. 4:39 I think this is a really fun way to get the URL inside of your Google Sheet with a little bit of Apps Script .</div>