How to use Google Sheets as your Lightweight Database with Python (1)

If you want use Google Sheets as a primary database, here is the guidebook:

Turn Google Sheets into Your Own Database with Python

But there are two things need to be modified:

1. there is a missing code in the article:

Based on the suggestion of ChatGPT, the missing part should be:

import gspread
from oauth2client.service_account import ServiceAccountCredentials

# Define the scope of access
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]

# Path to your JSON key file (replace 'your-credentials.json' with your actual JSON key file)
credentials = ServiceAccountCredentials.from_json_keyfile_name('your-credentials.json', scope)

# Authenticate and create a client
client = gspread.authorize(credentials)

# Open the desired Google Sheet by its title or URL
# Replace 'Your Google Sheet Name' with the name of your Google Sheet
sheet = client.open('Your Google Sheet Name').sheet1  # Change 'sheet1' to the name of your specific sheet

# Now you can work with the 'sheet' object to read and update data in Google Sheets

2. the Authorization method

The method of authorization in above code is

from oauth2client.service_account import ServiceAccountCredentials

However, this is not the most recommend method by Google. We should use the following instead:

from google.oauth2.service_account import Credentials

Sample Code:

import gspread
from google.oauth2.service_account import Credentials
import pandas as pd

# Define the scope of access
scope = ["https://spreadsheets.google.com/feeds", "https://www.googleapis.com/auth/drive"]

# Initialize credentials using the JSON key file
creds = Credentials.from_service_account_file('your-credentials.json', scopes=scope)

# Create a client
gc = gspread.authorize(creds)

# Open the desired Google Sheet by its title or URL
# Replace 'Your Google Sheet Name' with the name of your Google Sheet
sheet = gc.open('Your Google Sheet Name').sheet1  # Change 'sheet1' to the name of your specific sheet

# Define a dictionary with the data you want to add
data_to_add = {
    'Column1': 'Value1',
    'Column2': 'Value2',
    'Column3': 'Value3'
}

# Convert the dictionary to a Pandas DataFrame
df = pd.DataFrame(data_to_add, index=[0])

# Append the DataFrame to the Google Sheet
sheet.append_df(df, header=False)  # Set header=True if you want to include column headers

print("Record added to Google Sheets!")

refer:

How to use Google Spreadsheet as a database using Python