Submission Uploader

A desktop application for uploading student submissions to Google Drive and automatically linking them in your Google Sheets gradebook. This tool streamlines the process of managing assignment submissions by organizing files in Google Drive and updating spreadsheet entries with direct links.

Features

Intelligent File Matching: Match submission files by student ID or first/last name from Moodle exports
Batch Upload: Upload multiple student submission files to Google Drive
Automatic Linking: Automatically update Google Sheets with clickable hyperlinks to uploaded files
Link Conflict Prevention: Skip files that would overwrite existing submission links
Dual Interface: User-friendly PyQt5 GUI and powerful command-line interface
Smart Configuration: Persistent configuration with auto-loading and manual save functionality
One-Click Google Sign-In: Users authorize a third-party app in the browser — no Google Cloud setup
Enhanced Authentication: Visual status indicators and saved sessions via token.json
Unicode Support: Handles international characters in filenames safely
Progress Tracking: Real-time upload progress with detailed logging and emoji indicators

Prerequisites

Python 3.6 or higher (or the packaged .exe build)
Google account with access to Google Drive and Google Sheets

Quick Start

Packaged app (for most users)

Run SubmissionUploader.exe
Click Connect Google Account and approve access in your browser
Enter your Sheet ID, Drive folder ID, and submissions folder
Click Save Configuration, then Start Upload

You do not need a Google Cloud project or any credential files.

GUI Mode (from source)

Install dependencies: pip install -r requirements.txt
Run the application: python app.py
Follow the workflow:
- ✅ Configuration auto-loads from config.json (if exists)
- 🔐 Click Connect Google Account and approve access in your browser
- ⚙️ Fill/modify configuration fields as needed
- 📁 Use Select Folder next to the submissions path field
- 💾 Click Save Configuration to persist changes
- 🚀 Click Start Upload to begin processing

CLI Mode

Copy configuration: copy config.sample.json config.json
Edit config.json with your settings
Sign in: python cli_auth.py setup
Upload files: python uploader.py /path/to/submissions

Installing Dependencies

pip install -r requirements.txt

Google Sign-In (for users)

You do not need to create a Google Cloud project. This app is a third-party application — you only sign in and approve access.

Run the app (python app.py or the packaged executable).
Click Connect Google Account.
Sign in with the Google account that owns your Drive folder and spreadsheet.
On Google's consent screen, review the permissions and click Allow.
The app saves your session locally in token.json so you usually won't need to sign in again.

Permissions requested:

Access files this app creates in Google Drive
View and edit your Google Sheets spreadsheets

Note: Keep token.json private. It is your personal session file — do not share it or commit it to version control.

For developers: one-time Google Cloud setup

Only the person building or distributing the app needs a Google Cloud project. End users never see this.

Go to the Google Cloud Console and create a project.
Enable Google Drive API and Google Sheets API under APIs & Services > Library.
Configure the OAuth consent screen (app name, support email, scopes for Drive and Sheets).
- For a Google Workspace org only: set Audience to Internal so anyone in the domain can sign in.
- For personal Google accounts / multiple orgs: set Audience to External. While in Testing mode, add each user under Test users.
- For public External distribution, submit the app for Google verification.
Create an OAuth client ID with type Desktop app under Clients / Credentials.
Download the client JSON and save it as oauth_client.json in the project root (see oauth_client.sample.json).
Bundle oauth_client.json when building the executable (see Building Executables).

Direct link: Google Cloud credentials

Configuration

The application now uses a config.json file that loads into the GUI on startup with manual save functionality.

Configuration Workflow

GUI (app.py): Automatically loads settings from config.json on startup
Manual Save: Click "Save Configuration" button to save changes to config.json
CLI (uploader.py): Reads configuration from config.json at runtime
Unified Settings: Saved changes from GUI are immediately available for CLI usage

Initial Setup

Copy the sample configuration:
```
copy config.sample.json config.json
```
Edit your configuration: Open config.json and update the following fields:
- google_sheets.sheet_id: Your Google Sheet ID
- google_sheets.sheet_name: Sheet tab name (e.g., "A2", "Submissions")
- google_sheets.id_column: Column containing student IDs (e.g., "A")
- google_sheets.first_name_column: Column containing first names (e.g., "B")
- google_sheets.last_name_column: Column containing last names (e.g., "C")
- google_sheets.link_column: Column for file links (e.g., "N")
- google_sheets.start_row: Data start row (usually 2 or 3)
- google_drive.folder_id: Target Google Drive folder ID
- submissions.folder_path: Default path to submissions folder (optional)

Configuration Structure

{
  "google_sheets": {
    "sheet_id": "YOUR_SHEET_ID_HERE",
    "sheet_name": "Sheet1",
    "id_column": "A",
    "first_name_column": "B",
    "last_name_column": "C",
    "link_column": "N",
    "start_row": 3
  },
  "google_drive": {
    "folder_id": "YOUR_FOLDER_ID_HERE"
  },
  "submissions": {
    "folder_path": ""
  },
  "authentication": {
    "scopes": [
      "https://www.googleapis.com/auth/drive.file",
      "https://www.googleapis.com/auth/spreadsheets"
    ],
    "token_file": "token.json"
  },
  "output": {
    "summary_file": "upload_summary.txt"
  },
  "upload": {
    "mime_type": "application/octet-stream",
    "permissions": {
      "role": "reader",
      "type": "anyone"
    }
  }
}

Group mappings (Grouped mode)

To support course assignments where submissions are uploaded by one group member and should be recorded under a group name, the uploader can load a local CSV that maps members to group names. Add a group_mappings block to config.json pointing to your CSV and the column names to use:

  "group_mappings": {
    "file": "C:\\path\\to\\groups.csv",
    "member_first_name_column": "First Name",
    "member_last_name_column": "Last Name",
    "group_name_column": "Group Name"
  }

Notes:

The CSV must be readable from the path in file (absolute or relative to the project root).
The uploader looks for headers case-insensitively but you should provide the exact header names when possible. The expected headers are First Name, Last Name, and Group Name.
When grouped mode is enabled (the CSV loads successfully), the uploader will, for each filename, attempt the following in order:
1. Check matches.csv (pre-computed filename -> group matches)
2. Extract a student name from the filename and map the student to their group using the CSV
3. Fallback name extraction strategies (simple name parts)

If a mapped group name is not present in the sheet ID column, the uploader will create a new row and write the group name into the ID column before writing the link.

Usage

GUI Application

python app.py

Enhanced Features:

Auto-load Configuration: Settings automatically populate from config.json on startup
Integrated Folder Selection: Select submissions folder with button positioned next to the path field
Google Sign-In: Connect Google Account opens Google's consent screen — no credential file picker
Status indicators: Color-coded Connected / Not connected status in the GUI
Manual Configuration Save: Click "Save Configuration" to persist changes
Real-time Feedback: Visual confirmation of save operations and authentication status

Command Line Interface

Step 1: Authentication Setup

Use the CLI sign-in tool:

# Check if you are signed in
python cli_auth.py check

# Sign in with Google (opens browser)
python cli_auth.py setup

# Get help
python cli_auth.py help

The GUI Connect Google Account button runs the same flow and saves token.json.

Step 2: Upload Files

python uploader.py /path/to/submissions/folder

Dry-run (no Google API calls)

If you want to verify how files will be mapped before performing any uploads, use the dry-run flag. This maps filenames to groups/IDs using matches.csv and the optional group_mappings CSV, and writes a summary to upload_summary.txt without contacting Google APIs.

# Run a mapping-only dry-run and write results to upload_summary.txt
python uploader.py --dry-run

# Or provide a folder path and dry-run in one command
python uploader.py --dry-run "C:\\path\\to\\submissions"

The dry-run summary includes:

total files scanned
files that would be mapped (filename -> group/ID and match method)
files with no mapping (skipped)

Check upload_summary.txt in the project root after the dry-run completes.

Prerequisites for CLI:

config.json must exist with your settings
Google sign-in completed (python cli_auth.py setup or via the GUI)

Setting Up Your Upload

Sheet ID: The ID from your Google Sheets URL (the long string between /d/ and /edit)
Sheet Name: The name of the specific sheet tab (e.g., "Sheet1", "Gradebook")
ID Column: Column letter containing student IDs (e.g., "A", "B", "C")
Link Column: Column letter where file links will be placed (e.g., "D", "E", "F")
Start Row: Row number where student data begins (usually 2 if row 1 has headers)
Google Drive Folder ID: The ID of the destination folder (from the folder's URL)

File Organization and Matching

Supported File Formats: PDF, DOCX, and other common document formats

File Matching Strategies:

Primary: Student ID matching from Moodle export format
Secondary: First and last name matching when student ID fails

Moodle Export Naming Format:

FirstName LastName_SubmissionID_assignsubmission_file_StudentID_COMP1600_A1.pdf

Examples:

Aadam Seenath_1835025_assignsubmission_file_816050357_COMP1600_A1.pdf
Aaron Charran_1835097_assignsubmission_file_816049096_ COMP1600_A1.pdf
Billy Dee Williams_1834952_assignsubmission_file_816044707_COMP1600_A1.pdf

Legacy Format Support: The system also supports older naming conventions like:

816040296_A2.pdf
320053318_A2.pdf
400014890.A2.pdf

Smart Matching Algorithm:

Primary: Extract student ID from filename using regex patterns
Secondary: When ID matching fails, extract and match names:
- Exact first name + last name match
- First name + partial last name match
- Normalized comparison (handles hyphens, spaces, case differences)
Conflict Prevention: Skip files where target cells already contain links

Spreadsheet Requirements:

ID Column: Contains student IDs (e.g., 816050357, 320053318)
First Name Column: Contains first names for name-based matching
Last Name Column: Contains last names for name-based matching
Link Column: Target column for submission file links (skipped if already populated)

Building Executable

Prerequisites for Building

Install PyInstaller:
```
pip install pyinstaller
```
Ensure all dependencies are installed:
```
pip install -r requirements.txt
```

Building the GUI Application

Windows:

pyinstaller --onefile --windowed --add-data "oauth_client.json;." --name "SubmissionUploader" app.py

Mac/Linux:

pyinstaller --onefile --windowed --add-data "oauth_client.json:." --name "SubmissionUploader" app.py

Building the CLI Application

Windows:

pyinstaller --onefile --add-data "oauth_client.json;." --name "SubmissionUploaderCLI" uploader.py

Mac/Linux:

pyinstaller --onefile --add-data "oauth_client.json:." --name "SubmissionUploaderCLI" uploader.py

Build Options Explained

--onefile: Creates a single executable file
--windowed: Suppresses console window (GUI only)
--add-data: Bundles oauth_client.json (the app's OAuth client) in the build
--name: Sets the executable name

Advanced Build Configuration

Create a build.spec file for more control:

# -*- mode: python ; coding: utf-8 -*-

block_cipher = None

a = Analysis(
    ['app.py'],
    pathex=[],
    binaries=[],
    datas=[('oauth_client.json', '.')],
    hiddenimports=[
        'google.oauth2.credentials',
        'google_auth_oauthlib.flow',
        'googleapiclient.discovery',
        'googleapiclient.http'
    ],
    hookspath=[],
    hooksconfig={},
    runtime_hooks=[],
    excludes=[],
    win_no_prefer_redirects=False,
    win_private_assemblies=False,
    cipher=block_cipher,
    noarchive=False,
)

pyz = PYZ(a.pure, a.zipped_data, cipher=block_cipher)

exe = EXE(
    pyz,
    a.scripts,
    a.binaries,
    a.zipfiles,
    a.datas,
    [],
    name='SubmissionUploader',
    debug=False,
    bootloader_ignore_signals=False,
    strip=False,
    upx=True,
    upx_exclude=[],
    runtime_tmpdir=None,
    console=False,
    disable_windowed_traceback=False,
    argv_emulation=False,
    target_arch=None,
    codesign_identity=None,
    entitlements_file=None,
    icon='icon.ico'  # Optional: add your own icon
)

Then build with:

pyinstaller build.spec

Distribution Package

The built executable will be in the dist/ folder. For distribution:

Include required files:
- The executable
- config.json (template or pre-configured)
- Installation instructions

Create distribution folder:

SubmissionUploader_v1.0/
├── SubmissionUploader.exe (or SubmissionUploader on Mac/Linux)
├── config.json
├── README.txt
└── USER_GUIDE.txt

Build Troubleshooting

Common Issues:

Missing modules: Add to hiddenimports in spec file
File not found errors: Ensure all data files are included with --add-data
Large executable size: Consider using --exclude-module for unused packages
Slow startup: Use --onedir instead of --onefile for faster startup

Testing the Build:

Test the executable on a clean system without Python
Verify all features work correctly
Check that oauth_client.json is properly bundled
Test both GUI and Google sign-in flows

Troubleshooting

Configuration Issues

"Configuration file not found": Copy config.sample.json to config.json
UI not loading previous settings: Check that config.json is valid JSON
Settings not saving: Click "Save Configuration" button and ensure write permissions in the project directory

Sign-In Issues

❌ "App is not configured for Google sign-in":
- End users: contact whoever provided the app — the build is missing oauth_client.json
- Developers: copy oauth_client.sample.json to oauth_client.json and add your Desktop OAuth client
⚠️ "Not signed in":
- GUI: Click Connect Google Account
- CLI: Run python cli_auth.py setup
⚠️ Session expired:
- The app refreshes automatically when possible
- If refresh fails, click Connect Google Account again
❌ "Sign-in failed" / access blocked:
- If the app is in Google testing mode, your account must be listed as a test user
- Confirm you approved Drive and Sheets permissions on the consent screen
GUI status colors:
- Green: signed in
- Orange: sign-in needed
- Red: configuration or sign-in error

Upload Issues

File matching problems:
- Check that filenames follow Moodle export format or contain student IDs
- Verify first/last name columns are correctly configured for name-based matching
- Review upload log for specific matching failures
"Link already exists, skipping...":
- This is normal behavior - prevents overwriting existing submissions
- Files are automatically skipped if the target spreadsheet cell already has content
Permission errors:
- Verify Google account has edit access to the target Drive folder and Sheet
- Check that OAuth scopes include both Drive and Sheets permissions
API quota exceeded:
- Google APIs have usage limits; wait and retry if needed
- Consider smaller batch sizes for large numbers of files

UI and Configuration Issues

Configuration not loading:
- Ensure config.json exists and is valid JSON
- Check file permissions in the project directory
"Select Folder" button not working:
- Button is now positioned between the label and text field
- Selected folder automatically updates the submissions path configuration
Settings not persisting:
- Click "Save Configuration" button after making changes
- Manual save prevents accidental overwrites during form editing

CLI-Specific Issues

"Invalid JSON in configuration file": Validate your config.json syntax
Sign-in fails: Run python cli_auth.py setup or use Connect Google Account in the GUI
Upload script can't find config: Run from the same directory as config.json

Google Drive Folder ID

To find your Google Drive folder ID:

Open Google Drive in your browser
Navigate to the desired folder
Copy the folder ID from the URL: https://drive.google.com/drive/folders/FOLDER_ID_HERE

File Structure

Sub_Uploader/
├── app.py                 # Main GUI application with enhanced UI
├── uploader.py           # CLI upload script with name matching
├── cli_auth.py           # CLI authentication helper tool
├── config.json           # Configuration file (auto-loads in GUI)
├── oauth_client.json     # App OAuth client (developers only; bundled in releases)
├── oauth_client.sample.json
├── google_auth.py        # Shared Google sign-in helpers
├── token.json           # User session (auto-created after sign-in)
├── requirements.txt     # Python dependencies
├── readme.md           # Documentation (this file)
├── upload_summary.txt  # Upload report (generated after each run)
├── app.spec            # PyInstaller spec file (optional)
├── submissions/        # Example submissions folder with Moodle exports
│   ├── Aadam Seenath_1835025_assignsubmission_file_816050357_COMP1600_A1.pdf
│   ├── Aaron Charran_1835097_assignsubmission_file_816049096_ COMP1600_A1.pdf
│   └── ...
├── build/              # PyInstaller build artifacts
└── dist/               # Built executables
    ├── SubmissionUploader.exe (Windows)
    └── SubmissionUploaderCLI.exe

Key Files Explained

app.py: Enhanced GUI with improved authentication flow, smart folder selection, and visual feedback
uploader.py: CLI version with intelligent file matching (student ID + name fallback)
config.json: Unified configuration file that auto-loads in GUI and supports manual save
oauth_client.json: Desktop OAuth client for the app (maintainers add once; bundled in .exe builds)
token.json: Per-user session after Google sign-in (keep secure)
submissions/: Contains student submission files from Moodle exports

Security Notes

Keep oauth_client.json and token.json private — do not commit them to git
token.json is each user's personal session; treat it as sensitive
Maintainers: rotate the OAuth client in Google Cloud Console if it is ever exposed

Name		Name	Last commit message	Last commit date
.gitignore		.gitignore
SubmissionUploader.spec		SubmissionUploader.spec
app.py		app.py
build_gui.spec		build_gui.spec
cli_auth.py		cli_auth.py
compare_submissions.py		compare_submissions.py
config.sample.json		config.sample.json
google_auth.py		google_auth.py
groups.csv		groups.csv
oauth_client.sample.json		oauth_client.sample.json
readme.md		readme.md
requirements.txt		requirements.txt
uploader.py		uploader.py

Latest commit

History

Folders and files

Repository files navigation