Learn how to automate creating a template and moving the tab to the far right, or the end of the google sheets tabs.
Academy ↓
Hey there stranger!
How To Duplicate a Tab and Move it to the End in Google Sheets
About this Tutorial
Sheet Resources
Video Transcript
0:01 So, hello. In this video, you're going to learn how to duplicate a tab and move it to the end in Google Sheets automatically.
0:07 So here's a few things that we have already. We already have some Apps Script, uh, created that's in Extensions Apps Script.
0:15 We've created a menu here that just has two items. Uh, it's going to move a tab to the far right or far left.
0:23 This is in a previous video, and it's a very simple code. It's just saying, get the active spreadsheet, the entire file, It's going out how many total sheets there are and move the current active sheet to the end, that's the far right, with this total number of sheets.
0:39 It's just moving it there. But if you want to move it to the far left, we're just going to move the active sheet to number one, the first position of the total sheets.
0:48 But let's add to this. We want to create a script that takes this template, copies it, it's going to rename it to today's date.
0:56 And then, one issue is, if we duplicate this ourselves. Let's duplicate it and rename it November 7th. There we go.
1:06 Ah, now let's do that again. Gonna see something happen. Duplicate. It's going to create the duplication right next to the template.
1:16 So the point of this video is how do we create this template, or duplicate, of the template, and then move it all the way to the far right at the same time.
1:28 So let's call this November 6th. And let's just delete the sales data here. Maybe it is a real template and we want to put in that sales data.
1:38 Hit there. Okay, let's go back to our script and write a new script calling, ah, to duplicate that template. We're going to say function duplicate template.
1:53 I'm going to add some bars, we're going to say const ss equals spreadsheet app. dot get active spreadsheet. That's just calling the entire spreadsheet file.
2:06 We're also going to need template equals ss dot get sheet by name and we're going to call this template. That is just the name of the sheet that we want to copy.
2:23 Now we want to get today's date. const today equals new date we can format this if we want. Let's say const today string equals utilities dot format date today.
2:42 And what format do we want it in? Want it in GMT minus six time zone and let's put it in year year year hyphen m m hyphen day day.
3:00 Let's put it in that format. We can change that format just by changing this text here. All right. Now we need to actually copy it.
3:08 Uh, we're going to use, let's create a new sheet. New sheet equals template dot copy two. And we're going to copy it to the spreadsheet.
3:25 We want to set the name. So new sheet dot set name. And we're going to set it as the string of today.
3:36 And now we need to make sure that this is the active sheet. Yes, yes.
3:51 And we want to set active sheet as the new sheet. And now, once that's the active sheet, we can actually call this function here, moveTab, right.
4:11 So let's see this in action. I want to add item here. Uh, we're going to call it duplicateTemplate. And we're going to send it to duplicate template function.
4:29 See this name here? Matches this function name here. Once we have that done, we will refresh our sheet so that our custom menu will show up here next to help.
4:40 And now let's see if this works. We will need authorize first. And now we have today's date. And it is to the far right.
4:50 So we can go back to template. Let's, uh, rename this to something else, something else. And test it again. go up to custom menu, do duplicate template.
5:03 It's running the script and it's moving it to the far right and it is copying that template. Doing everything we want.
5:09 So to refresh, or review rather, we have created an unopened function that creates our custom menu. We have created a function that copies the duplicates the template and renames it as a formatted date.
5:28 And then Then it calls this other function, moveTabToFarRight, where which that one is just setting whatever active spreadsheet it is to the position of the number of total sheets.
5:39 So that's going to be moving it to the far right. If we, however, want to create a, uh, duplicate and move it to tab to the far left, we just use this function here, moveTabToFarLeft, instead of far right.
5:52 So there you go. We've created a duplicate and moved it to the end of the Google Sheet. Enjoy.
0:07 So here's a few things that we have already. We already have some Apps Script, uh, created that's in Extensions Apps Script.
0:15 We've created a menu here that just has two items. Uh, it's going to move a tab to the far right or far left.
0:23 This is in a previous video, and it's a very simple code. It's just saying, get the active spreadsheet, the entire file, It's going out how many total sheets there are and move the current active sheet to the end, that's the far right, with this total number of sheets.
0:39 It's just moving it there. But if you want to move it to the far left, we're just going to move the active sheet to number one, the first position of the total sheets.
0:48 But let's add to this. We want to create a script that takes this template, copies it, it's going to rename it to today's date.
0:56 And then, one issue is, if we duplicate this ourselves. Let's duplicate it and rename it November 7th. There we go.
1:06 Ah, now let's do that again. Gonna see something happen. Duplicate. It's going to create the duplication right next to the template.
1:16 So the point of this video is how do we create this template, or duplicate, of the template, and then move it all the way to the far right at the same time.
1:28 So let's call this November 6th. And let's just delete the sales data here. Maybe it is a real template and we want to put in that sales data.
1:38 Hit there. Okay, let's go back to our script and write a new script calling, ah, to duplicate that template. We're going to say function duplicate template.
1:53 I'm going to add some bars, we're going to say const ss equals spreadsheet app. dot get active spreadsheet. That's just calling the entire spreadsheet file.
2:06 We're also going to need template equals ss dot get sheet by name and we're going to call this template. That is just the name of the sheet that we want to copy.
2:23 Now we want to get today's date. const today equals new date we can format this if we want. Let's say const today string equals utilities dot format date today.
2:42 And what format do we want it in? Want it in GMT minus six time zone and let's put it in year year year hyphen m m hyphen day day.
3:00 Let's put it in that format. We can change that format just by changing this text here. All right. Now we need to actually copy it.
3:08 Uh, we're going to use, let's create a new sheet. New sheet equals template dot copy two. And we're going to copy it to the spreadsheet.
3:25 We want to set the name. So new sheet dot set name. And we're going to set it as the string of today.
3:36 And now we need to make sure that this is the active sheet. Yes, yes.
3:51 And we want to set active sheet as the new sheet. And now, once that's the active sheet, we can actually call this function here, moveTab, right.
4:11 So let's see this in action. I want to add item here. Uh, we're going to call it duplicateTemplate. And we're going to send it to duplicate template function.
4:29 See this name here? Matches this function name here. Once we have that done, we will refresh our sheet so that our custom menu will show up here next to help.
4:40 And now let's see if this works. We will need authorize first. And now we have today's date. And it is to the far right.
4:50 So we can go back to template. Let's, uh, rename this to something else, something else. And test it again. go up to custom menu, do duplicate template.
5:03 It's running the script and it's moving it to the far right and it is copying that template. Doing everything we want.
5:09 So to refresh, or review rather, we have created an unopened function that creates our custom menu. We have created a function that copies the duplicates the template and renames it as a formatted date.
5:28 And then Then it calls this other function, moveTabToFarRight, where which that one is just setting whatever active spreadsheet it is to the position of the number of total sheets.
5:39 So that's going to be moving it to the far right. If we, however, want to create a, uh, duplicate and move it to tab to the far left, we just use this function here, moveTabToFarLeft, instead of far right.
5:52 So there you go. We've created a duplicate and moved it to the end of the Google Sheet. Enjoy.