0:00 So a member of better sheets asked this automatic copy sheet question basically can we automatically copy a sheet a spreadsheet file and Create a new spreadsheet file But without the formulas and this gets a little Tricky if we want to do it automatically if we want to do it by hand.
0:21 This is what we would do we would take this Sheet which has formulas here their sums, right? Maybe you have something much more complicated, but you go here you can copy to new spreadsheet and it's going to create a brand new spreadsheet file.
0:35 We can open it up and then here we select the entire sheet. We can edit, copy, edit, paste special values only and now we have a sheet that says this is the answer, right?
0:52 But how do we do this automatically? And it gets a little tricky but I wrote out all of the Apps Script for you so you can just access this sheet if you're a BetterSheets member and get it.
1:02 But I'll walk through know how it works and I'll show you how it works as well. We have ah two tabs, one base which is the one we want to copy.
1:10 We have URL and this is where we're going to get a URL of a copy of the spreadsheet file. The reason I do this is because when it creates It wasn't a copy.
1:20 It wasn't. We'll create it in your Google Sheet Drive, but you have to go to your Google Sheet Drive, go to the most recent, find it, and access it.
1:25 But we can get it. When we create it, we can get the URL and we can copy and paste the URL right here into this A1 and that's what we're going to do.
1:33 We created also a custom menu so that we can do this all at all in one click. So create new file, and again, what it's going to do is it's going to make a new spreadsheet file of a space.
1:42 But it's going to do a couple other things because Apps Script is a little different than these functions that we have available to us manually.
1:48 But watch out, it will actually end up doing exactly the same thing. Right, it's making a copy. There, finish script.
1:55 If we go to URL, there we are. There's the URL, and we can open this, and we can see it's a little different than we did before.
2:02 We have named it new sheet for customer. Maybe we can create this sheet as like, I think the person asking this the member asking this was asking, hey, can I do this for a new customer every time, because they're doing estimates.
2:15 We do have this tab called copy of copy base. We can always rename this and always rename this sheet. But that is the same result that you have when you do it manually.
2:23 You end up with the sheet. And then you have to rename it and rename this sheet tab. So let me walk through the script, because it is a little different than what we did before, which is via the manual process.
2:34 Again, once we have this on open, so first off, we have an on open. We just assign it to this one script, new sheet with values only, and then once we click it, it's going to run this function.
2:47 What this function does is first it creates a spreadsheet called new sheet for customer. You can come into the Apps Script and edit this if you want a different name here.
2:56 Sometimes people are going to want to programmatically or automatically name this based on some input, totally fine, but for our case we just want the new sheet and then we'll go and rename it for the customer ourselves.
3:09 We're going to get the ID we need that. We're going to get the base which is this tab here. This is the tab we want to copy to the new spreadsheet file.
3:17 You can come in here and rename this to whatever you want if you're using this Apps Script yourself. We're going to then copy the base tab and set the name copy of base.
3:28 You can can rename this if you want. Just rename it both of these places or just leave this alone. This won't show up. This will just be an intermediary sort of a copy that we will end up copying and pasting values and then copying.
3:42 So you don't really need to do anything with this two lines of script. If you don't want Thank you. This option here, that's the key.
4:02 This is the copy and paste values. Okay, so then we take whatever we have done here, this value-pasted version, and we copy.
4:16 Copy it to a brand new spreadsheet file, which is the one we created up here. We then do a little bit of cleanup.
4:24 We set the URL of that new spreadsheet file to the URL tab, so if you do want to change this where it's setting it.
4:32 To do that, change the name, get sheet by URL, get sheet by name. Change this URL to whatever name you want and change this range to whatever you want.
4:39 Maybe it's a settings tab or something. Then we're going to do two pieces of cleanup. We need to delete the first sheet that was created in our new sheet.
4:47 So when we create. Get a new sheet up here. It creates a spreadsheet file with a sheet one where we go and delete that because we have a copy of it.
4:55 Uh we have a copy of the base in there. Then we actually ca- uh sorry. Then we delete the copy sheet.
5:02 This is this intermediary one. Go and delete that. If you want to save a copy of this uh meaning you save it in your spreadsheet file and make a copy, all you have to do is comment out this line.
5:15 So just at the beginning line add two slashes and it will comment it out command S, save it. And it won't delete that file or is that tab.
5:23 So again we can do this again and watch now it'll say copy of base and it won't be deleted. Let's see make sure it runs.
5:32 Probably there it is. Copy of base, finish script, it's done and we still have a copy of this. The issue will be though.
5:38 You can only have one name of a tab. Every tab needs to be unique. So when you try to run this again it won't run unless you rename this tab.
5:50 Okay that's one big deal. That's also one. That's why I deleted it. So we can delete it. Ah and we have here the new spreadsheet file of course.
5:59 Hopefully this is really fun for you. This is really interesting. I think this solves a problem that the member had.
6:05 I'm more than happy to solve member's problems. If you join, if you've joined better sheets and you're watching some better sheets down below is this exact sheet and this script you can copy and paste it and use it in your file.
6:18 If you're not yet a member of better sheets become one today at bettersheets.co and you get access to every single spreadsheet.
6:24 I- talk about in every single tutorial and new tools. If you do lifetime membership you get a ton of new tools like coupon code maker, only sheets which is the paywall for google sheets, really cool stuff, all for free if you become a lifetime member at bettersheets.co.