Automatically Copy Sheet to New File with Values Only, No Formulas

0:00 So a member of BetterSheets 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, this is what we would do. We would take this sheet, which has formulas here. They're 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. We can open it up, and then here we select the entire sheet. We can edit, copy, edit, paste special values only.

0:47 And now, we have a sheet that says, This is the answer, right? But how do we do this automatically? And it gets a little tricky, but I wrote out all of the app script for you. So, uh, you can just access this sheet if you're a better sheets member and get it. Uh, and, but I'll walk through how it works and I'll show you how it works as well. We have, uh, two tabs, one base, which is the one we want to copy. 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 a copy, it will 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. 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 a one. And that's what we're going to do.

1:33 We created also a custom menu so that. We can do this all in one click. So create new file. And again, what it's, what it's going to do is it's going to make a new spreadsheet file of a space, but it's going to do a couple other things because Apps Script , it's a little different than these functions that we have available to us manually. But watch out, it will actually end up doing exactly the same thing, right? It's making copy their finished script. 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. We have named it new sheet for customer. Maybe we can create this sheet as like, Uh, 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, they're doing estimates. We do have this tab called copy of copy base. We can always rename this, and always rename this sheet.

2:19 But that is the same result that you have when you do it manually. You end up with the sheet, and then you have to rename it, Uh, 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. Again, once we have, um, this on open. So first off, we have an on open menu. 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 . What this function does is first, it creates a spreadsheet called new sheet for customer. You can come into the app script and edit this if you want a different name here. Sometimes people are going to want to programmatically or automatically name this based on some input. Totally fine, but for our, our case, we just want the new sheet and then we'll go

3:05 and rename it for the customer ourselves. 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. 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. You can rename this if you want. Just rename it both of these places. Or just leave this alone, this won't show up. This'll just be an intermediary, sort of a copy that we will end up copying and pasting values and then copying. So you don't really need to do anything with this two lines of script if you don't want to. But what we're doing here is we're getting the sheet by name, this cop, we're creating that copy inside of this spreadsheet file.

3:52 We're getting the number of rows, the number of columns, and then we're taking that range and we're saying copy to , and this content's only true, this option here, that's the key. This is the copy and paste values . Okay, so then we take whatever we have done here, this, this paste, this value pasted version, and we copy it to a brand new spreadsheet file, which is the one we created up here. Okay. We then do a little bit of cleanup. 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 to, just 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. So when we create a new sheet up here, it creates a spreadsheet file with a sheet 1. We're, we go and delete that because we have a copy of it. Uh, we have a copy of the base in there. Then we actually co uh, sorry. Then we delete the copy sheet . This is this intermediary one. We 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. So just at the beginning of the 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? So again, we can do this again, and watch now it'll say copy of

5:26 base and it won't be deleted. Let's see, make sure it runs. 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, uh, you can only have one name of a tab. Every tab needs to be unique , so when you Uh, try to run this again, it won't run unless you rename this tab. Okay, that's one big deal. That's also why I deleted it. So we can delete it, uh, and we have here the new spreadsheet file, of course. Hopefully this is really fun for you, this is really interesting. I think this solves the problem that the member had. I'm more than happy to solve members problems. If you join the community. If you've joined BetterSheets and you're watching this on BetterSheets, down below

6:13 is this exact sheet and this script. You can copy and paste it and use it in your file. If you're not yet a member of BetterSheets, become one today at bettersheets. co and you get access to every single spreadsheet I talk about in every single tutorial and new tools. If you do lifetime membership, you get a ton of new tools, like Coupon Codemaker, OnlySheets , which is the paywall for Google Sheets. Really cool stuff. All for free if you become a lifetime member at bettersheets. co.