Suggestions for Wrapper and Convenient Methods (add protect/unprotect all)
- Dominant language
- Python
- Stars
- 7.5k
- Forks
- 978
- PR merge metrics
- No merged PRs in 30d
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 DataFrame
- `pandas.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.
Contributor guide
Assessment
This issue has not been assessed yet.