Python & Google Sheets

Photo by Philippe Bout on Unsplash

Environment: python3, macOS Mojave

1. Enable Google Sheets API

Access this site and enable the API.

Save the credentials that you’ll need later.

2. Install client library via pip

Save the following content as requirements.txt.

google-api-python-client
google-auth-httplib2
google-auth-oauthlib

Install the libraries with this command

pip3 install -r requirements.txt

3. Access Spreadsheet using the sample code

Following the sample code on the quickstart page, access the spreadsheet. The following is the entire sample. Two things you need to do; 1) replace SHEET_ID with yours (see below for how to get it), and 2) place the credentials.json file you downloaded in the step 1 on the same directory as this code.

I’m fetching the cell A1:A1, and my spreadsheet looks like this:

If you run the code, you’ll see…

$ python3 quickstart.py
hello, python

Yay!

Update the sheet

This is the example to update the sheet:

def put_values(creds):
    service = build('sheets', 'v4', credentials=creds)
    # Call the Sheets API
    sheet = service.spreadsheets()
    payload = {"values": [["how are you?"]]}
    sheet.values().update(
         spreadsheetId=SHEET_ID,
         range=RANGE_PUT,
         valueInputOption="USER_ENTERED",
         body=payload).execute()