Python automation for managing Google Sheets access

Other Questions Google Sheets
1

I’m dealing with a big problem. I’ve got over 100 Google Sheets that are shared with tons of people. Now I need to clean up the access list by removing folks who aren’t active anymore. I’m wondering if there’s a way to use Python to get a list of people who have actually contributed to each sheet. I can see this info in the version history, but I want to automate it.

I’ve been playing around with the gspread library to access the sheets, but I’m stuck on how to get the list of users who have made changes. Here’s a bit of code I’ve tried:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

scope = ['https://spreadsheets.google.com/feeds']
creds = ServiceAccountCredentials.from_json_keyfile_name('my_secret.json', scope)
client = gspread.authorize(creds)

sheet = client.open('My Sheet').sheet1

# How do I get the list of contributors?

Any ideas on how to tackle this? I’m pretty new to working with Google Sheets API, so I’d appreciate any tips or pointers. Thanks!

2

hey, have u tried using the Google Sheets API directly? it’s a bit more complex than gspread but it lets u access revision history. u’d need to set up the API, use the spreadsheets.get method with some specific parameters. it’s not super straightforward but it should give u what u need for managing all those sheets. good luck!

3

I’ve actually faced a similar challenge with managing access to multiple Google Sheets. While gspread is great for basic operations, it doesn’t provide direct access to version history or contributor information. For this task, you’ll need to use the Google Drive API alongside the Sheets API.

Here’s a rough outline of the approach I took:

  1. Set up the Google Drive API in your project.
  2. Use the Drive API to get the file ID of each sheet.
  3. Make a request to the Drive API’s revisions.list endpoint for each file ID.
  4. Parse the response to extract unique contributor information.

It’s a bit more complex than just using gspread, but it gets the job done. You’ll need to handle pagination for sheets with lots of revisions, and be mindful of API quotas.

One tip: consider batching your requests to minimize API calls and improve performance when dealing with numerous sheets. It took some trial and error, but the end result was worth it for managing our team’s access efficiently.

4

Having worked with Google Sheets automation before, I can suggest an alternative approach using the Google Sheets API directly instead of gspread. This method allows you to access the spreadsheet’s revision history, which is crucial for your task.

First, you’ll need to set up the Google Sheets API and authenticate your application. Then, you can use the spreadsheets.get method with the includeGridData parameter set to false and the revisionId parameter to retrieve revision information.

Here’s a basic example of how you might structure your code:

from googleapiclient.discovery import build
from google.oauth2 import service_account

SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']
SERVICE_ACCOUNT_FILE = 'path/to/service_account.json'

creds = service_account.Credentials.from_service_account_file(
    SERVICE_ACCOUNT_FILE, scopes=SCOPES)

service = build('sheets', 'v4', credentials=creds)

spreadsheet_id = 'your_spreadsheet_id'
result = service.spreadsheets().get(spreadsheetId=spreadsheet_id, 
                                    includeGridData=False, 
                                    fields='revisions').execute()

contributors = set()
for revision in result.get('revisions', []):
    contributors.add(revision['user']['emailAddress'])

print(f'Contributors: {contributors}')

This approach should give you the information you need to manage access effectively across your numerous sheets.