Member Richard asks this question. Answer: use Google Script and a Trigger.
Learn ↓
This tutorial is available for all users. Start with a 7 Day Free Trial today and get access.
Hey there stranger!
Let's not be strangers any more. Start a FREE TRIAL today to view this tutorial, right now.
Richard Asks: Copy Template Tab and Rename to Date Every Week
About this Tutorial
Sheet Resources
Video Transcript
00:00 Hello members. We've got a fun one today. We got a member question from Richard. He asked, um, basically he has a team leader who is expected to fill up a report every single week, um, which there's a couple of clicks that happen that he wants to automate and have the report ready for the, uh, team leaders to fill out.
00:27 So what he wants to do is take a sheet. We're going to call it template and copy it. So basically like a duplicate, but then he wants to name it the name of the date of the week, which is probably Monday and the end of the week, four, five days later, the end of the week, Monday and Friday, and have a hyphen there.
00:50 So like, um, w you'll see, 5 21 to 20, 22, something like this. It's a hyphen and fi and then four days later on the Friday, 22nd, 25th, 26th, 2022.
01:16 So something like that for every week, Monday and Friday, and have that ready for the person to re to fill in.
01:24 So this is very interesting because, um, the code to copy the code is only a few lines. We'll go through that and we need to then create a trigger.
01:39 So triggers can set a script to happen. And we can do that every single week on a specific day, on a specific, in a specific hour, we can't get the exact time.
01:51 It's not like a Cron job if you're used to like coding, uh, somewhere else in like JavaScript and things. Um, but you can get within an hour, you can do it in the middle of the night, before the day starts.
02:03 Um, let's get into this cause it's pretty fun. So we've already created our template. Um, and we're just trying to copy the sheet inside of this worksheet.
02:11 So we're just going to go up to essentially app script, start our coding IDE here. We're going to rename this function, uh, to something else, copy template.
02:25 Let's say, And we don't need any information inside these BRAC, the parentheses, but we do need to do something inside of this bracket.
02:37 The first thing we're going to do is variable. SS. This is a pretty standard thing in, um, uh, Google script.
02:46 Uh, we're going to get active sheet, actually active spreadsheet, and we definitely want to grab the template. We know we're going to copy template.
03:00 So we're going to get template equals S S dot get sheet by name, put it in parentheses template. Now the end result of all of this, uh, we're, we're gonna have some issues with the date, but let's do the last thing we need to do.
03:18 And we'll fill in the name later, template dot copy to, and where do we want to copy it? We want to copy it to this exact sheet.
03:28 So inside this copy, too, we are going to type in spreadsheet, pap dot, get active, just gets the active worksheet that we're in.
03:41 And now we're going to set the name because what happens is when we, we can actually stop this, actually let me save this and show you what happens the very first time you run this.
03:54 This is actually really important. You can run this from here. Since we run the selected function, you're going to have to authorize it.
04:01 I think, see, just go so slow. According to video, there we go. Review permissions the very first time, you're not going to have to do this, like every time.
04:14 So I select my own account. I allow execution and started execution complete. It says that, but if we go back to our, oh, there it is.
04:25 So sometimes the function doesn't run the first time you have to do it again, but here we go. We've got copy of template.
04:31 So it actually took the entire sheet, made a copy of it and renamed it. Copy of the template. Now, if we run that again, let's see what happens.
04:40 Copy of template one. We don't want that name. We want to be able to say what the date is. So let's do something real quick.
04:50 I will show you what happens. It can set name. Now what's the name we want. We can do anything here.
04:59 Like we can really just type in some text in quotes, run it, and it'll say anything. There it goes. If we do it again, it'll do so right now, I've got an error because it's trying to rename it anything, but all it already exists.
05:16 It already exists that we don't, we can't do that. We can't just have text. So let's do this. See, it gives us an error.
05:25 It says it already exists. So we could do new date. This is going to give us another issue and to save that I'm going to run it and look at this, get the entire day we get actually a timestamp.
05:39 So what's funny about this is you can actually name every new one times, cause it should be different every time you run it.
05:48 Yeah. So it's going to create new ones, but it's going to be a totally big name, pretty crazy here, right?
05:56 We need a format, our dates, and we're going to format our date by going up here and saying variable today, date equals let me use utilities, that format, date.
06:13 We want new date inside this parentheses. And how do we want to format it? We're going to format it with the time zone.
06:19 So we're going to say GMT, let's say plus zero. You can always go in here and you need to change it to your, uh, time zone.
06:28 Uh, we're going to just eat GMT and then we need to have the format, the format, a little difficult to figure out, but we want to capital M D D.
06:39 Um, and then why, why, why, why in four wise, there we go. So between capital, it depends, uh, basically there is slightly different between capital and lowercase.
06:52 It depends on if you have a leading zero or not. So let's see what happens here. We want to replace this new date here, delete that with today.
07:03 And now let's see what happens. We can click run, go back. And we have the date. We have today's date, July 11th.
07:12 Awesome. Well, we are almost there, right? We have copied the template we have named the date, but what we really need is the end of this hyphen.
07:24 And then we need seven, like not 15, 20, 22. We need basically four days later, right? If we have a Monday to Friday.
07:35 So how do we do that? Well, we definitely can't do this and I'll show you what we can do. Variable end date equals today, date plus four.
07:50 Let's try that and see what happens. Actually. We need to put this in. So one is the format. This need space, hyphen space at a plus sign and date.
08:05 So we're getting the today date plus this, uh, text, which is a hyphen and two spaces. And then end date.
08:11 Let's see what happens, because if it was this easy, we will be done, but it's not really there. What is this?
08:23 This is, this is, delete that. What did we do here? We got a weird number. We got 7 11, 20, 22, 4. It literally just added text at the end.
08:38 So this doesn't work. So what do we have to do? It took a little scrounging around, but we got, we, I got the answer.
08:47 What we need to do here is we will do variable someday equals new. Okay. We got the new day we got today.
08:58 That is today. Then we say, okay, variable in a week equals this. Some date Set date is a function that we get.
09:15 We basically, we take the new date and we set it to the new date, which is some date again, that get date.
09:27 And then plus four. So this is a bit complicated, but basically we take this new date and we want to set it.
09:35 We take it as basically nothing, but it's a new date. Then we set the date to that date. We get the date, we add four to it.
09:45 It is a little complicated, but then we want to format it again. So we'd stew what we did up here, which we can take all of this and copy and paste it.
09:56 But instead of new date here, right, we take end of week. And so now what should happen is that this end to end date here is going to be today plus four.
10:14 All right, let's go back. Let's delete this horrible 20, 20, 20,024, let's run this. We have an exception. Don't match the method, signature format date.
10:30 We did something wrong. Oh, I see what we did wrong. This needs to be new date. And inside of this, there we go.
10:46 Okay, we're missing. That's new. There we go. Now we save it. We run it. And we got a complete perf, oh my God, this worked out perfectly, July 11th.
11:03 And then four days later, that's a Monday that's today. July 11th is a Monday and the 15th is Friday. And now we have this great little script that essentially in one line copies of the template sets the name of it to today.
11:22 Date hyphen, the end of the day, end of the week. If we run this on Monday, right? If today's date, if the new date today is a Monday, we have to run this on Monday.
11:32 If we run this on Sunday, we have to do this plus four plus five, and we have to set. We have to add a line of code here that says, Hey, we're running on Sunday.
11:41 So we know we have to add one, okay, that's not in this video, but you can hopefully conjecture out of this and the week.
11:49 How did, if you know, you're setting on Sunday, how to do it from Monday to Friday, but this video is only going to cover this.
11:57 We're only going to do this setting on a Monday. How are we so sure that we can set it on Monday?
12:02 Well, two reasons, one, 100%. It is possible that we set a reminder in our email to come to this sheet and create a template for our team leaders.
12:15 So in each of these sheets, we can absolutely come here, uh, Monday morning, uh, not Sunday night, but Monday morning, we can come in here instead of having to click all these things and name the dates, we can literally go to extensions app, script and click on run.
12:31 It's actually going to get an error because it already exists. Probably. Yeah, it won't, it won't be able to rename it, but in a week it won't have the same name.
12:39 So we can do that. Very great. It saves us a few moments, but we still have to remember to come to this sheet every single week and do this.
12:50 And we have to remember to do it before our team leaders come in. What if, what if Google script could do this for us?
12:56 That's where we get triggers. This is an awesome thing. So I'm going to move my face, click add trigger. This is super fast.
13:07 It already has a copy template. Cause it's the only script in our app script. We definitely don't want to change where it's choosing to run the appointment.
13:15 The only thing we want to change is select event source. We're going to click this dropdown. We're going to go to time-driven.
13:22 We get some more options. Select the type of time we want a week timer because we want to do it every single week.
13:29 It shows up more options here. It's already sitting every Monday. Now what time now we can do it midnight to 1:00 AM.
13:38 We can do it like the hour before people come in, maybe six to seven, five to six. What basically I would set at the time I would, what I would do is I would set it the hour before I wake up.
13:52 And the moment I wake up, I just go check and make sure it worked out right. The first time I ever ran this, I'm going to click save.
14:00 Now, this is interesting because it'll happen within an hour. So try to set it, you know, an hour before you really need it, or more than an hour, you can do it midnight 1:00 AM definitely.
14:14 And I would definitely check it. These things have a way of, um, getting out of hand after a long time, like certain templates.
14:24 If you have a template that's only a few, let's say 10 columns and 20 rows. Not going to be a problem for years.
14:33 And you're probably gonna create a new template, uh, an entirely new sheet in a year for each year for these rural kinds of reports, maybe each fiscal year.
14:44 But if your cheat, if your template let's say is a hundred, 200 columns, 10,000 rows, you're gonna S you're gonna hit some limits and you're going to have to come in and either create a new sheet for maybe each quarter.
15:05 So I double check this and make sure that it runs every time, but it should, that's it. We have created a script that copies the template.
15:18 Renames it to both the day that, uh, that it's set. And four days later, or the fifth day, uh, we have triggered it.
15:28 So it runs automatic. It's gonna run automatically. You can check it a few times, and this is pretty cool. I hope Richard, this answers your question.
15:37 And if there's any other better sheets members that want their question answered, feel free to ask. This has been pretty fun.
15:45 Hopefully you learned something cool about AppleScript and triggers fight.
00:27 So what he wants to do is take a sheet. We're going to call it template and copy it. So basically like a duplicate, but then he wants to name it the name of the date of the week, which is probably Monday and the end of the week, four, five days later, the end of the week, Monday and Friday, and have a hyphen there.
00:50 So like, um, w you'll see, 5 21 to 20, 22, something like this. It's a hyphen and fi and then four days later on the Friday, 22nd, 25th, 26th, 2022.
01:16 So something like that for every week, Monday and Friday, and have that ready for the person to re to fill in.
01:24 So this is very interesting because, um, the code to copy the code is only a few lines. We'll go through that and we need to then create a trigger.
01:39 So triggers can set a script to happen. And we can do that every single week on a specific day, on a specific, in a specific hour, we can't get the exact time.
01:51 It's not like a Cron job if you're used to like coding, uh, somewhere else in like JavaScript and things. Um, but you can get within an hour, you can do it in the middle of the night, before the day starts.
02:03 Um, let's get into this cause it's pretty fun. So we've already created our template. Um, and we're just trying to copy the sheet inside of this worksheet.
02:11 So we're just going to go up to essentially app script, start our coding IDE here. We're going to rename this function, uh, to something else, copy template.
02:25 Let's say, And we don't need any information inside these BRAC, the parentheses, but we do need to do something inside of this bracket.
02:37 The first thing we're going to do is variable. SS. This is a pretty standard thing in, um, uh, Google script.
02:46 Uh, we're going to get active sheet, actually active spreadsheet, and we definitely want to grab the template. We know we're going to copy template.
03:00 So we're going to get template equals S S dot get sheet by name, put it in parentheses template. Now the end result of all of this, uh, we're, we're gonna have some issues with the date, but let's do the last thing we need to do.
03:18 And we'll fill in the name later, template dot copy to, and where do we want to copy it? We want to copy it to this exact sheet.
03:28 So inside this copy, too, we are going to type in spreadsheet, pap dot, get active, just gets the active worksheet that we're in.
03:41 And now we're going to set the name because what happens is when we, we can actually stop this, actually let me save this and show you what happens the very first time you run this.
03:54 This is actually really important. You can run this from here. Since we run the selected function, you're going to have to authorize it.
04:01 I think, see, just go so slow. According to video, there we go. Review permissions the very first time, you're not going to have to do this, like every time.
04:14 So I select my own account. I allow execution and started execution complete. It says that, but if we go back to our, oh, there it is.
04:25 So sometimes the function doesn't run the first time you have to do it again, but here we go. We've got copy of template.
04:31 So it actually took the entire sheet, made a copy of it and renamed it. Copy of the template. Now, if we run that again, let's see what happens.
04:40 Copy of template one. We don't want that name. We want to be able to say what the date is. So let's do something real quick.
04:50 I will show you what happens. It can set name. Now what's the name we want. We can do anything here.
04:59 Like we can really just type in some text in quotes, run it, and it'll say anything. There it goes. If we do it again, it'll do so right now, I've got an error because it's trying to rename it anything, but all it already exists.
05:16 It already exists that we don't, we can't do that. We can't just have text. So let's do this. See, it gives us an error.
05:25 It says it already exists. So we could do new date. This is going to give us another issue and to save that I'm going to run it and look at this, get the entire day we get actually a timestamp.
05:39 So what's funny about this is you can actually name every new one times, cause it should be different every time you run it.
05:48 Yeah. So it's going to create new ones, but it's going to be a totally big name, pretty crazy here, right?
05:56 We need a format, our dates, and we're going to format our date by going up here and saying variable today, date equals let me use utilities, that format, date.
06:13 We want new date inside this parentheses. And how do we want to format it? We're going to format it with the time zone.
06:19 So we're going to say GMT, let's say plus zero. You can always go in here and you need to change it to your, uh, time zone.
06:28 Uh, we're going to just eat GMT and then we need to have the format, the format, a little difficult to figure out, but we want to capital M D D.
06:39 Um, and then why, why, why, why in four wise, there we go. So between capital, it depends, uh, basically there is slightly different between capital and lowercase.
06:52 It depends on if you have a leading zero or not. So let's see what happens here. We want to replace this new date here, delete that with today.
07:03 And now let's see what happens. We can click run, go back. And we have the date. We have today's date, July 11th.
07:12 Awesome. Well, we are almost there, right? We have copied the template we have named the date, but what we really need is the end of this hyphen.
07:24 And then we need seven, like not 15, 20, 22. We need basically four days later, right? If we have a Monday to Friday.
07:35 So how do we do that? Well, we definitely can't do this and I'll show you what we can do. Variable end date equals today, date plus four.
07:50 Let's try that and see what happens. Actually. We need to put this in. So one is the format. This need space, hyphen space at a plus sign and date.
08:05 So we're getting the today date plus this, uh, text, which is a hyphen and two spaces. And then end date.
08:11 Let's see what happens, because if it was this easy, we will be done, but it's not really there. What is this?
08:23 This is, this is, delete that. What did we do here? We got a weird number. We got 7 11, 20, 22, 4. It literally just added text at the end.
08:38 So this doesn't work. So what do we have to do? It took a little scrounging around, but we got, we, I got the answer.
08:47 What we need to do here is we will do variable someday equals new. Okay. We got the new day we got today.
08:58 That is today. Then we say, okay, variable in a week equals this. Some date Set date is a function that we get.
09:15 We basically, we take the new date and we set it to the new date, which is some date again, that get date.
09:27 And then plus four. So this is a bit complicated, but basically we take this new date and we want to set it.
09:35 We take it as basically nothing, but it's a new date. Then we set the date to that date. We get the date, we add four to it.
09:45 It is a little complicated, but then we want to format it again. So we'd stew what we did up here, which we can take all of this and copy and paste it.
09:56 But instead of new date here, right, we take end of week. And so now what should happen is that this end to end date here is going to be today plus four.
10:14 All right, let's go back. Let's delete this horrible 20, 20, 20,024, let's run this. We have an exception. Don't match the method, signature format date.
10:30 We did something wrong. Oh, I see what we did wrong. This needs to be new date. And inside of this, there we go.
10:46 Okay, we're missing. That's new. There we go. Now we save it. We run it. And we got a complete perf, oh my God, this worked out perfectly, July 11th.
11:03 And then four days later, that's a Monday that's today. July 11th is a Monday and the 15th is Friday. And now we have this great little script that essentially in one line copies of the template sets the name of it to today.
11:22 Date hyphen, the end of the day, end of the week. If we run this on Monday, right? If today's date, if the new date today is a Monday, we have to run this on Monday.
11:32 If we run this on Sunday, we have to do this plus four plus five, and we have to set. We have to add a line of code here that says, Hey, we're running on Sunday.
11:41 So we know we have to add one, okay, that's not in this video, but you can hopefully conjecture out of this and the week.
11:49 How did, if you know, you're setting on Sunday, how to do it from Monday to Friday, but this video is only going to cover this.
11:57 We're only going to do this setting on a Monday. How are we so sure that we can set it on Monday?
12:02 Well, two reasons, one, 100%. It is possible that we set a reminder in our email to come to this sheet and create a template for our team leaders.
12:15 So in each of these sheets, we can absolutely come here, uh, Monday morning, uh, not Sunday night, but Monday morning, we can come in here instead of having to click all these things and name the dates, we can literally go to extensions app, script and click on run.
12:31 It's actually going to get an error because it already exists. Probably. Yeah, it won't, it won't be able to rename it, but in a week it won't have the same name.
12:39 So we can do that. Very great. It saves us a few moments, but we still have to remember to come to this sheet every single week and do this.
12:50 And we have to remember to do it before our team leaders come in. What if, what if Google script could do this for us?
12:56 That's where we get triggers. This is an awesome thing. So I'm going to move my face, click add trigger. This is super fast.
13:07 It already has a copy template. Cause it's the only script in our app script. We definitely don't want to change where it's choosing to run the appointment.
13:15 The only thing we want to change is select event source. We're going to click this dropdown. We're going to go to time-driven.
13:22 We get some more options. Select the type of time we want a week timer because we want to do it every single week.
13:29 It shows up more options here. It's already sitting every Monday. Now what time now we can do it midnight to 1:00 AM.
13:38 We can do it like the hour before people come in, maybe six to seven, five to six. What basically I would set at the time I would, what I would do is I would set it the hour before I wake up.
13:52 And the moment I wake up, I just go check and make sure it worked out right. The first time I ever ran this, I'm going to click save.
14:00 Now, this is interesting because it'll happen within an hour. So try to set it, you know, an hour before you really need it, or more than an hour, you can do it midnight 1:00 AM definitely.
14:14 And I would definitely check it. These things have a way of, um, getting out of hand after a long time, like certain templates.
14:24 If you have a template that's only a few, let's say 10 columns and 20 rows. Not going to be a problem for years.
14:33 And you're probably gonna create a new template, uh, an entirely new sheet in a year for each year for these rural kinds of reports, maybe each fiscal year.
14:44 But if your cheat, if your template let's say is a hundred, 200 columns, 10,000 rows, you're gonna S you're gonna hit some limits and you're going to have to come in and either create a new sheet for maybe each quarter.
15:05 So I double check this and make sure that it runs every time, but it should, that's it. We have created a script that copies the template.
15:18 Renames it to both the day that, uh, that it's set. And four days later, or the fifth day, uh, we have triggered it.
15:28 So it runs automatic. It's gonna run automatically. You can check it a few times, and this is pretty cool. I hope Richard, this answers your question.
15:37 And if there's any other better sheets members that want their question answered, feel free to ask. This has been pretty fun.
15:45 Hopefully you learned something cool about AppleScript and triggers fight.