Read, Write Data to Google Sheets with Python

In this tutorial, we'll read write data to Google Sheets with Python.

Google Sheets is a powerful tool for managing and automating data, especially when integrated with Python for tasks such as reading, writing, and updating data programmatically. In this tutorial, we'll walk through how to connect Python to Google Sheets using the Google Sheets API, and show you how to read data from a sheet and write data into it.

Prerequisites

• Basic knowledge of Python programming.
• A Google account to access Google Sheets.
• Python installed on your machine.
• The gspread and google-auth Python libraries installed.

Step 1: Setting Up Google Sheets API

1.1. Create a New Google Cloud Project

• Go to the Google Cloud Console.
• Create a new project by clicking the "Select a project" drop-down at the top of the page, then click "New Project."
• Give your project a name and click "Create."

1.2. Enable Google Sheets API

• Once your project is created, navigate to the Google Sheets API page.
• Click "Enable" to activate the API for your project.

1.3. Create Service Account Credentials

In the Cloud Console, go to the Credentials page.

• Click on "Create Credentials" and choose "Service Account."
• Follow the prompts and give your service account a name (e.g., "Python-Sheets-Access").
• Once created, go to the "Keys" section of your service account, and click "Add Key" > "Create new key."
• Select JSON format, and a JSON file with your service account credentials will be downloaded.

1.4. Share Your Google Sheet with the Service Account

• Open the Google Sheet you want to work with, or create a new sheet.
• Click "Share" in the top-right corner.

In the share dialog, use the email from the service account (found in the JSON file you downloaded) and give it editor permissions.

Step 2: Installing Required Python Libraries

To interact with Google Sheets, you’ll need to install the following Python libraries:

pip install gspread google-auth

• gspread: A Python API to interact with Google Sheets.
• google-auth: A library for authentication using Google APIs.

Step 3: Reading Data from Google Sheets with Python

We'll first demonstrate how to read data from a Google Sheet using the credentials created earlier.

3.1. Load the Service Account Credentials

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

import gspread
from google.oauth2.service_account import Credentials

# Define the scope of the API access
SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]

# Path to your service account key file
SERVICE_ACCOUNT_FILE = 'path/to/your/service_account_credentials.json'

# Authenticate using service account
creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)

# Connect to Google Sheets
client = gspread.authorize(creds)

3.2. Open the Google Sheet

After authenticating, open the specific Google Sheet you want to work with:

# Open the Google Sheet by its title
sheet = client.open("Your Google Sheet Title").sheet1  # Assuming you're working with the first sheet

# Alternatively, open the Google Sheet by its URL
# sheet = client.open_by_url('https://docs.google.com/spreadsheets/d/your-sheet-id/edit').sheet1

3.3. Read Data from the Sheet

Now that the sheet is open, you can read data from it. For example, to get all values from the sheet:

# Get all values from the sheet
data = sheet.get_all_values()

# Print the data
for row in data:
    print(row)

Step 4: Writing Data to Google Sheets with Python

Now let’s see how to write data to the Google Sheet.

4.1. Writing a Row of Data

To write a single row of data to your Google Sheet:

# Define a row of data to write
new_row = ["John Doe", "john@example.com", "Marketing"]

# Append the row to the sheet
sheet.append_row(new_row)

4.2. Writing Data to a Specific Cell

You can also write data to a specific cell in the Google Sheet. For example, writing data to cell A1:

# Write data to cell A1
sheet.update_acell('A1', 'Updated Value')

4.3. Batch Writing Data to Multiple Cells

If you want to update a range of cells:

# Define data for a range of cells
data_range = [
    ["Name", "Email", "Department"],
    ["Alice", "alice@example.com", "Engineering"],
    ["Bob", "bob@example.com", "HR"]
]

# Update the range in the sheet (starting from A1)
sheet.update('A1:C3', data_range)

Step 5: Automating Data Management Tasks

With the ability to read and write data to Google Sheets, you can now automate several tasks:

• Data extraction: Read data from Google Sheets and process it using Python (e.g., filtering, sorting, or performing calculations).
• Data entry: Automate the process of inserting rows of data based on input or results from another system.
• Updates: Modify specific values in a Google Sheet programmatically based on conditions or triggers in your Python code.
• Example: Simple Google Sheets Automation Script

Here’s an example script that reads data from a sheet, processes it, and writes a new row of data based on a condition:

import gspread
from google.oauth2.service_account import Credentials

# Authenticate and open the sheet
SCOPES = ["https://www.googleapis.com/auth/spreadsheets"]
SERVICE_ACCOUNT_FILE = 'path/to/your/service_account_credentials.json'
creds = Credentials.from_service_account_file(SERVICE_ACCOUNT_FILE, scopes=SCOPES)
client = gspread.authorize(creds)
sheet = client.open("Your Google Sheet Title").sheet1

# Read all values from the sheet
data = sheet.get_all_values()

# Print the header
print("Sheet Data:")
print(data[0])

# Check for a condition in the data and add a new row if condition is met
for row in data[1:]:
    name, email, department = row
    if department == "HR":
        print(f"Found HR employee: {name}")
        
        # Add a new row based on the condition
        new_employee = ["Sam", "sam@example.com", "HR"]
        sheet.append_row(new_employee)
        print("New employee added to the sheet!")

Conclusion

In this tutorial, we covered the basics of connecting Python to Google Sheets using the Google Sheets API. You learned how to authenticate with the API, read data from a Google Sheet, and write data into it. By integrating Python with Google Sheets, you can automate repetitive tasks, streamline data management processes, and create efficient workflows.

Next Steps

• Explore more advanced operations such as deleting rows, formatting cells, or handling multiple sheets.
• Implement error handling and logging for better script reliability.
• Combine Google Sheets automation with other APIs for more powerful integrations (e.g., fetching data from a database and writing it to Google Sheets).

Happy coding!

Checkout our dedicated servers and KVM VPS