EZSheets

A Pythonic interface to the Google Sheets API that actually works as of November 2019.

Installation

EZSheets can be installed from PyPI using pip:

pip install ezsheets

On macOS and Linux, installing EZSheets for Python 3 is done with pip3:

pip3 install ezsheets

If you run into permissions errors, try installing with the –user option:

pip install --user ezsheets

Before you can use EZSheets, you need to enable the Google Sheets and Google Drive APIs for your Google account. Visit the following web pages and click the Enable API buttons at the top of each:

You’ll also need to obtain three files, which you should save in the same folder as your .py Python script that uses EZSheets:

  • A credentials file named credentials-sheets.json
  • A token for Google Sheets named token-sheets.pickle
  • A token for Google Drive named token-drive.pickle

The credentials file will generate the token files. The easiest way to obtain a credentials file is to go to the Google Sheets Python Quickstart page at https://developers.google.com/sheets/api/quickstart/python/ and click the blue Enable the Google Sheets API button. You’ll need to log in to your Google account to view this page.

Clicking this button will bring up a window with a Download Client Configuration link that lets you download a credentials.json file. Rename this file to credentials-sheets.json and place it in the same folder as your Python scripts.

Once you have a credentials-sheets.json file, run the import ezsheets module. The first time you import the EZSheets module, it will open a new browser window for you to log in to your Google account. Click Allow.

The message about Quickstart comes from the fact that you downloaded the credentials file from the Google Sheets Python Quickstart page. Note that this window will open twice: first for Google Sheets access and second for Google Drive access. EZSheets uses Google Drive access to upload, download, and delete spreadsheets.

After you log in, the browser window will prompt you to close it, and the token-sheets.pickle and token-drive.pickle files will appear in the same folder as credentials-sheets.json. You only need to go through this process the first time you run import ezsheets.

If you encounter an error after clicking Allow and the page seems to hang, make sure you have first enabled the Google Sheets and Drive APIs from the links at the start of this section. It may take a few minutes for Google’s servers to register this change, so you may have to wait before you can use EZSheets.

Don’t share the credential or token files with anyone: treat them like passwords.

Quickstart

>>> import ezsheets

>>> ss = ezsheets.createSpreadsheet(title='My New Spreadsheet')

>>> ss.sheetTitles
('Sheet1',)

>>> ss.sheets
(Sheet(sheetId=0, title='Sheet1', rowCount=1000, columnCount=26),)

>>> sh = ss.sheets[0]
>>> sh.title
'Sheet1'

>>> sh.updateRow(1, ['Name', 'Species', 'Color', 'Weight'])
>>> sh.updateRow(2, ('Zophie', 'Cat', 'Gray', 11))
>>> sh[1, 1]
'Name'
>>> sh[1, 2]
'Zophie'
>>> sh['A2']
'Zophie'
>>> sh['A2'] = 'Pooka'
>>> sh['A2']
'Pooka'

The API section contains complete documentation.

Unit Tests

The unit test suite takes approximately 7 minutes to run, due to the throttling.

API

exception ezsheets.EZSheetsException

This class exists for this module to raise for EZSheets-specific problems.

ezsheets.IGNORE_QUOTA = False

TODO - create a context manager to wrap calls, so that we can do both preventative throttling and automated retries if it somehow raises an exception. Also, use a sqlite database so that multiple scripts use the same queue.

Features to add: - delete spreadsheets - download as csv/excel/whatever

class ezsheets.Sheet(spreadsheet, sheetId)

This class represents an individual worksheet inside a spreadsheet. Sheets are composed of columns and rows of cells, which contain a single string value.

columnCount

The number of columns in this Sheet object.

columnGroupControlAfter

TODO

frozenColumnCount

The integer number of frozen columns in this Sheet object. Frozen columns remain visible in the browser even as the user scrolls the Sheet. There must be at least one non-frozen column in the Sheet.

frozenRowCount

The integer number of frozen rows in this Sheet object. Frozen rows remain visible in the browser even as the user scrolls down the Sheet. There must be at least one non-frozen row in the Sheet.

get(*args)

Retrieve the value in a cell. The arguments to get() can either be two integers (column and row) or a single string such as ‘A1’.

hideGridlines

The Boolean setting of whether the gridlines in the Sheet are visible or not.

index

The integer index of this Sheet in it’s Spreadsheet’s tuple of Sheet objects.

rowCount

The number of rows in this Sheet object.

rowGroupControlAfter

TODO

sheetId

The unique ID string of this Sheet object in its Spreadsheet.

spreadsheet

The Spreadsheet object that contains this Sheet object.

tabColor

The color of the Sheet’s tab as displayed in the browser.

title

The title of this Sheet on Google Sheets. Both Spreadsheets and Sheets have titles.

class ezsheets.Spreadsheet(spreadsheetId)

This class represents a Spreadsheet on Google Sheets. Spreadsheets can contain one or more sheets, also called worksheets.

createSheet(title='', index=None, columnCount=26, rowCount=1000)

Create a new Sheet object in this Spreadsheet.

refresh()

Updates this Spreadsheet object’s Sheet objects with the current data of the spreadsheet and sheets on Google sheets.

sheetTitles

A tuple of the Sheet objects’ titles (as strings) in this Spreadsheet object.

spreadsheetId

The unique id for this Spreadsheet on Google Sheets.

title

The string title for this Spreadsheet on Google Sheets. Both Spreadsheets and Sheets have titles.

url

The URL for this Spreadsheet on Google Sheets.

ezsheets.getColumnLetterOf(columnNumber)

getColumnLetterOf(1) => ‘A’, getColumnLetterOf(27) => ‘AA’

ezsheets.getColumnNumberOf(columnLetter)

getColumnNumberOf(‘A’) => 1, getColumnNumberOf(‘AA’) => 27