How To Change Google Sheet Permissions with Python – TechToday

Programmatically sharing Google Sheets with specific users using the Python API

Sharing a Google Sheet with others is a simple 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 account or service?

Imagine you’ve created hundreds of external tables in BigQuery that consume data from multiple Google Sheets. If another service, such as Apache Airflow, runs queries that reference these tables, you need to ensure that the Airflow service account has sufficient permissions on all of these 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’ll demonstrate how to use Python and the Google Drive API to change the permissions of hundreds of Google Sheets at once.

Prerequisites

The first thing we need to do is to make sure that we have correctly obtained the user access credentials, with the required scopes. To do this, 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 open in your default browser, asking you to sign in to your Google Account. Please do this, as the credentials will be applied to all API calls that make use of the application’s default credentials client library that we’ll show in the next sections.

Next, 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

Find the ID of a Google Spreadsheet

Before we start 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.

To locate the file ID of a Google Spreadsheet, simply open it in your favorite web browser. The link should look similar to the one below:

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

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

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

For more details on how the Google Sheets URL is constructed, see the Google Sheets API overview.

Change Google Sheet permissions with Python Google API Client

First, let’s create a list consisting of the Google Sheet file IDs for which we will change permissions:

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

Now the second thing we need to do is to infer the default credentials of the application 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, we create a data class that contains (only) the fields needed 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’ll write a function that basically takes instances of a service and a permission along with a file ID and tries to create a new permission.

from typing import Optional
from 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 ours main() method to put all the pieces together and finally 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 is a completely revised version of the code that you can use to specify a new one

import logging
from dataclasses import asdict, dataclass
from typing import Optional
from 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 simple 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 long and tedious.

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

👉 Become a member and read all the stories on Medium. Your membership fee directly supports me and the other writers you read. You’ll also have full access to all stories on Medium.

👇Related Articles You May Also Like 👇