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.
- Complete async wrapping of the
gspreadAPI. AllgspreadAPI calls are run off the main thread in a threadpool executor. - Internal caching and reuse of
gspreadClient/Spreadsheet/Worksheetobjects. - 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(thenowaitkwarg). 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.
import asyncio
import gspread_asyncio
# from google-auth package
from google.oauth2.service_account import Credentials
# First, set up a callback function that fetches our credentials off the disk.
# gspread_asyncio needs this to re-authenticate when credentials expire.
def get_creds():
# To obtain a service account JSON file, follow these steps:
# https://gspread.readthedocs.io/en/latest/oauth2.html#for-bots-using-service-account
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
# Create an AsyncioGspreadClientManager object which
# will give us access to the Spreadsheet API.
agcm = gspread_asyncio.AsyncioGspreadClientManager(get_creds)
# Here's an example of how you use the API:
async def example(agcm):
# Always authorize first.
# If you have a long-running program call authorize() repeatedly.
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!")
# Allow anyone with the URL to write to this spreadsheet.
await agc.insert_permission(ss.id, None, perm_type="anyone", role="writer")
# Create a new spreadsheet but also grab a reference to the default one.
ws = await ss.add_worksheet("My Test Worksheet", 10, 5)
zero_ws = await ss.get_worksheet(0)
# Write some stuff to both spreadsheets.
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!")
# Turn on debugging if you're new to asyncio!
asyncio.run(example(agcm), debug=True)- This module does not define its own exceptions, it propagates instances of
gspread.exceptions.GSpreadException. - Always call
AsyncioGspreadClientManager.authorize(),AsyncioGspreadClient.open_*()andAsyncioGspreadSpreadsheet.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
gspreadlibrary does not support bulk appends of rows or bulk changes of cells. When this is donegspread_asynciowill support batching of these Google API calls without any changes to the Pythongspread_asyncioAPI. - I came up with the default 1.1 second delay between API calls (the
gspread_delaykwarg) 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_intervalof 45 minutes takes care of that just fine.
MIT
Development of gspread_asyncio is sponsored by Pro Football History.com, your source for NFL coaching biographies.
React
Typescript
Django
Laravel
Facebook
Microsoft
Google
Alibaba
D3
Tencent