File sharing and permissions in Google Sheets - Dashboard Guide

Dashboard Mode - File sharing and permissions

Goal

Understand who has access to shared files and what permissions they have.

Sample Data

File Name	Owner	Shared With	Permission	Last Modified
Project Plan	Alice	Bob	Editor	2024-06-10
Budget 2024	Charlie	Dana	Viewer	2024-06-08
Team Contacts	Alice	Eva	Commenter	2024-06-09
Marketing Ideas	Bob	Charlie	Editor	2024-06-07
Sales Report	Dana	Bob	Viewer	2024-06-11
Training Schedule	Eva	Alice	Editor	2024-06-06

Dashboard Components

• Total Files Shared: =COUNTA(A2:A7) counts all files listed.
• Unique Owners: =COUNTA(UNIQUE(B2:B7)) counts distinct owners.
• Editors Count: =COUNTIF(D2:D7,"Editor") counts files shared with Editor permission.
• Files Shared With Bob: =COUNTIF(C2:C7,"Bob") counts files shared with Bob.
• Latest Modified File: =INDEX(A2:A7,MATCH(MAX(E2:E7),E2:E7,0)) shows the file with the most recent modification date.
• Permission Summary Table: A small table showing count of each permission:
  =QUERY(D2:D7,"select D, count(D) group by D label count(D) 'Count'",0)

Dashboard Layout

+----------------------+-----------------------+
| Total Files Shared   | Unique Owners          |
| [KPI: 6]            | [KPI: 5]              |
+----------------------+-----------------------+
| Editors Count        | Files Shared With Bob  |
| [KPI: 3]            | [KPI: 2]              |
+----------------------+-----------------------+
| Latest Modified File                         |
| [Sales Report]                              |
+---------------------------------------------+
| Permission Summary Table                     |
| +------------+-------+                       |
| | Permission | Count |                       |
| | Editor     | 3     |                       |
| | Viewer     | 2     |                       |
| | Commenter  | 1     |                       |
| +------------+-------+                       |
+---------------------------------------------+

Interactivity

Add a filter dropdown for "Shared With" names. When you select a name, all KPIs and the permission summary update to show data only for files shared with that person.

For example, selecting "Bob" filters the data to files shared with Bob. The KPIs recalculate counts and latest file accordingly.

Self Check

If you add a filter for "Shared With" = "Alice", which components update and what are their new values?

• Total Files Shared: 1
• Unique Owners: 1 (Eva)
• Editors Count: 1
• Files Shared With Bob: 0
• Latest Modified File: Training Schedule
• Permission Summary Table: Editor = 1

Key Result

Dashboard shows file sharing details: counts, permissions, and recent activity filtered by shared user.