Manipulating Google Spreadsheets with oauth2client and gspread in Python

This guide introduces how to programmatically manipulate Google Spreadsheet data using Python’s oauth2client and gspread libraries. Google Spreadsheets is a convenient tool for managing data in the cloud, and you can automate and manipulate that data using Python.

Prerequisites

1. Installing Google API Client Libraries

Install the following libraries.

pip install gspread oauth2client

2. Setting up Google API Project

1. Create a new project in Google Cloud Console.
2. Enable Google Sheets API and Google Drive API.
3. Create credentials and select “Service Account Key”. Download the JSON format key and grant appropriate permissions to the project.

3. Sharing the Spreadsheet

Create a spreadsheet and give editing permissions to the email address of the service account you just created.

Python Script Example

Here’s an example script to access Google Spreadsheet data using Python.

import gspread
from oauth2client.service_account import ServiceAccountCredentials

def main():
    # Define the scope for Google Sheets API
    scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive']
    
    # Load credentials
    creds = ServiceAccountCredentials.from_json_keyfile_name('path/to/credentials.json', scope)
    
    # Initialize client
    client = gspread.authorize(creds)
    
    # Open spreadsheet by ID
    sheet = client.open_by_key('your_spreadsheet_id').sheet1
    
    # Read data
    data = sheet.get_all_records()
    print(data)
    
    # Write to cell
    sheet.update_cell(2, 2, 'Hello, World!')

if __name__ == "__main__":
    main()

Notes

• Modify the spreadsheet ID and JSON file path to match your environment.
• For security reasons, do not share the credentials file (JSON file) with others.
• For the JSON file loaded by ServiceAccountCredentials.from_json_keyfile_name, specify the file path of the downloaded service account key.

References

• gspread official repository
• Qiita article: How to use Google Sheets API