Python script to manage Google Sheets user permissions

Other Questions Google Sheets
1

I’m managing over 100 Google Sheets documents that are shared with many users. I need to clean up the access permissions by removing people who haven’t actually made any changes to the sheets. Can Python help me get a list of users who have actively edited each sheet by checking the revision history?

I’ve been using the gspread library to connect to the sheets, but I can’t figure out how to retrieve the contributor information from the edit history.

from oauth2client.service_account import ServiceAccountCredentials
from googleapiclient.discovery import build

# Set up Drive Activity API access
activity_scope = ['https://www.googleapis.com/auth/drive.activity.readonly']
credentials = ServiceAccountCredentials.from_json_keyfile_name('service_key.json', scopes=activity_scope)

activity_service = build('driveactivity', 'v2', credentials=credentials)

user_actions = activity_service.activity().query(body={"filter":"detail.action_detail_case:EDIT", 
                                                      "itemName":"items/abc123",
                                                      "consolidationStrategy":"legacy"}).execute()

# Set up People API access
people_scope = ['https://www.googleapis.com/auth/contacts.readonly']
people_creds = ServiceAccountCredentials.from_json_keyfile_name('service_key.json', scopes=people_scope)

people_service = build('people', 'v1', credentials=people_creds)
user_info = people_service.people().get(resourceName='people/9876543210',personFields='names,emailAddresses').execute()

When I try to get user details using the People API, I only get back this limited response:

{'resourceName': 'people/9876543210',
 'etag': '%EgcBAj0JPjcuGgQBAgUH'}

Why is the email address missing from the response? Am I missing something in my API call?

2

This happens because service accounts can’t access user profile data outside their domain. The People API won’t give you email addresses for external users when you’re using a service account. I ran into the same thing when auditing sheet permissions at my company. Skip the Activity API and use the Drive API’s revisions endpoint instead. Just call drive_service.revisions().list(fileId='sheet_id') to get revision history - it includes the lastModifyingUser field with email addresses. No People API needed. Also, make sure your service account has domain-wide delegation turned on if you’re using G Suite/Workspace. Without it, you’ll keep hitting permission walls when trying to access user data.

3

Yeah, that’s a common auth issue. I ran into the same thing cleaning up our department’s sheets. Skip the People API headaches and use Drive API instead - it’s way more reliable. Start with drive_service.files().get(fileId='your_id', fields='lastModifyingUser') for recent stuff, then hit drive_service.revisions().list() for the full history. The revisions response gives you actual editor emails right there. Just heads up - Google only keeps revision history for so long, so if you need older data, start monitoring now. Also make sure your service account has the right Drive scopes. You’ll need at least ‘https://www.googleapis.com/auth/drive.readonly’ or this won’t work.

4

the People API only shows emails for users within your domain when using service accounts. try the Drive API’s permission endpoint - drive_service.permissions().list(fileId='your_sheet_id') to list all users with access, then cross-ref that with your activity data.