Enter Google Drive File Name Get URL in Google Sheets

0:00 So we had a member ask, can you insert a file into a Google sheet cell? And a very interesting answer is, well no, but that's not what's interesting about it. What's interesting about it is sort of, you can sort of do it. So what the workaround and their workaround is, they have a Google Drive file. They put a file in Google Drive, and then they put a URL inside of a Google sheet cell. And they wanna know, is there a better way? And I can sort of just automate that. So while, no, you can't insert a file into a Google sheet cell, you can make the process of putting a URL of a Google Drive file much easier. All right, let me share with you how this works.

0:47 So essentially I want to take the name of a Google Sheet file, like I have a Google Sheets, uh, a Google document. It's called Google Sheets template . I'm gonna just type that in there. And I want the URL to show up here because the way that I would do this normally is I would go to Google Drive, drop that file in and figure out what the URL is and copy the URL over here. But I know the name of it and at least if it's got a unique name, I can do this. So let's go do this right now. Let's go do Extensions app script . And it's only gonna be a few lines of code, but there is one. Big trick with it, and I'm gonna show you, we're gonna run into an error . I'm gonna fix that error in this video. Okay. Just zoomed in a little too much. There we go. So let's call this enter file name, get URL and our function .

1:36 At first, I thought, let's do on edit, you have an event or e. And then we were, anytime there's an edit, we're going to run a function called Get URL that we'll create. And we just wanted to use the file name here and we're gonna return the, the file name here, or sorry, the URL here. If we have the file name, we can do something very quick and say variable files equals drive app. Get files by name and we can just look for the file name. And the one weird thing before we get to the error that's gonna happen is we can do variable files equals files next. So that's the weird thing right here is this is going to

2:23 give us back a list of files. Even if it's one file, we have to use this files next to get the file and then we can get file, do get URL three lines of code right there to enter a file name and get a URL. But we wanna be able to do that quote unquote automatically. Want be able to enter a file name here and get the URL here? Well, how do we know if we're editing here and we have a value, that's where the unedited comes in. We're gonna have variable row equals e dot Range . Do get row. We're gonna say variable call or column, E range do get column. And the variable file name is equal to E value, whatever we're typing in. And if row is greater than one, double ampersand for and column is equal to

3:11 one, meaning we're in that first column. So wherever your column is that you're putting the name in. Put the number of the column there and if both of those are true, then we're gonna do spreadsheet app that get active sheet , that GI range , it's gonna be the row and the column plus one. So wherever you want to put the actual URL, just figure out where that is in relation to that column that you're on. And then in parentes, set value. And we're going to run the function . Get URL with the file, name, this file name or e value. Now there's probably gonna be one extra thing we need to add here, which is if there is no file name, then we're just gonna return blank. So that means just if there's none.

3:58 Stop and just return literally nothing. We could also file or log, uh, any errors we might try here. So let's try and, uh, curly brackets, let's try all of this. And this is a nice way to not get it errors all the time or just to log the error . And we're gonna catch in parentheses, error . And we're just gonna log or not log. The error message and we'll also return none found. Let's save all of this and this oned. Let's go over here and start typing Google Sheets templates . We got none found, but I know that there is url, so something's up. Let's go see what it is. And remember, we logged this error message.

4:43 Let's go back to our executions. It says Completed it's refresh. Their specified permissions are not sufficient to call drive. So what this means is we have to give it permissions well. I'm gonna skip ahead a little bit because we could run this, give it permissions and it's still not going to work. We must do one crazy thing, which is we need to not use a simple, a simple trigger here. We need to name this something, we'll call it. Have file name, get URL and this E. We're going to keep. But we're just gonna save this as this function . Have file name, get URL. And over on the left side, we're gonna go over to triggers go on the bottom right, add trigger , and we're gonna choose have file name, get URL as the function .

5:30 We're gonna run from spreadsheet and on edit. What's different about this? It's an installable trigger , so when we hit save, it's gonna ask us to authorize and it's gonna give us the ability to have more authorizations. Even though this looks and feels exactly the same as if you just ran it in your console, gave it the permissions, gave simple trigger versus the installable trigger . The simple trigger doesn't have all the permissions the installable trigger does, or more. So now we've installed that sugar on edit. Let's go back to our sheet, go to the next row and I'm going to just type the same thing. Google Sheets templates . And what is the answer? It's going to be an actual URL Done for us Automatic.

6:20 All we have to do is enter the name of our Google Drive file and we get that URL. So over in our app script , let's go back and review a little bit. We've used a normal function , not the on edit one. We've created two functions here, right? We get the URL with the file, which is fairly simple. It's just these three lines here. We've made it a little complicated with our, if there's none return, try to give us an error message if, if there's an error , but it's really those three lines. Drive app. Do get files by name. Use the file. Name files, next file. Do get U RL done. And we're just doing it when we are editing the right column and the right cells . There we go. Hope you enjoyed that one. Uh, hope you enjoyed that little workaround of getting a URL when you enter

7:08 a Google Drive file in a Google sheet You are watching better sheets here on YouTube. Make sure you check out this video or this video and subscribe right now to get more tips, tricks, how tos, get more out of your Google sheets than you ever have before. I'm excited to be making a ton more videos here. Ask me questions down in the comments and I will answer them in future videos. But for right now, right here, one of these videos is gonna be your next Google sheet.