Google Sheets Integration with Python: A Step-by-Step Guide

Google Sheets is a powerful cloud-based tool that allows users to store and analyze data collaboratively. Integrating Google Sheets with Python opens up endless automation possibilities, from data entry and report generation to real-time updates. In this guide, we’ll walk through the entire process of integrating Google Sheets with Python using the gspread library.

Why Integrate Google Sheets with Python?

• Automate Data Entry: Update sheets automatically from scripts.
• Analyze Data: Fetch data for further processing.
• Generate Reports: Write results directly to a shared sheet.
• Real-time Collaboration: Keep teams updated with real-time data.

Step 1: Enable Google Sheets API

To interact with Google Sheets from Python, we first need to enable the Google Sheets API and obtain credentials.

1. Enable API Access

1. Go to the Google Cloud Console.
2. Create a new project (or select an existing one).
3. Navigate to APIs & Services > Library.
4. Search for Google Sheets API and enable it.
5. Also, enable the Google Drive API (needed for file access permissions).

2. Create Service Account Credentials

1. Go to APIs & Services > Credentials.
2. Click on Create Credentials > Service Account.
3. Assign it a name and click Create & Continue.
4. Under “Grant this service account access,” select Editor role (optional but useful).
5. After creating, go to the Keys tab and click Add Key > JSON.
6. Download the JSON file (keep it secure).

3. Share Your Google Sheet

• Open the Google Sheet where you want to write data.
• Click Share and add the email address from the service account JSON file.
• Set permissions to Editor.

Step 2: Install Required Libraries

Use the following command to install gspread and oauth2client:

pip <span>install </span>gspread google-auth google-auth-oauthlib google-auth-httplib2
pip <span>install </span>gspread google-auth google-auth-oauthlib google-auth-httplib2
pip install gspread google-auth google-auth-oauthlib google-auth-httplib2

Enter fullscreen mode Exit fullscreen mode

Step 3: Authenticate with Google Sheets

Create a Python script (google_sheets.py) and load the service account credentials.

<span>import</span> <span>gspread</span>
<span>from</span> <span>google.oauth2.service_account</span> <span>import</span> <span>Credentials</span>
<span># Load Google Sheets API credentials </span><span>SERVICE_ACCOUNT_FILE</span> <span>=</span> <span>"</span><span>your-service-account-file.json</span><span>"</span>  <span># Update with your JSON file </span><span>SCOPES</span> <span>=</span> <span>[</span><span>"</span><span>https://www.googleapis.com/auth/spreadsheets</span><span>"</span><span>]</span>
<span># Authenticate and create the client </span><span>creds</span> <span>=</span> <span>Credentials</span><span>.</span><span>from_service_account_file</span><span>(</span><span>SERVICE_ACCOUNT_FILE</span><span>,</span> <span>scopes</span><span>=</span><span>SCOPES</span><span>)</span>
<span>client</span> <span>=</span> <span>gspread</span><span>.</span><span>authorize</span><span>(</span><span>creds</span><span>)</span>
<span>import</span> <span>gspread</span>
<span>from</span> <span>google.oauth2.service_account</span> <span>import</span> <span>Credentials</span>

<span># Load Google Sheets API credentials </span><span>SERVICE_ACCOUNT_FILE</span> <span>=</span> <span>"</span><span>your-service-account-file.json</span><span>"</span>  <span># Update with your JSON file </span><span>SCOPES</span> <span>=</span> <span>[</span><span>"</span><span>https://www.googleapis.com/auth/spreadsheets</span><span>"</span><span>]</span>

<span># Authenticate and create the client </span><span>creds</span> <span>=</span> <span>Credentials</span><span>.</span><span>from_service_account_file</span><span>(</span><span>SERVICE_ACCOUNT_FILE</span><span>,</span> <span>scopes</span><span>=</span><span>SCOPES</span><span>)</span>
<span>client</span> <span>=</span> <span>gspread</span><span>.</span><span>authorize</span><span>(</span><span>creds</span><span>)</span>
import gspread
from google.oauth2.service_account import Credentials

# Load Google Sheets API credentials SERVICE_ACCOUNT_FILE = "your-service-account-file.json"  # Update with your JSON file SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]

# Authenticate and create the client creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
client = gspread.authorize(creds)

Enter fullscreen mode Exit fullscreen mode

Step 4: Open a Google Sheet

Now, let’s open a Google Sheet by its ID (found in the sheet URL):

<span># Open the Google Sheet by ID </span><span>SHEET_ID</span> <span>=</span> <span>"</span><span>your-google-sheet-id</span><span>"</span>  <span># Replace with your actual sheet ID </span><span>sheet</span> <span>=</span> <span>client</span><span>.</span><span>open_by_key</span><span>(</span><span>SHEET_ID</span><span>).</span><span>sheet1</span>  <span># Access the first worksheet </span>
<span># Open the Google Sheet by ID </span><span>SHEET_ID</span> <span>=</span> <span>"</span><span>your-google-sheet-id</span><span>"</span>  <span># Replace with your actual sheet ID </span><span>sheet</span> <span>=</span> <span>client</span><span>.</span><span>open_by_key</span><span>(</span><span>SHEET_ID</span><span>).</span><span>sheet1</span>  <span># Access the first worksheet </span>
# Open the Google Sheet by ID SHEET_ID = "your-google-sheet-id"  # Replace with your actual sheet ID sheet = client.open_by_key(SHEET_ID).sheet1  # Access the first worksheet 

Enter fullscreen mode Exit fullscreen mode

If you prefer to open it by name:

<span>sheet</span> <span>=</span> <span>client</span><span>.</span><span>open</span><span>(</span><span>"</span><span>Your Sheet Name</span><span>"</span><span>).</span><span>sheet1</span>  <span># Replace with your sheet name </span>
<span>sheet</span> <span>=</span> <span>client</span><span>.</span><span>open</span><span>(</span><span>"</span><span>Your Sheet Name</span><span>"</span><span>).</span><span>sheet1</span>  <span># Replace with your sheet name </span>
sheet = client.open("Your Sheet Name").sheet1  # Replace with your sheet name 

Enter fullscreen mode Exit fullscreen mode

Step 5: Read Data from Google Sheets

To read all values:

<span>data</span> <span>=</span> <span>sheet</span><span>.</span><span>get_all_records</span><span>()</span>
<span>print</span><span>(</span><span>data</span><span>)</span>
<span>data</span> <span>=</span> <span>sheet</span><span>.</span><span>get_all_records</span><span>()</span>
<span>print</span><span>(</span><span>data</span><span>)</span>
data = sheet.get_all_records()
print(data)

Enter fullscreen mode Exit fullscreen mode

To read a specific cell:

<span>value</span> <span>=</span> <span>sheet</span><span>.</span><span>cell</span><span>(</span><span>2</span><span>,</span> <span>1</span><span>).</span><span>value</span>  <span># Read value from row 2, column 1 </span><span>print</span><span>(</span><span>value</span><span>)</span>
<span>value</span> <span>=</span> <span>sheet</span><span>.</span><span>cell</span><span>(</span><span>2</span><span>,</span> <span>1</span><span>).</span><span>value</span>  <span># Read value from row 2, column 1 </span><span>print</span><span>(</span><span>value</span><span>)</span>
value = sheet.cell(2, 1).value  # Read value from row 2, column 1 print(value)

Enter fullscreen mode Exit fullscreen mode

Step 6: Write Data to Google Sheets

To insert a new row:

<span>row_data</span> <span>=</span> <span>[</span><span>"</span><span>John Doe</span><span>"</span><span>,</span> <span>"</span><span>john@example.com</span><span>"</span><span>,</span> <span>"</span><span>Data Analyst</span><span>"</span><span>]</span>
<span>sheet</span><span>.</span><span>append_row</span><span>(</span><span>row_data</span><span>)</span>
<span>print</span><span>(</span><span>"</span><span> Row added successfully!</span><span>"</span><span>)</span>
<span>row_data</span> <span>=</span> <span>[</span><span>"</span><span>John Doe</span><span>"</span><span>,</span> <span>"</span><span>john@example.com</span><span>"</span><span>,</span> <span>"</span><span>Data Analyst</span><span>"</span><span>]</span>
<span>sheet</span><span>.</span><span>append_row</span><span>(</span><span>row_data</span><span>)</span>
<span>print</span><span>(</span><span>"</span><span> Row added successfully!</span><span>"</span><span>)</span>
row_data = ["John Doe", "john@example.com", "Data Analyst"]
sheet.append_row(row_data)
print(" Row added successfully!")

Enter fullscreen mode Exit fullscreen mode

To update a specific cell:

<span>sheet</span><span>.</span><span>update_cell</span><span>(</span><span>2</span><span>,</span> <span>2</span><span>,</span> <span>"</span><span>Updated Value</span><span>"</span><span>)</span>  <span># Update row 2, column 2 </span><span>print</span><span>(</span><span>"</span><span> Cell updated successfully!</span><span>"</span><span>)</span>
<span>sheet</span><span>.</span><span>update_cell</span><span>(</span><span>2</span><span>,</span> <span>2</span><span>,</span> <span>"</span><span>Updated Value</span><span>"</span><span>)</span>  <span># Update row 2, column 2 </span><span>print</span><span>(</span><span>"</span><span> Cell updated successfully!</span><span>"</span><span>)</span>
sheet.update_cell(2, 2, "Updated Value")  # Update row 2, column 2 print(" Cell updated successfully!")

Enter fullscreen mode Exit fullscreen mode

Step 7: Delete Data from Google Sheets

To clear an entire row:

<span>sheet</span><span>.</span><span>delete_row</span><span>(</span><span>2</span><span>)</span>  <span># Deletes the second row </span><span>print</span><span>(</span><span>"</span><span> Row deleted successfully!</span><span>"</span><span>)</span>
<span>sheet</span><span>.</span><span>delete_row</span><span>(</span><span>2</span><span>)</span>  <span># Deletes the second row </span><span>print</span><span>(</span><span>"</span><span> Row deleted successfully!</span><span>"</span><span>)</span>
sheet.delete_row(2)  # Deletes the second row print(" Row deleted successfully!")

Enter fullscreen mode Exit fullscreen mode

Advanced Features

1. Creating a New Google Sheet

<span>new_sheet</span> <span>=</span> <span>client</span><span>.</span><span>create</span><span>(</span><span>"</span><span>My New Google Sheet</span><span>"</span><span>)</span>
<span>print</span><span>(</span><span>f</span><span>"</span><span> New sheet created: </span><span>{</span><span>new_sheet</span><span>.</span><span>url</span><span>}</span><span>"</span><span>)</span>
<span>new_sheet</span> <span>=</span> <span>client</span><span>.</span><span>create</span><span>(</span><span>"</span><span>My New Google Sheet</span><span>"</span><span>)</span>
<span>print</span><span>(</span><span>f</span><span>"</span><span> New sheet created: </span><span>{</span><span>new_sheet</span><span>.</span><span>url</span><span>}</span><span>"</span><span>)</span>
new_sheet = client.create("My New Google Sheet")
print(f" New sheet created: {new_sheet.url}")

Enter fullscreen mode Exit fullscreen mode

2. Sharing a Google Sheet

<span>new_sheet</span><span>.</span><span>share</span><span>(</span><span>"</span><span>someone@example.com</span><span>"</span><span>,</span> <span>perm_type</span><span>=</span><span>"</span><span>user</span><span>"</span><span>,</span> <span>role</span><span>=</span><span>"</span><span>writer</span><span>"</span><span>)</span>
<span>print</span><span>(</span><span>"</span><span> Sheet shared successfully!</span><span>"</span><span>)</span>
<span>new_sheet</span><span>.</span><span>share</span><span>(</span><span>"</span><span>someone@example.com</span><span>"</span><span>,</span> <span>perm_type</span><span>=</span><span>"</span><span>user</span><span>"</span><span>,</span> <span>role</span><span>=</span><span>"</span><span>writer</span><span>"</span><span>)</span>
<span>print</span><span>(</span><span>"</span><span> Sheet shared successfully!</span><span>"</span><span>)</span>
new_sheet.share("someone@example.com", perm_type="user", role="writer")
print(" Sheet shared successfully!")

Enter fullscreen mode Exit fullscreen mode

Conclusion

Integrating Google Sheets with Python can significantly improve productivity and automate data management tasks. This guide covered:
Enabling Google Sheets API
Authenticating with Python
Reading, writing, and updating Google Sheets data
Advanced features like creating and sharing sheets

Start building your own Python-powered Google Sheets automation today!

原文链接：Google Sheets Integration with Python: A Step-by-Step Guide