Manage Google Spreadsheets with Python and Gspread

Alexander Molochko
Apr 5 · 5 min read

More and more people prefer using online services like Google Sheets, Google Docs to native solutions. Business uses SaaS Services extensively as well. Personally, I have implemented quite a lot of projects that leverage such services. The intent of this tutorial is to show how you can easily perform CRUD operations on spreadsheets using Python powered by the gspread library.

Getting started

In most cases, you will need to call the Google Spreadsheet API from your application programmatically. Simply put, there are two types of google credential accounts: one requires interaction with a user, another one is designed to be used by applications, not humans. As you may guess we will be using the latter one.

Setting Up Google Sheets API

First of all, you need to create a project in the Google API Console. You are free to use an existing one.

Creating a new project

Next, we need to activate the Google Sheets API in the library section of the project.

Enable Sheets API

Having the API enabled, we need to create credentials. The Service Account credentials are exactly what we need.

Creating a service account key

Enter all required information and select the JSON key type.

Service account key generation

Save the generated JSON file into your project directory. We will use it in a while.

The last thing you need to do with Google Sheets is to give permissions to an email address associated with your service credentials. Open the saved JSON file and find a value for the client_email property. In my case — sheets-manager@pythongspreadapi.iam.gserviceaccount.com.

Copy the email address, go to a Google Sheet document, next click on the Share button and finally give this address editor permissions.

Share the sheet with the service email address

Altogether, now we are ready to consume the API from a Python application.

Accessing Google Sheets from Python

Having everything set up we can start using Google Sheets from Python. As I have already mentioned we will be using the gspread library. You can refer to the documentation to find out all available features.

We are going to create a basic CRUD example. Let’s start by defining the main method.

def main():
args = sys.argv[1:]
credentials_path = args[0]
sheet_id = args[1]
manager = GoogleSheetManager(credentials_path=credentials_path, sheet_id=sheet_id)
manager.start_session()
rows = manager.get_all_rows()
cols = manager.get_col_values(2)
manager.close_session()
pass

As you can see from the snippet about we are expecting a credentials path and a sheet id from command line arguments.

In my case the command looks as follows:

python __main__.py D:\Development\Python\PythonGspread\googlesheets\credentials\PythonGspreadAPI-efba49a6b797.json 1d6v6WFpinxAdB8mjmi1r1Mv6xwarn5dwalYKX1BpQaM

Next, let’s create the GoogleSheetManager class to wrap the library, hiding all implementation details.

You may find similar guides, however, one important change was recently introduced. We need to init a client session to start using the API.

def __init__(self, credentials_path, sheet_id, scopes=DEFAULT_SCOPES):
self.sheet_id = sheet_id
self._init_credentials(credentials_path=credentials_path, scopes=scopes)
def _init_credentials(self, credentials_path, scopes):
self.credentials = service_account.Credentials.from_service_account_file(credentials_path, scopes=scopes)
self.client = gspread.Client(auth=self.credentials)
def start_session(self):
self.client.session = AuthorizedSession(self.credentials)
def close_session(self):
if self.client.session is not None:
self.client.session.close()

There are alternative ways to auth the client, but that is the most simple one.

Creating data in Google Sheets

Let’s implement appending and inserting a new row to a worksheet.

def append_row(self, row_values, worksheet_number=0):
worksheet = self._get_worksheet(worksheet_number)
return worksheet.append_row(row_values)
def insert_row(self, row_values, row_index, worksheet_number=0):
worksheet = self._get_worksheet(worksheet_number)
return worksheet.insert_row(row_values, row_index)

We are simply delegating calls to the gspread library. The next example shows how to call these methods, everything is pretty straightforward.

new_row = ("Some Name", "Some Phone Number", "Some Address")
manager.append_row(new_row)
manager.insert_row(new_row, 2)

Reading data from Google Sheets

There are a bunch of ways you can read data from a worksheet, starting from a single cell value to reading complex ranges. We will define methods that are usually enough for most applications.

def get_row(self, row_number, worksheet_number=0):
worksheet = self._get_worksheet(worksheet_number)
return worksheet.row_values(row_number)
def get_cell_value(self, cell_row, cell_col, worksheet_number=0):
worksheet = self._get_worksheet(worksheet_number)
return worksheet.cell(cell_row, cell_col)
def get_all_rows(self, worksheet_number=0):
worksheet = self._get_worksheet(worksheet_number)
return worksheet.get_all_records(head=1)
def get_cell_value_alpha(self, alpha_index, worksheet_number=0):
worksheet = self._get_worksheet(worksheet_number)
return worksheet.acell(alpha_index)
def get_col_values(self, col_num, worksheet_number=0):
worksheet = self._get_worksheet(worksheet_number)
return worksheet.col_values(col=col_num)
def get_row_count(self, worksheet_number=0):
worksheet = self._get_worksheet(worksheet_number)
return worksheet.row_count()

Updating data in Google Sheets

As usual, we need to update a single cell value or a whole role. This can be done in the following way:

def update_cell_value(self, cell_row, cell_col, value, worksheet_number=0):
worksheet = self._get_worksheet(worksheet_number)
return worksheet.update_cell(cell_row, cell_col, value=value)
def update_row(self, row_number, row_values, worksheet_number=0):
worksheet = self._get_worksheet(worksheet_number)
cell_range = self._build_range(row_number, len(row_values))
cell_list = worksheet.range(cell_range)
for i in range(0, len(row_values)):
cell_list[i].value = row_values[i]
return worksheet.update_cells(cell_list)

To update a row we are selecting a range at first and then, after updating values, we make a single API call to update the row.

Deleting data from Google Sheets

Finally, to delete a row from a worksheet we can use the following method:

def delete_row(self, row_number, worksheet_number=0):
worksheet = self._get_worksheet(worksheet_number)
return worksheet.delete_row(row_number)

Conclusion

In this article, we have seen how to easily manage worksheets from Python applications. We created a simple wrapper for the gspread library to hide underlying details. The full project source code is available at the git hub repository.

Alexander Molochko

Written by

Software Engineer https://crosp.net

Welcome to a place where words matter. On Medium, smart voices and original ideas take center stage - with no ads in sight. Watch
Follow all the topics you care about, and we’ll deliver the best stories for you to your homepage and inbox. Explore
Get unlimited access to the best stories on Medium — and support writers while you’re at it. Just $5/month. Upgrade