How to use Python, PySide6 and Google Sheets to make a Database App (2)

Let’s begin with the end in mind. We will use an APP to operate Google Sheets and make it a lightweight database.

The example I am going to use is an investment company which manages many investors. Each investor can invest or withdraw at any time and can have more than one account. After their investment, we need to calculate the dividends.

Let’s Build the Framework of the APP first. The library we will use to build the APP is PySide6, similar as PyQt5 and PyQt6.

First of all, import the related libraries:

import sys
import gspread
from google.oauth2.service_account import Credentials
from PySide6.QtWidgets import QApplication, QMainWindow, QWidget, QHBoxLayout, QVBoxLayout, QListWidget, QLabel, QPushButton, QLineEdit, QMenu, QListWidgetItem, QScrollArea, QGridLayout, QGroupBox, QComboBox
from PySide6.QtGui import QFont
from PySide6.QtCore import QPoint, Qt

Then, we build the MainWindow Class. There are 3 parts in this Class

1. Initialization (__init__)

2. Methods

3. Main Execution (if __name__ == “__main__”)

1. Initialization (__init__)

• Inherits from QMainWindow.
• Sets up the main window settings (title, geometry).
• Initializes the central widget and main layout.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):
        super().__init__()

        # Main Window Settings
        self.setWindowTitle("My Application")
        self.setGeometry(100, 100, 1000, 600)

        # Central Widget and Layout
        central_widget = QWidget()
        self.main_layout = QHBoxLayout(central_widget)

• Creates a vertical menu bar with items like “Dashboard”, “Investors”, etc.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...

        # Vertical Menu Bar (Top-level)
        self.menu_bar = QListWidget()
        self.menu_bar.addItems(["Dashboard", "Investors", "Accounts", "Transactions", "Dividends", "Reports", "Search"])
        self.menu_bar.setFixedWidth(150)

• Sets up the main content area as a QLabel with a QVBoxLayout.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...

        # Main Content Area (Create it here)
        self.content = QLabel("Main Content Area", alignment=Qt.AlignCenter)
        self.content.setLayout(QVBoxLayout())  # Create a QVBoxLayout for the content area

• Assembles the main layout by adding the menu bar and content area.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...

        # Assemble Main Layout
        self.main_layout.addWidget(self.menu_bar)
        self.main_layout.addWidget(self.content)

        # Set Central Widget
        self.setCentralWidget(central_widget)

• Connects the itemClicked signal of the menu bar to the show_secondary_menu method.
• Defines second-level menus for each top-level menu item.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...

        # Connect Signals
        self.menu_bar.itemClicked.connect(self.show_secondary_menu)

        # Second-level Menus
        self.second_level_menus = {
            "Investors": ["Add Investor", "Delete Investor", "Edit Investor"],
            "Accounts": ["Add Account", "Edit Account", "Delete Account"],
            "Transactions": ["Investment", "Withdraw"],
            "Dividends": ["Monthly", "Quarterly", "Annual"],
            "Reports": ["Quarterly", "Annual"],
            "Search": []
        }

• Sets the default selection to “Dashboard” and styles the menus.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...

        # Set 'Dashboard' as the default selected item and trigger click
        self.set_default_selection()

        # UI Styling
        self.style_menus()

2. Methods

• style_menus: Applies custom styling to the menu items.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...

    def style_menus(self):
        # Style the top-level menu to look like buttons
        self.menu_bar.setStyleSheet("""
            QListWidget::item {
                border: 1px solid #dcdcdc;
                padding: 5px;
                background-color: #f0f0f0;
            }
            QListWidget::item:selected {
                background-color: #a0a0a0;
            }
        """)

• set_default_selection: Finds and selects the "Dashboard" item by default.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...

    def set_default_selection(self):
        # Find the 'Dashboard' item and set it as the current item
        for i in range(self.menu_bar.count()):
            if self.menu_bar.item(i).text() == "Dashboard":
                self.menu_bar.setCurrentItem(self.menu_bar.item(i))
                self.show_secondary_menu(self.menu_bar.item(i))
                break

• show_secondary_menu: Displays a second-level menu when a top-level item is clicked. If no second-level items are available, it directly updates the main content.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...


    def show_secondary_menu(self, item: QListWidgetItem):
        if not item:
            return

        # Identify the selected top-level menu item
        top_level_item = self.menu_bar.currentItem().text()

        # Check if there are second-level menu items
        second_level_items = self.second_level_menus.get(top_level_item, [])

        if not second_level_items:
            # If no second-level menu items, update main content directly
            self.update_main_content(top_level_item)
            return

        # Prepare the second-level menu
        second_level_menu = QMenu(self)

        # Add items to the second-level menu and connect them
        for item_text in second_level_items:
            action = second_level_menu.addAction(item_text)
            action.triggered.connect(lambda checked=False, text=item_text: self.handle_secondary_action(top_level_item, text))

        # Show the menu to the right of the top-level menu item
        item_rect = self.menu_bar.visualItemRect(item)
        pos = self.menu_bar.mapToGlobal(QPoint(self.menu_bar.width(), item_rect.top()))
        second_level_menu.exec(pos)

• handle_secondary_action: Responds to second-level menu item clicks, showing forms like AddInvestorForm or AddAccountForm.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...

    def handle_secondary_action(self, top_level_item, second_level_item):
        # Handle the action based on the top-level and second-level item
        if top_level_item == "Investors" and second_level_item == "Add Investor":
            self.display_add_investor_form()
        elif top_level_item == "Accounts" and second_level_item == "Add Account":
            self.display_add_account_form()        
        # Add more conditions here for other menu items
        # ...

• clear_content: Clears the current widgets in the main content area.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...

    def clear_content(self):
        # Remove existing widgets in the content area
        while self.content.layout().count():
            widget = self.content.layout().takeAt(0).widget()
            if widget is not None:
                widget.deleteLater()

• update_main_content: Updates the main content area with the text or forms based on user actions.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...
    def update_main_content(self, text):
        # Update the main content area with the text of the clicked second-level menu item
        self.content.setText(text)

• display_add_investor_form and display_add_account_form: Clear the content area and display the respective forms.

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...

    def display_add_investor_form(self):
        # Clear existing content
        self.clear_content()

        # Set the main content label's text for Add Investor
        self.update_main_content("")

        # Display the AddInvestorForm
        add_investor_form = AddInvestorForm(google_sheets_client)
        self.content.layout().addWidget(add_investor_form)

class MainWindow(QMainWindow):
    def __init__(self, google_sheets_client):

        # ...

    def display_add_account_form(self):
        # Clear existing content
        self.clear_content()

        # Display the AddAccountForm
        add_account_form = AddAccountForm(google_sheets_client)
        self.content.layout().addWidget(add_account_form)

Main Execution (if __name__ == "__main__")

• Creates a QApplication instance.
• Initializes a GoogleSheetsClient with the provided JSON configuration for Google Sheets API access.
• Creates an instance of MainWindow and shows it.
• Starts the event loop of the application.

if __name__ == "__main__":
    app = QApplication(sys.argv)
    google_sheets_client = GoogleSheetsClient('starlit-granite-408019-c7be633025c8.json')
    window = MainWindow(google_sheets_client)
    window.show()
    sys.exit(app.exec())