Wow, it finally works, hahaha. After trying many times (first time using a script). What I didn't know was after changing the CopyWithProtections file name with my file name and running the script I needed to go back to my original sheet and click the "App Script" in the Extension tab. I can now duplicate my tabs for each month with my ranges still protected and people who make their own copy of my file still have protected ranges in them. Thanks for the script @SpencerFarris ! You've saved me tons of time!
hi. i'd copied your script but there's an error occur. here's what it says. 8:07:16 AM Error TypeError: Cannot read property 'getProtections' of null copyWithProtections @ Copy of Code.gs:4 how can I possibly use your script in 2022 update?
Hello. Excellente video. Unfortunately, the new sheet generated does not copy the protections. In my case, the original sheet is protected but I set exceptions in some cells. Would it be the cause of the script not to run properly?
Yes, that's exactly why. This script is written with range protections in mind and sheet protections work completely differently. I honestly need to just make a new video about that
Hi Spencer! You and I were chatting on Google Doc Editor Community, but my question was flagged and profile disabled for some reason. I hope you can still help me! This is what I responded: I want a user to be able to duplicate the "Monthly Budget" tab at their leisure for numerous months. I would like the tab they duplicate (whether it be the original "monthly budget" tab or not) to bring everything over: formulas, format, and cell protection. Also, they will probably rename the sheets as they go (not sure if that's relevant to the script). The protection in place is "can edit: with warnings". Right now, I am using the script from this video, but when I duplicate a tab manually, it doesn't bring over the protections. I can only create exact duplicates if I run the script multiple times. But I would like the user to be able to duplicate a tab at will and get everything over (like excel). Not sure if this is possible. Thanks again for your help! (-:
Okay Spencer. I am sure someone asked this below, or this video answers it (HA!) - but this is all very new to me and I don't really know what I am doing. What I have is a single google sheet with about 11 titled tabs at the bottom. I successfully was able to share the entire google sheet with 11 tabs, and keep the protected ranges throughout all of them simply by making a copy. Both on Computer and Mobile. Yay, check! Yet, when I make a copy after UNchecking off the boxes of 1. Editors can change permissions and share and 2. Viewers and commenters can see the option to download, print, and copy in the share settings - it resets itself. I know there's got to be a solution out there, because I can't always copy from my phone right away for a client and make these settings mobily. Unless I am missing a step? First, what exactly does Editors changing permissions even mean? I understand the sharing portion, but as for the changing permission - does this mean the same 2 check box items listed above? Second, Is there ANY way to ever share a file where NO ONE except me (owner) can print, make a copy and download? I hate that I am only given the option to not allow "viewers" or "commenters" to have this access when I still need people to make edits, but I don't want them making copies, printing or downloading! I hope that makes sense! I appreciate your help in posting this videos!!!
The first means that Editors can change if it's shared with individuals or with anyone with the link. You cannot restrict Editors from really doing anything with a file. There is a push for Google to adjust that, but right now an Editor is always able to download/print/copy/whatever
Hey! When I duplicate sheet using script specific editors lose privelage to edit on ranges and only I can do. How to fix this ! Eg : column b1 me and say " Jay " can edit. Column b3 me and say " max " can edit. But when I make a duplicate only I can edit they cannot. I have to share range manually.
@spencer thank you for this excellent walk-through and script! I have it working in my Google Sheets workbook. It's all working fine, but the script executes *really* slowly. It literally takes 4-5 minutes to copy one sheet (tab) with protections. (The sheet being copied has some fairly complex formulas and a lot of individual cell protections, but still...) Do you have any tips on how I could speed up the script execution time? Thanks so much.
Hello, Sir! Your video is really helpful. I need to copy a sheet with sheet protection except certain range instead. I've tried to change the "p = nSheet.getRange" but in the end i got some errors. What should i do to make the script run? I need your help. Thanks in advance.
Hi @Spencer! This really saved me from a lot of things, so thank you. I've got it working. Got a question though, what should I do if I want to apply these to multiple sheets? Thanks!
thank you for this. I'm wondering if this will be the fix I need for my sheet. I have a sheet (a template per say) I want people to be able to copy and keep on their computer. I want them to be able to edit some cells but not all of them. So far whenever I share it with them even when I change the share link and add "copy" or "template/preview" they are still able to edit the locked cells. Will changing the scrip allow me share a copy of my file with them that they can edit but still not be able to edit locked cells?
The owner of a sheet can *never* be forbidden from editing a range. If you want your users to have a personalized copy with protections then you need to make the copy for them so that your account is the owner. Also, G Sheets aren't stored on an individuals computer. They are all cloud-based.
@@SpencerFarris ahhh ok. So if I'm giving the sheet to multiple people and I want each of them to be able to edit but still have locked cells I would need to make a different copy for each person and then share it with them individually. Is that correct?
function copyWithProtections() { // Get the active spreadsheet and the template sheet const spreadsheet = SpreadsheetApp.getActiveSpreadsheet(); const templateSheet = spreadsheet.getSheetByName("Template"); // Copy the template sheet and set a new name const newSheet = templateSheet.copyTo(spreadsheet); newSheet.setName("Copied Sheet " + (spreadsheet.getNumSheets() - 1)); // Adjust this to set the desired name // Get the protections of the template sheet const protections = templateSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE); // Loop through the protections and apply them to the new sheet protections.forEach(protection => { const rangeNotation = protection.getRange().getA1Notation(); const newProtection = newSheet.getRange(rangeNotation).protect(); // Remove existing editors and apply original protection settings newProtection.removeEditors(newProtection.getEditors()); if (protection.canDomainEdit()) { newProtection.setDomainEdit(true); } else { newProtection.setDomainEdit(false); } // Copy the description newProtection.setDescription(protection.getDescription()); // Copy the user and group editors const editors = protection.getEditors(); if (editors.length > 0) { newProtection.addEditors(editors); } }); // Get and apply sheet protection if exists const sheetProtection = templateSheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0]; if (sheetProtection) { const newSheetProtection = newSheet.protect(); newSheetProtection.removeEditors(newSheetProtection.getEditors()); if (sheetProtection.canDomainEdit()) { newSheetProtection.setDomainEdit(true); } else { newSheetProtection.setDomainEdit(false); } newSheetProtection.setDescription(sheetProtection.getDescription()); const sheetEditors = sheetProtection.getEditors(); if (sheetEditors.length > 0) { newSheetProtection.addEditors(sheetEditors); } } }
Spencer, I have a template with protections where faculty will be making a COPY of the template. How do I keep the protections in a duplicated google sheet from a template?
When I try to Run it, I get "Exception: Range not found"?? It does copy the Sheet but does not copy the permissions. Can it also be modified that rather than it naming it as "1", it will go up in months instead? So if I copy January, it calls it February, and then March, etc.
Nevermind, I don't think this will work for me. There are updates on a monthly basis. So when I get to October, I will likely want to copy September rather than the original January one each and every time. So with this code, it won't work will it?
Hi, Spencer. I can see your Persistence, thanks for helping people over the comment section. Why don't you drop your Venmo in your video description just so when people want to tip you for your efforts. I also do have a question. Why can't I access Google Script? Like when I go to extensions and choose Script, it just won't load???
Hi Spencer... I have a Google Worksheet with several tabs for our agents to track their daily productivity. I've created a "master" template spreadsheet that I need to duplicate while keeping the protections on all the various sheets/tabs within the worksheet. Your script illustrated here is great for duplicating one sheet/tab within a spreadsheet, however, do you know how I can make a copy of the while file while keeping the protections?
So I would have it loop through all the tabs of the copy, get the protections from the analogous tab of the original, and apply those protections to the copied tab.
Hi Spencer, Thanks for the video. I literally just copy pasted your script above (making sure my sheet was named "Template" as well) and it worked for me. The only issue I now have is that it created the copy at the end (complete right side) of all the sheet tabs and it takes me ages to manually pull each individual sheet back to the left. I have a sheet for each day of the year (= 365 sheets) in my file. Is there any way I can create the copy and have it positioned on the left end of the tabs? Thanks again
@@SpencerFarris Thank you. It worked when I executed it as a separate function. Is there any way I can incorporate this line into the script you provided above in this video ("copy with protections") so it can be done in one execution rather than having to do the move once the copy has been created? Apologies for the inconvenience. I am new to script. Thanks again
@@SpencerFarris Hi Spencer, Thanks for the persistent effort to help me. I'm still not able to get it to work in one execution. This is what I've done. Could you let me know where I have gone wrong: SCRIPT: function copyWithProtections(){ const sh = SpreadsheetApp.getActiveSpreadsheet(); const ss = sh.getSheetByName("Template"); const prot = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE) let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1); let p; for (let i in prot){ p = nSheet.getRange(prot[i].getRange().getA1Notation()).protect(); p.removeEditors(p.getEditors()); if (p.canDomainEdit()) { p.setDomainEdit(false); } } } function myFunction() { nSheet.getActiveSpreadsheet().moveActiveSheet(2); }
@@TenzingDavidSherpa Put that moveActiveSheet() call inside the other function, not as a separate. If you are still having issues please add me as an editor on the sheet
I am having issues and I am pretty sure it is user error lol. Do you have services or package in which you can assist with setting up protections on a series of google sheets?
Hello sir , thanks for this big help, saved a lots of my time . however i have a problem. when the script is finish and i have the copy, its only me that i have the permession to edit, is there any way that script copy the exact editors? cause i have like 20 ppl using the sheet and i want only 5 of them have the access.
I use this. But not working. It just copy from template only but not protecting it function copyWithProtections(){ const sh = SpreadsheetApp.getActiveSpreadsheet(); const ss = sh.getSheetByName("Template"); const prot = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1); let nProt = nSheet.protect();
@@SpencerFarris function copyWithProtections(){ const sh = SpreadsheetApp.getActiveSpreadsheet(); const ss = sh.getSheetByName("Template"); const prot = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1); let nProt = nSheet.protect(); p = nSheet.getRange;
Great Information...Thanks But, Who is Using this Script (triggering the Script), They Can also Edit the Protected Cells. Is there any way to Edit only by Sheet Owners Not by the Triggerers?? @spencer Awaiting for your Reply
Thank you! I see this works when duplicating a sheet within the same document. Is there something to protect various protected sheets/ranges in a document when someone makes a copy of the entire document? Like if I was selling a document to someone and they needed their own individual copy of an entire document?@@SpencerFarris
Great video!! Helped me out tremendously. However, how do I keep custom permission for the ranges the same? For example, I have set a range so that only owner and 1 other user (user1) can edit and I have set another range so that owner and a different user (user2) can edit. How do I keep those custom permissions the same?
Hi @Spencer, thank you for the great idea. I have a protected Sheet that I shared with my editors to edit in the selected cell range only, and I am duplicating the same sheet every month. Now I tried to use this script to protect the sheet, But I could not get it to work for me. Please help me if you can. That would be great.!
Excellent video. The only issue I am having is an error of "Exception: Range not found" for line 10 of the code. I am copying the protection of an entire sheet with 10 exempt ranges so I changed that line of code to reflect sheet not range. Everything else is exactly the same. I was looking for a solution in other comments with similar issue and saw you mention "ON EDIT FUNCTIONS SHOULD NOT BE RUN MANUALLY" but I have no clue what that means. Think you could help me out?
But you actually have a different issue. SHEET protections are stored differently than RANGE protections. You need to call SpreadsheetApp.ProtectionType.SHEET. I *think* it should work like this: function copyWithProtections(){ const sh = SpreadsheetApp.getActiveSpreadsheet(); const ss = sh.getSheetByName("Template"); const prot = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET).getUnprotectedRanges();
let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1); let nProt = nSheet.protect(); nProt.setUnprotectedRanges(unprotected) }
@@SpencerFarris Thank you very much for the reply. I was able to get what I needed for the short term redoing my format using several ranges protections but it would be a whole lot cleaner if I could get this to work getting the unprotected ranges. I got the following error using the code you suggested. "TypeError: ss.getProtections(...).getUnprotectedRanges is not a function copyWithProtections @ SheetCopy.gs:4"
@@SpencerFarris I loved your video!!! I am not familiar with these codes. Can you put the complete code so I can protect a protected sheet that has several editable field ranges?
Thanks for the helpful video. I'm getting "TypeError: Cannot read property 'getSheetByName' of null" error although i typed my sheet name... Please help😊
@@SpencerFarris I'm getting the same error if I click run and I don't understand your reply. You click run in the video and if I just manually duplicate the sheet it doesn't change as the script is set to do so I'm not sure how to make it cooperate. X3 I've not used Google's scripts before so this is a new task for me.
I half figured it out, just isn't holding protection settings for Sheet instead of Ranges. Had to go under the sheet, go to extensions, then apps script. I put the code there and ran it which now duplicated the template but does not carry protection settings unless I change the script back to Ranges and manually specify where can't be edited. Doing a Sheet and specifying where it could be edited was much cleaner but I'm not familiar enough with Google scripts to know the commands to make it work. This method does require me to click run though so I'm not sure if you're expecting it to be done a different way?
Error code in case anyone is interested when trying to run it with SHEET in place of RANGE on line 4 :) Exception: Range not found at copyWithProtections(Code:10:16)
@@Cryostatic Right, there is some other changes to make it actually work for SHEET protections. I apologize for the misinformation in my first comment. The wording from Elgebzaya made it sound like that was the problem and I was too lazy to check the code at the time :p
Thanks so much for the video!! What I'm looking to do is create circa 368 new sheets(tabs) all from the same template that contains protections, as I get the name of each new sheet from a list of cells. I've attached a script on how to make multiple new sheets from a list, is there a way to combine this with your scripts to have each new sheet be a copy of the template while retaining its protections? Thanks! function makeTabs2() { var ss = SpreadsheetApp.getActiveSpreadsheet(); var sheet = ss.getSheetByName("FireDeptList"); var last = sheet.getLastRow();//identifies the last active row on the sheet
for (var i = 1; i < last; i++) {
var tabName = sheet.getRange(i + 1, 1).getValue();//get the range in column A and get the value. try { if (ss.getSheetByName(tabName) == null) { var create = ss.insertSheet(tabName);//create a new sheet with the value } } catch (err) { Logger.log(err);
@@SpencerFarris Hi Spencer, great video, instructions and explanation - I have just subscribed to your channel. I have been trying to solve this issue for the past 24 hours, however I have 153 ranges in exception on a protected sheet, and need to create 10+ sheets, working off a tracking point mouse is too fatiguing. If you could help with this I would be grateful. Thanks, DT
hi - this is great, but I need a tweak and wondered if you could assist? I have a spreadsheet that has multiple tabs, one of which is a template tab. I need my team, who have shared access to the spreadsheet, to be able to copy the template tab, including all the permissions on the template tab. I am a newbie when it comes to scripts, but what I noticed with the one you posted here is that the team would have to go to the scripts section and click 'run' in order to duplicate a tab, not feasible since scrips are more of an expert level function. Any help would be awesome! We use our spreadsheet to manage our adoptable cat population - PURRS
Thanks for the helpful video. What if instead of range protection I want to call sheet protection? I'm getting an "range not found" error. I tried to change the "p = nSheet.getRange" to sheet protection but that just yielded another error. Any additional guidance would be great!
@@SpencerFarris Oh, that's funny, I was just looking at how to copy both range and sheet protection, and I see these comments were just from these past couple of days.
Firstly, thank you, this has saved me a bunch of time! However, on my original sheet, I have the protection set to 'show a warning when editing this range'. But when running the script, the new sheet has protection set to 'restrict who can edit this range' instead of 'show a warning when editing this range'. How can I correct this?
There's a isWarningOnly parameter that can be set. So I think (haven't tested) it should be: function copyWithProtections(){ const sh = SpreadsheetApp.getActiveSpreadsheet(); const ss = sh.getSheetByName("Template"); const prot = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE)
let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1); let p; for (let i in prot){ p = nSheet.getRange(prot[i].getRange().getA1Notation()).protect().setWarningOnly(true); } }
Can anyone help me with this. I have tried this with the sheet I need to copy. I keep getting errors when I try to run the script. I have no idea what I am doing wrong!
@@SpencerFarris TypeError: Cannot read property 'getSheetByName' of Null - I have watched your video countless times but I am not versed in Scripts at all so I am trying to teach myself this one function!
@@SpencerFarris I just copied and pasted your script: function copyWithProtections(){ const sh = SpreadsheetApp.getActiveSpreadsheet(); const ss = sh.getSheetByName("Template"); const prot = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1); let p;
for (let i in prot){ p = nSheet.getRange(prot[i].getRange().getA1Notation()).protect(); p.removeEditors(p.getEditors()); if (p.canDomainEdit()) { p.setDomainEdit(false); } } }
Hi Spencer, great video, instructions and explanation - I have just subscribed to your channel - even if you have retired from creating new ones. This solution is the opposite of what I need to do. I have a protected sheet, with 153 ranges in exception that can be edited. I need to create 10+ sheets, and working off a tracking point mouse is too fatiguing. If you could help with this I would be grateful. Thanks, DT
@@SpencerFarris in my situation also here's what error occurred; 8:07:16 AM Error TypeError: Cannot read property 'getProtections' of null copyWithProtections @ Copy of Code.gs:4
Hi Spenser, it's a great video. I followed your steps but there this error : Exception: The parameters (String) don't match the method signature for SpreadsheetApp.getActiveSpreadsheet. What should I do now?
This is brilliant! Can't believe not everybody uses this in their spreadsheets.
Glad it's helped!
Flies to script like we know exactly where to find it. I appreciate the vid, well done!
My later videos do show the menu to get there, but I was very new to making these when I did this one. Glad you enjoyed.
Wow, it finally works, hahaha. After trying many times (first time using a script). What I didn't know was after changing the CopyWithProtections file name with my file name and running the script I needed to go back to my original sheet and click the "App Script" in the Extension tab. I can now duplicate my tabs for each month with my ranges still protected and people who make their own copy of my file still have protected ranges in them. Thanks for the script @SpencerFarris ! You've saved me tons of time!
SO GLAD
hi. i'd copied your script but there's an error occur. here's what it says.
8:07:16 AM Error
TypeError: Cannot read property 'getProtections' of null
copyWithProtections @ Copy of Code.gs:4
how can I possibly use your script in 2022 update?
THANK YOU!!!!!!!! This is exactly what I needed. What if you want to duplicate a whole notebook with protections?
I just made a new video about making a copy of SHEET protections. Is that what you needed?
Hello. Excellente video. Unfortunately, the new sheet generated does not copy the protections. In my case, the original sheet is protected but I set exceptions in some cells. Would it be the cause of the script not to run properly?
Yes, that's exactly why. This script is written with range protections in mind and sheet protections work completely differently. I honestly need to just make a new video about that
Thank you so much! You just save me and my team a ton of time!
Hooray! :D
Hi Spencer! You and I were chatting on Google Doc Editor Community, but my question was flagged and profile disabled for some reason. I hope you can still help me! This is what I responded:
I want a user to be able to duplicate the "Monthly Budget" tab at their leisure for numerous months. I would like the tab they duplicate (whether it be the original "monthly budget" tab or not) to bring everything over: formulas, format, and cell protection. Also, they will probably rename the sheets as they go (not sure if that's relevant to the script). The protection in place is "can edit: with warnings". Right now, I am using the script from this video, but when I duplicate a tab manually, it doesn't bring over the protections. I can only create exact duplicates if I run the script multiple times. But I would like the user to be able to duplicate a tab at will and get everything over (like excel).
Not sure if this is possible. Thanks again for your help! (-:
It is only possible to duplicate the cell protections if the duplicate tab is made with the script
Ok, thank you so much for responding!! That helps a lot. @@SpencerFarris
@@aleshamunson1290 Good luck! And I have no idea why your post was flagged, that's really really weird
Okay Spencer. I am sure someone asked this below, or this video answers it (HA!) - but this is all very new to me and I don't really know what I am doing. What I have is a single google sheet with about 11 titled tabs at the bottom. I successfully was able to share the entire google sheet with 11 tabs, and keep the protected ranges throughout all of them simply by making a copy. Both on Computer and Mobile. Yay, check!
Yet, when I make a copy after UNchecking off the boxes of 1. Editors can change permissions and share and 2. Viewers and commenters can see the option to download, print, and copy in the share settings - it resets itself. I know there's got to be a solution out there, because I can't always copy from my phone right away for a client and make these settings mobily. Unless I am missing a step?
First, what exactly does Editors changing permissions even mean? I understand the sharing portion, but as for the changing permission - does this mean the same 2 check box items listed above?
Second, Is there ANY way to ever share a file where NO ONE except me (owner) can print, make a copy and download? I hate that I am only given the option to not allow "viewers" or "commenters" to have this access when I still need people to make edits, but I don't want them making copies, printing or downloading!
I hope that makes sense! I appreciate your help in posting this videos!!!
The first means that Editors can change if it's shared with individuals or with anyone with the link.
You cannot restrict Editors from really doing anything with a file. There is a push for Google to adjust that, but right now an Editor is always able to download/print/copy/whatever
@@SpencerFarris thank you for the reply!!! I really wish they offered that!!
And what would be your suggestion for carrying over the permissions?
Hey!
When I duplicate sheet using script specific editors lose privelage to edit on ranges and only I can do. How to fix this !
Eg : column b1 me and say " Jay " can edit.
Column b3 me and say " max " can edit.
But when I make a duplicate only I can edit they cannot. I have to share range manually.
Can you add me as an editor and we can work through it?
thanks Spencer, great one. Really helpful. Do you have apps script to copy sheet into another speadsheet with protection?
What exactly do you mean?
@spencer thank you for this excellent walk-through and script! I have it working in my Google Sheets workbook. It's all working fine, but the script executes *really* slowly. It literally takes 4-5 minutes to copy one sheet (tab) with protections. (The sheet being copied has some fairly complex formulas and a lot of individual cell protections, but still...) Do you have any tips on how I could speed up the script execution time? Thanks so much.
All that has to do with your computer and Internet, and the difficult of copying the sheet itself. Oftne there isn't a way to speed it up
Hello, Sir! Your video is really helpful. I need to copy a sheet with sheet protection except certain range instead. I've tried to change the "p = nSheet.getRange" but in the end i got some errors. What should i do to make the script run? I need your help. Thanks in advance.
Do you make it? Need help here
I need this function too
Did you make it work?
Sorry it's been SO LONG. I just put out a new video with SHEET protections
Hi @Spencer! This really saved me from a lot of things, so thank you. I've got it working. Got a question though, what should I do if I want to apply these to multiple sheets? Thanks!
Instead of just the Template sheet? Create an array with the desired sheet names and loop through them.
I used your copy sheet, however I prefer to protect the SHEET and add exceptions. How do I modify this script for SHEET?
I honestly need to make another video with that
thank you for this. I'm wondering if this will be the fix I need for my sheet. I have a sheet (a template per say) I want people to be able to copy and keep on their computer. I want them to be able to edit some cells but not all of them. So far whenever I share it with them even when I change the share link and add "copy" or "template/preview" they are still able to edit the locked cells. Will changing the scrip allow me share a copy of my file with them that they can edit but still not be able to edit locked cells?
The owner of a sheet can *never* be forbidden from editing a range. If you want your users to have a personalized copy with protections then you need to make the copy for them so that your account is the owner.
Also, G Sheets aren't stored on an individuals computer. They are all cloud-based.
@@SpencerFarris ahhh ok. So if I'm giving the sheet to multiple people and I want each of them to be able to edit but still have locked cells I would need to make a different copy for each person and then share it with them individually. Is that correct?
@@TheCraftyBrick Yes :)
How would I edit this code to include both RANGE and SHEET permissions?
function copyWithProtections() {
// Get the active spreadsheet and the template sheet
const spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
const templateSheet = spreadsheet.getSheetByName("Template");
// Copy the template sheet and set a new name
const newSheet = templateSheet.copyTo(spreadsheet);
newSheet.setName("Copied Sheet " + (spreadsheet.getNumSheets() - 1)); // Adjust this to set the desired name
// Get the protections of the template sheet
const protections = templateSheet.getProtections(SpreadsheetApp.ProtectionType.RANGE);
// Loop through the protections and apply them to the new sheet
protections.forEach(protection => {
const rangeNotation = protection.getRange().getA1Notation();
const newProtection = newSheet.getRange(rangeNotation).protect();
// Remove existing editors and apply original protection settings
newProtection.removeEditors(newProtection.getEditors());
if (protection.canDomainEdit()) {
newProtection.setDomainEdit(true);
} else {
newProtection.setDomainEdit(false);
}
// Copy the description
newProtection.setDescription(protection.getDescription());
// Copy the user and group editors
const editors = protection.getEditors();
if (editors.length > 0) {
newProtection.addEditors(editors);
}
});
// Get and apply sheet protection if exists
const sheetProtection = templateSheet.getProtections(SpreadsheetApp.ProtectionType.SHEET)[0];
if (sheetProtection) {
const newSheetProtection = newSheet.protect();
newSheetProtection.removeEditors(newSheetProtection.getEditors());
if (sheetProtection.canDomainEdit()) {
newSheetProtection.setDomainEdit(true);
} else {
newSheetProtection.setDomainEdit(false);
}
newSheetProtection.setDescription(sheetProtection.getDescription());
const sheetEditors = sheetProtection.getEditors();
if (sheetEditors.length > 0) {
newSheetProtection.addEditors(sheetEditors);
}
}
}
Sorry it's been SO LONG. I just made a new video showing how to do SHEET permissions. It's different
Hey Spencer Thank you for your video and help
Glad it worked
Spencer, I have a template with protections where faculty will be making a COPY of the template. How do I keep the protections in a duplicated google sheet from a template?
Is the shown method not working for you?
@@SpencerFarris Ah, so your instructions will work for forced copies of a template... ok
When I try to Run it, I get "Exception: Range not found"?? It does copy the Sheet but does not copy the permissions. Can it also be modified that rather than it naming it as "1", it will go up in months instead? So if I copy January, it calls it February, and then March, etc.
Nevermind, I don't think this will work for me. There are updates on a monthly basis. So when I get to October, I will likely want to copy September rather than the original January one each and every time. So with this code, it won't work will it?
@@givemeapps123 It should still work fine. Your error above is because ON EDIT FUNCTIONS SHOULD NOT BE RUN MANUALLY
You save my life, thank you for scripts.. but, i have a question, how do we keep editor on the next sheet after duplication is complete ?..
What do you mean?
Hi, Spencer. I can see your Persistence, thanks for helping people over the comment section. Why don't you drop your Venmo in your video description just so when people want to tip you for your efforts.
I also do have a question. Why can't I access Google Script? Like when I go to extensions and choose Script, it just won't load???
Do you get an error message, or just forever loading?
Hi Spencer... I have a Google Worksheet with several tabs for our agents to track their daily productivity. I've created a "master" template spreadsheet that I need to duplicate while keeping the protections on all the various sheets/tabs within the worksheet. Your script illustrated here is great for duplicating one sheet/tab within a spreadsheet, however, do you know how I can make a copy of the while file while keeping the protections?
So I would have it loop through all the tabs of the copy, get the protections from the analogous tab of the original, and apply those protections to the copied tab.
@spencer, is it possible to protect selected sheets, same range in a google sheet file
Yes, though I don't have that written up at present
TypeError: Cannot read properties of null (reading 'getSheetByName')
why??
Hm, not sure. Can you post your code here and I'll look.
Hi Spencer,
Thanks for the video. I literally just copy pasted your script above (making sure my sheet was named "Template" as well) and it worked for me.
The only issue I now have is that it created the copy at the end (complete right side) of all the sheet tabs and it takes me ages to manually pull each individual sheet back to the left. I have a sheet for each day of the year (= 365 sheets) in my file.
Is there any way I can create the copy and have it positioned on the left end of the tabs?
Thanks again
Use SpreadsheetApp.getActiveSpreadsheet().moveActiveSheet(2); to move that sheet to the second position.
@@SpencerFarris Thank you. It worked when I executed it as a separate function.
Is there any way I can incorporate this line into the script you provided above in this video ("copy with protections") so it can be done in one execution rather than having to do the move once the copy has been created?
Apologies for the inconvenience. I am new to script. Thanks again
@@TenzingDavidSherpa nSheet.getActiveSpreadsheet().moveActiveSheet(2);
@@SpencerFarris Hi Spencer,
Thanks for the persistent effort to help me. I'm still not able to get it to work in one execution. This is what I've done. Could you let me know where I have gone wrong:
SCRIPT:
function copyWithProtections(){
const sh = SpreadsheetApp.getActiveSpreadsheet();
const ss = sh.getSheetByName("Template");
const prot = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE)
let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1);
let p;
for (let i in prot){
p = nSheet.getRange(prot[i].getRange().getA1Notation()).protect();
p.removeEditors(p.getEditors());
if (p.canDomainEdit()) {
p.setDomainEdit(false);
}
}
}
function myFunction() {
nSheet.getActiveSpreadsheet().moveActiveSheet(2);
}
@@TenzingDavidSherpa Put that moveActiveSheet() call inside the other function, not as a separate.
If you are still having issues please add me as an editor on the sheet
I am having issues and I am pretty sure it is user error lol. Do you have services or package in which you can assist with setting up protections on a series of google sheets?
Let's talk about it. spencer.farris@gmail.com
Hello sir , thanks for this big help, saved a lots of my time . however i have a problem. when the script is finish and i have the copy, its only me that i have the permession to edit, is there any way that script copy the exact editors? cause i have like 20 ppl using the sheet and i want only 5 of them have the access.
same issue - did you ever find a solution?
Sorry it's been SO LONG. Do you still need help?
Hi Spencer. Can you help me? How to protect SHEET instead RANGE?
I use this. But not working. It just copy from template only but not protecting it
function copyWithProtections(){
const sh = SpreadsheetApp.getActiveSpreadsheet();
const ss = sh.getSheetByName("Template");
const prot = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1);
let nProt = nSheet.protect();
nProt.setUnprotectedRanges
}
The code for protecting a sheet does work a bit differently. What have you tried?
@@SpencerFarris function copyWithProtections(){
const sh = SpreadsheetApp.getActiveSpreadsheet();
const ss = sh.getSheetByName("Template");
const prot = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET);
let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1);
let nProt = nSheet.protect(); p = nSheet.getRange;
nProt.setprotectedRanges
}
Great Information...Thanks
But, Who is Using this Script (triggering the Script), They Can also Edit the Protected Cells.
Is there any way to Edit only by Sheet Owners Not by the Triggerers??
@spencer Awaiting for your Reply
Yes, you need to use an Installed Trigger instead so that you can make it run as the sheet owners
I am sooo confused but this is exactly what I need for one of my documents. Do I copy the entire code verbatim? Where do I paste the code to??
Extensions > Apps Script
Delete what's there, paste this
Thank you! I see this works when duplicating a sheet within the same document. Is there something to protect various protected sheets/ranges in a document when someone makes a copy of the entire document? Like if I was selling a document to someone and they needed their own individual copy of an entire document?@@SpencerFarris
@@jessicaweaverrrIf someone makes a copy of a document THEY are they owner, not you, so protections wouldn't do anything there.
i cannot press the run button what do you think is the problem?
Best guess is it didn't save
does this also work when you copy the entire document and rename? 2020-12-09 copied to new document 2020-12-10?
It should, yes.
Great video!! Helped me out tremendously. However, how do I keep custom permission for the ranges the same? For example, I have set a range so that only owner and 1 other user (user1) can edit and I have set another range so that owner and a different user (user2) can edit. How do I keep those custom permissions the same?
You should be able to call protection.getEditors() from the first range/protection and use that in the second.
@@SpencerFarris Thanks for the speedy reply. However, I don't know anythig about scripting. Where exactly do I put protection.getEditors().
@@camillehenry7484 If you want me to write it for you, please share the sheet with me spencer.farris@gmail.com and show exactly what you want done
@@SpencerFarris Thanks. Appreciated.
how to copy protection sheet wit exeption some range?
What?
Hi @Spencer, thank you for the great idea.
I have a protected Sheet that I shared with my editors to edit in the selected cell range only, and I am duplicating the same sheet every month. Now I tried to use this script to protect the sheet, But I could not get it to work for me. Please help me if you can. That would be great.!
Please share the sheet with me. spencer.farris@gmail.com
Excellent video. The only issue I am having is an error of "Exception: Range not found" for line 10 of the code. I am copying the protection of an entire sheet with 10 exempt ranges so I changed that line of code to reflect sheet not range. Everything else is exactly the same. I was looking for a solution in other comments with similar issue and saw you mention "ON EDIT FUNCTIONS SHOULD NOT BE RUN MANUALLY" but I have no clue what that means. Think you could help me out?
On Edit functions run automatically whenever an edit is made.
But you actually have a different issue. SHEET protections are stored differently than RANGE protections. You need to call SpreadsheetApp.ProtectionType.SHEET. I *think* it should work like this:
function copyWithProtections(){
const sh = SpreadsheetApp.getActiveSpreadsheet();
const ss = sh.getSheetByName("Template");
const prot = ss.getProtections(SpreadsheetApp.ProtectionType.SHEET).getUnprotectedRanges();
let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1);
let nProt = nSheet.protect();
nProt.setUnprotectedRanges(unprotected)
}
@@SpencerFarris Thank you very much for the reply. I was able to get what I needed for the short term redoing my format using several ranges protections but it would be a whole lot cleaner if I could get this to work getting the unprotected ranges.
I got the following error using the code you suggested. "TypeError: ss.getProtections(...).getUnprotectedRanges is not a function
copyWithProtections @ SheetCopy.gs:4"
@@SpencerFarris I loved your video!!! I am not familiar with these codes. Can you put the complete code so I can protect a protected sheet that has several editable field ranges?
@@ArsenalTirePerformanceTech The script is in the description
Thanks for the helpful video. I'm getting "TypeError: Cannot read property 'getSheetByName' of null" error although i typed my sheet name... Please help😊
Don't run the script, it runs automatically whenever an edit occurs.
@@SpencerFarris I'm getting the same error if I click run and I don't understand your reply. You click run in the video and if I just manually duplicate the sheet it doesn't change as the script is set to do so I'm not sure how to make it cooperate. X3 I've not used Google's scripts before so this is a new task for me.
I half figured it out, just isn't holding protection settings for Sheet instead of Ranges. Had to go under the sheet, go to extensions, then apps script. I put the code there and ran it which now duplicated the template but does not carry protection settings unless I change the script back to Ranges and manually specify where can't be edited. Doing a Sheet and specifying where it could be edited was much cleaner but I'm not familiar enough with Google scripts to know the commands to make it work. This method does require me to click run though so I'm not sure if you're expecting it to be done a different way?
Error code in case anyone is interested when trying to run it with SHEET in place of RANGE on line 4 :)
Exception: Range not found
at copyWithProtections(Code:10:16)
@@Cryostatic Right, there is some other changes to make it actually work for SHEET protections.
I apologize for the misinformation in my first comment. The wording from Elgebzaya made it sound like that was the problem and I was too lazy to check the code at the time :p
Thanks so much for the video!! What I'm looking to do is create circa 368 new sheets(tabs) all from the same template that contains protections, as I get the name of each new sheet from a list of cells. I've attached a script on how to make multiple new sheets from a list, is there a way to combine this with your scripts to have each new sheet be a copy of the template while retaining its protections? Thanks!
function makeTabs2() {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheet = ss.getSheetByName("FireDeptList");
var last = sheet.getLastRow();//identifies the last active row on the sheet
for (var i = 1; i < last; i++) {
var tabName = sheet.getRange(i + 1, 1).getValue();//get the range in column A and get the value.
try {
if (ss.getSheetByName(tabName) == null) {
var create = ss.insertSheet(tabName);//create a new sheet with the value
}
}
catch (err) {
Logger.log(err);
}
}
}
Basically you'd just call my script inside your for loop
hello . i have protecteted sheet not range , and i have 18 excepted cells in it. i want to dublicate this sheet ! please help me as soo as u can
So you want to duplicate that sheet and retain the protection + excepted cells?
@@SpencerFarris Hi Spencer, great video, instructions and explanation - I have just subscribed to your channel. I have been trying to solve this issue for the past 24 hours, however I have 153 ranges in exception on a protected sheet, and need to create 10+ sheets, working off a tracking point mouse is too fatiguing. If you could help with this I would be grateful. Thanks, DT
@@SpencerFarrisyes, yes, can you please suggest that -- I am not able to get that to work
@@tusharagarwal2657 Sorry, suggest what?
Dear,
I share my file with another coworker. He wants to duplicate this file. How does he do this?
Are you still having issues?
hi - this is great, but I need a tweak and wondered if you could assist? I have a spreadsheet that has multiple tabs, one of which is a template tab. I need my team, who have shared access to the spreadsheet, to be able to copy the template tab, including all the permissions on the template tab.
I am a newbie when it comes to scripts, but what I noticed with the one you posted here is that the team would have to go to the scripts section and click 'run' in order to duplicate a tab, not feasible since scrips are more of an expert level function.
Any help would be awesome! We use our spreadsheet to manage our adoptable cat population - PURRS
Sorry it's been SO LONG. Do you and the kitties still need help?
How to restrict someone from making a copy of my entire spreadsheet in google sheets?
Thanks.
Give them view access and toggle "Viewers cannot make copies."
Editors cannot be prevented from making a copy.
Is there any chance that I can make a worksheet name that increments as month names?
Sure, but there's not enough in your description to build anything
@@SpencerFarris Instead of 1,2,3.... as names of the worksheet, can it be January, February, etc...
@@angiesalazar2364 Sure, not sure what help you're asking for
Thanks for the helpful video. What if instead of range protection I want to call sheet protection? I'm getting an "range not found" error. I tried to change the "p = nSheet.getRange" to sheet protection but that just yielded another error. Any additional guidance would be great!
So you're copying a sheet that is protected? Are you one of the users with permissions on the sheet?
@@SpencerFarris yes, I own the whole workbook.
@@20100685 Can you add me to it? spencer.farris@gmail.com
@@SpencerFarris Oh, that's funny, I was just looking at how to copy both range and sheet protection, and I see these comments were just from these past couple of days.
@@SpencerFarris Why does it not copy the name of the protected regions over to the new sheet?
Firstly, thank you, this has saved me a bunch of time!
However, on my original sheet, I have the protection set to 'show a warning when editing this range'. But when running the script, the new sheet has protection set to 'restrict who can edit this range' instead of 'show a warning when editing this range'.
How can I correct this?
There's a isWarningOnly parameter that can be set. So I think (haven't tested) it should be:
function copyWithProtections(){
const sh = SpreadsheetApp.getActiveSpreadsheet();
const ss = sh.getSheetByName("Template");
const prot = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE)
let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1);
let p;
for (let i in prot){
p = nSheet.getRange(prot[i].getRange().getA1Notation()).protect().setWarningOnly(true);
}
}
@@SpencerFarris with a bit of investigation and your help I have got it to work...thank you so much, saved a lot of time!!!
@@SpencerFarris Also, liked and subscribed :-)
Hey I am having a bit of trouble doing this can you help me out?
Yes, what's up?
@@SpencerFarris Is it possible to copy permisons even when copying to a whole new spreadsheet that I do not own?
@@Lionxdanger You must own it
Hey, this was really helpful. I need another help from you. Can you please share where i cant message you?
My email is in the description
Hi, can we use this for duplicate sheet ?
The protections only copy if you use the script to copy the sheet; using the File > Duplicate option doesn't run the script
Can I lock cell after data entry automatically using appscript
I have another video on "lock cell after edit." Is that what you want?
hanks for the code
What if they use Make a Copy in file tab?
There is nothing you can do to change the behavior of menu items. This method only works using the custom function.
Could you make it available somewhere I can copy and paste it?
What do you mean?
@@SpencerFarris I mean I want to copy it into a document of mine.
@@johngraham2250 the script is in the description and you can make a copy of the Sheet
@@SpencerFarris how do I make a copy of th Sheet?
@@johngraham2250 The link is in the description. Open it then File > "Make a copy"
Can anyone help me with this. I have tried this with the sheet I need to copy. I keep getting errors when I try to run the script. I have no idea what I am doing wrong!
Hey Nathan. What errors are you getting?
@@SpencerFarris TypeError: Cannot read property 'getSheetByName' of Null - I have watched your video countless times but I am not versed in Scripts at all so I am trying to teach myself this one function!
@@nathanbaxa9542 Can you post your script here? That's an unusual error for you to be getting.
@@SpencerFarris TypeError: Cannot read property 'getProtections' of null copyWithProtections @Code.gs4
@@SpencerFarris I just copied and pasted your script:
function copyWithProtections(){
const sh = SpreadsheetApp.getActiveSpreadsheet();
const ss = sh.getSheetByName("Template");
const prot = ss.getProtections(SpreadsheetApp.ProtectionType.RANGE);
let nSheet = ss.copyTo(sh).setName(sh.getNumSheets()-1);
let p;
for (let i in prot){
p = nSheet.getRange(prot[i].getRange().getA1Notation()).protect();
p.removeEditors(p.getEditors());
if (p.canDomainEdit()) {
p.setDomainEdit(false);
}
}
}
Hi Spencer, great video, instructions and explanation - I have just subscribed to your channel - even if you have retired from creating new ones.
This solution is the opposite of what I need to do. I have a protected sheet, with 153 ranges in exception that can be edited. I need to create 10+ sheets, and working off a tracking point mouse is too fatiguing. If you could help with this I would be grateful. Thanks, DT
I just created a new video for SHEET protections
Where I get script google?
Extensions > Apps Script
i copied the code and cant run it, help
What error are you seeing?
@@SpencerFarris in my situation also
here's what error occurred;
8:07:16 AM Error
TypeError: Cannot read property 'getProtections' of null
copyWithProtections @ Copy of Code.gs:4
@@SpencerFarris I have to finish the setup for my business. help me please. it seems not working in current updates, maybe?
@@streetlight7444 Don't run the function manually, it runs automatically whenever an edit occurs
@@SpencerFarris oh.. So I don't need to run it repeatedly.. thanks
update plssss
2024
WDYM?
Hi Spenser, it's a great video. I followed your steps but there this error :
Exception: The parameters (String) don't match the method signature for SpreadsheetApp.getActiveSpreadsheet.
What should I do now?
Sorry it's been SO LONG. Do you still need help?
Hi Spencer ... I am your Channel Subscriber & just sent you email with Subject "Need Support" .. Kindly help..