gspread_asyncio
An asyncio wrapper for burnash's excellent Google Spreadsheet API library. gspread_asyncio
isn't just a plain asyncio wrapper around the gspread
API, it implements several useful and helpful features on top of those APIs. It's useful for long-running processes and one-off scripts.
Requires Python >= 3.8.
![CI status](https://github.com/dgilman/gspread_asyncio/actions/workflows/ci.yml/badge.svg)
Features
- Complete async wrapping of the
gspread
API. All gspread
API calls are run off the main thread in a threadpool executor. - Internal caching and reuse of
gspread
Client
/Spreadsheet
/Worksheet
objects. - Automatic renewal of expired credentials.
- Automatic retries of spurious failures from Google's servers (HTTP 5xx).
- Automatic rate limiting with defaults set to Google's default API limits.
- Many methods that don't need to return a value can optionally return an already-scheduled
Future
(the nowait
kwarg). You can ignore that future, allowing forward progress on your calling coroutine while the asyncio event loop schedules and runs the Google Spreadsheet API call at a later time for you.
Example usage
import asyncio
import gspread_asyncio
from google.oauth2.service_account import Credentials
def get_creds():
creds = Credentials.from_service_account_file("serviceacct_spreadsheet.json")
scoped = creds.with_scopes([
"https://spreadsheets.google.com/feeds",
"https://www.googleapis.com/auth/spreadsheets",
"https://www.googleapis.com/auth/drive",
])
return scoped
agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
async def example(agcm):
agc = await agcm.authorize()
ss = await agc.create("Test Spreadsheet")
print("Spreadsheet URL: https://docs.google.com/spreadsheets/d/{0}".format(ss.id))
print("Open the URL in your browser to see gspread_asyncio in action!")
await agc.insert_permission(ss.id, None, perm_type="anyone", role="writer")
ws = await ss.add_worksheet("My Test Worksheet", 10, 5)
zero_ws = await ss.get_worksheet(0)
for row in range(1, 11):
for col in range(1, 6):
val = "{0}/{1}".format(row, col)
await ws.update_cell(row, col, val + " ws")
await zero_ws.update_cell(row, col, val + " zero ws")
print("All done!")
asyncio.run(example(agcm), debug=True)
Observational notes and gotchas
- This module does not define its own exceptions, it propagates instances of
gspread.exceptions.GSpreadException
. - Always call
AsyncioGspreadClientManager.authorize()
, AsyncioGspreadClient.open_*()
and AsyncioGspreadSpreadsheet.get_worksheet()
before doing any work on a spreadsheet. These methods keep an internal cache so it is painless to call them many times, even inside of a loop. This makes sure you always have a valid set of authentication credentials from Google. - The only object you should store in your application is the
AsyncioGspreadClientManager
(agcm
). - Right now the
gspread
library does not support bulk appends of rows or bulk changes of cells. When this is done gspread_asyncio
will support batching of these Google API calls without any changes to the Python gspread_asyncio
API. - I came up with the default 1.1 second delay between API calls (the
gspread_delay
kwarg) after extensive experimentation. The official API rate limit is one call every second but however Google measures these things introduces a tiny bit of jitter that will get you rate blocked if you ride that limit exactly. - Google's service reliability on these endpoints is surprisingly bad. There are frequent HTTP 500s and the retry logic will save your butt in long-running scripts or short, one-shot, one-off ones.
- Experimentation also found that Google's credentials expire after an hour and the default
reauth_interval
of 45 minutes takes care of that just fine.
License
MIT
Development of gspread_asyncio is sponsored by Pro Football History.com, your source for NFL coaching biographies.