Previously

Google Sheets Client

First of all, we need to create a Google Sheets Client.

class GoogleSheetsClient:
    def __init__(self, credentials_file):
        self.credentials_file = credentials_file
        self.scope = ["https://spreadsheets.google.com/feeds", 'https://www.googleapis.com/auth/spreadsheets',
                      "https://www.googleapis.com/auth/drive.file", "https://www.googleapis.com/auth/drive"]
        self.creds = Credentials.from_service_account_file(self.credentials_file, scopes=self.scope)
        self.client = gspread.authorize(self.creds)

    def open_spreadsheet(self, spreadsheet_id):
        return self.client.open_by_key(spreadsheet_id)

    def append_row(self, worksheet, data):
        worksheet.append_row(data)

if __name__ == "__main__":
    app = QApplication(sys.argv)
    app_icon = QIcon("logo.png")
    app.setWindowIcon(app_icon)
    google_sheets_client = GoogleSheetsClient('starlit-granite-XXXX.json')
    window = MainWindow(google_sheets_client)
    window.show()
    sys.exit(app.exec())

Replace the “starlit-granite-XXXX.json” with your own json file.

Finance Database

The example I will use is an investment company. 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, which is similar to PyQt5 and PyQt6, and is “available under both Open Source (LGPLv3/GPLv2) and commercial license”.

First of all, import the related libraries:

from PySide6.QtWidgets import (
    QApplication, QMainWindow, QWidget, QVBoxLayout, QHBoxLayout, QListWidget,
    QLabel, QPushButton, QLineEdit, QMenu, QListWidgetItem, QScrollArea,
    QGridLayout, QGroupBox, QComboBox, QDialog, QHeaderView, QTableWidget,
    QTableWidgetItem, QMessageBox, QSizePolicy, QFileDialog, QSpacerItem
)
from PySide6.QtGui import QFont, QIcon
from PySide6.QtCore import QPoint, Qt

Then, we build the MainWindow Class. The structure is:

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("MyAPP")
        self.setGeometry(100, 100, 1000, 600)

        # Store the Google Sheets client as an attribute
        self.google_sheets_client = google_sheets_client

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

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

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

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

        # Set Central Widget
        self.setCentralWidget(central_widget)

        # Center the window on the screen
        self.center_window()

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

        # Second-level Menus
        self.second_level_menus = {
            "Investors": ["Add Investor", "Edit Investor"],
            "Accounts": ["Add Account", "Edit Account"],
            "Transactions": ["Add Transaction", "Delete Transaction"]
        }

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

        # UI Styling
        self.style_menus()

Connect the menu with the functions

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

    def center_window(self):
        # Get the primary screen
        screen = QApplication.primaryScreen()

        # Get the size of the screen
        screen_geometry = screen.geometry()

        # Calculate the position to center the window
        x = (screen_geometry.width() - self.width()) // 2
        y = (screen_geometry.height() - self.height()) // 2

        # Set the position
        self.move(x, y)

    def style_menus(self):
        # Style the top-level menu to look like buttons
        self.menu_bar.setStyleSheet()

    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

    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
            if top_level_item == "Dashboard":
                self.clear_content()
                self.content.layout().addWidget(Dashboard(google_sheets_client))
            elif top_level_item == "Reports":
                self.clear_content()
                self.display_reports_table()    
            elif top_level_item == "Balances":
                self.clear_content()
                self.display_balances_form()
            elif top_level_item == "Dividends":
                self.clear_content()
                self.display_dividents_form()
            elif top_level_item == "Settings":
                self.clear_content()
                self.display_settings_form()
            else:
                self.update_main_content(top_level_item)

        # 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)

    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 == "Investors" and second_level_item == "Edit Investor":
            self.display_edit_investor_form()
        elif top_level_item == "Accounts" and second_level_item == "Add Account":
            self.display_add_account_form()        
        elif top_level_item == "Accounts" and second_level_item == "Edit Account":
            self.display_edit_account_form()
        elif top_level_item == "Transactions" and second_level_item == "Add Transaction":
            self.display_transaction_form()
        elif top_level_item == "Transactions" and second_level_item == "Delete Transaction":
            self.display_del_transaction_form()
        # Add more conditions here for other menu items
        # ...

    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()

    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)