Skip to content

README.md

Latest commit

 

History

History
81 lines (57 loc) · 2.9 KB

README.md

File metadata and controls

81 lines (57 loc) · 2.9 KB
Raw

Co-Teach Monthly Report

A Google Apps Script project that automates the generation of monthly co-teaching student reports for NISD elementary campuses. The script pulls Cognos report data from Gmail, processes it, and produces a formatted Google Sheets workbook with per-campus student data and staffing ratio calculations.

How It Works

  1. Searches Gmail for Cognos report emails (CSV attachments) received on the trigger date
  2. Parses each tab-delimited CSV attachment and identifies the campus from the filename
  3. Filters student rows to instruction sets 40, 41, and 42, and removes unnecessary columns
  4. Writes the cleaned data to individual campus tabs in a new Google Sheet
  5. Generates an "Updated Target Ratios" summary sheet with:
    • Live =count() formulas for filtered student counts per campus
    • Co-teacher ratio calculations (1:15)
    • Instructional aide ratio calculations (1:30)
    • Raw total student counts before filtering
  6. Sends a completion email with a link to the report and a list of any missing campuses

Campuses

Boone 125, Cable 101, Forester 141, Galm 144, Helotes 104, Howsman 119, Kuentz 191, Los Reyes 236, McDermott 158

Project Structure

Code.js              Main Apps Script source (all functions)
appsscript.json      GAS project manifest (scopes, runtime)
.clasp.json          clasp deployment config
__tests__/           Jest + fast-check test suites
  coTeachReport.test.js
  updatedTargetRatiosSheet.test.js
  fixtures/          Sample CSV data for testing

Prerequisites

  • Node.js (for running tests locally)
  • clasp (for deploying to Google Apps Script)

Setup

npm install

To authenticate and push to Google Apps Script:

npx clasp login
npx clasp push

Running Tests

npm test

Tests use Jest with fast-check for property-based testing. GAS globals (GmailApp, SpreadsheetApp, etc.) are mocked in the test files.

Deployment

The project uses clasp to sync local files with the Apps Script project. Files matching patterns in .claspignore (tests, node_modules, config files) are excluded from deployment.

npx clasp push    # push local changes to Apps Script
npx clasp pull    # pull remote changes locally
npx clasp open    # open the script in the Apps Script editor

OAuth Scopes

The script requires the following Google API scopes (configured in appsscript.json):

  • gmail.readonly — read Cognos report emails and attachments
  • spreadsheets — create and write to Google Sheets
  • drive — create files in a specific Drive folder
  • script.send_mail — send the completion notification email

Entry Point

generateCoTeachReport() is the main function. It is intended to be run via a time-driven trigger in the Apps Script editor (e.g., monthly).