Late Token Tracker

Compute remaining late tokens per student from a local file or Google Sheet, then write a Canvas-importable CSV.

Quick Read

What this script does

• Reads token requests from .xlsx, .csv, or Google Sheets URLs
• Supports public sheets (no auth), service account auth, and OAuth user sign-in
• Keeps only the latest request per student+assignment, then sums across assignments
• Writes Canvas-compatible output (default file: canvas_late_tokens.csv)

Command pattern

python late_token_tracker.py <input_file_or_sheet_url> [options]

Most common commands

# Public Google Sheet (no auth)
python late_token_tracker.py "https://docs.google.com/spreadsheets/<SPREADSHEET_ID>" --preview

# Protected Google Sheet with service account
python late_token_tracker.py "https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit" \
  --service-account ~/Downloads/service-account.json --preview

# Protected Google Sheet with OAuth
python late_token_tracker.py "https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit" \
  --oauth-client ~/Downloads/client_secrets.json --preview

# Local file
python late_token_tracker.py tokens.xlsx --preview

Key options

• --service-account PATH: service account key for protected sheets
• --oauth-client PATH: OAuth desktop client file for interactive sign-in
• --token-file PATH: OAuth token cache location (default: token.json)
• --total-tokens N: starting tokens per student (default: 4)
• --score-column NAME: Canvas score column name (default: Late Submission Tokens)
• --output PATH: output CSV path (default: canvas_late_tokens.csv)
• --grades-sheet PATH: used exported Canvas grades CSV to validate netids (default: cs380_canvas_grades_sheet.csv)
• --unmatched-report PATH: manual-review CSV for netids that do not match Canvas (default: unmatched_netids_review.csv)
• --preview: show per-student summary before writing

Table of Content

• Installation: Installation
• Full setup by auth method: Detailed Setup Guide
• Input requirements: Input Sheet Format
• More command examples: Usage Examples
• Common errors and fixes: Troubleshooting

Installation

Install Python dependencies:

pip install -r requirements.txt

---

Detailed Setup Guide

Public Sheet (No Auth Required)

If the Google Sheet is publicly accessible and is shared as "Anyone with the link":

python late_token_tracker.py "https://docs.google.com/spreadsheets/d/<SPREADSHEET_ID>/edit" --preview

---

Service Account (Recommended for Automation)

Pros: Non-interactive, secure, best for scripts and CI/CD pipelines.
Cons: Requires Google Cloud project setup.

Step 1: Create a Google Cloud Project and Service Account

1. Go to Google Cloud Console.
2. Create a new project or select an existing one (e.g., class-late-token-tracker).
3. Enable the Google Sheets API:
   • APIs & Services → Library
   • Search "Google Sheets API"
   • Click Enable
4. Create a service account:
   • IAM & Admin → Service Accounts
   • Click "Create Service Account"
   • Name: e.g., late-token-reader
   • Click Create
   • Click Done

Step 2: Create and Download JSON Key

1. In Service Accounts, click the service account you just created.
2. Go to the "Keys" tab.
3. Click "Add Key" → "Create new key" → select "JSON" → click "Create".
4. A JSON file downloads automatically. Save it locally (e.g., ~/Downloads/service-account.json).

Step 3: Share the Sheet with the Service Account

1. Extract the service account email from the JSON:

   grep '"client_email"' ~/Downloads/service-account.json

   You'll see something like: "client_email": "late-token-reader@my-project.iam.gserviceaccount.com"

2. Open your Google Sheet.

3. Click Share (top right).

4. Paste the service account email.

5. Give it Viewer access (or Editor if you plan to modify the sheet).

6. Click Share.

Step 4: Run the Script

python late_token_tracker.py "https://docs.google.com/spreadsheets/d/..." \
  --service-account ~/Downloads/service-account.json --preview

---

OAuth (User Sign-In)

Pros: Any authorized user can sign in with their own Google account.
Cons: Requires OAuth consent screen setup; interactive sign-in each time (or reuse cached token).

Step 1: Enable Google Sheets API

1. Go to Google Cloud Console.
2. APIs & Services → Library → Search "Google Sheets API" → Enable.

Step 2: Configure OAuth Consent Screen

1. APIs & Services → OAuth consent screen.
2. Choose External (or Internal if available for your organization).
3. Fill in:
   • App name: Late Token Tracker
   • User support email: your email
   • Developer contact: your email
4. Click Save & Continue.
5. On "Scopes" → Add or Remove Scopes:
   • Search for and select: https://www.googleapis.com/auth/spreadsheets.readonly
   • Click Update → Save & Continue
6. On "Test users", click Add users:
   • Add emails: student_name_year@u.northwestern.edu (or any authorized users)
   • Click Save & Continue
7. Review and click Back to Dashboard.

Step 3: Create OAuth Client ID

1. APIs & Services → Credentials.
2. Click "Create Credentials" → OAuth Client ID.
3. Choose Desktop app.
4. Name it (e.g., Late Token Tracker CLI).
5. Click Create.
6. Click Download (or the download icon).
7. Save as client_secrets.json (e.g., ~/Downloads/client_secrets.json).

Step 4: Run the Script

python late_token_tracker.py "https://docs.google.com/spreadsheets/d/..." \
  --oauth-client ~/Downloads/client_secrets.json --preview

A browser window opens; sign in with a Google account that has access to the sheet. Credentials are saved to token.json (reused on next run).

Optional: Change Token Storage Location

python late_token_tracker.py "<SHEET_URL>" \
  --oauth-client ~/Downloads/client_secrets.json \
  --token-file ~/.tokens/late-token.json --preview

---

Input Sheet Format

Your token request sheet must include:

Required columns (case-insensitive):

• Student ID: Column named netid, username, student id, or name
• Token usage: Column containing token, late, or extension
• Assignment (optional): Group requests by assignment name
• Timestamp (optional): timestamp, submitted, date, or time

Cell values:

• Token count: 1, 2.5, 1 token, 2 tokens, etc.
• Multiple rows per student/assignment: only the latest (by timestamp) is used
• Multiple assignments per student: tokens are summed together

Example sheet:

Student ID	Assignment	Tokens	Timestamp
alice.smith	Essay 1	1	2025-01-15
alice.smith	Essay 1	2	2025-01-20
bob.jones	Project A	1 token	2025-01-18

Output: Alice gets 2 tokens (latest for Essay 1), Bob gets 1 token.

---

Usage Examples

Display Help

python late_token_tracker.py --help

Preview Results Before Writing

python late_token_tracker.py "https://docs.google.com/spreadsheets/d/<ID>/edit" --preview

Custom Token Count (default: 4)

python late_token_tracker.py tokens.xlsx --total-tokens 10 --preview

Custom Score Column Name

python late_token_tracker.py tokens.xlsx \
  --score-column "Late Tokens Available" --preview

Custom Output File

python late_token_tracker.py tokens.xlsx --output my_grades.csv

Validate Netids Against the Canvas Export

python late_token_tracker.py tokens.xlsx \
  --grades-sheet cs380_canvas_grades_sheet.csv \
  --unmatched-report unmatched_netids_review.csv --preview

This checks the extracted netid against the Canvas export's SIS Login ID column using the raw value from the token sheet. Any netid that does not match is written to the manual-review report with its occurrence count and source rows so a grader can debug it before importing into Canvas.

Write to Canvas (without preview)

python late_token_tracker.py tokens.xlsx --output canvas_late_tokens.csv
# Then import canvas_late_tokens.csv into Canvas

---

Troubleshooting

Error	Cause	Solution
Service account key file not found	Wrong path to JSON	Use full path: ~/Downloads/service-account.json or /Users/you/Downloads/...
OAuth client_secrets file not found	Wrong path to client_secrets.json	Same as above; verify path with ls -la
Access Denied	Service account/OAuth user not shared the sheet	Share the sheet with the service account email or OAuth user email
Sheet is empty	No data in the sheet	Check the sheet has headers and data rows
No student ID column found	Column name mismatch	Ensure column name contains student id, netid, username, or name
No token usage columns found	Column name mismatch	Ensure column name contains token, late, or extension

---

Best Practices

1. Test with --preview first to see results before writing.
2. Use service accounts for automation (scripts, scheduled jobs).
3. Use OAuth only for interactive user access where users sign in themselves.
4. Keep credentials private: add *.json and token.json to .gitignore.
5. Reuse OAuth tokens: once signed in, token.json is cached and reused (no re-login on next run).
6. Group by latest assignment: if a student requests multiple times for the same assignment, only the latest request is counted.

---

Output

File: canvas_late_tokens.csv (or custom with --output)
Format: Canvas-compatible gradebook import

SIS Login ID	Late Submission Tokens
alice.smith	3
bob.jones	4