Connect Google Sheet with python project using Service-Account

 Connecting Google Sheets with Python Using a Service Account 🚀

Google Sheets is a powerful tool for managing data, and integrating it with Python can automate workflows, update spreadsheets, and extract insights seamlessly. In this tutorial, we'll learn how to connect Google Sheets with a Python project using a Service Account, which allows secure and automated access to Google Sheets without manual authentication.

📌 Why Use a Service Account?

A Service Account is a special type of Google account used for server-to-server authentication. Unlike OAuth authentication, which requires user consent, a service account enables applications to access Google Sheets directly without human intervention. This is perfect for:

✅ Automated data entry & updates
✅ Backend applications managing spreadsheets
✅ Data extraction & analysis tasks

📌 Step 1: Enable Google Sheets API

Go to Google Cloud Console:

👉 https://console.cloud.google.com/

Click Select a project (or create a new one).

In the Navigation Menu (☰ on the top left), go to APIs & Services > Library.

Search for Google Sheets API and click Enable.

Search for Google Drive API and click Enable.

📌 Step 2: Create a Service Account

In the Navigation Menu, go to APIs & Services > Credentials.

Click Create Credentials → Select Service Account.

Fill in the Service Account Name and Description.

Click Create & Continue.

In the Grant this service account access section, select Editor (or a required role).

Click Done.

📌 Step 3: Generate the JSON Key

Go to APIs & Services > Credentials.

Find your service account under the Service Accounts section.

Click on the service account name.

Go to the Keys tab.

Click Add Key → Create new key.

Select JSON and click Create.

A JSON file will be downloaded automatically (e.g., your-service-account.json).

📌 Step 4: Use the JSON Key in Your Script

Move the downloaded your-service-account.json file to your project folder.

script

import gspread

from oauth2client.service_account import ServiceAccountCredentials

# Define the scope

scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]

# Load credentials from the JSON key file (download from Google Cloud Console)

creds = ServiceAccountCredentials.from_json_keyfile_name("your-service-account.json", scope)

# Authorize the client

client = gspread.authorize(creds)

# Open the Google Sheet by name

spreadsheet = client.open("scrap_sheet")

sheet = spreadsheet.sheet1  # This selects the first Worksheet

headers = ["Price", "Color", "Title", "Description", "Variants"]

# Update the first row with column headers

sheet.insert_row(headers, index=1)

# Add sample data in the second row (Modify as needed)

sample_data = ["$20", "Red", "T-Shirt", "Comfortable cotton T-shirt", "S, M, L, XL"]

sheet.insert_row(sample_data, index=2)

print("Columns added and sample data inserted successfully!")