Programmitaclly sharing Google Sheets with specific users using the Python API
Sharing a Google Sheet with others is a simple and simple task that may be done through the user interface. Nonetheless, what if it is advisable share multiple Google Sheets with a selected user or service account?
Imagine you will have created lots of of external tables on BigQuery that eat data from various Google Sheets. If one other service, like Apache Airflow, executes queries referencing those tables, it is advisable be sure that the Airflow’s service account has sufficient permissions on all those Sheets. But manually sharing (i.e. granting Viewer or Editor permissions) lots of of Sheets with a selected principal is nearly unimaginable, and would take several hours.
On this tutorial, we are going to exhibit how one can use Python and the Google Drive API to vary permissions for lots of of Google Sheets directly
Prerequisites
The very first thing we’d like to do, is to be sure that that now we have successfully obtained user access credentials, with the required scopes. To do, simply run the next 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 achieve this because the credentials will apply to all API calls that make use of the Application Default Credentials client library that we’ll exhibit in the following few sections.
Then be sure that to put in 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 start with coding an automatic solution for granting permissions to Google Sheet files, we first need to seek out out all of the IDs for every individual file of interest.
With a view to locate the file ID of a Google Sheet, simply open it in your selected web browser. The link must be much like 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 as 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 an inventory consisting of the Google Sheet file IDs for which we’re going to change the permissions:
google_sheet_ids = [
'abc-1234',
'def-5678',
'ghi-9123',
]
Now the second thing we’d like to do is to infer the applying default credentials and create the service for Google Drive.
import google.auth
from googleapiclient.discovery import construct 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 = construct('drive', 'v3', credentials=creds, cache_discovery=False)
return service
Now, let’s create a dataclass that comprises (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}` shouldn't be a sound type. {allowed_types=}')
allowed_roles = ['commenter', 'reader', 'writer', 'fileOrganizer', 'organizer', 'owner']
if self.role not in allowed_roles:
raise ValueError(f'`{self.role}` shouldn't be a sound role. {allowed_roles=}')
In the following step, we’re going to jot down a function that essentially takes instances of a service and a permission together with a file ID, and attempts to create a brand 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 brand new `permission` for the required `file_id`
"""
logging.info(f'Creating recent permission {permission} for {file_id=}')
try:
request = service.permissions().create(
fileId=file_id,
body=asdict(permission),
sendNotificationEmail=False,
)
response = request.execute()
logging.info(f'Latest permission for {file_id=}: {response=}')
except HttpError as error:
logging.error(f'An error has occurred while attempting to grant {permission=} to {file_id=}')
logging.error(f'Error was: {error}')
if not skip_on_failure:
raise error
Now, let’s write our important() method to place all of the pieces together and eventually share the Google Sheets of interest with our goal user.
def important():
google_sheet_ids = [
'abc-1234',
'def-5678',
'ghi-9123',
]service = create_service()
permission = Permission(type='user', role='author', emailAddress='example@example.com')
for file_id in google_sheet_ids:
create_permission(service=service, permission=permission, file_id=file_id)
Full Code
Here’s a totally revised version of the code that you may use with a purpose to specify a brand new
import logging
from dataclasses import asdict, dataclass
from typing import Optionalfrom googleapiclient.discovery import construct, 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 = construct('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 brand new `permission` for the required `file_id`
"""
logging.info(f'Creating recent permission {permission} for {file_id=}')
try:
request = service.permissions().create(
fileId=file_id,
body=asdict(permission),
sendNotificationEmail=False,
)
response = request.execute()
logging.info(f'Latest permission for {file_id=}: {response=}')
except HttpError as error:
logging.error(f'An error has occurred while attempting to grant {permission=} to {file_id=}')
logging.error(f'Error was: {error}')
if not skip_on_failure:
raise error
def important():
google_sheet_ids = [
'abc-1234',
'def-5678',
'ghi-9123',
]
service = create_service()
permission = Permission(type='user', role='author', emailAddress='example@example.com')
for file_id in google_sheet_ids:
create_permission(service=service, permission=permission, file_id=file_id)
if __name__ == '__main__':
important()
Final Thoughts
Granting access to a single Google Sheet for a user is an easy task that may be done through the User Interface. Simply click ‘Share’ in the highest right corner of the spreadsheet, enter the user’s email address, and select their role. Nonetheless, in relation to sharing permissions for lots of of spreadsheets or users, this process can change into time-consuming and tedious.
On this tutorial, we demonstrated how one can use the Google Drive API and the Python Google API client to programmatically assign permissions to multiple Google Sheets. I hope you found this text useful. In the event you encounter any difficulties running the code snippet to your specific use case, please let me know within the comments below, and I’ll do my best to enable you.
👉 Develop into a member and browse every story on Medium. Your membership fee directly supports me and other writers you read. You’ll also get full access to each story on Medium.
👇Related articles you could also like 👇