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, QtThen, 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)