How to Build an Online Database APP by Python, PySide6 and Google Sheets (1): Google Sheets API

Previously

Google Sheets is a gift for those who want to build a lightweight, highly efficient, and almost free online database. Here are some guides for your reference:

How to Convert Google Sheets to a Lightweight and Self-running Database with Google Apps Script (1)

How to Convert Google Sheets to a Lightweight and Self-running Database with Google Apps Script (2)

How to Convert Google Sheets to a Lightweight and Self-running Database with Google Apps Script (3)

Build an APP with an interface

Now, you have already modified the Google Sheets to a database. It’s great. What if you want to go further: using an APP to operate the database?

The advantages are obvious:

1. You don’t need to open your eyes and look for a specific cell in the sheets.
2. You don’t need to worry about misoperation since you can add a valid-check function in the APP.
3. What you see is what you get.

Google Sheets API

The first thing in building a Python APP for Google Sheets is to get its API. Here is a detailed handbook:

Turn Google Sheets into Your Own Database with Python

But there are two things that 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 the above code is

from oauth2client.service_account import ServiceAccountCredentials

However, this is not the method that Google recommends the most. You can use the following library 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!")

Another Reference

How to use Google Spreadsheet as a database using Python

Next Step:

How to Build an Online Database APP by Python, PySide6 and Google Sheets (2)-Main Window