Get every editor if you're looking for the sheets and documents you've shared with other people and forgot them! Or just want to get a list of them into a Google Sheet.
Explore ↓
Hey there stranger!
Get Every Editor on Every Google File I Own
About this Tutorial
Sheet Resources
Video Transcript
00:00 If we are looking for old sheets or old documents that we've shared with someone, I'm going to show you how to find every single sheet or document or every Google Drive file you've ever shared with someone who is an editor.
00:15 Not just viewer, but editor. Meaning, this is useful for two things. One, I have sheets that I give away a lot, and sometimes people request access to them, and if they request editor access, I may not see that, and I might just click approve, not change it to view.
00:32 So, some errors may happen through actual human error that says, hey, I've given edit access to someone for the sheet that I didn't mean to.
00:41 Or, I'm just looking for the editor of a sheet, or an old sheet that I don't remember the name of, like, but I remember the person that is working on that with me, uh, and it doesn't come up in Google Drive search, so let's just look through every single sheet I've ever edited with someone else.
01:02 I'm gonna show you how to do this. So, here's a list of literally the sheet URL, the email of the person, and the name of the sheet, so that I can see who it is.
01:12 So, let's go over to Extensions, Apps Script. We're gonna write a little bit of Apps Script, but I'm gonna walk you through every single step, and we're gonna get the whole thing done fairly quickly.
01:25 I'm gonna get Editors, rename that. Our function is gonna be Get Editors, and the very thing, very first thing we want to do is variable sheet email.
01:36 Equals SpreadsheetApp.getActiveSpreadsheet.getSheetByName. We need to put parentheses here. GetSheetByName, and we're gonna get Editors.
01:49 Now, the whole structure of this is we're gonna get, in Google Drive, owned by me. We're going to then go through each file, get the Editor.
02:18 Actually, we're going to get the list of editors. If they match. So I'll put it down here. If they match me, ignore, but anyone else get the name, URL, and email address.
02:45 Put it in the editors tab. So that's why we need to get the editors, because ultimately we're going to put it in there.
02:51 Okay. So that's all we're going to do. Let's get our own email. This is pretty simple. Variable my email equals.
03:01 Now we could do the owner of this particular spreadsheet, but we could also do session dot get active user, get email.
03:08 That's me. Or whoever's using this. Maybe there's someone else you need. We want to get all the files, so it's variable files equals drive app dot search files.
03:21 Now the search, this is going to be the most interesting part. In double quotes, we're going to put in single quotes, me, in owners.
03:30 That is it. Now, this is equivalent to going to Google Drive, And in the top, like drive dot google dot com yourself, and in the top, writing owner, me, and just searching for that.
03:44 So, you cannot put owner colon me here, as the search, because this is a slightly different search, it's sort of searching in a SQL format.
03:54 Whereas, on Google Drive itself, if you just go to drive dot google dot com, and you search for owner colon me, you're that's the correct operator to manually search for it.
04:04 But this is it in this search files. I think that's very interesting. So, wow, files dot has next. Now, this is interesting, because every single result of this files is going to have a next, except for the last one.
04:23 So every year, it's basically. It's basically going to give you a really long list of files, however many files you have, and then you check dot has next, and if it's true, we're going to do this.
04:34 So as long as this is true, as long as we have files, and at the very end, it's going to say, there is no more files.
04:39 So it's going to be false, and it's going to stop. So this is like a for loop, but it's called a while loop.
04:44 And our file is equal to files dot next. And I know. This sounds super complicated, and this sounds like sort of a little wrong, that your current file is the files dot next, but that is how it's done when you're going through this Drive app.
05:02 And this is a little in, obvi, unobvious, unintuitive, but it is, or, it works. Okay, so we have our file, and now our sheet name.
05:13 Of whatever file that is, or document name, is equal to file dot get name, very simple. Variable sheet URL, or the URL of any file we get here, is file dot get URL.
05:29 Our editors, our editors equals file get editors, very simple. Now, if you want to do other things, file dot get, you can see there's other options here.
05:43 You can get the ID, it lasts updated. You can get who the owner is, but in this particular case, it's going to be all of ours, because we're searching for all of the sheet, all of the files that have us as the owner.
05:54 Uh, but there's some other things here that you might want, uh, to get. But, uh, for these purposes, we're just going to do this.
06:04 So now, we need to loop through each of the editors. So we're going to go through editors dot for each, and here, it's a little weird, but this is going to be a variable name.
06:21 This editor is actually any word we want. We're going to do this arrow function, and inside of here, we're going to do curly brackets, and say the email equals editor dot get email.
06:40 Again, this editor word here is any variable we want, and we just say, basically, there's a list of editors, there's some results, we're going to name them edit.
06:49 Editor, or anything, and then from that, we're going to get the email, and here's a trick, we can say if email is not equal to my email, and then, uh, end parenthesis, then we're going to have a curly brackets.
07:11 So if it's not my email, I want to do sheet, which is this editor sheet, dot append row, and in square brackets, we're going to have three things, which we set up here.
07:24 We're going to have our sheet URL, comma, email of the person, and the sheet So, at the very end, let's do, spreadsheet app, dot get, UI, dot alert, hey, we're done.
07:46 Save all of that. We're going to run it, and it's going to ask us for approval. We're going to continue once we've selected our account.
08:11 Click continue. It's going to ask us this the very first time we do it, that authorization has to run, but now every time we do it, it's not going to ask us.
08:20 And it's starting. So, this was the, like, sort of a dummy version, but, It's rolling through, trying to find them all.
08:36 Let's put a logger here, just so we see what's going on. We'll save this and run it. And we can see it's going through every single file, so it's gonna go through a bunch.
09:01 And there it's found a bunch of spreadsheets of mine. Put them in this sheet. There we go. And this is great for finding any files in your Google Drive or Google Workspace.
09:12 I actually, by the way, running this, I found a video that is over a gigabyte that I had shared with someone and said, hey, download this, and then I didn't delete it after they had downloaded it.
09:23 They had confirmed they downloaded it, and then I wanted to delete it, and I never did. So this saved me a gigabyte of Drive space.
09:31 Really awesome. Really great to see all of the sheets I've ever made, or even every document, everything. One extra thing you might want to do, in case you have a lot of files and you're only looking for spreadsheet files or even document files, I'm going to add a line here, basically we're going to
09:48 say, variable files, same as me in owners, except we're going to add a MIME type inside of the code. Quotes, we can say, and MIME type equals, and in single quotes, this is a little complicated but it's going to be application slash BND dot Google apps dot spreadsheet.
10:21 Now this we'll only look in spreadsheets and we'll copy and paste this and look in document. So this will only look in document.
10:29 So you can edit this back in, in replacement of this being owners, if you want to only look in documents.
10:42 Only search documents and this one only search spreadsheets. There you go. So I'll delete the other comments and format this document.
10:59 There you go. So you can get the, uh, this link to the sheet down below. And if you're interested in doing more coding and really don't know where to start on better sheets, there's a course called Spreadsheet Automation 101.
11:14 That plus a bunch of other courses are called Master Spreadsheet Automation on Udemy. So on Udemy, there's Master Spreadsheet Automation that includes Spreadsheet Automation 101 and other courses available on BetterSheets.
11:27 So wherever you are, if you want to get the course standalone, get it at Udemy or get it here on bettersheets.co over at Spreadsheet Automation 101.
00:15 Not just viewer, but editor. Meaning, this is useful for two things. One, I have sheets that I give away a lot, and sometimes people request access to them, and if they request editor access, I may not see that, and I might just click approve, not change it to view.
00:32 So, some errors may happen through actual human error that says, hey, I've given edit access to someone for the sheet that I didn't mean to.
00:41 Or, I'm just looking for the editor of a sheet, or an old sheet that I don't remember the name of, like, but I remember the person that is working on that with me, uh, and it doesn't come up in Google Drive search, so let's just look through every single sheet I've ever edited with someone else.
01:02 I'm gonna show you how to do this. So, here's a list of literally the sheet URL, the email of the person, and the name of the sheet, so that I can see who it is.
01:12 So, let's go over to Extensions, Apps Script. We're gonna write a little bit of Apps Script, but I'm gonna walk you through every single step, and we're gonna get the whole thing done fairly quickly.
01:25 I'm gonna get Editors, rename that. Our function is gonna be Get Editors, and the very thing, very first thing we want to do is variable sheet email.
01:36 Equals SpreadsheetApp.getActiveSpreadsheet.getSheetByName. We need to put parentheses here. GetSheetByName, and we're gonna get Editors.
01:49 Now, the whole structure of this is we're gonna get, in Google Drive, owned by me. We're going to then go through each file, get the Editor.
02:18 Actually, we're going to get the list of editors. If they match. So I'll put it down here. If they match me, ignore, but anyone else get the name, URL, and email address.
02:45 Put it in the editors tab. So that's why we need to get the editors, because ultimately we're going to put it in there.
02:51 Okay. So that's all we're going to do. Let's get our own email. This is pretty simple. Variable my email equals.
03:01 Now we could do the owner of this particular spreadsheet, but we could also do session dot get active user, get email.
03:08 That's me. Or whoever's using this. Maybe there's someone else you need. We want to get all the files, so it's variable files equals drive app dot search files.
03:21 Now the search, this is going to be the most interesting part. In double quotes, we're going to put in single quotes, me, in owners.
03:30 That is it. Now, this is equivalent to going to Google Drive, And in the top, like drive dot google dot com yourself, and in the top, writing owner, me, and just searching for that.
03:44 So, you cannot put owner colon me here, as the search, because this is a slightly different search, it's sort of searching in a SQL format.
03:54 Whereas, on Google Drive itself, if you just go to drive dot google dot com, and you search for owner colon me, you're that's the correct operator to manually search for it.
04:04 But this is it in this search files. I think that's very interesting. So, wow, files dot has next. Now, this is interesting, because every single result of this files is going to have a next, except for the last one.
04:23 So every year, it's basically. It's basically going to give you a really long list of files, however many files you have, and then you check dot has next, and if it's true, we're going to do this.
04:34 So as long as this is true, as long as we have files, and at the very end, it's going to say, there is no more files.
04:39 So it's going to be false, and it's going to stop. So this is like a for loop, but it's called a while loop.
04:44 And our file is equal to files dot next. And I know. This sounds super complicated, and this sounds like sort of a little wrong, that your current file is the files dot next, but that is how it's done when you're going through this Drive app.
05:02 And this is a little in, obvi, unobvious, unintuitive, but it is, or, it works. Okay, so we have our file, and now our sheet name.
05:13 Of whatever file that is, or document name, is equal to file dot get name, very simple. Variable sheet URL, or the URL of any file we get here, is file dot get URL.
05:29 Our editors, our editors equals file get editors, very simple. Now, if you want to do other things, file dot get, you can see there's other options here.
05:43 You can get the ID, it lasts updated. You can get who the owner is, but in this particular case, it's going to be all of ours, because we're searching for all of the sheet, all of the files that have us as the owner.
05:54 Uh, but there's some other things here that you might want, uh, to get. But, uh, for these purposes, we're just going to do this.
06:04 So now, we need to loop through each of the editors. So we're going to go through editors dot for each, and here, it's a little weird, but this is going to be a variable name.
06:21 This editor is actually any word we want. We're going to do this arrow function, and inside of here, we're going to do curly brackets, and say the email equals editor dot get email.
06:40 Again, this editor word here is any variable we want, and we just say, basically, there's a list of editors, there's some results, we're going to name them edit.
06:49 Editor, or anything, and then from that, we're going to get the email, and here's a trick, we can say if email is not equal to my email, and then, uh, end parenthesis, then we're going to have a curly brackets.
07:11 So if it's not my email, I want to do sheet, which is this editor sheet, dot append row, and in square brackets, we're going to have three things, which we set up here.
07:24 We're going to have our sheet URL, comma, email of the person, and the sheet So, at the very end, let's do, spreadsheet app, dot get, UI, dot alert, hey, we're done.
07:46 Save all of that. We're going to run it, and it's going to ask us for approval. We're going to continue once we've selected our account.
08:11 Click continue. It's going to ask us this the very first time we do it, that authorization has to run, but now every time we do it, it's not going to ask us.
08:20 And it's starting. So, this was the, like, sort of a dummy version, but, It's rolling through, trying to find them all.
08:36 Let's put a logger here, just so we see what's going on. We'll save this and run it. And we can see it's going through every single file, so it's gonna go through a bunch.
09:01 And there it's found a bunch of spreadsheets of mine. Put them in this sheet. There we go. And this is great for finding any files in your Google Drive or Google Workspace.
09:12 I actually, by the way, running this, I found a video that is over a gigabyte that I had shared with someone and said, hey, download this, and then I didn't delete it after they had downloaded it.
09:23 They had confirmed they downloaded it, and then I wanted to delete it, and I never did. So this saved me a gigabyte of Drive space.
09:31 Really awesome. Really great to see all of the sheets I've ever made, or even every document, everything. One extra thing you might want to do, in case you have a lot of files and you're only looking for spreadsheet files or even document files, I'm going to add a line here, basically we're going to
09:48 say, variable files, same as me in owners, except we're going to add a MIME type inside of the code. Quotes, we can say, and MIME type equals, and in single quotes, this is a little complicated but it's going to be application slash BND dot Google apps dot spreadsheet.
10:21 Now this we'll only look in spreadsheets and we'll copy and paste this and look in document. So this will only look in document.
10:29 So you can edit this back in, in replacement of this being owners, if you want to only look in documents.
10:42 Only search documents and this one only search spreadsheets. There you go. So I'll delete the other comments and format this document.
10:59 There you go. So you can get the, uh, this link to the sheet down below. And if you're interested in doing more coding and really don't know where to start on better sheets, there's a course called Spreadsheet Automation 101.
11:14 That plus a bunch of other courses are called Master Spreadsheet Automation on Udemy. So on Udemy, there's Master Spreadsheet Automation that includes Spreadsheet Automation 101 and other courses available on BetterSheets.
11:27 So wherever you are, if you want to get the course standalone, get it at Udemy or get it here on bettersheets.co over at Spreadsheet Automation 101.