File sharing and permissions in Google Sheets - Real Business Scenario

Scenario Mode

👤 Your Role: You are an IT support specialist at a mid-sized company.

📋 Request: Your manager wants a clear report showing which employees have access to which shared files and their permission levels.

📊 Data: You have a list of shared files, employee names, and their permission levels (View, Comment, Edit).

🎯 Deliverable: Create a spreadsheet that organizes this data and highlights employees with Edit permissions for quick review.

Sample Data

File Name	Employee	Permission
Project Plan	Alice	Edit
Project Plan	Bob	View
Budget	Charlie	Comment
Budget	Alice	View
Meeting Notes	Bob	Edit
Meeting Notes	David	View
Design Doc	Charlie	Edit
Design Doc	David	Comment

1

Step 1: Sort the data by File Name and then by Employee to organize the list.

Select all data, then use Data > Sort range: first by 'File Name' A-Z, then by 'Employee' A-Z.

Expected Result

Data rows are ordered alphabetically by File Name and Employee.

2

Step 2: Create a new column named 'Edit Access' to mark employees with Edit permission.

In cell D2, enter formula: =IF(C2="Edit", "Yes", "No") and copy down.

Expected Result

Column D shows 'Yes' for rows where Permission is Edit, otherwise 'No'.

3

Step 3: Apply conditional formatting to highlight rows where 'Edit Access' is 'Yes'.

Select all data rows, then Format > Conditional formatting, Custom formula: =$D2="Yes", set fill color light green.

Expected Result

Rows with Edit permission are highlighted in light green.

4

Step 4: Create a summary table showing each employee and the count of files they can Edit.

List unique employees in column F starting F2. In G2 enter: =COUNTIFS(B:B,F2,C:C,"Edit") and copy down.

Expected Result

Summary table shows each employee with the number of files they can edit.

5

Step 5: Sort the summary table by the count of Edit permissions in descending order.

Select summary table, then Data > Sort range by column G, Z -> A.

Expected Result

Employees with most Edit permissions appear at the top.

Final Result

File Name     Employee    Permission   Edit Access
--------------------------------------------------
Budget       Alice       View         No
Budget       Charlie     Comment      No
Design Doc   Charlie     Edit         Yes
Design Doc   David       Comment      No
Meeting Notes Bob        Edit         Yes
Meeting Notes David       View         No
Project Plan Alice       Edit         Yes
Project Plan Bob         View         No

Summary Table:
Employee    Edit Files Count
----------------------------
Alice       2
Bob         1
Charlie     1
David       0

✓Alice has Edit access to 2 files, the most among employees.

✓Bob and Charlie each have Edit access to 1 file.

✓David does not have Edit access to any files.

✓Highlighting helps quickly identify who can edit files.

Bonus Challenge

Create a filter view that shows only employees with Edit permissions and their files.

Show Hint

Use Data > Filter views, then filter the 'Edit Access' column to show only 'Yes'.