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.
0:10 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 work around and their work around 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.
0:26 And they want to know, is there a better way? And I can sort of just automate that. So, well, no, you can't insert a file into a Google sheet cell.
0:37 You can make the process of putting a URL of a Google Drive file much easier. Alright, let me share with you how this works.
0:48 So, essentially, I want to take the name of a Google Sheet file. Like, I have a Google Sheet, uh, a Google document called Google Sheet's template.
0:58 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.
1:12 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.
1:18 Let's go do extensions, appscript, and it's only gonna be a few lines of code. But there is one big trick with it.
1:25 And I'm gonna show you. We're gonna run into an error. I'm gonna fix that error in this video. Okay. I'm just zoomed in a little.
1:36 Too much. There we go. So let's call this. Enter file name. Get URL. And our function at first, I thought, let's do on edit.
1:50 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 will.
2:00 Create, and we just wanted to use the file name here, and we're going to return. The file name here, or sorry, the URL here.
2:13 If we have the file name, we can do something very quick. And say variable files equals drive app that get files.
2:24 By name. And we can just look for the file name. And the one weird thing, before we get to the error that's going to happen, is we can do variable file equals files.next.
2:43 So that's the weird thing right here, is this is going to give us back a list of files. Even if it's one file, we have to use this files.next to get the file.
2:54 And then we can get file.getURL. Three lines of code right there to enter a file name and get a URL.
3:02 But we want to be able to do that quote unquote automatically. We want to be able to enter a file name here and get the URL here.
3:09 Well, how do we know if we're editing here and we have a value? That's where we where the on edit comes in.
3:15 We're going to have variable row equals e dot range dot get row. We're going to say variable call or column e dot range dot get column.
3:29 And the variable file name is equal to e dot value, whatever we're typing in. And if row is great. Then one.
3:38 Double ampersand for and. Column is equal to one. Meaning we're in that first columns. Wherever your column is that you're putting the name in.
3:47 Put the number of the column there. And if both of those are true. Then we're going to do spreadsheet app dot get active sheet dot get.
4:01 It range. It's going to be the row. And the column plus one. So wherever you want to put the actual URL.
4:08 Just figure out where that is in relation to that column that you're on. And then end parentheses set value. And we're going to run the function get URL with the file name.
4:21 This file name. Or e.value. Now there's probably going to be one extra thing we need to add here which is if there is no file name.
4:37 Then we're just going to return. Blank. So that means just if there's none. 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, clearly brackets.
4:58 Let's try all of this. And this is a nice way to not get errors all the time or just to log the error.
5:06 And we're going to catch in parentheses error. And we're just going to log or dot log. The error dot message.
5:16 And we'll also return non-found. Let's save all of this. And this onEdit, let's go over here and start typing Google Sheets Templates.
5:32 We get non-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.
5:42 Let's go back to our executions. Says completed. It's refresh. They're specified permissions are not sufficient to call drive. So what this means is we have to give it permissions.
6:09 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.
6:15 We must do one crazy thing, which is we need to not use a simple trigger here. We need- to name this something.
6:28 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.
6:44 Go on the bottom right, add trigger. And we're gonna choose have file name. Get URL as the function we're gonna run from spreadsheet and on edit.
6:53 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.
7:04 Even though this looks and feels exactly the same as if you just ran it in your console, gave it the permissions.
7:11 The simple trigger versus the- installable trigger. The simple trigger doesn't have all the permissions. The installable trigger does. Or more.
7:22 So now we've installed that trigger on edit. Let's go back to our sheet, go to the next row. And I'm gonna just type the same thing, Google Sheets Templates.
7:33 And what is the answer? It's going to be a- Him Actual URL. Done for us. Automatic. All we have to do is enter the name of our Google Drive file.
7:44 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.
7:55 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.
8:00 We've- Hated a little complicated with our- if there's none, return, try to give us an error message. If there's an error.
8:09 But it's really those three lines. Drive app.get files by name. Use the file name. Files.next. File.get URL. Done. And we're just doing it when we are editing the right column.
8:24 And the right cells. pounds. 8 There we go. Hope you enjoyed that one. I hope you enjoyed that little work around of getting a URL when you enter a Google Drive file in a cool sheet.