Related Service Counselor Email Project

Automates email notifications to district social workers when a referral form is submitted and indicates that a related service counselor is involved in the case.

Features

• Sends email notifications to designated recipients when a referral form is submitted.
• Checks the submission to determine whether a related service counselor is involved.

How It Works

1. A Google Apps Script trigger fires when a new row is added to the linked Google Sheet (via form submission).
2. The script reads the last row and checks column Y for a related service counselor's name.
3. If the value is not N/A, an email is sent to the recipients defined in Script Properties.

Files

• Code.js — Main script containing the email notification logic.
• appsscript.json — Project manifest for Google Apps Script.
• .clasp.json — Local clasp configuration (ignored by git; each developer sets their own).
• NISD_Social_Services_Referral_Form.pdf — Example of the Google Form used with this script. Use this as a reference when building your own form.

Prerequisites

• Node.js and clasp installed (npm install -g @google/clasp)
• A Google account with access to the target Apps Script project
• A Google Form and linked Sheet structured like the example in NISD_Social_Services_Referral_Form.pdf, where:
  • Column B contains the submitter's name
  • Column Y contains the related service counselor's name (or N/A)

Setup

1. Clone this repository.

2. Log in to clasp: clasp login

3. Update .clasp.json with your own scriptId (found in the Apps Script project under Project Settings).

4. Push the code: clasp push

5. In the Apps Script editor, go to Project Settings → Script Properties and add the following:

   Property	Value
   EMAIL_RECIPIENTS	Comma-separated list of recipient email addresses
   REFERRAL_FORM_LINK	Full URL to the referral Google Sheet

6. Set up a trigger for sendEmail to run On form submit: Triggers → Add Trigger → sendEmail → From spreadsheet → On form submit.

Attribution

Originally created for Northside Independent School District (NISD). Adapted for general use as an open resource. See LICENSE for details.