-
Notifications
You must be signed in to change notification settings - Fork 967
Description
Is your feature request related to a problem? Please describe.
First of all, thanks for all the effort you've put into this package already - it's really helped me on my latest project.
I created the following general purpose 'wrapper' for quickly creating a Worksheet/Tab based on key, title, sheet name, sheet index, or account path. It also includes convenience methods for:
.view()
: Opening a worksheet in your browser directly.protect()
: Protecting a whole sheet (except for the service account running the code).unprotect()
: Unprotecting a whole sheet.df
: Converting directly to a Pandas DataFramepandas.DataFrame.to_gspread()
: Saving a Pandas DataFrame (created using.df
) back to its associated worksheet
I've also added convenience attributes to the worksheet object:
.email
: the service account email (needs to be 'invited' to share a Google Sheet before having access)
.account_path
: path to the service account JSON file
It wasn't immediately obvious to me where to add these in your structure otherwise I'd have created a pull request but the basic code is below if you think this would be a helpful utility to include?
Describe the solution you'd like
from pathlib import Path
import webbrowser
import gspread
def _view(self: gspread.worksheet.Worksheet):
"""Open this Google Sheet in the default web browser"""
webbrowser.open(self.url)
def _protect(self):
body = {
"requests": [{
"addProtectedRange": {"protectedRange": {
"range": {"sheetId": self._properties['sheetId'],},
"warningOnly": False,
"description": f"LOCKED by: {self.email}",
"editors": {
"domainUsersCanEdit": False,
"users": [self.email]
},}}}]}
try:
return self.spreadsheet.batch_update(body)
except gspread.exceptions.APIError as api:
print(f"'{self._properties['title']}' is already protected")
return api
def _unprotect(self):
range_id = self.spreadsheet.list_protected_ranges(self.id).pop()
range_id = range_id['protectedRangeId']
body = {
"requests":
[{"deleteProtectedRange": {"protectedRangeId": range_id}}]
}
try:
return self.spreadsheet.batch_update(body)
except gspread.exceptions.APIError as api:
print(f"'{self._properties['title']}' is already protected")
return api
def _df(self):
"""
Return a Pandas Dataframe from the specified Google Sheet object.
Add attribute:
pd.DataFrame.sheet : gspread.worksheet.Worksheet
Add method:
df.to_gspread()
"""
global pd # Just-in-time import, so as not to 'force' Pandas requirement
import pandas as pd
df = pd.DataFrame(self.get_all_records(numericise_ignore=['all']))
df.sheet = self
setattr(pd.DataFrame, "to_gspread", _to_gspread)
return df
def _to_gspread(self: pd.DataFrame, *args, **kwargs):
"""
Save (ie. clear and update) the Pandas DataFrame to its parent Google Sheet
(gspread.worksheet.Worksheet object) specified in self.sheet
"""
sheet = self.sheet
sheet.clear()
sheet.update([self.columns.values.tolist()] + self.values.tolist())
print(f"Saved: {sheet.spreadsheet.title}\n{sheet.url}")
def Worksheet(key_or_title=None, sheet_name="", sheet_index=0, account_path=""):
"""
Return a Google Worksheet object (gspread.worksheet.Worksheet) from the
specified workbook and worksheet, or the first worksheet if not specified.
sheet_index uses normal Python counting ie. starts at 0.
Added helper methods and shortcuts:
gspread.worksheet.Worksheet.protect()
gspread.worksheet.Worksheet.unprotect()
gspread.worksheet.Worksheet.view()
gspread.worksheet.Worksheet.df
pandas.DataFrame.to_gspread()
.account_path = account_path
.email = client.auth._service_account_email
"""
if key_or_title is None and "WORKBOOK_ID" in globals():
key_or_title = WORKBOOK_ID
if account_path == "" and "GOOGLE_SERVICE_ACCOUNT_JSON_PATH" in globals():
account_path = GOOGLE_SERVICE_ACCOUNT_JSON_PATH
try:
account_path = account_path.resolve()
client = gspread.service_account(filename=account_path)
except FileNotFoundError:
print(f"FileNotFoundError:\n{account_path}")
return
try:
book = client.open(key_or_title)
except gspread.exceptions.SpreadsheetNotFound:
try:
book = client.open_by_key(key_or_title)
except gspread.exceptions.APIError:
print(f"gspread.exceptions.APIError:\n '{key_or_title}' not recognised as a key or title.")
return
try:
if sheet_name:
sheet = book.worksheet(sheet_name)
else:
sheet = book.worksheets()[sheet_index]
except (gspread.exceptions.WorksheetNotFound, IndexError, NameError):
key = f"'{sheet_name}'" if sheet_name else f"index {sheet_index}"
print(f"gspread.exceptions.WorksheetNotFound:\n sheet {key} not found.")
sheet = None
# Add helper methods and shortcuts
setattr(gspread.worksheet.Worksheet, "view", _view)
setattr(gspread.worksheet.Worksheet, "protect", _protect)
setattr(gspread.worksheet.Worksheet, "unprotect", _unprotect)
setattr(gspread.worksheet.Worksheet, 'df', property(_df))
sheet.account_path = account_path
sheet.email = client.auth._service_account_email
return sheet
If you like (some or all of) this and want to incorporate it, I'd be happy submit relevant updates to the README doc.