Jacob Kaplan-Moss

Today I Learned…

A DictWriter interface for Google Spreadsheets

I’ve been using Python’s csv library approximately forever so its interfaces – particularly the DictReader and DictWriter interfaces – are almost literally muscle memory at this point. So when I needed to push a bunch of data to Google Sheets, I naturally wanted an interface that worked like DictWriter.

Here’s the code:

class WorksheetDictWriter:
    Something like a `csv.DictWriter`, except for a `gspread.Worksheet`

    def __init__(self, worksheet: gspread.Worksheet, fieldnames: list[str], column="A"):
        self.fieldnames = fieldnames
        self.worksheet = worksheet
        self.column = column
        self.current_row = 1

    def writeheader(self):
        self.writerow(dict(zip(self.fieldnames, self.fieldnames)))

    def writerow(self, row: dict[str, str]):
        to_write = []
        for name in self.fieldnames:
            to_write.append(row.get(name, ""))
        self.worksheet.update(f"{self.column}{self.current_row}", [to_write])
        self.current_row += 1

    def writerows(self, rows: list[dict[str, str]]):
        for row in rows:

I use this snippet like this:

data = [
    {"Name": "John Lennon", "Birthdate": "October 9, 1940"},
    {"Name": "Paul McCartney", "Birthdate": "June 18, 1942"},
    {"Name": "George Harrison", "Birthdate": "February 25, 1943"},
    {"Name": "Ringo Starr", "Birthdate": "July 7, 1940"},

creds = keyring.get_password("gspread", "credentials")
gss = gspread.service_account_from_dict(json.loads(creds))
document = gss.open("Name Of Sheet")
worksheet = document.get_worksheet(0)

writer = WorksheetDictWriter(worksheet, fieldnames=["Name", "Birthdate"])

What’s up with the keyring business? See here.

Nothing particularly tricky, but it makes me happy.