Guide for Using the Google Sheets API in Python
Objective:
The objective of this article is to guide readers through the process of utilizing the Google Sheets API effectively. It provides step-by-step instructions on obtaining credentials, setting up a service account and configuring permissions. By offering practical code examples and best practices, the article help readers in leveraging the power of the Google Sheets API for their own applications and data automation needs.
Table of content
- Introduction
- Turning on Google Sheets API, creating a service account and setting permissions.
- Using the Google Sheets API with python
- Best Practices and Tips
- Conclusion
Introduction
Google sheets is not the leading provider of spreadsheets services; Microsoft Excel and the widely used LibreOffice Calc have been people’s favorite during years. However, Google sheets has been growing strong in recent years, providing to their users a robust service that can stand up to its competitors. Furthermore, it has stood out by providing additional features such as cloud-based storage, real-time collaboration, and the ability to enhance functionality using JavaScript.
The use of Google Sheets API grant us the freedom to access to our spreadsheets as long as we have internet access and, by using the correct permissions, we can edit them from whatever project we create.
Obtaining Google Sheets API Licenses
First of all, we will see how to get the permissions to the API, you will just need to follow step by step and you will be ready to start coding and interacting with Google Sheets.
- Sign in to your google account
- Create a project by going to “https://console.cloud.google.com”, click on the “select a project” button, click on “new project”, give it the name that you want and then click on “create”.
3. Turn the the Google Sheet API on by going to “https://console.cloud.google.com/apis/enableflow?apiid=sheets.googleapis.com” , click on “next” and the on “enable”.
4. Turn the Identity and Access Management (IAM) API and Google Drive API on by going to “https://console.cloud.google.com/flows/enableapi?apiid=iam.googleapis.com&redirect=https://console.cloud.google.com&_ga=2.65235882.588727536.1699328678-1491191614.1697658258” and “https://console.cloud.google.com/apis/enableflow?apiid=drive.googleapis.com&project=semiotic-anvil-402518”, following the same steps as step number 3.
5. Go to “https://console.cloud.google.com/projectselector2/iam-admin/serviceaccounts/create?walkthrough_id=iam--create-service-account&_ga=2.65235882.588727536.1699328678-1491191614.1697658258#step_index=1” and select your project (the one you created on step 2) to create a service account, this will give you a more secure method to access your spreadsheets and it also allows you to edit them without having to verify your app with google to get all features.
Once you select your project, you’re going to give your service account a name and click on “create and continue”.
6. After creating the account, you will be asked to give it a role, select “Owner” to avoid any issues and then click “done”.
7. Now click on your service account, go to keys section, click on “create a new key” and download the Json file. This Json file is your credential and will be required to run your program.
8. Once you have your service account go to “https://console.cloud.google.com/apis/credentials/consent”, select “External”, add the required data and continue to the “scopes” screen, once you’re there click on “add or remove scopes”, add all the scopes related to Google Sheets API and click on continue.
10. On the “test users” screen, add your service account and follow the process until you complete it.
Once you’re done, just make sure to the save the Json file correctly, since it provides access to some of your personal information on Google.
Using the Google Sheets API with python
With the necessary credentials and permissions in place, we are set to begin our code. To get started, let’s install the required packages and import the necessary modules.
pip install - upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlibfrom google.oauth2 import service_account
from google.auth.transport.requests import Request
from googleapiclient.discovery import buildNext, we’ll create a class to streamline the usage of the Google Sheets API, beginning with the constructor and a method to create a spreadsheet.
class Spreadsheet:
def __init__(self, service_account_path, sheet_id = None, sheet_name = None,):
"""
Initializes a Spreadsheet object with the necessary credentials and service connections.
Args:
service_account_path (str): The path to the service account JSON key file.
sheet_id (str): Optional. The ID of an existing Google Sheets document.
sheet_name (str): Optional. The name of the sheet within the Google Sheets document.
Raises:
ValueError: If `sheet_id` is provided without a corresponding `sheet_name`.
"""
scopes = ["https://www.googleapis.com/auth/drive.file",
"https://www.googleapis.com/auth/spreadsheets"]
self.creds = service_account.Credentials.from_service_account_file(
service_account_path, scopes=scopes)
self.drive_service = build('drive', 'v3', credentials= self.creds)
self.service = build("sheets", "v4", credentials=self.creds)
self.sheet_id = sheet_id
if self.sheet_id is not None and self.sheet_name is None:
raise ValueError("Sheet name needed when given sheet_id")
def make_spreadsheet(self, title):
'''
Create a new spreadsheet with service account.
Args:
title (str): The title of the new spreadsheet.
Raises:
ValueError: If the object is already linked to a Google spreadsheet.
Returns:
None
'''
if self.sheet_id:
raise ValueError("Object already linked to a google spreadsheet")
else:
spreadsheet = self.drive_service.files().create(
body={
'name': title,
'mimeType': 'application/vnd.google-apps.spreadsheet'
}
).execute()
self.sheet_id = spreadsheet['id']
self.sheet_name = 'Sheet1'This class serves as a representation of a Google Spreadsheet. It encapsulates functionality to interact with the Google Sheets API and provides an organized structure to facilitate ease of use for common operations on spreadsheets.
With these two methods, we can instantiate a Spreadsheet object and subsequently create a Google Spreadsheet linked to our object. Alternatively, you have the option to specify an existing spreadsheet's ID and sheet name. However, keep in mind that for this option to work, the spreadsheet must be shared with your service (as you would do with a friends account) account with writer permissions.
If you create a spreadsheet, it will be stored on your service account, so if you want to have direct access from Google Sheets website, you need to share it with your primary account with this method.
def give_permission(self, primary_email):
'''
Give write permission to a user for the linked Google spreadsheet.
Args:
primary_email (str): The primary email address of the user to grant write permission.
Returns:
None
'''
new_file_permission = {
'type': 'user',
'role': 'writer',
'emailAddress': primary_email,
}
permission_response = self.drive_service.permissions().create(
fileId=self.sheet_id, body=new_file_permission).execute()Now, let's explore how to write or retrieve cell values with the following methods.
def get_cell_value(self, cell):
'''
Retrieve the value of a specific cell in the linked Google spreadsheet.
Args:
cell (str): The cell address in A1 notation (e.g., "A1", "B2").
Returns:
list: A list containing the value of the specified cell. If the cell is empty or undefined, the list will be empty.
'''
range = self.sheet_name + '!' + cell + ':' + cell
result = self.service.spreadsheets().values().get(spreadsheetId=self.sheet_id, range=range).execute()
values = result.get("values", [])
return values
def set_cell_value(self, cell, value):
'''
Set the value of a specific cell in the linked Google spreadsheet.
Args:
cell (str): The cell address in A1 notation (e.g., "A1", "B2").
value (str): The value to be set in the specified cell.
Returns:
None
Raises:
Any exceptions that may occur during the API request.
'''
range = self.sheet_name + '!' + cell
request_body = {
"values": [[value]]
}
request = self.service.spreadsheets().values().update(
spreadsheetId=self.sheet_id,
range=range,
body=request_body,
valueInputOption="RAW"
)
response = request.execute() With these two methods, we can read or write to one cell at a time, which is beneficial for individual cell operations. However, when dealing with multiple cells, making separate API calls for each can be inefficient. To address this, we’ll now explore methods that allow us to edit or retrieve values from multiple cells with just one API call.
def get_range_values(self, cell_1, cell_2):
'''
Retrieve the values from a specified range in the linked Google spreadsheet.
Args:
cell_1 (str): The starting cell address in A1 notation (e.g., "A1", "B2").
cell_2 (str): The ending cell address in A1 notation.
Returns:
list: A list of lists representing the values in the specified range.
Each inner list corresponds to a row in the range.
Note:
If the specified range is empty or undefined, an empty list will be returned.
'''
range = f'{self.sheet_name}!{cell_1}:{cell_2}'
result = self.service.spreadsheets().values().get(spreadsheetId=self.sheet_id, range=range).execute()
values = result.get("values", [])
return values
def set_range_values(self, cell_1, cell_2, values):
'''
Set the values in a specified range of the linked Google spreadsheet.
Args:
cell_1 (str): The starting cell address in A1 notation (e.g., "A1", "B2").
cell_2 (str): The ending cell address in A1 notation.
values (list): A list of lists representing the values to be set in the specified range.
Each inner list corresponds to a row in the range.
Returns:
None
Raises:
Any exceptions that may occur during the API request.
'''
range = f'{self.sheet_name}!{cell_1}:{cell_2}'
request_body = {
"values": values
}
request = self.service.spreadsheets().values().update(
spreadsheetId=self.sheet_id,
range=range,
body=request_body,
valueInputOption="RAW"
)
response = request.execute()With these seven methods, we are well-equipped to efficiently utilize the API. Now, let’s see an example to demonstrate their practical use.
sheet = Spreadsheet("credentials.json") #Initiate a Spreadsheet without a google spreadsheet linked
sheet.make_spreadsheet('hello world') #Make a new spreadsheet named hello world
sheet.give_permission('xyz@gmail.com') #grant acces to our main email
sheet.set_cell_value('A1', 'hello') #change A1 cell value to 'hello'
sheet.set_cell_value('B1', 'world') #change B1 cell value to 'world'
A1 = str(sheet.get_cell_value('A1')[0])
A1_B1 = str(sheet.get_range_values('A1', 'B1')[0])
print("A1 ", A1)
print("A1:B1 ", A1_B1)A1 [‘hello’]
A1:B1 [‘hello’, ‘world’]
While intentionally simple, consider this example as an introduction to the vast potential it may have for your needs.
With the implementation of this class and its set of seven methods, you gain the capability to perform a wide array of operations tailored to your specific needs. This versatile toolkit not only facilitates common tasks but also empowers you to execute more intricate actions seamlessly.
Best Practices and Tips
- Opt for Google Service Account Credentials
When interacting with the Google Sheets API, it’s advisable to use Google Service Account credentials instead of your primary account credentials. I didn’t show how to use your primary account credentials because doing so poses certain risks and limitations. Creating sheets using your primary account requires app verification from Google, adding an extra step to the process. In contrast, utilizing Service Account credentials not only provides a more secure option but also allows for seamless sheet creation
2. Prefer get_range_values over get_cell_value for improved performance
It’s recommended to favor the get_range_values method over get_cell_value to enhance the efficiency of your Google Sheets API calls. Calling get_cell_value frequently, especially in rapid succession, may lead to rate-limiting issues, resulting in errors. This method can take around 6.8 seconds for every 40 calls.
On the other hand, using get_range_values for 40 cells consumes the same time as a single get_cell_value call and doesn't trigger rate-limiting (240 API calls per minute) blocks. This approach not only improves performance but also ensures a smoother experience when retrieving values from multiple cells. Consider this optimization to enhance the responsiveness and reliability of your API interactions.
3. Optimize with set_range_values over set_cell_values for bulk updates
Similar to reading values, when it comes to updating cell values, it’s beneficial to use the set_range_values method instead of multiple calls to set_cell_values. Frequent use of set_cell_values in rapid succession may lead to rate-limiting (120 API calls per minute) issues and potential errors. The time it takes for multiple set_cell_values calls can accumulate significantly.
Conclusion
In concluding our exploration of the Google Sheets API permissions and Python integration, we’ve covered fundamental steps and shared practical tips for efficiency. However, there’s more to discover. Take the opportunity to dive into the specifics of service accounts, delve into the nuances of OAuth consent screen scopes, and explore advanced features. This article serves as a starting point, providing a foundation for your deeper exploration into the intricacies of the Google Sheets API. For further insights, consider researching these topics in more detail.
References
- Google Sheets API Overview — https://developers.google.com/sheets/api/guides/concepts
