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?

Alessandro · Accepted Answer · 2020-05-25 10:35:52Z

1

Approach

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

Get EDIT activity for your google sheets using Drive Activities API
Get editors people ids from the actors object resource in the Drive Activities API response body.
Get editors email addresses from People API with the editors people ids.
List all file permissions on you google sheets with Drive API Permissions resource endpoint.
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

answered May 25, 2020 at 10:35

Alessandro

3,0861 gold badge10 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

ayadav Over a year ago

Thanks for the reply. The activities().query is giving me HttpError. I have updated my code in the problem description.

Alessandro Over a year ago

Drive Activity API uses different scopes than Drive API. Use "https://www.googleapis.com/auth/drive.activity.readonly" instead of "https://www.googleapis.com/auth/drive"

ayadav Over a year ago

thanks, that worked. Now I am stuck at people-api response. The output doesn't contain the email address. I have updated the description. Thanks again!

Alessandro Over a year ago

Always look at the scopes needed to perform an operation in the documentation "Try It" feature. Apparently the scope contacts.readonly is not enough to perform this operation. You should be able to make it work with the userinfo scopes.

ayadav Over a year ago

The "Try it" gives me the detail but not in python. I did try different scopes but nothing helps. It doesn't give me the "name" key. I wonder it is due to accounts not being public. Could that be the reason? [link] (stackoverflow.com/questions/50766097/…)

|

Collectives™ on Stack Overflow

Google sheets with python

1 Answer 1

Approach

References

7 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Approach

References

7 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related