How To Change Google Sheet Permissions with Python

By Jessie Hobb On May 5, 2023

Programmitaclly sharing Google Sheets with specific users using the Python API

Sharing a Google Sheet with others is an easy and straightforward task that can be done through the user interface. However, what if you need to share multiple Google Sheets with a specific user or service account?

Imagine you have created hundreds of external tables on BigQuery that consume data from various Google Sheets. If another service, like Apache Airflow, executes queries referencing those tables, you need to ensure that the Airflow’s service account has sufficient permissions on all those Sheets. But manually sharing (i.e. granting Viewer or Editor permissions) hundreds of Sheets with a specific principal is almost impossible, and would take several hours.

In this tutorial, we will demonstrate how to use Python and the Google Drive API to change permissions for hundreds of Google Sheets at once

Prerequisites

The first thing we need to do, is to make sure that we have successfully obtained user access credentials, with the required scopes. To do, simply run the following command:

gcloud auth application-default login --scopes=https://www.googleapis.com/auth/spreadsheets,https://www.googleapis.com/auth/drive,https://www.googleapis.com/auth/iam.test

A window will then be opened to your default browser, asking you to login to your Google Account. Please do so since the credentials will apply to all API calls that make use of the Application Default Credentials client library that we will demonstrate in the next few sections.

Then make sure to install the Python API client google-api-python-client via pip (ideally in a fresh virtual environment):

$ python3 -m pip install google-api-python-client

Finding the id of a Google Sheet

Before we get started with coding an automated solution for granting permissions to Google Sheet files, we first need to find out all the IDs for each individual file of interest.

In order to locate the file ID of a Google Sheet, simply open it in your preferred web browser. The link should be similar to the one shown below:

https://docs.google.com/spreadsheets/d/abc-defg-1234/edit#gid=0

abc-defg-1234 corresponds to the Google Sheet’s ID.

https://docs.google.com/spreadsheets/d/spreadsheetId/edit#gid=0

For more details with regards to how the Google Sheets URL is constructed, refert to the Google Sheets API Overview.

Changing Google Sheet permissions with Python Google API Client

Firstly, let’s create a list consisting of the Google Sheet file IDs for which we are going to change the permissions:

google_sheet_ids = [
'abc-1234',
'def-5678',
'ghi-9123',
]

Now the second thing we need to do is to infer the application default credentials and create the service for Google Drive.

import google.auth
from googleapiclient.discovery import build def create_service() -> Resource:
"""
Creates a Google Drive (v3) service to interact with the API
"""
scopes = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive',
]
creds, project_id = google.auth.default(scopes=scopes)
service = build('drive', 'v3', credentials=creds, cache_discovery=False)
return service

Now, let’s create a dataclass that contains (only) the fields required to specify a permission, based on the Permissions REST Resource.

from dataclasses import dataclass@dataclass
class Permission:
"""
Class that corresponds to the `permission` REST resource
https://developers.google.com/drive/api/reference/rest/v3/permissions#Permission
"""
type: str
role: str
emailAddress: str
def __post__init__(self):
"""Validate input"""
allowed_types = ['user', 'group', 'domain', 'anyone']
if self.type not in allowed_types:
raise ValueError(f'`{self.type}` is not a valid type. {allowed_types=}')
allowed_roles = ['commenter', 'reader', 'writer', 'fileOrganizer', 'organizer', 'owner']
if self.role not in allowed_roles:
raise ValueError(f'`{self.role}` is not a valid role. {allowed_roles=}')

In the next step, we are going to write a function that essentially takes instances of a service and a permission along with a file ID, and attempts to create a new permission.

from typing import Optionalfrom googleapiclient.discovery import Resource
from googleapiclient.errors import HttpError
def create_permission(
service: Resource,
permission: Permission,
file_id: str,
skip_on_failure: Optional[bool] = True,
):
"""
Creates a new `permission` for the specified `file_id`
"""
logging.info(f'Creating new permission {permission} for {file_id=}')
try:
request = service.permissions().create(
fileId=file_id,
body=asdict(permission),
sendNotificationEmail=False,
)
response = request.execute()
logging.info(f'New permission for {file_id=}: {response=}')
except HttpError as error:
logging.error(f'An error has occurred while trying to grant {permission=} to {file_id=}')
logging.error(f'Error was: {error}')
if not skip_on_failure:
raise error

Now, let’s write our main() method to put all the pieces together and eventually share the Google Sheets of interest with our target user.

def main():
google_sheet_ids = [
'abc-1234',
'def-5678',
'ghi-9123',
]service = create_service()
permission = Permission(type='user', role='writer', emailAddress='[email protected]')
for file_id in google_sheet_ids:
create_permission(service=service, permission=permission, file_id=file_id)

Full Code

Here’s a fully revised version of the code that you can use in order to specify a new

import logging
from dataclasses import asdict, dataclass
from typing import Optionalfrom googleapiclient.discovery import build, Resource
from googleapiclient.errors import HttpError
import google.auth
logging.basicConfig(
format='[%(asctime)s] {%(pathname)s:%(lineno)d} %(levelname)s - %(message)s',
level=logging.INFO,
)
def create_service() -> Resource:
"""
Creates a Google Drive (v3) service to interact with the API
"""
scopes = [
'https://www.googleapis.com/auth/spreadsheets',
'https://www.googleapis.com/auth/drive',
]
creds, project_id = google.auth.default(scopes=scopes)
service = build('drive', 'v3', credentials=creds, cache_discovery=False)
return service
def create_permission(
service: Resource,
permission: Permission,
file_id: str,
skip_on_failure: Optional[bool] = True,
):
"""
Creates a new `permission` for the specified `file_id`
"""
logging.info(f'Creating new permission {permission} for {file_id=}')
try:
request = service.permissions().create(
fileId=file_id,
body=asdict(permission),
sendNotificationEmail=False,
)
response = request.execute()
logging.info(f'New permission for {file_id=}: {response=}')
except HttpError as error:
logging.error(f'An error has occurred while trying to grant {permission=} to {file_id=}')
logging.error(f'Error was: {error}')
if not skip_on_failure:
raise error
def main():
google_sheet_ids = [
'abc-1234',
'def-5678',
'ghi-9123',
]
service = create_service()
permission = Permission(type='user', role='writer', emailAddress='[email protected]')
for file_id in google_sheet_ids:
create_permission(service=service, permission=permission, file_id=file_id)
if __name__ == '__main__':
main()

Final Thoughts

Granting access to a single Google Sheet for a user is a straightforward task that can be done through the User Interface. Simply click ‘Share’ in the top right corner of the spreadsheet, enter the user’s email address, and choose their role. However, when it comes to sharing permissions for hundreds of spreadsheets or users, this process can become time-consuming and tedious.

In this tutorial, we demonstrated how to use the Google Drive API and the Python Google API client to programmatically assign permissions to multiple Google Sheets. I hope you found this article useful. If you encounter any difficulties running the code snippet for your specific use case, please let me know in the comments below, and I will do my best to help you.

👉 Become a member and read every story on Medium. Your membership fee directly supports me and other writers you read. You’ll also get full access to every story on Medium.

👇Related articles you may also like 👇