Manage User Permissions Programmatically with Apps Script (Google Sheet)

Managing who can view or edit a Google Sheet is essential, especially when collaborating with teams, clients, or students. Google Apps Script makes it possible to programmatically manage user permissions, so you can automate access control without clicking through sharing settings manually.

Whether you’re building a shared tracking sheet, classroom planner, or business dashboard, Apps Script lets you add or remove collaborators dynamically. It’s beginner-friendly and perfect for anyone looking to save time while keeping their data secure.

Why Use Apps Script for Permission Management?

Using Google Sheets’ built-in share button is fine for one-off use, but if you want to scale, automate, or respond to changes (like adding new team members from a list), you need a better way. Apps Script offers just that.

Real-Life Scenario: Share Access with Sales Reps Automatically

Imagine a company that uses a master sales tracker in Google Sheets. Each rep should only have view-only access to their own sheet. Instead of sharing files manually every time a new rep joins, you want to automate it using a permissions list.

Sample Permissions Table

Email	Permission
alice@example.com	viewer
bob@example.com	editor

Step-by-Step: How to Automate Permissions in Google Sheets

1. Create a Sheet with User Data

Create a tab called “UserPermissions”
Add two columns: Email and Permission
List the emails and permission levels (viewer, editor)

2. Open Apps Script

Go to Extensions → Apps Script
Delete any existing code and paste the script below

3. Add the Script


function setSheetPermissions() {
  const sheet = SpreadsheetApp.getActiveSpreadsheet();
  const permissionSheet = sheet.getSheetByName("UserPermissions");
  const data = permissionSheet.getDataRange().getValues();
  const file = DriveApp.getFileById(sheet.getId());

  for (let i = 1; i < data.length; i++) {
    const email = data[i][0];
    const permission = data[i][1].toLowerCase();

    // Remove previous permissions if needed
    try {
      file.removeViewer(email);
      file.removeEditor(email);
    } catch (e) {
      // Ignore errors if user has no previous permissions
    }

    if (permission === "viewer") {
      file.addViewer(email);
    } else if (permission === "editor") {
      file.addEditor(email);
    }
  }
}

4. Run the Script

Click the Run ▶️ button
Authorize the script the first time it runs
Watch as users are granted permissions automatically!

Key Benefits

Save Time: No need to manually share sheets with each person
Central Control: Use a single tab to manage access
Reduce Errors: Avoid giving the wrong permission accidentally

Pro Tips

Use Script Triggers to run the script daily or when sheet is edited
Add a third column for Status (e.g., Shared, Error) for tracking
Always validate email format to avoid script errors

Quick-Reference Cheat Sheet

Function	Purpose
`DriveApp.getFileById()`	Access the current sheet as a Drive file
`file.addViewer(email)`	Gives user view-only access
`file.addEditor(email)`	Gives user edit access
`file.removeViewer(email)`	Removes view access
`file.removeEditor(email)`	Removes edit access

Managing permissions programmatically in Google Sheets with Apps Script is a powerful way to stay organized and efficient—especially when working with larger teams or recurring users. With a simple setup, you can grant or revoke access automatically, saving time and reducing the chance for errors.

Try it out with your team or client list and turn your sheet into a smart, secure collaboration hub!