How to Use Google Sheets API with Python: Read, Write, Update, Delete

Published: 17 January 2025
on channel: ProgrammingKnowledge

12,067

In this detailed tutorial, learn how to use the Google Sheets API through Google Cloud Platform (GCP) with Python to perform CRUD operations (Read, Write, Update, and Delete). Follow along as we guide you through API setup, authentication, and Python code integration. Automate your Google Sheets workflows with ease!

---

Steps to Use Google Sheets API with Python

#### 1. *Set Up Google Cloud Platform (GCP)*
1. **Enable Google Sheets API**:
Go to the [GCP Console](https://console.cloud.google.com/).
Create a new project or select an existing one.
Navigate to **APIs & Services - Library**, search for **Google Sheets API**, and enable it.

2. **Create Service Account Credentials**:
Go to **APIs & Services - Credentials - Create Credentials - Service Account**.
Provide a name and assign the *Editor* role.
Download the *JSON key file* to your system.

3. **Share Your Google Sheet**:
Open your Google Sheet and share it with the service account email (found in the JSON key file).

---

#### 2. *Set Up Python Environment*
1. **Install Required Libraries**:
Run the following command in your terminal:
```bash
pip install --upgrade google-api-python-client google-auth-httplib2 google-auth-oauthlib
```

2. **Authenticate Using the JSON Key**:
Place the JSON key file in your project directory and rename it (e.g., `credentials.json`).

---

#### 3. *Python Code for CRUD Operations*

Here’s an overview of the Python code:

*1. Authenticate and Connect to the API:*
```python
from googleapiclient.discovery import build
from google.oauth2.service_account import Credentials

Load credentials
SCOPES = ['https://www.googleapis.com/auth/sprea...]
SERVICE_ACCOUNT_FILE = 'credentials.json'

credentials = Credentials.from_service_account_file(
SERVICE_ACCOUNT_FILE, scopes=SCOPES)

service = build('sheets', 'v4', credentials=credentials)

Specify the spreadsheet ID
SPREADSHEET_ID = 'your-spreadsheet-id'
```

*2. Read Data:*
```python
sheet = service.spreadsheets()
result = sheet.values().get(spreadsheetId=SPREADSHEET_ID, range="Sheet1!A1:D10").execute()
rows = result.get('values', [])
print("Data from Sheet1:", rows)
```

*3. Write Data:*
```python
data = {
'values': [['Name', 'Age'], ['John Doe', 30], ['Jane Smith', 25]]
}
sheet.values().update(
spreadsheetId=SPREADSHEET_ID,
range="Sheet1!A1",
valueInputOption="RAW",
body=data
).execute()
```

*4. Update Data:*
```python
data = {
'values': [['Updated Name']]
}
sheet.values().update(
spreadsheetId=SPREADSHEET_ID,
range="Sheet1!A2",
valueInputOption="RAW",
body=data
).execute()
```

*5. Delete Data:*
```python
sheet.values().clear(
spreadsheetId=SPREADSHEET_ID,
range="Sheet1!A1:D10"
).execute()
```

---

Tips:
Replace `"your-spreadsheet-id"` with the actual spreadsheet ID (from the URL).
Use `range` to specify the desired cells for operations.

---

Applications:
Automate data entry.
Build dashboards dynamically.
Synchronize databases with Google Sheets.

Master Google Sheets API with Python and streamline your workflow today!

#GoogleSheetsAPI #Python #GCP #CRUDOperations #GoogleCloudPlatform #Automation

On this page of the site you can watch the video online How to Use Google Sheets API with Python: Read, Write, Update, Delete with a duration of hours minute second in good quality, which was uploaded by the user ProgrammingKnowledge 17 January 2025, share the link with friends and acquaintances, this video has already been watched 12,067 times on youtube and it was liked by 98 viewers. Enjoy your viewing!

113