[00:00] do you ever need to run a script at a
[00:04] particular time each day or at
[00:06] particular time intervals in today's
[00:08] video I'm going to show you how to write
[00:11] a script that you can use to add a
[00:12] certain amount of time every time the
[00:15] script is run or that you can run at a
[00:17] specific time like 9:00 a.m. and it's
[00:19] going to run at 9: not at anywhere
[00:21] between 9 and
[00:21] [Music]
[00:25] 10: all right so let's go ahead and jump
[00:29] right in so we're doing two primary
[00:31] things and so the first thing is the
[00:34] major thing right which is scheduling
[00:36] the script to run again every time it
[00:38] runs based on the time period that you
[00:39] want it to run at so whether that's
[00:43] every day at a particular time or if you
[00:46] R want it to run multiple times a day
[00:48] and so the other part of it is that
[00:50] we're going to do a little cleanup as we
[00:52] go and so every time we run we're going
[00:54] to delete the trigger that it just ran
[00:56] under so let's go ahead and Jump Right
[00:58] In let's start by deleting our old
[01:00] trigger and so we'll just call this
[01:02] delete old
[01:04] trigger and then what we're going to do
[01:05] is we're going to use the property
[01:08] service to track this and so I'm going
[01:11] to check Property Service to see if
[01:12] there's anything existing which the
[01:14] first time we run it there is not going
[01:17] to be we're just going to get the script
[01:18] properties and then we're going to get a
[01:20] property so the property that we're
[01:23] going to call is script
[01:26] ID and so if this does not exist it's
[01:30] going to be null and so we're going to
[01:33] say if it's not null then we're going to
[01:36] delete that trigger and so to delete
[01:37] that trigger we're going to get the list
[01:37] of
[01:40] triggers and that is script app get
[01:46] triggers and then we're just going to
[01:50] quickly iterate through the triggers we
[01:57] here and then inside this we're going to
[02:05] triggers unique
[02:12] ID is equal to the script ID if we've
[02:14] saved it then what we're going to do is
[02:18] we're actually going to delete that
[02:21] trigger and then we can just use that
[02:24] iteration right there and so that will
[02:27] delete our old
[02:30] trigger so this is just a little clean
[02:31] because otherwise what happens in your
[02:33] triggers here as you add more and more
[02:35] triggers you're going to have all the
[02:37] old ones here and it gets a little bit
[02:39] tiresome to have a bunch of them there
[02:41] so let's jump into the main part here
[02:44] and that is determining our new Trigger
[02:46] Time and so what we're doing here is
[02:49] every time the script runs we want to
[02:50] automatically set up to run the next
[02:52] time we want it to
[02:54] run and so let's go ahead and just set
[02:56] some variables like for example let's
[02:59] just do let add minutes and so we'll
[03:02] start start with adding minutes and then
[03:04] I'll show you how to change it if you
[03:06] want to add a day so for example you
[03:08] want it want it to run at 9:00 a.m.
[03:11] every day and so next let's figure out
[03:14] the current time and so let's just do
[03:17] let right now equals to new
[03:19] date and so that will get the current
[03:21] date and
[03:25] time and then let's go ahead and say let
[03:29] this hour equal to right now and then
[03:31] we're going to modif by this so This
[03:35] Hour set hours and we're going to set
[03:38] this because what happens is Google run
[03:40] within a time slot that you've given it
[03:43] and so if you set the script to run at a
[03:45] certain minute for example it's going to
[03:48] run within that minute and so if we
[03:51] don't adjust this then it's going to
[03:52] keep sliding and you're going to kind of
[03:56] have the same effect as when you just do
[03:58] run at certain
[04:00] hours and so what we're going to do here
[04:03] is get right now get
[04:06] hours and if you can see up here here's
[04:09] our variables that we can set here or
[04:11] the arguments so hours minutes seconds
[04:14] and milliseconds so this determines what
[04:16] we want to do here so if for example you
[04:17] were setting it for a certain time each
[04:19] day this is where you'd set like 9 or
[04:21] something like that but let's go ahead
[04:26] and just do this right now get
[04:30] minutes 0 0
[04:32] so what this is doing is it's getting
[04:34] the hours and the minutes from the
[04:36] current time but it's disregarding the
[04:38] seconds and then setting them to
[04:40] zero and so at this point we're going to
[04:45] do let next runtime we're going to do a
[04:46] function we're going to write a new
[04:48] function here we're going to call it
[04:50] minutes later and then we're going to
[04:53] use this hour and add
[04:56] minutes so let's go ahead and write that
[04:58] function real quick so this is going to
[05:00] be a function outside of this one it's
[05:01] going to be just a little helper
[05:04] function and so we're going to call this
[05:06] minutes later and then what we're going
[05:10] to take is right now and
[05:13] minutes and so inside this function what
[05:15] we're going to do is let's start with
[05:17] let this time equals
[05:20] to right now get
[05:23] time and so what that's going to do is
[05:27] get time in milliseconds since was
[05:28] January 1st
[05:30] 1970 and then we're going to do some
[05:32] math on it and that's why we're getting
[05:34] the time so we're going to do let our
[05:39] add and that's going to be minutes
[05:41] time 60
[05:45] seconds time 1,000 milliseconds so this
[05:50] minutes
[05:56] time and so our future date
[06:03] our future time is going to be a new
[06:06] date and then we'll use that new time
[06:08] and then we can return to our
[06:12] date so what this helper function is
[06:18] going to do is take our current time and
[06:21] then add a specific number of minutes
[06:23] and so we can go
[06:25] ahead and take a look at what this will
[06:28] look like let's do a console log and
[06:30] we'll do right
[06:34] now and next run
[06:37] time so let's run this real quick to see
[06:38] what this looks like we'll have to
[06:42] quick and then here we go so current
[06:49] time so
[06:53] 1712 and 1717 and if you notice there's
[06:55] no second on here it's stripping those
[06:58] off so it's taking the current time and
[07:02] adding 5 minutes and so this is the next
[07:06] runtime right here and then this is the
[07:09] right now so it looks like it's working
[07:14] perfectly so we have a 5 minute next
[07:17] runtime and so now all we need to do is
[07:21] create our trigger and then we'll save
[07:24] that trigger ID back to this script ID
[07:26] so we can delete it the next time we run
[07:30] this so let's do let new trigger
[07:33] and this is going to be script app new
[07:35] trigger and then inside the new trigger
[07:37] you reference the function name which is
[07:41] in and then this will be a time based
[07:48] trigger and then what we're going to use
[07:51] the method is going to be at and we're
[07:52] not going to use that hour we're not
[07:53] going to use every whatever we're going
[07:55] to use at and so this is where we can
[08:00] specify a specific time and this will be
[08:03] our next runtime that we came up with so
[08:05] now we can go ahead and create and then
[08:08] we want to get that unique
[08:11] ID and so now we can go to the property
[08:15] service again get scri properties set
[08:17] property and we're going to do that
[08:17] script
[08:24] trigger so at this point we got
[08:30] everything in place to do a 5 minute
[08:32] increment and so if you ran this right
[08:35] now it would set a new trigger for 5
[08:38] minutes from now and then it would run
[08:41] every finite 5 minutes from then on
[08:44] out and so at this point obviously just
[08:45] running the script isn't going to be
[08:45] super
[08:49] useful but we could add our rest of
[08:54] here and so what it'll do is it'll clean
[08:59] up our old triggers it'll process figure
[09:02] out when the next trigger needs to run
[09:03] and then you can run what you're
[09:05] actually running in your script and this
[09:07] will all run automatically so let's go
[09:09] ahead and run it and just see if it
[09:10] creates a trigger so let's check our
[09:13] triggers nothing here right now let's go
[09:15] ahead and run this and so here is when
[09:18] our next trigger should be so
[09:21] 1719 and so let's jump into our
[09:23] triggers up and here we go there is one
[09:25] so let's go ahead and
[09:28] edit and there is our trigger time so
[09:31] that is where working perfectly so I'm
[09:32] going to go ahead and delete this just
[09:32] for
[09:36] now and then let's go back to the editor
[09:38] and so the other thing I want to show
[09:41] you is what if you want to run this at a
[09:44] specific time each day and so often what
[09:46] happens is maybe you want it to run at 9
[09:49] 9:00 a.m. or 8:00 a.m. and when you've
[09:52] done the run at hour it runs anywhere
[09:56] between you know 9 and and 10 so it's
[09:58] it's not working quite the way you're
[10:00] wanting it to work so let's go ahead and
[10:04] look at what it takes to do a specific
[10:07] time every day so we're going to
[10:09] refactor some of this and so one thing
[10:12] we're going to do is let's add another
[10:14] helper function real
[10:16] quick and so this one we're going to
[10:16] call
[10:20] function add
[10:24] days and and this one we're going to do
[10:27] selected date and days so this going to
[10:29] be similar to this minutes later which
[10:31] be slightly different so I'm going to
[10:35] actually steal
[10:38] this and what we're going to do
[10:42] here is in this one let's go ahead and
[10:45] flip this around real quick so 1,000
[10:48] milliseconds and then we need there's 60
[10:49] Su in a
[10:53] minute and 60 minutes in an hour and 2
[10:56] 24 hours in a day and that time is days
[10:59] so if we say one day we need
[11:03] it to be one times the sum of those and
[11:05] so when you do this get time it's the
[11:08] mill sunant since um
[11:12] 1970 and so we just need to turn the
[11:15] date into a number so we can do some
[11:16] math on it and then we'll turn it back
[11:20] into a date so here's our add and then
[11:23] new time add plus this time and then
[11:25] return future dat so we only had to do a
[11:27] little bit of tweaking to get this to
[11:30] work um me actually
[11:32] make sure I finish this here select a
[11:35] date there we go I didn't have that
[11:37] right now in there all right so now we
[11:40] have our ad days so let's look at what
[11:42] it takes to modify this to run on days
[11:46] instead of minutes so we don't need this
[11:48] add minutes we can change this to add
[11:52] days and let's just say
[11:54] one and this is where we can change our
[11:54] set
[12:00] hours so we we can
[12:04] do um this day for example instead of
[12:07] right now and then this day we're going
[12:08] to set
[12:11] hours and then we're going to do this
[12:16] days so uh we else I need to change this
[12:22] one to add
[12:25] days oh speaking of rich I can't have
[12:29] this the same name as that so let's do
[12:33] add there we go all right so last thing
[12:40] here so this is where you can just set
[12:42] what you want the time to be so let's
[12:45] say you want to run at 9:00 a.m. you can
[12:47] just set just like that and you can
[12:48] actually set it if you want to run at
[12:50] 9915 you can set it like that or if you
[12:54] want to run at 7 or at uh
[12:58] 2:00 so you can just determine what time
[13:01] you want it to run here every day and
[13:03] this is how you would set
[13:06] that and then you can just add that day
[13:08] and so let's go ahead and
[13:11] just put these off like
[13:14] that let's go ahead and just run this
[13:17] and see what it comes up with so we have
[13:21] July 9 700 a.m. and so our next run time
[13:24] is going to be tomorrow at 700
[13:31] a.m. and so let's go ahead and do this
[13:32] and then actually run
[13:35] this and so we should have a trigger for
[13:39] a.m. so let's check this
[13:48] out so July 10 7 a.m. all right Perfect
[13:49] all right so that is it for today's
[13:52] video make sure to check out our other
[13:55] videos for more tutorials on Google
[13:59] Sheets and appscript so I'll put a link
[14:02] to a Google Sheets project with this
[14:04] script in it so that way you can get to
[14:08] it and I'll have both copies in there of
[14:10] the adding minutes or the adding days
[14:13] that where you guys can jump in here and
[14:15] get started so as always thanks for