Manage Google Spreadsheets with Python and Gspread

Manage Google Spreadsheets with Python and GspreadAlexander MolochkoBlockedUnblockFollowFollowingApr 4More 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 startedIn 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 APIFirst of all, you need to create a project in the Google API Console.

You are free to use an existing one.

Creating a new projectNext, we need to activate the Google Sheets API in the library section of the project.

Enable Sheets APIHaving the API enabled, we need to create credentials.

The Service Account credentials are exactly what we need.

Creating a service account keyEnter all required information and select the JSON key type.

Service account key generationSave 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 addressAltogether, now we are ready to consume the API from a Python application.

Accessing Google Sheets from PythonHaving 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() passAs 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:DevelopmentPythonPythonGspreadgooglesheetscredentialsPythonGspreadAPI-efba49a6b797.

json 1d6v6WFpinxAdB8mjmi1r1Mv6xwarn5dwalYKX1BpQaMNext, 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 SheetsLet’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 SheetsThere 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 SheetsAs 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 SheetsFinally, 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)ConclusionIn 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.

.. More details

Leave a Reply