Google Sheets Automation with gspread
This guide demonstrates how to read and write data to Google Sheets using the gspread library. It uses a Service Account for server-to-server authentication, which is ideal for bots and automation scripts.
Modules Used:
gspread: A Python API for Google Sheets.google-auth: For handling authentication.- argparse: To handle command-line arguments.
Prerequisites
- Google Cloud Console: Create a new project.
- Enable APIs: Enable Google Sheets API and Google Drive API.
- Service Account:
- Go to "Credentials" -> "Create Credentials" -> "Service Account".
- Name it (e.g., "sheets-bot").
- Go to the "Keys" tab of the new service account -> "Add Key" -> "Create new key" -> "JSON".
- Download the file and rename it to
service_account.json.
- Share the Sheet:
- Open the Google Sheet you want to automate.
- Click "Share".
- Paste the client_email found inside your
service_account.jsonfile. - Give it "Editor" access.
Installation
pip install gspreadThe Code
Save this as sheets_bot.py.
import gspread
import argparse
def interact_with_sheet(filename, action, row_data=None):
# 1. Authenticate using the Service Account
# gspread looks for 'service_account.json' in the current directory by default
try:
gc = gspread.service_account(filename='service_account.json')
except Exception as e:
print(f"Authentication Error: {e}")
print("Ensure 'service_account.json' is in the current directory.")
return
try:
# 2. Open the Spreadsheet
# You can open by title, url, or key. Title is easiest if unique.
sh = gc.open(filename)
# Select the first worksheet (tab)
worksheet = sh.sheet1
if action == "read":
print(f"Reading data from '{filename}'...")
# Get all records as a list of dictionaries
records = worksheet.get_all_records()
for i, record in enumerate(records, start=2): # Row 1 is header
print(f"Row {i}: {record}")
elif action == "add":
if row_data:
print(f"Adding row to '{filename}'...")
worksheet.append_row(row_data)
print("Row added successfully.")
else:
print("Error: No data provided to add.")
elif action == "update":
# Example: Update cell B2
print(f"Updating cell B2 in '{filename}'...")
worksheet.update_acell('B2', 'Updated via Python')
print("Cell updated.")
except gspread.exceptions.SpreadsheetNotFound:
print(f"Error: Spreadsheet '{filename}' not found. Did you share it with the service account email?")
except Exception as e:
print(f"Error: {e}")
if __name__ == "__main__":
parser = argparse.ArgumentParser(description="Google Sheets Automation")
parser.add_argument("filename", help="Name of the Google Sheet")
parser.add_argument("action", choices=['read', 'add', 'update'], help="Action to perform")
parser.add_argument("--data", nargs='+', help="Data to add (for 'add' action)")
args = parser.parse_args()
interact_with_sheet(args.filename, args.action, args.data)Usage
-
Read Data:
python sheets_bot.py "My Spreadsheet" read -
Add a Row:
python sheets_bot.py "My Spreadsheet" add --data "John Doe" "john@example.com" "Active" -
Update a Cell (Hardcoded B2):
python sheets_bot.py "My Spreadsheet" update