GSpread: Automate Google Sheet with Python
Google Sheets was released on the 9th of March 2006 and in 2009 Google Sheets API was launched for the first time. Sheets API has undergone multiple upgrades since then. As of the time of writing this article, Google sheets API is currently at v4.
The availability of this API changed the game for many software developers (data analysts, engineers or scientists) who needed to interact with Google Sheets easily without manual operations. Google Sheets API has detailed documentation that explains the use of its REST endpoints on this page.
In 2011, a wrapper on the Google Sheets API was created — GSpread. The main purpose of this wrapper is to ensure that Google Sheets could be easily accessible with a focus on what operations you need to perform on the go and helper functions built into this wrapper to help with all kinds of transformations that could be needed.
Requirements
- Gmail/Google Account
- Python 3.6+
- Google Sheet
Setup Credentials
Since GSpread is built on Google Sheets API some authentication needs to be done for the app via a google account to allow access to sheets via script. Different types of authentications can be done to be able to access the Sheets API, which includes:
In this article, the service account keys will be used. To set up your service account credential (JSON), you can visit this official documentation page. When the service_account.json is downloaded, it should look like this:
The next step is to copy the client_email and share with the google sheet to be connected to.
Setup GSpread
To get started with GSpread,
$ pip install gspreadGSpread Deep-dive
Connecting to Sheet
Connecting with the sheet from GSpread can be done via URL, sheet id (a.k.a key) or sheet name
Getting or connecting to a particular tab or sheet in the spreadsheet can be done by calling the sheet1 function when the tab has the default sheet title Sheet1 — and when the sheet title is not Sheet1 the specific title name can be used via the worksheet function. To access the sheet/tab by their index or order on the spreadsheet theget_worksheet the function is used.
A worksheet can be easily created or deleted in an existing spreadsheet
Get Data from Sheet
Accessing data from the sheet can be done to access them from a row, or column, as a 2D list (i.e list of lists) or as a key-value pair (i.e dictionary), in which each object will represent columns as keys and their corresponding values per row.
Something to note here, the index for row or column starts from 1 and not 0 when using the row_values or col_values function.
Other methods that can help fetch data are:
get()fetches all values from a range of cells.batch_get()can fetch values from multiple ranges of cells with one API call.
When there are columns with formulas, these formulas might not be pulled from the sheet, their actual values will be pulled. To ensure the formulas from the sheet are gotten, the value_render_option the parameter can be passed to get_records
Dump Data to Sheet
Updating values to the sheet could be cell operation or batch operation, which would cover more than just one cell at a time. Saving data to the sheet via a cell operation could be done via the update function.
To update more than a cell at once:
This update happens based on the specified corner address, that is, the data dump will kick off from A1 for the first list [first_name, last_name] and the next list will follow the same pattern.
In the case where multiple values need to be sent to the sheet via this batch process, the column names can be written as the first list while the values can be unpacked into the update function.
The output will look like this
For a scenario where a formula needs to be dumped onto the sheet as a formula and not a string, the value_input_option param needs to be set to USER_ENTEREDin the update function, the default is RAW
Another way of doing a batch update on the sheet is using the values_update function on the spreadsheet
To an existing sheet, rows can also be appended using the append_rows function:
Sheet Formatting
Sheet formatting could come in handy when certain specifications of the sheet need to be handled automatically. The sample snippet below shows how to do this:
The range for the colour fields is from 0.0–1.0. Formatting could get more interesting, hence, gspread-formatting (a tool built on gspread) was built specifically for handling formatting with gspread.
Error Handling
The exceptions for gspread connection with Google sheets can be found in gspread.exceptions.APIError and this can be used in handling the API-specific errors. Some of the common errors are:
- Authentication Error: This is usually caused by wrong credentials or not setting up the credentials with the sheet at all.
- Error from disabled Google Sheet API: If the credential project does not include Google sheets API in its enabled apps.
- Quota Error: Google Sheets API allows 100 requests in 100 seconds, if the request or API call exceeds this, the action called will fail and return an error. Batch operation solves this most times because it ensures the API is called fewer times.
- Server Error: This occurs when Google Sheets API service is down or under maintenance. This is not usually frequent but is a possibility. The status page for Google API services can be used to monitor this.
More on GSpread exceptions and error handling can be found here
Conclusion
Other functionalities of GSpread include —
- Granting access to other users for a google sheet
- Parsing ingested data with Pandas
- Parsing NumPy data into gspread
- Creating new spreadsheets
- Using regex to find cells that match a pattern
and many more!
GSpread comes with a lot of benefits and functionalities which reduce ingestion, dumping and even transformation of data via Google Sheets API. For a more detailed breakdown of this tool and its other functionalities kindly visit the official page.
If you find this article helpful, kindly give some clap and follow. Thanks!
