Quickstart to the Google Sheets

Complete the steps described in the rest of this page to create a simple Python command-line application that makes requests to the Google Sheets API.

Prerequisites

To run this quickstart, you'll need:

  • Python 2.6 or greater

  • The pip package management tool

  • A Google account

Step 1: Turn on the Google Sheets API

Click this button to create a new Cloud Platform project and automatically enable the Google Sheets API:

Enable the Google Sheets APIIn resulting dialog click DOWNLOAD CLIENT CONFIGURATION and save the file credentials.json to your working directory.

Step 2: Install the Google Client Library

Run the following command to install the library using pip:

pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib

See the library's installation page for the alternative installation options.

Step 3: Set up the sample

Create a file named quickstart.py in your working directory and copy in the following code:

sheets/quickstart/quickstart.pyView on GitHub

from __future__ import print_function
import pickle
import os.path
from googleapiclient.discovery import build
from google_auth_oauthlib.flow import InstalledAppFlow
from google.auth.transport.requests import Request

# If modifying these scopes, delete the file token.pickle.
SCOPES = ['https://www.googleapis.com/auth/spreadsheets.readonly']

# The ID and range of a sample spreadsheet.
SAMPLE_SPREADSHEET_ID = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms'
SAMPLE_RANGE_NAME = 'Class Data!A2:E'

def main():
    """Shows basic usage of the Sheets API.
    Prints values from a sample spreadsheet.
    """
    creds = None
    # The file token.pickle stores the user's access and refresh tokens, and is
    # created automatically when the authorization flow completes for the first
    # time.
    if os.path.exists('token.pickle'):
        with open('token.pickle', 'rb') as token:
            creds = pickle.load(token)
    # If there are no (valid) credentials available, let the user log in.
    if not creds or not creds.valid:
        if creds and creds.expired and creds.refresh_token:
            creds.refresh(Request())
        else:
            flow = InstalledAppFlow.from_client_secrets_file(
                'credentials.json', SCOPES)
            creds = flow.run_local_server(port=0)
        # Save the credentials for the next run
        with open('token.pickle', 'wb') as token:
            pickle.dump(creds, token)

    service = build('sheets', 'v4', credentials=creds)

    # Call the Sheets API
    sheet = service.spreadsheets()
    result = sheet.values().get(spreadsheetId=SAMPLE_SPREADSHEET_ID,
                                range=SAMPLE_RANGE_NAME).execute()
    values = result.get('values', [])

    if not values:
        print('No data found.')
    else:
        print('Name, Major:')
        for row in values:
            # Print columns A and E, which correspond to indices 0 and 4.
            print('%s, %s' % (row[0], row[4]))

if __name__ == '__main__':
    main()

Step 4: Run the sample

Run the sample using the following command:

python quickstart.py

  1. The sample will attempt to open a new window or tab in your default browser. If this fails, copy the URL from the console and manually open it in your browser.

    If you are not already logged into your Google account, you will be prompted to log in. If you are logged into multiple Google accounts, you will be asked to select one account to use for the authorization.

  2. Click the Accept button.

  3. The sample will proceed automatically, and you may close the window/tab.

done It worked!warning There was a problem

Notes

  • Authorization information is stored on the file system, so subsequent executions will not prompt for authorization.

  • The authorization flow in this example is designed for a command-line application. For information on how to perform authorization in a web application, see Using OAuth 2.0 for Web Server Applications.

Further reading

Troubleshooting

This section describes some common issues that you may encounter while attempting to run this quickstart and suggests possible solutions.

AttributeError: 'Module_six_moves_urllib_parse' object has no attribute 'urlparse'

This error can occur in Mac OSX where the default installation of the six module (a dependency of this library) is loaded before the one that pip installed. To fix the issue, add pip's install location to the PYTHONPATH system environment variable:

  1. Determine pip's install location with the following command:

    pip show si | grep "Location:" | cut -d " " -f2

  2. Add the following line to your ~/.bashrc file, replacing <pip_install_path> with the value determined above:

    export PYTHONPATH=$PYTHONPATH:<pip_install_path>

  3. Reload your ~/.bashrc file in any open terminal windows using the following command:

source ~/.bashrc

TypeError: sequence item 0: expected str instance, bytes found

This error is due to a bug in httplib2, and upgrading to the latest version should resolve it:

pip install --upgrade httplib2

Cannot uninstall 'six'. It is a distutils installed project...

When running the pip install command you may receive the following error:

Cannot uninstall 'six'. It is a distutils installed project and thus we
cannot accurately determine which files belong to it which would lead to
only a partial uninstall.

This can happen on Mac OSX when pip attempts to upgrade the six package that came pre-installed. To work around this issue you can add the flag --ignore-installed six to the pip install command listed in Step 2.

This app isn't verified.

The OAuth consent screen that is presented to the user may show the warning "This app isn't verified" if it is requesting scopes that provide access to sensitive user data. These applications must eventually go through the verification process to remove that warning and other limitations. During the development phase you can continue past this warning by clicking Advanced > Go to {Project Name} (unsafe).

Reference : https://developers.google.com/sheets/api/quickstart/python

PreviousA Guide to ExcelNextPython Excel Tutorial: The Definitive Guide

Last updated