Web Applications Stack Exchange is a question and answer site for power users of web applications. It only takes a minute to sign up.
Sign up to join this community
I have created my spreadsheet with protected cells scattered throughout the document in Google Sheets. Now I want to duplicate the spreadsheet and have the protected cells in the new worksheet keep the same protection. The new spreadsheet would be in the same workbook (tabs). I have tried copy and paste into the new worksheet and everything copies but the protection.
How would I accomplish this task?
I regret you cannot with 'native' functionality (ie not a script). Support has:
If an editor duplicates the protected sheet, copies the workbook, or uploads a new version, sheet protection will be disabled. Also, if a viewer creates a copy of the spreadsheet, the data on the protected sheet can be edited in the copy.
And this warning:
Protected sheets and ranges can help prevent people from unintentionally modifying content in a spreadsheet, but they should not be used for security purposes because not all actions are blocked (for example, printing, copying and pasting, and importing and exporting). Only share spreadsheets with people you trust.
Protected ranges are sheet specific. You may have noticed that when protecting ranges, even if only supplying range references, the relevant sheet name is prepended to these automatically. You might sacrifice those in your 'existing' sheet (say S1) for the sake of your copied sheet (say S2) by manually changing all the S1! to S2! in the Protected sheets and ranges window.
In Google Sheets how do I duplicate a sheet along with its permission
This page has a description on how to copy sheets within the same workbook keeping the protections thru using a script. I have used it and it worked great. Now if only I could figure out how to do the same with a workbook.
As already was mentioned by pnuts, unfortunately there is not a built-in feature to copy a protected range and keep the protection settings.
The alternative is to use Google Apps Script to apply the protection settings, it could be used to copy the sheet among other things. On Class Protection there are several examples that could help to start doing a script. If you need to start learning from the basics, see https://developers.google.com/apps-script/overview
