Google Sheets Protector Template

This template provides a Google Sheet pre-configured with an Apps Script to manage granular sheet (tab) permissions using Google Groups. This allows you to grant different Google Groups edit access to specific tabs within the same spreadsheet, while keeping other tabs protected.

Project Status: Alpha

This project is currently in alpha status.

How it Works

1. Centralized Group Management: Your organization's Google Groups (managed by a system like gdrive_permissions1) are the source of truth for user membership.
2. SheetPermissions Tab: You define which Google Group has edit access to which sheet (tab) within this spreadsheet.
3. Apps Script Automation: The embedded Apps Script reads your configuration and automatically applies Google Sheets protections, granting edit access to the specified Google Groups.

Getting Started

Follow these steps to use this template:

1. Make a Copy:

   • Open this template Google Sheet.
   • Go to File > Make a copy.
   • Rename the copy as desired and save it to your Google Drive.

2. Import Your Data (Optional):

   • If you have an existing spreadsheet you want to protect, copy its sheets (tabs) into your new, copied template spreadsheet.
   • To do this, open your existing spreadsheet, right-click on a tab, select Copy to > Existing spreadsheet..., and choose your new template copy. Repeat for all relevant tabs.

3. Configure Permissions:

   • Go to the SheetPermissions tab in your copied spreadsheet.
   • Each row defines a permission rule. You can specify either a Sheet Name for an individual sheet or a Sheet Name Pattern for a group of sheets.
   • For a single sheet:
     • Enter the exact name of the sheet (tab) in the "Sheet Name" column.
     • Enter the full email address of the Google Group in the "Group Email" column.
   • For a group of sheets:
     • Leave the "Sheet Name" column blank.
     • In the "Sheet Name Pattern" column, enter a regular expression to match multiple sheet names.
     • Use .* as part of your pattern (e.g., Project.* to match all sheets starting with "Project").
     • Use a single * to match all sheets in the spreadsheet.
     • Enter the corresponding "Group Email".
   • Example:

     Sheet Name	Sheet Name Pattern	Group Email
     My Data Tab		my-team-editors@yourdomain.com
     	Audit-.*	auditors-group@yourdomain.com
     	*	all-staff-viewers@yourdomain.com

   Note: Rules for individual sheets (using "Sheet Name") take precedence over pattern-based rules. If a sheet is matched by both a specific rule and a pattern, the specific rule will be applied.

4. Run the Script:

   • In your copied spreadsheet, look for a new custom menu item at the top called "Sheet Protector".
   • Click on "Sheet Protector" > "Apply Permissions".

5. Authorize the Script (First Time Only):

   • The first time you run the script, Google will prompt you to authorize it. This is a standard security measure.
   • Click "Review permissions," select your Google account, and then click "Allow" to grant the necessary permissions (primarily to manage your spreadsheets).

6. Verify:

   • Check your sheets to ensure the protections have been applied correctly. Users who are not part of the specified Google Group should find the protected sheets read-only.

Important Notes

• Script Owner Always Has Access: The owner of the spreadsheet (you, after making a copy) will always have full edit access to all sheets, regardless of the protections applied by the script.
• Group Membership Changes: When users are added to or removed from the Google Groups you specify, their access to the protected sheets will automatically update. You do not need to re-run the script for group membership changes to take effect.
• "Show Instructions" Menu: You can always access these instructions by clicking "Sheet Protector" > "Show Instructions".
• Initial Setup: If the SheetPermissions or Instructions tabs are ever accidentally deleted, you can manually run the setupInitialSheets() function from the Apps Script editor (Extensions > Apps Script) to recreate them.