Hi everyone! I need help figuring out how to set up one main Google Sheets file that everyone in my company can use without messing up the original.
We have this audit checklist spreadsheet that all our staff members need to use. Right now they make copies and save them to their personal drives. The problem is people keep changing important cells that have formulas in them. Then they complain about errors, but it’s because they deleted stuff they weren’t supposed to touch.
I want to know if there’s a way to have just one protected sheet that automatically updates when I change the master version. Everyone would need access but they shouldn’t be able to edit the formula cells.
I looked into using IMPORTRANGE but that seems like it would be a nightmare. We have tons of dropdown menus and lots of employees. I’d have to make hundreds of copies and rename them all individually. That doesn’t seem like the right approach.
Does anyone know a better way to handle this? I really need a solution that keeps the original safe while still letting people use it for their work. Thanks for any suggestions!
Here’s what worked for us: protect the master sheet first, then use Google Drive sharing smartly. I locked down all formula cells and formatting in the original, then shared it as “View only” to everyone.
The trick is a simple script that copies the protected template to each user’s drive when they need it. Script keeps all protections but gives them their own copy. You can trigger it through a web app or just a shared link.
Takes 30 minutes to set up, then it’s automatic. No template gallery headaches and nobody can break the master. I’ve been doing this for two years with our project tracking sheets.
Google Workspace’s template gallery is perfect for this. Create your audit checklist as a template and publish it to your org’s gallery. When people need it, they’ll get a fresh copy with all protections intact. Here’s how: go to your master sheet, click the template gallery icon, hit “Submit template” and make it available to your organization. Before you submit, protect those formula cells - select them and go to Data > Protect sheets and ranges. Best part? Updates to your original template show up in new copies automatically. No IMPORTRANGE headaches or manual sending. Your team gets clean copies every time, formulas stay protected.
honestly, google forms is the way to go! just set up a form linked to ur master sheet. lock the original and let folks fill out the form. that way everything goes where it’s supposed to without messing up ur formulas. way better than managing permissions!