Complete the steps described in the rest of this page, and in about five minutes you'll have a simple Python command-line application that makes requests to the Google Sheets API.
To run this quickstart, you'll need:
- Python 2.6 or greater.
- The pip package management tool.
- Access to the internet and a web browser.
- A Google account.
Step 1: Turn on the Google Sheets API
- Use this wizard to create or select a project in the Google Developers Console and automatically turn on the API. Click Continue, then Go to credentials.
- On the Add credentials to your project page, click the Cancel button.
- At the top of the page, select the OAuth consent screen tab. Select an Email address, enter a Product name if not already set, and click the Save button.
- Select the Credentials tab, click the Create credentials button and select OAuth client ID.
- Select the application type Other, enter the name "Google Sheets API Quickstart", and click the Create button.
- Click OK to dismiss the resulting dialog.
- Click the (Download JSON) button to the right of the client ID.
- Move this file to your working directory and rename it
Step 2: Install the Google Client Library
Run the following command to install the library using pip:
pip install --upgrade google-api-python-client
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
from __future__ import print_function import httplib2 import os from apiclient import discovery from oauth2client import client from oauth2client import tools from oauth2client.file import Storage try: import argparse flags = argparse.ArgumentParser(parents=[tools.argparser]).parse_args() except ImportError: flags = None # If modifying these scopes, delete your previously saved credentials # at ~/.credentials/sheets.googleapis.com-python-quickstart.json SCOPES = 'https://www.googleapis.com/auth/spreadsheets.readonly' CLIENT_SECRET_FILE = 'client_secret.json' APPLICATION_NAME = 'Google Sheets API Python Quickstart' def get_credentials(): """Gets valid user credentials from storage. If nothing has been stored, or if the stored credentials are invalid, the OAuth2 flow is completed to obtain the new credentials. Returns: Credentials, the obtained credential. """ home_dir = os.path.expanduser('~') credential_dir = os.path.join(home_dir, '.credentials') if not os.path.exists(credential_dir): os.makedirs(credential_dir) credential_path = os.path.join(credential_dir, 'sheets.googleapis.com-python-quickstart.json') store = Storage(credential_path) credentials = store.get() if not credentials or credentials.invalid: flow = client.flow_from_clientsecrets(CLIENT_SECRET_FILE, SCOPES) flow.user_agent = APPLICATION_NAME if flags: credentials = tools.run_flow(flow, store, flags) else: # Needed only for compatibility with Python 2.6 credentials = tools.run(flow, store) print('Storing credentials to ' + credential_path) return credentials def main(): """Shows basic usage of the Sheets API. Creates a Sheets API service object and prints the names and majors of students in a sample spreadsheet: https://docs.google.com/spreadsheets/d/1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms/edit """ credentials = get_credentials() http = credentials.authorize(httplib2.Http()) discoveryUrl = ('https://sheets.googleapis.com/$discovery/rest?' 'version=v4') service = discovery.build('sheets', 'v4', http=http, discoveryServiceUrl=discoveryUrl) spreadsheetId = '1BxiMVs0XRA5nFMdKvBdBZjgmUUqptlbs74OgvE2upms' rangeName = 'Class Data!A2:E' result = service.spreadsheets().values().get( spreadsheetId=spreadsheetId, range=rangeName).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, row)) if __name__ == '__main__': main()
Step 4: Run the sample
Run the sample using the following command:
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.
- Click the Accept button.
- The sample will proceed automatically, and you may close the window/tab.
- 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.
- Google Developers Console help documentation
- Google APIs Client for Python documentation
- Google Sheets API PyDoc documentation
- Sheets API リファレンス ドキュメント
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
system environment variable:
Determine pip's install location with the following command:
pip show six | grep "Location:" | cut -d " " -f2
Add the following line to your
<pip_install_path>with the value determined above:
~/.bashrcfile in any open terminal windows using the following command:
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