Google sheets with python

Question

I have more than 100 google sheets that are shared with a lot of people. I am trying to remove inactive people from the access list. Is there a way in python to extract the list of people who have contributed to the google sheet from the version history? I used gspread library to access the sheet but not sure how to get the list of contributing users.

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

scope = ['https://www.googleapis.com/auth/drive.activity.readonly']
creds = ServiceAccountCredentials.from_json_keyfile_name('accessAPI.json', scopes = scope)

drive_service = build('driveactivity', 'v2', credentials=creds)

edit_activities = drive_service.activity().query(body={"filter":"detail.action_detail_case:EDIT", 
                                                       "itemName":"items/xyz",
                                                       "consolidationStrategy":"legacy"}).execute()

# Call the People API
scope = ['https://www.googleapis.com/auth/contacts.readonly']
creds = ServiceAccountCredentials.from_json_keyfile_name('accessAPI.json', scopes = scope)

service = build('people', 'v1', credentials=creds)
results = service.people().get(resourceName='people/1080745054',personFields='names,emailAddresses').execute()

Running a people ID through people API gives back the below result. It doesn't contain the email address

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

Is the output being truncated?

Answer 1

Approach

Using Python you can achieve this behavior passing through the Drive API and the People API.

1. Get EDIT activity for your google sheets using Drive Activities API
2. Get editors people ids from the actors object resource in the Drive Activities API response body.
3. Get editors email addresses from People API with the editors people ids.
4. List all file permissions on you google sheets with Drive API Permissions resource endpoint.
5. Update the permission if the user is not in the editor list according to your logic.

Here is the proposed script in pseudocode:

loop your_google_sheets:
    edit_activities = drive_service.activites().query(filter="detail.action_detail_case:EDIT", itemName="items/"+your_google_sheets.id)
    editors = edit_activities.get(actors_ids)
    loop editors:
        editors_emails += people_service.people().get(resourceName=editors.personName, personFields="emailAddresses")
    file_permissions = drive_service.permissions().list(fileId=your_google_sheets.id)
    loop file_permissions:
        update_if_not_editor(editors_email, file_permissions.id) # Implement your own logic

References

People API get

Drive Activities API query

Drive API Permissions list

Drive API Permissions update