Skip to main content

Chapter 15 - Google Sheets (Python)

Here's a concise walkthrough of the main ideas in Chapter 15, each with a small example.

Why Google Sheets + EZSheets​

  • Google Sheets is a free, web-based spreadsheet app with an official API, but that API is complex; EZSheets is a wrapper library that simplifies common tasks.
  • You'll use EZSheets to create/upload/download spreadsheets, read/write data, and work with Google Forms responses and a fake blockchain project.

Installing and setting up EZSheets​

You install EZSheets with pip, then do a one-time Google Cloud setup to get credentials and tokens.

One-time Google Cloud setup (5 parts)​

  1. Create a Google Cloud project – at https://console.cloud.google.com, click "Select a project" → "New Project", accept the generated name/ID, click "Create".
  2. Enable APIs – in "APIs & Services → Library", enable both "Google Sheets API" and "Google Drive API".
  3. Configure OAuth consent screen – choose External, set app name, support email, and developer contact email, add scopes for .../auth/drive and .../auth/spreadsheets, add test users (the Gmail addresses whose spreadsheets your scripts will access), then review the summary.
  4. Create credentials – "APIs & Services → Credentials → + Create Credentials → OAuth client ID → Desktop App → Create → Download JSON". Rename the downloaded file to credentials-sheets.json and place it next to your scripts.
  5. Log in with credentials – in the folder containing credentials-sheets.json, start Python and run import ezsheets; EZSheets opens a browser, you approve access for Sheets and Drive, and it creates token-drive.pickle and token-sheets.pickle files in that folder.

If you later accidentally leak these files (they act like passwords for Sheets access), revoke them by deleting the OAuth client in the Cloud Console's Credentials page, then recreate credentials and tokens.

Spreadsheet objects​

A Google spreadsheet is an ezsheets.Spreadsheet object with attributes like title, id, url, sheetTitles, and sheets.

Creating, uploading, listing spreadsheets​

New blank spreadsheet:

import ezsheets

ss = ezsheets.Spreadsheet()
ss.title = 'Title of My New Spreadsheet'
print(ss.url, ss.id)

Load an existing spreadsheet (by redirect URL, full URL, or plain ID):

example_ss = ezsheets.Spreadsheet('https://autbor.com/examplegs')
ss1 = ezsheets.Spreadsheet(
    'https://docs.google.com/spreadsheets/d/1TzOJxh.../'
)
ss2 = ezsheets.Spreadsheet('1TzOJxhNKr15tzdZxTqtQ3EmDP6em_elnbtmZIcyu8vI')

Upload a local file (Excel, ODS, CSV, TSV) to Google Sheets:

ss = ezsheets.upload('my_spreadsheet.xlsx')
print(ss.title)  # 'my_spreadsheet'

List all spreadsheets in your Google account (including Trash):

ezsheets.listSpreadsheets()
# {'1J-Jx6N...': 'Education Data', ...}

Accessing spreadsheet attributes and copying sheets​

You can copy a sheet from a read-only spreadsheet into a new one:

import ezsheets

example_ss = ezsheets.Spreadsheet('https://autbor.com/examplegs')
ss = ezsheets.Spreadsheet()
example_ss.sheets[0].copyTo(ss)
ss.sheets[0].delete()  # remove default Sheet1
ss.title = 'Sweigart Books'
print(ss.sheetTitles)  # ('Copy of Books',)

Refresh to reflect remote changes:

ss.refresh()

Downloading spreadsheets in various formats​

ss = ezsheets.Spreadsheet('https://autbor.com/examplegs')
ss.downloadAsExcel()             # 'Sweigart_Books.xlsx'
ss.downloadAsODS()               # 'Sweigart_Books.ods'
ss.downloadAsCSV()               # first sheet only
ss.downloadAsTSV()
ss.downloadAsPDF()
ss.downloadAsHTML()              # ZIP of HTML files
ss.downloadAsExcel('custom.xlsx')

CSV/TSV can only contain one sheet; EZSheets downloads the first sheet unless you reorder sheets.

Deleting spreadsheets​

Move to Drive Trash:

ss.delete()

Permanently delete:

ss.delete(permanent=True)

listSpreadsheets() still shows trashed spreadsheets until they're permanently deleted.

Sheet objects​

Each Spreadsheet has one or more Sheet objects (worksheets) accessible via ss.sheets[index], ss[sheet_title], or ss.Sheet().

Creating, moving, deleting sheets​

ss = ezsheets.Spreadsheet()
ss.title = 'Multiple Sheets'
print(ss.sheetTitles)         # ('Sheet1',)

ss.Sheet('Spam')
ss.Sheet('Eggs')
print(ss.sheetTitles)         # ('Sheet1', 'Spam', 'Eggs')

ss.Sheet('Bacon', 0)          # insert at index 0
print(ss.sheetTitles)         # ('Bacon', 'Sheet1', 'Spam', 'Eggs')

# Reorder using index attribute
ss.sheets[0].index = 2
print(ss.sheetTitles)         # ('Sheet1', 'Spam', 'Bacon', 'Eggs')

Delete and clear:

ss.sheets[0].delete()         # delete first sheet
sheet = ss['Eggs']
sheet.clear()                 # clear all cell data, sheet remains

Copy sheets between spreadsheets:

ss1 = ezsheets.Spreadsheet()
ss1.title = 'First Spreadsheet'
ss1.sheets[0].title = 'Spam'

ss2 = ezsheets.Spreadsheet()
ss2.title = 'Second Spreadsheet'
ss2.sheets[0].title = 'Eggs'

ss1[0].updateRow(1, ['Some', 'data', 'in', 'the', 'first', 'row'])
ss1[0].copyTo(ss2)
print(ss2.sheetTitles)        # ('Eggs', 'Copy of Spam')

Reading and writing cell data​

Basic cell access​

String-style address: 'A1'. Tuple-style address: (column, row) as integers (1-based).

ss = ezsheets.Spreadsheet()
ss.title = 'My Spreadsheet'
sheet = ss.sheets[0]

sheet['A1'] = 'Name'
sheet['B1'] = 'Age'
sheet['C1'] = 'Favorite Movie'

sheet['A2'] = 'Alice'
sheet['B2'] = 30         # will be stored and returned as '30'
sheet['C2'] = 'RoboCop'

print(sheet['A1'])       # 'Name'
print(sheet['A2'])       # 'Alice'
print(sheet[2, 1])       # 'Age' (column 2, row 1 == B1)
print(sheet['B2'])       # '30'

All values are returned as strings, even numbers.

Converting addresses and columns​

Helper functions:

import ezsheets

print(ezsheets.convertAddress('A2'))      # (1, 2)
print(ezsheets.convertAddress(1, 2))      # 'A2'
print(ezsheets.getColumnLetterOf(2))      # 'B'
print(ezsheets.getColumnNumberOf('B'))    # 2
print(ezsheets.getColumnLetterOf(999))    # 'ALK'
print(ezsheets.getColumnNumberOf('ZZZ'))  # 18278

Reading and writing whole rows/columns​

Updating many cells one-by-one is slow; EZSheets lets you get/update entire rows or columns at once.

import ezsheets

ss = ezsheets.upload('produceSales3.xlsx')
sheet = ss.sheets[0]

print(sheet.getRow(1))      # ['PRODUCE', 'COST PER POUND', 'POUNDS SOLD', 'TOTAL', '', '']
print(sheet.getRow(2))      # ['Potatoes', '0.86', '21.6', '18.58', '', '']

print(sheet.getColumn(1))   # column A values
print(sheet.getColumn('A')) # same as getColumn(1)

Update a row:

sheet.updateRow(3, ['Pumpkin', '11.50', '20', '230'])
print(sheet.getRow(3))      # ['Pumpkin', '11.50', '20', '230', '', '']

Update a column with transformed data:

column_one = sheet.getColumn(1)
for i, value in enumerate(column_one):
    column_one[i] = value.upper()

sheet.updateColumn(1, column_one)

Update all rows in one request:

rows = sheet.getRows()
rows[10][0] = 'PUMPKIN'
rows[10][2] = '400'
rows[10][3] = '904'
sheet.updateRows(rows)

Resize the sheet:

print(sheet.rowCount)       # e.g. 23758
print(sheet.columnCount)    # 6
sheet.columnCount = 4       # deletes extra columns at the end

Google Forms​

  • Google Forms can write responses into a Google Sheets spreadsheet.
  • With EZSheets, your Python scripts can read that spreadsheet and automate responses (e.g., check for new rows and send notifications using scheduling from Chapter 19 and messaging from Chapter 20).
  • This demonstrates Python as "glue" connecting web forms + Sheets + schedulers + notifications.

Project: Fake Blockchain Cryptocurrency Scam (Boringcoin)​

Spreadsheet at https://autbor.com/boringcoin stores a toy "blockchain" with columns: sender, recipient, amount.

Rules:

  • If sender is 'PRE-MINE', coins are created from nothing and added to recipient.
  • Otherwise, subtract amount from sender and add to recipient.
  • All balances are tracked by name.

Step 1: Audit the blockchain (balances)​

import ezsheets

ss = ezsheets.Spreadsheet('https://autbor.com/boringcoin')
accounts = {}  # name -> balance

for row in ss.sheets[0].getRows():
    sender, recipient, amount = row[0], row[1], int(row[2])

    if sender == 'PRE-MINE':
        accounts.setdefault(recipient, 0)
        accounts[recipient] += amount
    else:
        accounts.setdefault(sender, 0)
        accounts.setdefault(recipient, 0)
        accounts[sender] -= amount
        accounts[recipient] += amount

print(accounts)

total = 0
for amount in accounts.values():
    total += amount
print('Total Boringcoins:', total)

Output shows balances per account and total (1,000,000,000 Boringcoins from the pre-mine).

Step 2: Make transactions via script​

Program addBoringcoinTransaction.py:

  • Reads 3 command-line args: sender, recipient, amount.
  • Appends a new row to the bottom of the blockchain sheet.
# addBoringcoinTransaction.py
import sys, ezsheets

if len(sys.argv) != 4:
    print('Usage: python addBoringcoinTransaction.py SENDER RECIPIENT AMOUNT')
    sys.exit(1)

sender, recipient, amount = sys.argv[1], sys.argv[2], sys.argv[3]
ss = ezsheets.Spreadsheet('https://autbor.com/boringcoin')
sheet = ss.sheets[0]
rows = sheet.getRows()
rows.append([sender, recipient, amount])
sheet.updateRows(rows)

This demonstrates combining command-line arguments, EZSheets, and Google Sheets to modify a shared "ledger".

Overall idea of the chapter​

Chapter 15 shows how to automate Google Sheets with EZSheets: one-time OAuth setup, creating/uploading/downloading spreadsheets, reading and writing cells/rows/columns in bulk, managing sheets, and building real projects (Boringcoin audit + transactions). The key advantage over openpyxl is that Google Sheets are live on the web and shareable.