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/spreadsheets']
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