A sheet that runs on a clock
Run Apps Script at Certain Specific Times: How to Automatically Manage Timed Triggers
Sheets Ninja
Come in. Read the notes, or watch the walkthrough.
Key words and concepts
- Clock for the sheet — a time you set so the little program runs by itself (a time-driven trigger)
- The little Google program next to the sheet — a short program that lives with the spreadsheet (Apps Script)
- Add a clock — the Triggers / clock button in the editor
- Pick the hour — daily / hourly / weekly, you choose
- Clean up old clocks — remove a trigger you no longer want so it does not keep firing
RESOURCES
- Clocks that start a little program — Google’s page on clocks and other starters. Simple clocks, and clocks you install. Time-driven (clock) is on the table here.
- Clocks you install — including a time you pick — How to add a clock from the editor, and how a program can add or remove its own clock. Time-driven clocks live here.
- The clock builder — hour, day, week — The official command list for picking the hour: every hour, every day, a weekday, a minute you name.
- What the program hears when a clock fires — The note the program gets when a clock runs — the hour, the day, the time zone.
- The little program next to a sheet — How to open the program editor from a sheet (Extensions, then Apps Script). Mentions time-driven clocks at the end.
Read summary
What this is
A Google Sheet can run a little program on a clock you set, with no extra paid scheduler. This film shows how to pick the hour, and how to throw away an old clock so it does not keep firing.
Who it is for
People who already keep records in Sheets and want the sheet to do a job at 9:00, not “sometime between 9 and 10.” You will see a few lines of code; the teacher types them.
What you will be able to do
Explain, in ordinary words, how a sheet can set its own next run, clean up the old clock, and pick a real hour instead of a loose hour window — with no extra paid scheduler.
Takeaways
- A Google Sheet can run a little program on a clock you set. You do not need a separate paid scheduler.
- The usual clock button often fires “sometime in that hour.” This film shows how to pick the minute — 9:00, not anywhere between 9 and 10.
- Each time the program runs, it first throws away the old clock so the list does not pile up.
- It remembers the last clock’s id in a small notebook next to the program, so it knows which one to remove.
- Then it writes a new clock for the next time — five minutes from now, or tomorrow at 9:00.
- If you leave leftover seconds on the clock, the time slowly slides later each run. The teacher zeros those out.
- The same idea works for minutes or for days. You change the helper, not the whole program.
- After you run it once, you can open the clock list and see the new time sitting there.
Timestamps
- Why the sheet should run at a time you pick
- Clean up the old clock first
- Find the saved clock and delete it
- Decide the next run — minutes, or a day
- Set the hour and drop leftover seconds so the clock does not slide
- A helper that adds minutes to now
- How that helper turns minutes into a future time
- Test: five minutes from now
- Create the new clock and save its id
- Run it and see the clock appear
- A helper that adds days instead of minutes
- Turn a day into a number so you can add it
- Pick the hour — 9:00 in the morning, or 9:15
- Test: tomorrow at 7:00 in the morning
Watch video
Full transcript
00:00 — do you ever need to run a script at a particular time each day or at particular time intervals in today's video I'm going to show you how to write a script that you can use to add a certain amount of time every time the script is run or that you can run at a specific time like 9:00 a.m. and it's going to run at 9: not at anywhere between 9 and 10: all right so let's go ahead and jump right in so we're doing two primary things and so the first thing is the major thing right which is scheduling the script to run again every time it runs based on the time period that you want it to run at so whether that's every day at a particular time or if you R want it to run multiple times a day and so the other part of it is that we're going to do a little cleanup as we go and so every time we run we're going to delete the trigger that it just ran under so let's go ahead and Jump Right In let's start by deleting our old
01:00 — trigger and so we'll just call this delete old trigger and then what we're going to do is we're going to use the property service to track this and so I'm going to check Property Service to see if there's anything existing which the first time we run it there is not going to be we're just going to get the script properties and then we're going to get a property so the property that we're going to call is script ID and so if this does not exist it's going to be null and so we're going to say if it's not null then we're going to delete that trigger and so to delete that trigger we're going to get the list of triggers and that is script app get triggers and then we're just going to quickly iterate through the triggers we here and then inside this we're going to
02:05 — triggers unique ID is equal to the script ID if we've saved it then what we're going to do is we're actually going to delete that trigger and then we can just use that iteration right there and so that will delete our old trigger so this is just a little clean because otherwise what happens in your triggers here as you add more and more triggers you're going to have all the old ones here and it gets a little bit tiresome to have a bunch of them there so let's jump into the main part here and that is determining our new Trigger Time and so what we're doing here is every time the script runs we want to automatically set up to run the next time we want it to run and so let's go ahead and just set some variables like for example let's just do let add minutes and so we'll start start with adding minutes and then I'll show you how to change it if you
03:06 — want to add a day so for example you want it want it to run at 9:00 a.m. every day and so next let's figure out the current time and so let's just do let right now equals to new date and so that will get the current time and then let's go ahead and say let this hour equal to right now and then we're going to modif by this so This Hour set hours and we're going to set this because what happens is Google run within a time slot that you've given it and so if you set the script to run at a certain minute for example it's going to run within that minute and so if we don't adjust this then it's going to keep sliding and you're going to kind of have the same effect as when you just do run at certain hours and so what we're going to do here is get right now get hours and if you can see up here here's
04:09 — our variables that we can set here or the arguments so hours minutes seconds and milliseconds so this determines what we want to do here so if for example you were setting it for a certain time each day this is where you'd set like 9 or something like that but let's go ahead and just do this right now get minutes 0 0 so what this is doing is it's getting the hours and the minutes from the current time but it's disregarding the seconds and then setting them to zero and so at this point we're going to do let next runtime we're going to do a function we're going to write a new function here we're going to call it minutes later and then we're going to use this hour and add minutes so let's go ahead and write that function real quick so this is going to be a function outside of this one it's going to be just a little helper function and so we're going to call this minutes later and then what we're going
05:10 — to take is right now and minutes and so inside this function what we're going to do is let's start with let this time equals to right now get time and so what that's going to do is get time in milliseconds since was January 1st 1970 and then we're going to do some math on it and that's why we're getting the time so we're going to do let our add and that's going to be minutes time 60 seconds time 1,000 milliseconds so this minutes time and so our future date our future time is going to be a new date and then we'll use that new time and then we can return to our
06:12 — date so what this helper function is going to do is take our current time and then add a specific number of minutes and so we can go ahead and take a look at what this will look like let's do a console log and we'll do right now and next run time so let's run this real quick to see what this looks like we'll have to quick and then here we go so current time so 1712 and 1717 and if you notice there's no second on here it's stripping those off so it's taking the current time and adding 5 minutes and so this is the next runtime right here and then this is the right now so it looks like it's working
07:14 — perfectly so we have a 5 minute next runtime and so now all we need to do is create our trigger and then we'll save that trigger ID back to this script ID so we can delete it the next time we run this so let's do let new trigger and this is going to be script app new trigger and then inside the new trigger you reference the function name which is in and then this will be a time based trigger and then what we're going to use the method is going to be at and we're not going to use that hour we're not going to use every whatever we're going to use at and so this is where we can specify a specific time and this will be our next runtime that we came up with so now we can go ahead and create and then we want to get that unique ID and so now we can go to the property
08:15 — service again get scri properties set property and we're going to do that script trigger so at this point we got everything in place to do a 5 minute increment and so if you ran this right now it would set a new trigger for 5 minutes from now and then it would run every finite 5 minutes from then on out and so at this point obviously just running the script isn't going to be super useful but we could add our rest of here and so what it'll do is it'll clean up our old triggers it'll process figure out when the next trigger needs to run and then you can run what you're actually running in your script and this will all run automatically so let's go ahead and run it and just see if it creates a trigger so let's check our triggers nothing here right now let's go
09:15 — ahead and run this and so here is when our next trigger should be so 1719 and so let's jump into our triggers up and here we go there is one so let's go ahead and edit and there is our trigger time so that is where working perfectly so I'm going to go ahead and delete this just for now and then let's go back to the editor and so the other thing I want to show you is what if you want to run this at a specific time each day and so often what happens is maybe you want it to run at 9 9:00 a.m. or 8:00 a.m. and when you've done the run at hour it runs anywhere between you know 9 and and 10 so it's it's not working quite the way you're wanting it to work so let's go ahead and look at what it takes to do a specific time every day so we're going to refactor some of this and so one thing we're going to do is let's add another helper function real
10:16 — quick and so this one we're going to call function add days and and this one we're going to do selected date and days so this going to be similar to this minutes later which be slightly different so I'm going to actually steal this and what we're going to do here is in this one let's go ahead and flip this around real quick so 1,000 milliseconds and then we need there's 60 Su in a minute and 60 minutes in an hour and 2 24 hours in a day and that time is days so if we say one day we need it to be one times the sum of those and so when you do this get time it's the mill sunant since um 1970 and so we just need to turn the date into a number so we can do some
11:16 — math on it and then we'll turn it back into a date so here's our add and then new time add plus this time and then return future dat so we only had to do a little bit of tweaking to get this to work um me actually make sure I finish this here select a date there we go I didn't have that right now in there all right so now we have our ad days so let's look at what it takes to modify this to run on days instead of minutes so we don't need this add minutes we can change this to add days and let's just say one and this is where we can change our set hours so we we can do um this day for example instead of right now and then this day we're going to set hours and then we're going to do this
12:16 — days so uh we else I need to change this one to add days oh speaking of rich I can't have this the same name as that so let's do add there we go all right so last thing here so this is where you can just set what you want the time to be so let's say you want to run at 9:00 a.m. you can just set just like that and you can actually set it if you want to run at 9915 you can set it like that or if you want to run at 7 or at uh 2:00 so you can just determine what time you want it to run here every day and this is how you would set that and then you can just add that day and so let's go ahead and just put these off like that let's go ahead and just run this
13:17 — and see what it comes up with so we have July 9 700 a.m. and so our next run time is going to be tomorrow at 700 a.m. and so let's go ahead and do this and then actually run this and so we should have a trigger for a.m. so let's check this out so July 10 7 a.m. all right Perfect all right so that is it for today's video make sure to check out our other videos for more tutorials on Google Sheets and appscript so I'll put a link to a Google Sheets project with this script in it so that way you can get to it and I'll have both copies in there of the adding minutes or the adding days that where you guys can jump in here and get started so as always thanks for