burnash / burnash/gspread

Suggestions for Wrapper and Convenient Methods (add protect/unprotect all)

Open
#1,208 10 comments 0 reactions 1 assignee Claimed by @PFython View on GitHub
Feature Request good first issue
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

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.