Automating Google Sheets with Python

 

Automating Google Sheets with Python

Google Sheets is widely used for collaborative data management, making automation essential for tasks such as data entry, reporting, and analysis. Python provides the gspread and pandas libraries to read, write, and modify Google Sheets programmatically.

---

Installing Required Libraries

To work with Google Sheets in Python, install the necessary libraries:

pip install gspread pandas

• gspread: Connects and manipulates Google Sheets.
• pandas: Handles structured data efficiently.

---

Setting Up Google Sheets API

1. Enable Google Sheets API

   • Go to Google Cloud Console.
   • Create a new project and enable "Google Sheets API".
   • Enable "Google Drive API" (for file access).

2. Create Service Account Credentials

   • In Google Cloud Console, navigate to IAM & Admin > Service Accounts.
   • Create a new service account and download the JSON key file.

3. Share Google Sheet with Service Account

   • Open your Google Sheet.
   • Click Share and grant Editor access to the email from the JSON key file.

---

Connecting to Google Sheets

Authenticating and Opening a Sheet

import gspread

# Load credentials and authorize
gc = gspread.service_account(filename="service_account.json")

# Open Google Sheet by name
sheet = gc.open("My Spreadsheet").sheet1  # Opens the first sheet

# Read cell A1
value = sheet.acell("A1").value
print(f"Value in A1: {value}")

---

Reading Data from Google Sheets

Reading a Single Cell

cell_value = sheet.acell("B2").value
print(f"Value in B2: {cell_value}")

---

Reading a Row

row_values = sheet.row_values(2)  # Get all values from row 2
print(row_values)

---

Reading a Column

column_values = sheet.col_values(1)  # Get all values from column A
print(column_values)

---

Reading an Entire Sheet into a DataFrame

import pandas as pd

# Convert sheet data into DataFrame
data = sheet.get_all_records()
df = pd.DataFrame(data)
print(df)

---

Writing Data to Google Sheets

Writing to a Single Cell

sheet.update("B2", "Updated Value")
print("Cell B2 updated successfully.")

---

Writing a Row of Data

new_row = ["Product", "Price", "Stock"]
sheet.append_row(new_row)
print("New row added.")

---

Writing a DataFrame to Google Sheets

data = {
    "Product": ["Laptop", "Phone", "Tablet"],
    "Price": [1000, 500, 300],
    "Stock": [10, 20, 15]
}

df = pd.DataFrame(data)

# Write DataFrame to Google Sheets
sheet.update([df.columns.values.tolist()] + df.values.tolist())

print("DataFrame written to Google Sheets.")

---

Modifying Google Sheets

Updating Multiple Cells

sheet.update("A2:B3", [["Updated1", "Updated2"], ["Updated3", "Updated4"]])
print("Cells updated successfully.")

---

Deleting a Row

sheet.delete_rows(2)  # Deletes row 2
print("Row 2 deleted.")

---

Downloading Google Sheets Data

Export Google Sheets to CSV

df.to_csv("google_sheet_data.csv", index=False)
print("Data exported as CSV.")

---

Export Google Sheets to Excel

df.to_excel("google_sheet_data.xlsx", index=False)
print("Data exported as Excel.")

---

Automating Google Sheets Reports

Generating a Report Based on Conditions

# Filter products with price > 500
filtered_df = df[df["Price"] > 500]

# Write filtered data to Google Sheets
filtered_sheet = gc.open("My Spreadsheet").worksheet("Filtered Data")
filtered_sheet.update([filtered_df.columns.values.tolist()] + filtered_df.values.tolist())

print("Filtered report generated.")

---

Conclusion

This section covered automating Google Sheets with Python, including reading, writing, modifying, and exporting data. These techniques are useful for real-time reporting, data synchronization, and process automation.

Would you like additional examples or modifications?