Skip to content

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

@PFython

Description

@PFython

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.

Metadata

Metadata

Assignees

Projects

No projects

Milestone

No milestone

Relationships

None yet

Development

No branches or pull requests

Issue actions