I’m new to Google Sheets and need help with protecting my template. I have a main template that multiple users access, but I want to prevent accidental edits to the original file.
My goal is to automatically create a copy when someone opens the template. The new copy should have the original name plus today’s date added to it. This way users work on their own version instead of the master template.
The automatic copying should only happen once when the template is first accessed. If someone reopens their personal copy later, it shouldn’t trigger another duplication.
I’m looking for a solution using Google Apps Script or any other method that works reliably. Code examples would be really helpful since I’m just starting out with this.
Had this exact problem last year when we rolled out budget templates. The Apps Script idea works, but I found something simpler. Skip the file open trigger - just add a custom button that runs the script when clicked. It duplicates the sheet, stamps the date, and opens the copy in a new tab. Users get more control and won’t accidentally create duplicates. Here’s the game changer: I used property service to track who already made copies each month. Stops people from creating multiple versions when they forget they already have one. Two things that’ll bite you - double-check sharing permissions on your template file or the script fails silently for non-owners. And during busy times, the script runs slow, so throw in a loading message so users don’t think it’s broken.
Been dealing with this exact thing at work. Laura’s Apps Script approach works but gets messy when you scale up or need more control.
I’d automate this differently. Set up a system that monitors when someone accesses your template, then auto-triggers the duplication. You can add date stamps, user IDs, and folder organization without touching the original.
Best part? You can extend it later. Want email notifications when copies get made? Automatic cleanup of old versions? Integration with your project management system? All doable when you build the automation right.
I’ve seen this pattern work across different file types and platforms. One client needed it for Excel files, another for PDFs. Same logic, different endpoints.
Check out Latenode for this workflow automation. Handles Google Sheets operations really well and you can build the whole flow visually without getting stuck in Apps Script syntax.
hey jack, u can use the onOpen() trigger in Apps Script. first, check if the current file is ur template, then use DriveApp.getFileById() to make a copy with today’s date. don’t forget to redirect users to their new file automagically!