Create a simple report in a Google Sheet that documents every file in your Google Drive that was created in the last 24 hours.
Academy ↓
Hey there stranger!
Every Google Drive File Created in Last 24 Hours
About this Tutorial
Sheet Resources
Video Transcript
00:00 Here's every single drive file created in the last 24 hours in my Google Drive. Here is the Apps Script to do that.
00:07 Ah, but we're gonna do something a little bit different and we're gonna edit this and make this way easier to use.
00:14 But if you don't trust me, let's do sheet.new slash 3 and create a new sheet. We'll call this new sheet and let's see if it runs, if we run this trigger, if it runs.
00:27 So we go into drive created, run and it's going to append everything. So there it is, new sheet. And now every time I run this it's gonna append to the sheet so it creates these files again or at least these lines again.
00:41 Where did I get this? I got this from Digital Inspiration. Ahmit is fantastic. He created this originally, but we're going to edit this and make sure that we can use it to track our basically any files created, modified, but for first I want to show you what his function looks like.
01:03 His function looks like this which is drive modified report and what it does is it looks for everything that's modified.
01:10 If you can see here it says modified date is greater than whatever the start time is one day ago. And so we can run this every 24 hours.
01:20 We can try to create an email from this and say hey here's all the modified em uhm sheets and actually everything in our drive.
01:28 So even if we do doc.new let's keep it as untitled. Let's delete everything here and run the modified one. So let's see, actually just create it again.
01:41 Let's do that and you'll see. Is it on? Ah, we are offline. Okay, we're back online. Let's try, I think it needs to actually have something in here, not just blank.
01:57 So let's run modified again, or sorry create it again. And it will say, there it is, untitled. So, if we run this every 24 hours, every single time we create a file it'll show up.
02:10 But, the one that actually Amit created was drive modified. And it says modified date, one day ago, and we want to be able to edit these things.
02:21 So, I'm going to show you in this video how to edit these things and how to create the one that I did, which is, um, the created report.
02:27 Um, so, we'll start with this and we'll walk through it a little bit. We get the active spreadsheet, we're just getting the sheet that we're on right now.
02:38 We're getting the current time zone where where we are. We create a new date and then we say, okay, a new date one day ago is just today minus this many milliseconds.
02:49 So, it's one day times 24 hours times 60 minutes times 60 seconds times 1000 milliseconds. And that gets us one day ago, so literally 24 hours ago.
02:58 That's our start time. And then we're going to go through the Drive app and do a driveapp.search files and what the search is going to be is that it's either trashed, yes or no, and the modified date is going to be greater than whatever one day ago is in milliseconds.
03:17 So, this modified date is in this like millisecond time. Uhm, and then it will go through each and every file, it will get ah, this is just the list of files, files, and this dot has next and next is pretty complicated or rather confusing sometimes.
03:35 When we're searching through Drive app files, it will give us one file. Then we have to say, does it have a next one, yes or no, and if it has a next one, then we go to the next one, dot next, and that one, we are going to get the name, the URL, and the date created here, which actually this is date
03:55 modified, even though it says the variable is date created, it's actually date modified, uhm. And so, sorry, that actually is the date created, sorry, my, my bad.
04:08 This modified date is the actual modified date. But in order to only get the, uh, files that are date created within the last day, what I did is, I added, uh, a if statement here.
04:22 And I said, if the date created is greater than the start time, we are going to only then, and if it's greater than the start time, actually append the file.
04:36 So, let's look at what is the modified one. So, you'll see modified has a lot. So we'll Select our modified and run it.
04:41 Uhm, it's anything that's been edited by me, by a script. It's a ton of things that are like, even shared files in my drive.
04:50 This is a lot of stuff. And it was like way too much, because it's a lot of sheets that like, someone else modified, but it's shared with me or in my inbox.
04:59 Like this one million checkboxes is a video I made where it's literally a million checkboxes, a million cells, a million checkboxes.
05:04 And if anyone edits that, ever, because anyone has edit access, uhm, this will show up in here. Uhm, there's all types of stuff.
05:13 So I didn't want all of these. I didn't want this entire report. I wanted only the ones that were create, actually created.
05:23 What this allows me to do is run this report and I can then see what is, this is great for like time tracking, uhm, also if you lose files in your drive and you're like, oh, I need to find that file that I just created today, uhm, but it's untitled.
05:40 Which one, one of these untitled documents is it, uhm, that will show up here, but I want to stop having to go over to my, uhm, Apps Script and run this, so let's create a little bit of a menu and we're going to go to snippets.
06:00 BetterSheets.co slash snippets, we'll find this custom menu one, we're just going to copy this, uh, code. And we're going to enter it here in a function unopen, only one unopen can exist, we're going to create a report menu here.
06:14 The first item is, we're going to get created report. And the second one is going to be modified report. And all we have to do is put in the second argument here, the actual function name, there we go, save that, make sure it's saved, and now we refresh the sheet, and we will have We have a report here
06:39 , notated. And now we can say, OK, I just want to create this. Uh, I do author, need to authorize it, OK.
06:47 Very first time I run it. Through here, let's do it again. And it's just going to append. So that's one thing is I can always now save this as like, oh, created on Tuesday.
07:00 Tuesday, July 23rd. There you go. Now, if I want to create a new report, just hit plus. Now I'm on a new sheet. Now I want modified report.
07:11 And now I can take this exact same name once it's done. And I can quickly say my name. Modified. So this is great also if you are trying to keep track of time or projects and say what did I modify today.
07:28 If you're doing this for a boss, if you're doing this for a company and you're like hey I need to just prove that I modified these sheets or these documents today on this day.
07:37 Uh and we have this here as a report. One good thing. Another good thing about appending these as well is that what we can do is instead of having just these individual reports we can say uh created.
07:51 Let's do that. And now this is the act of, we can change, oh my god I need to move, why can't I move this over.
08:02 Let's just move this back. Okay, so now instead of appending to the active sheet I want the script to just append to create it.
08:11 And I want to run this report every single day. So I want to show you how to automatically do that.
08:16 Again we have this drive created report. I'm going to actually copy this so that you have everything, all of these functions separately.
08:26 But instead of drive created report on created tab. That's what we're going to do. We can add this to our UI, add this to our menu if we want and I will do that now.
08:39 I'll show you the whole process. I'm going to add item, add to created and we'll take the function name and move it here.
08:50 Or not move it, but copy it there. Make sure it's all caps created and instead of get active sheet, we are going to do dot Get sheet by name, so easy, created.
09:04 That is it. So now every single time I run this, I will append to the created tab. So let's run, actually let me show you that it is in the menu and let's test it before we do our um automation.
09:22 I always like to test it, add to created. We'll run it and there it is. So even if I'm on this tab, add to created, it finished it, it added here.
09:36 Let's do just Just in case, I was going to say activated, but we don't need to in this case. We want to trigger this every day to just keep adding all of these documents that we've added, created.
09:49 How do we do that? We're going to go over to our triggers, but before we go there, I want to double remind you the function is called drive created report on created tab.
09:58 That is the function that we're going to use. Automate. Go over to the tab triggers on the left side. On the bottom right, add trigger.
10:07 Which function are we going to run? We're going to run drive created report on created tab. We are going to select instead of an event source from spreadsheet, we're going to select time-driven.
10:18 And here, we're going to select every single day. It's going to run, let's say we want to run it just after midnight, but before we start work the next day.
10:28 Um, and it's going to capture everything within the last 24 hours, right? So 2am to 3am is a good time that it captures generally that day.
10:37 We could absolutely do midnight to 1am, but I do find, and this is common in some industries, that people will work beyond midnight, but expect that that work is reported on for the day before midnight, if that makes sense.
10:55 So I will say maybe even like 3 to 4am or 4 to 5am. If you want to be really sure, no one's burning the midnight oil, but let's do 2 to 3am, save.
11:08 And now we know that we triggered this while the spreadsheet was open and we used it. I'm going to allow this to run for a day or two and see if it works and I'll come back.
11:18 If there's any errors, it'll show up here and we can always fix also go over to executions and see if there's any errors here.
11:25 Errors that may occur are include like no rows left, like if it's appending a row it probably will not happen, so let's even test that out.
11:36 Let's just delete all of the rows there and run it and see if we get an error. It probably won't because it's using the function append row.
11:44 I do know that sometimes when writing code and we don't use append row we just go say like go to last row plus one and add the data there, we are finding last row adding one and if that next last row doesn't exist It will fail, but appending row will just go to last and append it.
12:05 Um this is really cool we can find every drive file created in the last 24 hours and automate it and we've also created this awesome report menu here where we can run this report manually if we want.
12:17 Uh again thank you to Amit Agrawal. Thank Amit Agrawal for the original Apps Script and hope you enjoyed these modifications we did to the Apps Script.
12:27 Bye.
00:07 Ah, but we're gonna do something a little bit different and we're gonna edit this and make this way easier to use.
00:14 But if you don't trust me, let's do sheet.new slash 3 and create a new sheet. We'll call this new sheet and let's see if it runs, if we run this trigger, if it runs.
00:27 So we go into drive created, run and it's going to append everything. So there it is, new sheet. And now every time I run this it's gonna append to the sheet so it creates these files again or at least these lines again.
00:41 Where did I get this? I got this from Digital Inspiration. Ahmit is fantastic. He created this originally, but we're going to edit this and make sure that we can use it to track our basically any files created, modified, but for first I want to show you what his function looks like.
01:03 His function looks like this which is drive modified report and what it does is it looks for everything that's modified.
01:10 If you can see here it says modified date is greater than whatever the start time is one day ago. And so we can run this every 24 hours.
01:20 We can try to create an email from this and say hey here's all the modified em uhm sheets and actually everything in our drive.
01:28 So even if we do doc.new let's keep it as untitled. Let's delete everything here and run the modified one. So let's see, actually just create it again.
01:41 Let's do that and you'll see. Is it on? Ah, we are offline. Okay, we're back online. Let's try, I think it needs to actually have something in here, not just blank.
01:57 So let's run modified again, or sorry create it again. And it will say, there it is, untitled. So, if we run this every 24 hours, every single time we create a file it'll show up.
02:10 But, the one that actually Amit created was drive modified. And it says modified date, one day ago, and we want to be able to edit these things.
02:21 So, I'm going to show you in this video how to edit these things and how to create the one that I did, which is, um, the created report.
02:27 Um, so, we'll start with this and we'll walk through it a little bit. We get the active spreadsheet, we're just getting the sheet that we're on right now.
02:38 We're getting the current time zone where where we are. We create a new date and then we say, okay, a new date one day ago is just today minus this many milliseconds.
02:49 So, it's one day times 24 hours times 60 minutes times 60 seconds times 1000 milliseconds. And that gets us one day ago, so literally 24 hours ago.
02:58 That's our start time. And then we're going to go through the Drive app and do a driveapp.search files and what the search is going to be is that it's either trashed, yes or no, and the modified date is going to be greater than whatever one day ago is in milliseconds.
03:17 So, this modified date is in this like millisecond time. Uhm, and then it will go through each and every file, it will get ah, this is just the list of files, files, and this dot has next and next is pretty complicated or rather confusing sometimes.
03:35 When we're searching through Drive app files, it will give us one file. Then we have to say, does it have a next one, yes or no, and if it has a next one, then we go to the next one, dot next, and that one, we are going to get the name, the URL, and the date created here, which actually this is date
03:55 modified, even though it says the variable is date created, it's actually date modified, uhm. And so, sorry, that actually is the date created, sorry, my, my bad.
04:08 This modified date is the actual modified date. But in order to only get the, uh, files that are date created within the last day, what I did is, I added, uh, a if statement here.
04:22 And I said, if the date created is greater than the start time, we are going to only then, and if it's greater than the start time, actually append the file.
04:36 So, let's look at what is the modified one. So, you'll see modified has a lot. So we'll Select our modified and run it.
04:41 Uhm, it's anything that's been edited by me, by a script. It's a ton of things that are like, even shared files in my drive.
04:50 This is a lot of stuff. And it was like way too much, because it's a lot of sheets that like, someone else modified, but it's shared with me or in my inbox.
04:59 Like this one million checkboxes is a video I made where it's literally a million checkboxes, a million cells, a million checkboxes.
05:04 And if anyone edits that, ever, because anyone has edit access, uhm, this will show up in here. Uhm, there's all types of stuff.
05:13 So I didn't want all of these. I didn't want this entire report. I wanted only the ones that were create, actually created.
05:23 What this allows me to do is run this report and I can then see what is, this is great for like time tracking, uhm, also if you lose files in your drive and you're like, oh, I need to find that file that I just created today, uhm, but it's untitled.
05:40 Which one, one of these untitled documents is it, uhm, that will show up here, but I want to stop having to go over to my, uhm, Apps Script and run this, so let's create a little bit of a menu and we're going to go to snippets.
06:00 BetterSheets.co slash snippets, we'll find this custom menu one, we're just going to copy this, uh, code. And we're going to enter it here in a function unopen, only one unopen can exist, we're going to create a report menu here.
06:14 The first item is, we're going to get created report. And the second one is going to be modified report. And all we have to do is put in the second argument here, the actual function name, there we go, save that, make sure it's saved, and now we refresh the sheet, and we will have We have a report here
06:39 , notated. And now we can say, OK, I just want to create this. Uh, I do author, need to authorize it, OK.
06:47 Very first time I run it. Through here, let's do it again. And it's just going to append. So that's one thing is I can always now save this as like, oh, created on Tuesday.
07:00 Tuesday, July 23rd. There you go. Now, if I want to create a new report, just hit plus. Now I'm on a new sheet. Now I want modified report.
07:11 And now I can take this exact same name once it's done. And I can quickly say my name. Modified. So this is great also if you are trying to keep track of time or projects and say what did I modify today.
07:28 If you're doing this for a boss, if you're doing this for a company and you're like hey I need to just prove that I modified these sheets or these documents today on this day.
07:37 Uh and we have this here as a report. One good thing. Another good thing about appending these as well is that what we can do is instead of having just these individual reports we can say uh created.
07:51 Let's do that. And now this is the act of, we can change, oh my god I need to move, why can't I move this over.
08:02 Let's just move this back. Okay, so now instead of appending to the active sheet I want the script to just append to create it.
08:11 And I want to run this report every single day. So I want to show you how to automatically do that.
08:16 Again we have this drive created report. I'm going to actually copy this so that you have everything, all of these functions separately.
08:26 But instead of drive created report on created tab. That's what we're going to do. We can add this to our UI, add this to our menu if we want and I will do that now.
08:39 I'll show you the whole process. I'm going to add item, add to created and we'll take the function name and move it here.
08:50 Or not move it, but copy it there. Make sure it's all caps created and instead of get active sheet, we are going to do dot Get sheet by name, so easy, created.
09:04 That is it. So now every single time I run this, I will append to the created tab. So let's run, actually let me show you that it is in the menu and let's test it before we do our um automation.
09:22 I always like to test it, add to created. We'll run it and there it is. So even if I'm on this tab, add to created, it finished it, it added here.
09:36 Let's do just Just in case, I was going to say activated, but we don't need to in this case. We want to trigger this every day to just keep adding all of these documents that we've added, created.
09:49 How do we do that? We're going to go over to our triggers, but before we go there, I want to double remind you the function is called drive created report on created tab.
09:58 That is the function that we're going to use. Automate. Go over to the tab triggers on the left side. On the bottom right, add trigger.
10:07 Which function are we going to run? We're going to run drive created report on created tab. We are going to select instead of an event source from spreadsheet, we're going to select time-driven.
10:18 And here, we're going to select every single day. It's going to run, let's say we want to run it just after midnight, but before we start work the next day.
10:28 Um, and it's going to capture everything within the last 24 hours, right? So 2am to 3am is a good time that it captures generally that day.
10:37 We could absolutely do midnight to 1am, but I do find, and this is common in some industries, that people will work beyond midnight, but expect that that work is reported on for the day before midnight, if that makes sense.
10:55 So I will say maybe even like 3 to 4am or 4 to 5am. If you want to be really sure, no one's burning the midnight oil, but let's do 2 to 3am, save.
11:08 And now we know that we triggered this while the spreadsheet was open and we used it. I'm going to allow this to run for a day or two and see if it works and I'll come back.
11:18 If there's any errors, it'll show up here and we can always fix also go over to executions and see if there's any errors here.
11:25 Errors that may occur are include like no rows left, like if it's appending a row it probably will not happen, so let's even test that out.
11:36 Let's just delete all of the rows there and run it and see if we get an error. It probably won't because it's using the function append row.
11:44 I do know that sometimes when writing code and we don't use append row we just go say like go to last row plus one and add the data there, we are finding last row adding one and if that next last row doesn't exist It will fail, but appending row will just go to last and append it.
12:05 Um this is really cool we can find every drive file created in the last 24 hours and automate it and we've also created this awesome report menu here where we can run this report manually if we want.
12:17 Uh again thank you to Amit Agrawal. Thank Amit Agrawal for the original Apps Script and hope you enjoyed these modifications we did to the Apps Script.
12:27 Bye.