Step-by-Step Guide: How to Update Google Sheets Using Python

 

✅ Step 1: Set up a Google Cloud project

1️⃣ Go to Google Cloud Console.
2️⃣ Create a new project (or select an existing one).
3️⃣ Go to APIs & Services → Library.
4️⃣ Search for Google Sheets API → Enable it.
5️⃣ Search for Google Drive API → Enable it.

---

✅ Step 2: Create service account credentials

1️⃣ Go to APIs & Services → Credentials.
2️⃣ Click Create Credentials → Service Account.
3️⃣ Name the service account (e.g., my-sheet-updater).
4️⃣ After creating, under the Keys section, click Add Key → Create new key → JSON.
✅ This downloads a .json file — save this securely (you will use it in your Python script).

---

✅ Step 3: Share your Google Sheet

1️⃣ Open your Google Sheet in your browser.
2️⃣ Click Share (top right).
3️⃣ In the Add people box, paste the client_email from your JSON file (look inside the file for "client_email": "...").
4️⃣ Give it Editor permission.
✅ This lets the script access the sheet.

---

✅ Step 4: Install Python libraries

Open your terminal or command prompt and install the needed packages:

pip install gspread oauth2client

✅ Step 5: Write the Python script

Here’s a minimal working example:

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27

import gspread from oauth2client.service_account import ServiceAccountCredentials # Set the path to your downloaded JSON key file json_path = 'C:/project/your-service-account-file.json' # Define the API scopes scope = ['https://spreadsheets.google.com/feeds', 'https://www.googleapis.com/auth/drive'] # Authenticate using the JSON key creds = ServiceAccountCredentials.from_json_keyfile_name(json_path, scope) client = gspread.authorize(creds) # Open your Google Sheet by name sheet = client.open('Test_Data').sheet1 # or .worksheet('Sheet1') if multiple tabs # Read data (for testing) data = sheet.get_all_records() print("Current Data:", data) # Example: Update cell B2 sheet.update('B2', 'Updated by Python!') # Example: Update a row (second row, full values) sheet.update('2', ['Company Name', 'LinkedIn URL', 'Website', 'Name', 'Title', 'Email', 'Address', 'Suite', 'City', 'State', 'Zip']) print("Update complete!")

✅ Step 6: Run the script

Just run:

python your_script.py

 ✅ If it connects correctly, it will update your Google Sheet.

⚠ Common issues & fixes

• 403 error / no access → Make sure you shared the sheet with the service account email.

• Spreadsheet not found → Check that the sheet name in the code exactly matches (including capitalization).

• JSON file issues → Ensure you’re using the service account credentials, not OAuth or other types.