My Apps Script Runs Without Errors Except for One Issue

[Robert Mcleod]

Dear Google Apps Script Community:

My name is Robert, and I am an intermediate school math teacher. I desperately need someone's assistance within the Google Apps Scripts community to prevent me from losing my mind. I want to create a Google Apps Script that will automatically send a message to my email account when the suspension period expiration date(Column F) has arrived for a student(Column B) starting with Row 2.

Column A is the incident date. Column C is the negative behavior, Column D is the negative consequence. Column E is the number of suspension days added to Column A in order to produce the suspension period expiration date. I want the date to have the following example format: Thu, Mar 05, 2026. The trigger settings are: 1) sendExpirationEmails; 2) Head; Time-driven; Day timer; 6am to 7am; and Notify me daily. I manually tested the script, and it runs without errors. However, it doesn’t send me the email message/notification I am expecting. I checked spam and junk mail folders as well with no success. With the help of Google's A.I. search results., and studying YouTube videos, I am able to provide you with the following code:

function sendExpirationEmails() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const startRow = 2; // Start processing from row 2
  const emailRecipient = "robert...@ssdedu.org"; // Fixed email recipient

  // Fetch all data from the active sheet starting from row 2
  const lastRow = sheet.getLastRow();
  // Columns B (index 1) and F (index 5)
  const dataRange = sheet.getRange(startRow, 2, lastRow - startRow + 1, 5); // Get columns B through F
  const data = dataRange.getValues();

  // Get today's date and normalize it to the start of the day for accurate comparison
  const today = new Date();
  const todayNormalized = new Date(today.getFullYear(), today.getMonth(), today.getDate());

  for (let i = 0; i < data.length; ++i) {
    const row = data[i];
    const studentName = row[0]; // Column B is the first element in the fetched range
    const expirationDate = row[4]; // Column F is the fifth element

    // Check if the expirationDate is valid and matches today's date
    if (expirationDate instanceof Date) {
      const expirationDateNormalized = new Date(expirationDate.getFullYear(), expirationDate.getMonth(), expirationDate.getDate());

      if (expirationDateNormalized.valueOf() === todayNormalized.valueOf()) {
        const subject = `${studentName} has reached the end of the behavior suspension period.`;
        const body = `Hi Robert,\n\nThis is an automated notification. The behavior suspension period for student ${studentName} has ended today.\n\nRegards,\nAutomated Script`;

        // Send the email
        MailApp.sendEmail(emailRecipient, subject, body);
        Logger.log(`Email sent for ${studentName}`);
      }
    }
  }
}

Sincerely,
Robert

[Mario Rossi]

Hi Robert — thanks for the clear description. Qui sotto trovi i problemi più probabili e una versione corretta e robusta dello script con istruzioni sintetiche.

Problemi frequenti

• getActiveSheet() può restituire un foglio diverso quando lo script è eseguito da trigger: usa getActiveSpreadsheet().getSheetByName("NomeFoglio").
• Il trigger time-driven esegue lo script con l'account e le autorizzazioni attive; assicurati che lo script abbia autorizzato MailApp (controlla Cronologia esecuzioni / Log).
• La colonna F potrebbe contenere testo o risultati di formula non convertiti in Date: verifica instanceof Date oppure interpreta la stringa.
• lastRow < startRow causa errori o range vuoto: aggiungi controllo.
• Per confronti affidabili confronta solo la data (anno/mese/giorno) o usa Utilities.formatDate.

Script proposto (adattalo sostituendo "NomeFoglio" con il nome del foglio reale):

function sendExpirationEmails() {
const ss = SpreadsheetApp.getActiveSpreadsheet();
const sheet = ss.getSheetByName("NomeFoglio"); // <-- modifica qui
if (!sheet) return;

const startRow = 2;
const emailRecipient = "robert...@ssdedu.org";
const lastRow = sheet.getLastRow();
if (lastRow < startRow) return;

// Legge colonne B..F (5 colonne)

const dataRange = sheet.getRange(startRow, 2, lastRow - startRow + 1, 5);

const data = dataRange.getValues();

const tz = Session.getScriptTimeZone();

const today = new Date();

const todayKey = Utilities.formatDate(today, tz, "yyyy-MM-dd");

for (let i = 0; i < data.length; i++) {

const row = data[i];

const studentName = row; // Colonna B
const expirationCell = row; // Colonna F

let expDate = null;
if (expirationCell instanceof Date) {
  expDate = expirationCell;
} else if (expirationCell && typeof expirationCell === "string") {
  // prova a parsare stringa ISO o formati comuni
  const parsed = new Date(expirationCell);
  if (!isNaN(parsed)) expDate = parsed;
}

if (!expDate) continue;

const expKey = Utilities.formatDate(expDate, tz, "yyyy-MM-dd");
if (expKey === todayKey) {
  const subject = `${studentName} has reached the end of the behavior suspension period.`;
  const formattedDate = Utilities.formatDate(expDate, tz, "EEE, MMM dd, yyyy");
  const body = `Hi Robert,\n\nThis is an automated notification. The behavior suspension period for student ${studentName} ends today (${formattedDate}).\n\nRegards,\nAutomated Script`;

  MailApp.sendEmail(emailRecipient, subject, body);
  Logger.log(`Email sent for ${studentName} (${formattedDate})`);
}

}
}

Controlli/diagnosi rapida

• Esegui lo script manualmente e verifica i log (View > Executions / Stackdriver) per vedere se entra nel ramo di invio.
• Verifica Time zone del progetto: File > Project properties > Time zone (deve corrispondere al formato che usi).
• Controlla che il trigger "sendExpirationEmails" sia registrato per il progetto corretto e che l'account abbia autorizzato MailApp.

Se vuoi, posso:

• adattare lo script al nome del tuo foglio,
• aggiungere un campo per evitare invii ripetuti (es. una colonna "Notified" che segna inviato),
• o fornire esempi per il parsing di formati di data specifici che hai nel foglio.

Dimmi quale opzione preferisci.

[Robert Mcleod]

Hi...I provided you with a link to view my spreadsheet. I'm thinking you can re-create the spreadsheet, and run your proposed script to see whether or not it works for you. If it works, you can simply send it back to me. My Google Apps Script knowledge is amateur-level compared to yours. Therefore, I don't know any more specific information I want or need besides the script automatically sending me the email message. By the way, I believe your native language is Italian.Therefore, some of the Italian within the script didn't convert to English.

Robert

Bathroom, Hallway and Classroom Behavior Tracker

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.
To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CACrgDdoS-gDne5DHsGgGgrtYY1ctytVYRgBmyMp78MnnVU5kfQ%40mail.gmail.com.

[Mario Rossi]

Hi Robert — grazie per il link. Posso adattare, testare e restituire lo script; prima però ho bisogno di due conferme rapide:

1. Permessi: puoi concedere accesso di visualizzazione/modifica al file (o fare una copia e condividerla)? Se preferisci non condividere, indicami il nome esatto del foglio e conferma le colonne: colonna B = nome studente, colonna F = data di fine (inizio dati riga 2).
2. Destinatario e comportamento: confermi che l'email va a robert...@ssdedu.org? Vuoi evitare invii ripetuti usando una colonna "Notified" che segna gli studenti già notificati?

Posso inoltre convertire i testi dall'italiano all'inglese nello script e aggiungere log e controllo fusi orari. Dimmi come procedere e faccio tutto; ti invio lo script funzionante e istruzioni per installare il trigger.

[Bittu Kumar]

What 

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CACrgDdpxbu2vtG%3DgHbgOXXDXhwCo7jsZTOx6eCh%3DC9Bbvtx3sA%40mail.gmail.com.

[Mario Rossi]

Hi Bittu — thanks for writing. Do you mean you need clarification on my two quick questions?

1. Share access: do you prefer to grant view/edit access to the file (or share a copy), or should I work from the sheet name/column mapping you confirmed (B = student name, F = end date, data from row 2)?
2. Recipient/behavior: should emails go to robert...@ssdedu.org, and do you want a "Notified" column to avoid repeated sends?

Tell me which option you prefer (or any other requirement) and I’ll adapt, test and return the script with instructions.

[Robert Mcleod]

Ok...I have decided to grant you access to edit my script with the attached link.

1. Share access: do you prefer to grant view/edit access to the file (or share a copy), or should I work from the sheet name/column mapping you confirmed (B = student name, F = end date, data from row 2)? Ok...I have decided to grant you access to edit my script with the attached link.
2. Recipient/behavior: should emails go to robert...@ssdedu.org, and do you want a "Notified" column to avoid repeated sends? Yes...emails should go to robert...@ssdedu.org, and I think adding a "Notified" column to avoid repeated sends is a great idea. My preference for the "Notified" column is Column G.

My Apps Script with Shared Edit Access

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CACrgDdr0Pb7Vydz6zEG%3D-4D_2K1%2BeExGx1UKDTJtbzZ9pqJDPw%40mail.gmail.com.

[Mario Rossi]

Hi Robert — thanks, I’ve received the edit link and can work directly in the spreadsheet.

I’ll:

• Use Column B = student name, Column F = end date, starting from row 2.
• Send emails to robert...@ssdedu.org.
• Add a “Notified” column in Column G to avoid repeated sends.

I’ll implement, test the script, and post the updated code and brief instructions in the sheet (or here) when done. Any other preferences (email subject/body template or time to run) before I proceed?

[Emerson Maia]

The reason the emails are not being sent is that the original script is trying to read the entire row (row) instead of the specific columns (row[0] and row[4]). In addition, the email address contained invalid characters.

Here is your code rewritten using Object-Oriented Programming and modern JavaScript (V8) methods. It fixes the indexing issues and formats the date exactly as requested.

class SuspensionManager {

  constructor(sheetName, recipientEmail) {
    this.sheetName = sheetName;
    this.recipientEmail = recipientEmail;
    this.tz = Session.getScriptTimeZone();
    this.todayKey = Utilities.formatDate(new Date(), this.tz, "yyyy-MM-dd");
  }

  getSheet() {
    return SpreadsheetApp.getActiveSpreadsheet().getSheetByName(this.sheetName);
  }

  formatCustomDate(date) {
    // Formata para o padrão: Qui, 05 de mar de 2026
    const formatter = new Intl.DateTimeFormat('pt-BR', {
      weekday: 'short', day: '2-digit', month: 'short', year: 'numeric'
    });
    const parts = formatter.format(date).replace(/\./g, '');
    return parts.charAt(0).toUpperCase() + parts.slice(1);
  }

  notifyExpiringSuspensions() {
    const sheet = this.getSheet();
    if (!sheet) return;

    const lastRow = sheet.getLastRow();
    if (lastRow < 2) return;

    // Lê os dados da coluna B (2) até a F (6) -> 5 colunas no total
    const data = sheet.getRange(2, 2, lastRow - 1, 5).getValues();

    const expiringToday = data.map(row => ({
      studentName: row[0],   // Coluna B
      expirationDate: row[4] // Coluna F
    })).filter(record => {
      if (!(record.expirationDate instanceof Date)) return false;
      const expKey = Utilities.formatDate(record.expirationDate, this.tz, "yyyy-MM-dd");
      return expKey === this.todayKey;
    });

    expiringToday.forEach(record => {
      const formattedDate = this.formatCustomDate(record.expirationDate);
      const subject = `${record.studentName} chegou ao fim do período de suspensão.`;
      const body = `Olá Robert,\n\nEsta é uma notificação automática. O período de suspensão do aluno ${record.studentName} termina hoje (${formattedDate}).\n\nAtenciosamente,\nScript Automático`;

      MailApp.sendEmail(this.recipientEmail, subject, body);
    });
  }
}

function sendExpirationEmails() {
  const sheetName = "NomeFoglio"; // Altere para o nome da sua aba
  const emailRecipient = "robert...@ssdedu.org"; // E-mail limpo
 
  const manager = new SuspensionManager(sheetName, emailRecipient);
  manager.notifyExpiringSuspensions();
}

	Documentos de especialistas Emerson Maia	Goiânia Brasil
		+55 62 991911266

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CACrgDdrzagaZjEGVd%3DkVDWDmFTjPb3JLcPXE00kB9G7RKPt5Bw%40mail.gmail.com.

[Mario Rossi]

Hi Emerson — thanks, that’s very helpful.

I appreciate the OO rewrite and the fixes (indexing, date formatting, and cleaned email). I’ll:

• Update the sheet name and deploy your script.
• Add a “Notified” column (Column G) to prevent duplicate sends.
• Test with a few rows and confirm results.

Quick question: do you have a preferred time/triggers for the daily run (time-based trigger) or should I schedule it to run once a day at 8:00 AM local script time?

Thanks again — I’ll report back after testing.

[Robert Mcleod]

Hi Emerson...I don't know whether or not you tested the script before you responded to my post, but I simply want to let you know I still did not receive a message sent to my email address.

Robert

[Mario Rossi]

Hi Robert,

thanks — sorry it’s still not arriving. Quick checklist to identify the issue:

1. Verify the script actually ran successfully: open Apps Script > Executions (or View > Execution log) and confirm there’s a successful run at the expected time. Share any error messages if present.
2. Check the script’s send call and recipient: confirm the exact email address passed to MailApp.sendEmail / GmailApp.sendEmail and that it’s not empty or overwritten.
3. Check authorization & scopes: if the script requires Gmail permissions, ensure you (or the script owner) have authorized them.
4. Check spam/filters and “Sent” folder: see if the message landed in spam or the account’s Sent items (if using GmailApp).
5. Check quotas: confirm remaining quota with MailApp.getRemainingDailyQuota() — if quota is exhausted, sends will be suppressed.
6. Test with a minimal send: run a simple test function like
   function testSend(){ MailApp.sendEmail('your.a...@example.com','Test','Body'); }
   and confirm it arrives.
7. Add logging: wrap send in try/catch and Logger.log() or console.log the recipient and result so you can trace execution.

If you want, paste the sendEmail code snippet and any execution log entries here and I’ll review them.

[Keith Andersen]

A simple onEdit script will not send emails.

Only an installed onEdit trigger will.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CACrgDdqy2qEweG6ggkdKy%3DGFZPbmHhaZ%2BUfjMH%3Dx1tM16JCQ%3DQ%40mail.gmail.com.

[Mario Rossi]

Thanks Keith — useful clarification.

I’ll switch to an installed onEdit trigger and test again. Quick question: any tips to avoid common pitfalls (scopes/authorization differences, checking event object properties, or whether the trigger must be created by the sheet owner to allow sending)? If it still fails I’ll post the execution log and code snippet.

Thanks again.

[George Ghanem]

That is correct. All simple triggers like onEdit, onOpen, onChange.. can not call any facility outside of the document container itself.

 You can change the name of the function and then define it as a manual trigger and authorize it and it will then run and perform those actions.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAFKgK%2BE_qDKSn5SNRN7_h-7gvsNeyzbFh6e_4NGdzDUr6L%3DKNA%40mail.gmail.com.

[Mario Rossi]

Thanks George — clear and helpful. I’ll rename the function, create an installed trigger, authorize it, and run the tests you suggested. I’ll report back with results or any errors. If you have a recommended guide or exact steps for creating the installed trigger, please share.

[George Ghanem]

you should be able to define the manual trigger from the Apps-Script editor window. On left hand side, choose triggers, then from that window, click on bottom right the add trigger button to create the trigger as you see in attached screenshot.

On 2026-03-12 16:18, Mario Rossi wrote:

Thanks George — clear and helpful. I’ll rename the function, create an installed trigger, authorize it, and run the tests you suggested. I’ll report back with results or any errors. If you have a recommended guide or exact steps for creating the installed trigger, please share.

--
You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CACrgDdqYW_o8DTEYNohGT6KFcgtbbQs4Q68M9u77shL--u3Ptw%40mail.gmail.com.

[Mario Rossi]

Thanks George — chiaro e utile. Procederò a rinominare la funzione, creare il trigger installato (Editor > Triggers > Add Trigger come nello screenshot), autorizzarlo ed eseguire i test; ti riferirò i risultati o eventuali errori.

Se puoi, mi confermi solo due cose rapide?

• Hai raccomandazioni su quale Deployment scegliere (Head vs Latest code)?
• Ci sono problemi comuni di autorizzazioni o scope da controllare quando si crea il trigger?

Grazie ancora per l’aiuto.

[Keith Andersen]

Head is fine.

Also, if this spreadsheet is shared to you and you install a trigger, it will run off of your account and be subject to your quotas. 

It is best to have the owner of the spreadsheet install the trigger.

My website: https://sites.google.com/view/klaweb/
Passions: God, Family, Scriptures, Learning, Data Management, Google Sheets + App Script and much more!

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CACrgDdqDW4zfytVt91RoSBUGCZPemMyeVU_18Jy%3DNPjZD%2BYcUQ%40mail.gmail.com.

[Mario Rossi]

Ciao Keith — grazie per la indicazione sul trigger.

Due rapidi chiarimenti sul messaggio di Mario:

• Deployment: se vuoi che il trigger esegua sempre l’ultima versione che stai modificando in Editor, scegli “Head” (o “Latest code” nell’IDE nuovo). Se invece vuoi stabilità e ripetibilità (evitare che una modifica in sviluppo cambi il comportamento del trigger), crea una versione e usa quel deployment specifico.
• Autorizzazioni / scope: i trigger installati richiedono che l’utente che li installa autorizzi gli scope necessari (Sheets, Drive, Gmail, ecc.) in base alle API usate. Controlli comuni:
• Installa il trigger con l’account proprietario del foglio (come hai suggerito) perché il trigger gira con l’account che lo installa e sarà soggetto ai suoi limiti/quota.
• Verifica che lo script abbia dichiarato o richiesto gli scope necessari; se compaiono errori di autorizzazione, rimuovi e reinstalla il trigger per forzare la schermata di consenso.
• In ambienti Google Workspace, controlla eventuali restrizioni amministrative o la necessità di approvazione dell’app OAuth.

Se vuoi, ti aiuto a verificare gli scope effettivi richiesti dallo script o a creare una versione stabile per il deployment.

Buona giornata.

[Robert Mcleod]

Keith, George and Maria...I truly appreciate your assistance...I guess I was naive to think although this task is too difficult for an amateur such as myself  experts such as you would consider this to be an easy task to complete. My respect for the complexities to run Google Apps Scripts successfully has increased exponentially.

Robert

--

You received this message because you are subscribed to the Google Groups "Google Apps Script Community" group.
To unsubscribe from this group and stop receiving emails from it, send an email to google-apps-script-c...@googlegroups.com.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CACrgDdopU-LmM72Dtm11fo8rb8TFDQhjRFLnxuHLvL1_fH4dJw%40mail.gmail.com.

[Mario Rossi]

Hi Robert,

grazie per il messaggio — niente da scusare: Apps Script può diventare complesso anche per chi ha esperienza. Se vuoi, posso aiutarti a risolvere il problema passo dopo passo.

Per procedere utilemente, puoi indicarmi o inviare:

• lo snippet di codice coinvolto (o il file .gs);
• eventuali messaggi di errore presenti in Execution log o nella Console;
• come hai configurato il trigger (tipo, account che lo ha installato, deployment usato);
• se lo script accede a servizi che richiedono autorizzazioni particolari.

Se preferisci, posso anche suggerire una checklist rapida da seguire (creare versione stabile, reinstallare trigger per forzare autorizzazioni, controllare quota/limiti, verificare scopes nel manifest).

Dimmi come vuoi procedere e ti supporto volentieri.

Cordiali saluti,
[Il tuo nome]

[George Ghanem]

You are welcome!

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAGSpW-BbBRSEc9-%2B78eb_d84maZ5nr%3D7PteQJ1mtGb%3DfGP3vKw%40mail.gmail.com.

[Mario Rossi]

Hi George — you’re welcome. Glad I could help.

If you want, I can review the script’s scopes or help create a stable deployment/version for the trigger. Dimmi pure quale preferisci.

Best regards.

[Emerson Maia]

Hi Robert,

Please note that I haven't tested this directly in your spreadsheet. I built and refined this solution entirely based on the details and the original code you shared in your message.

To make this completely foolproof, you don't need to edit the code at all anymore.

I created a custom menu that handles everything. When you click Suspension System > Initialize System, a prompt will ask you for the name of the sheet tab. The script will find it and save its underlying Sheet ID. This is a great safeguard because if anyone renames the sheet later, the code won't break!

To keep it secure, this menu only appears for the Owner. Because it uses an "Installable Trigger," it runs under your permissions. If another authorized teacher updates a date to today, the system will seamlessly detect their edit and send the notification email directly to your inbox.

How to use it:

1. Delete all previous scripts and paste the code below.

2. Save and refresh your Google Sheets page.

3. Click the new Suspension System > Initialize System menu.

4. Type the name of your sheet when prompted and click OK. The script will automatically create the daily and edit triggers for you.

Complete Code:

function onOpen() {

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const ownerEmail = ss.getOwner().getEmail();
  const currentUserEmail = Session.getActiveUser().getEmail();

  // Only show the menu to the spreadsheet owner
  if (currentUserEmail === ownerEmail || currentUserEmail === '') {
    SpreadsheetApp.getUi()
      .createMenu('Suspension System')
      .addItem('Initialize System', 'initializeSystem')
      .addItem('Run Manual Check Now', 'sendExpirationEmails')
      .addToUi();
  }
}

function initializeSystem() {
  const ui = SpreadsheetApp.getUi();
 
  // Prompt the user for the sheet name
  const response = ui.prompt(
    'System Setup',
    'Please enter the EXACT name of the sheet tab where the student data is located:',
    ui.ButtonSet.OK_CANCEL
  );

  if (response.getSelectedButton() !== ui.Button.OK) return;

  const sheetName = response.getResponseText().trim();
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName(sheetName);

  if (!sheet) {
    ui.alert('Error', `Sheet "${sheetName}" not found. Please check the name and try again.`, ui.ButtonSet.OK);
    return;
  }

  // Save the Sheet ID (gid) to Script Properties. This prevents the script from breaking if the sheet is renamed.
  const sheetId = sheet.getSheetId();
  PropertiesService.getScriptProperties().setProperty('TARGET_SHEET_ID', sheetId.toString());

  // Remove existing triggers to prevent duplicates
  const triggers = ScriptApp.getProjectTriggers();
  triggers.forEach(trigger => ScriptApp.deleteTrigger(trigger));
 
  // 1) Create Daily Time-Driven Trigger (6 AM to 7 AM)
  ScriptApp.newTrigger('sendExpirationEmails')
    .timeBased()
    .everyDays(1)
    .atHour(6)
    .create();
   
  // 2) Create Installable Edit Trigger for manual changes
  ScriptApp.newTrigger('handleEditEvent')
    .forSpreadsheet(ss)
    .onEdit()
    .create();
   
  ui.alert('Success!', 'The system is initialized. The daily trigger and the manual edit trigger are now active. You are all set!', ui.ButtonSet.OK);
}

class SuspensionManager {
  constructor() {
    // Automatically gets the email of the user who initialized the triggers
    this.recipientEmail = Session.getEffectiveUser().getEmail();

    this.tz = Session.getScriptTimeZone();
    this.todayKey = Utilities.formatDate(new Date(), this.tz, "yyyy-MM-dd");
   

    // Retrieve the saved Sheet ID (Handling 0 properly since it's a valid ID)
    const idString = PropertiesService.getScriptProperties().getProperty('TARGET_SHEET_ID');
    this.targetSheetId = idString !== null ? parseInt(idString, 10) : null;
  }

  getSheet() {
    if (this.targetSheetId === null) return null;
    const sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
    return sheets.find(s => s.getSheetId() === this.targetSheetId) || null;
  }

  formatCustomDate(date) {
    const formatter = new Intl.DateTimeFormat('en-US', {

      weekday: 'short', day: '2-digit', month: 'short', year: 'numeric'
    });

    return formatter.format(date);
  }

  sendNotification(studentName, expirationDate) {
    const formattedDate = this.formatCustomDate(expirationDate);

    const subject = `${studentName} has reached the end of the behavior suspension period.`;

    const body = `Hi Robert,\n\nThis is an automated notification. The behavior suspension period for student ${studentName} ends today (${formattedDate}).\n\nRegards,\nAutomated Script`;

    MailApp.sendEmail(this.recipientEmail, subject, body);
    console.log(`Email sent for ${studentName}`);
  }

  checkAllRows() {
    const sheet = this.getSheet();
    if (!sheet) {
      console.error("Target sheet not found. Please run 'Initialize System' again.");

      return;
    }

    const lastRow = sheet.getLastRow();
    if (lastRow < 2) return;

    const data = sheet.getRange(2, 2, lastRow - 1, 5).getValues();

    const expiringToday = data.map(row => ({
      studentName: row[0],

      expirationDate: row[4]

    })).filter(record => {
      if (!(record.expirationDate instanceof Date)) return false;
      const expKey = Utilities.formatDate(record.expirationDate, this.tz, "yyyy-MM-dd");
      return expKey === this.todayKey;
    });

    expiringToday.forEach(record => this.sendNotification(record.studentName, record.expirationDate));
  }

  checkSingleRow(rowNumber) {

    const sheet = this.getSheet();
    if (!sheet) return;
   

    const data = sheet.getRange(rowNumber, 2, 1, 5).getValues()[0];
    const studentName = data[0];
    const expirationDate = data[4];

    if (expirationDate instanceof Date) {
      const expKey = Utilities.formatDate(expirationDate, this.tz, "yyyy-MM-dd");
      if (expKey === this.todayKey) {
        this.sendNotification(studentName, expirationDate);
      }
    }
  }
}

function sendExpirationEmails() {
  const manager = new SuspensionManager();
  manager.checkAllRows();
}

function handleEditEvent(e) {
  if (!e || !e.range) return;
 
  const manager = new SuspensionManager();
  if (manager.targetSheetId === null) return;

  const sheetId = e.range.getSheet().getSheetId();
  if (sheetId !== manager.targetSheetId) return;

  const row = e.range.getRow();
  const col = e.range.getColumn();

  // If edit happens in Column F (Column 6) and below header
  if (col === 6 && row >= 2) {
    manager.checkSingleRow(row);
  }
}

I believe this covers all your requirements. If anything goes wrong during testing, please reply with the exact error messages so I can help you more accurately.

	Documentos de especialistas Emerson Maia	Goiânia Brasil
		+55 62 991911266

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/3032740d-f2d3-4fd5-a498-ca3d5bd1659f%40gmail.com.

[Robert Mcleod]

Hi Emerson...It is Robert from the Google Apps Script Community. I recently granted you access as an editor for my Google Sheet until I can confirm my apps script works perfectly. Your new script is extremely impressive, and runs without errors. However, I haven't received an email to robert...@ssdedu.org since I manually ran the script a short while ago.

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CADH-QrBdCA3b6ydtMDdsPhUy3rfQ%2BJNdOtt_qha6XG5qk7G9EQ%40mail.gmail.com.

[Robert Mcleod]

Wait a minute!!!...Your script finally worked. I made the mistake of first running the script from the apps script screen rather than running it from the new menu item you created. Emerson...You are phenomenal!!!....Thank you so much!!! I am going to take a break for a while. If I encounter any problems again in the near future, I will be in touch with you.

Robert

[Emerson Maia]

I'm glad it worked, just let me know if you need anything else.

Expert  Docs Emerson Maia

Goiânia Go Brazil

+55 62 991911266

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAGSpW-AbPb7%2BtN1cmnoRzQSWpTp5Aa69WA2LQcQAT5fz_EsMAA%40mail.gmail.com.

[Robert Mcleod]

Hi Emerson...For my initial script request, I stated I need 100 rows of student data to be read. I recently decided to have at least 200 rows of student data beginning with Row 2. Does the newest script you made for me need to be edited, or is it already configured for '"x" rows of data?

Robert

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CADH-QrD1OiUn67ssuZ4QU9tZuQTsWrTG7ZcNZK9cQRhmAs3_6A%40mail.gmail.com.

[Kildere S Irineu]

Há dois problemas diferentes no contexto que você trouxe, e cada um teve uma causa-raiz diferente.

1) Problema de lentidão no Google Sheets / Apps Script

No trecho que você trouxe antes, a causa final ficou bem clara:

havia um erro de fórmula em outro campo/aba da planilha, e isso fazia um dos scripts demorar minutos; ao corrigir o erro, o script voltou ao normal.

Diagnóstico correto

Nesse caso, o Apps Script não estava “ruim” por causa do código em si. O problema estava no estado da planilha:

• fórmulas com erro (#ERROR!, #REF!, #N/A, etc.)
• recálculos pesados
• dependências entre abas
• fórmulas voláteis ou muito amplas

Solução definitiva para esse cenário

Quando um script do Sheets ficar lento de repente:

1. revise todas as abas em busca de erros de fórmula
2. reduza fórmulas pesadas e intervalos abertos demais
3. evite ler a planilha inteira sem necessidade
4. use leitura em lote e processe em memória
5. teste o tempo de getValues() com logs
6. verifique abas auxiliares, mesmo que o script não use diretamente aquela aba

Conclusão desse primeiro caso

A resposta “definitiva” já apareceu no próprio histórico que você trouxe: o erro de fórmula em outra parte da planilha era o verdadeiro gargalo. Então, para lentidão repentina, a primeira verificação deve ser a saúde das fórmulas da planilha, antes de mexer no Apps Script.

---

2) Problema do script que “rodava sem erro”, mas não enviava e-mail

No PDF que você enviou, o problema era outro: o script de Robert rodava sem erros aparentes, mas o e-mail não chegava. A discussão mostra que houve várias hipóteses, mas a causa prática ficou confirmada no final.

Quem acertou cada parte do problema

Mario Rossi

• apontou corretamente que getActiveSheet() pode falhar em execução por trigger
• sugeriu usar uma aba específica
• chamou atenção para timezone, parsing de data, logs e autorização.

Keith Andersen

• acertou um ponto central: gatilhos simples não enviam e-mail; para isso, é preciso usar gatilho instalável.

George Ghanem

• reforçou corretamente que triggers simples como onEdit não podem chamar serviços externos como envio de e-mail; a solução é criar um trigger manual/instalável e autorizá-lo.

Emerson Maia

• entregou a solução mais completa e prática:
  • menu personalizado
  • rotina de inicialização
  • armazenamento do sheetId
  • trigger diário
  • trigger instalável de edição
  • execução manual pelo menu
• no fim, foi a solução dele que efetivamente funcionou para o Robert.

Qual foi a causa final confirmada?

A confirmação veio do próprio Robert no fim da conversa: ele percebeu que o script funcionou quando executou pelo novo menu criado pela solução do Emerson, e que antes ele estava rodando “do jeito errado”, direto da tela/editor do Apps Script.

Ou seja, a solução definitiva desse segundo caso foi a combinação de:

• execução/autorização corretas
• gatilhos instaláveis
• inicialização do sistema via menu
• não depender de getActiveSheet()
• usar uma arquitetura mais robusta para identificar a aba e disparar notificações

---

Solução definitiva consolidada

Juntando todo o contexto, a resposta definitiva é esta:

A) Quando o problema for lentidão

A causa mais provável não é o Apps Script em si, mas a planilha sobrecarregada ou com erro de fórmula.

Faça sempre esta sequência:

• procure erros em todas as abas
• elimine fórmulas quebradas
• reduza fórmulas voláteis e matrizes desnecessárias
• leia apenas o intervalo necessário
• registre tempos com log

B) Quando o problema for “script roda mas não envia e-mail”

A arquitetura correta é:

• não usar getActiveSheet() para lógica crítica
• usar getSheetByName() ou, melhor ainda, salvar o sheetId
• usar gatilho instalável, não trigger simples
• autorizar corretamente MailApp/GmailApp
• criar rotina de inicialização
• ter coluna de controle, como Notified, para evitar envios duplicados
• executar o fluxo pelo menu/setup correto quando a solução depender de configuração inicial

---

Minha conclusão final sobre “quem deu a solução definitiva”

Se for o caso de lentidão, a solução definitiva foi a observação que você mesmo trouxe:
havia erro de fórmula na planilha, e corrigir isso resolveu a lentidão.

Se for o caso do e-mail que não era enviado, a solução definitiva foi a do Emerson Maia, mas com contribuições importantes de:

• Keith Andersen e George Ghanem, ao esclarecerem a necessidade de trigger instalável
• Mario Rossi, ao apontar problemas de aba ativa, data e autorização.

---

Regra prática definitiva para evitar os dois tipos de problema

Em projetos com Google Sheets + Apps Script, trate sempre estas 4 frentes como obrigatórias:

1. Saúde da planilha
   Verifique erros de fórmula, intervalos gigantes, abas auxiliares pesadas e recálculo excessivo.
2. Contexto de execução
   Não dependa de aba ativa, usuário ativo ou execução manual aleatória no editor.
3. Triggers corretos
   Para enviar e-mail, acessar serviços externos ou rodar automação real, use gatilhos instaláveis e autorize os escopos necessários.
4. Arquitetura robusta
   Use IDs fixos, propriedades do script, logs, coluna de status (Notified) e tratamento explícito de data/timezone.

Se você quiser, eu posso transformar isso agora em um modelo definitivo de script Google Apps Script, já pronto para:

• ler a aba correta
• detectar datas do dia
• enviar e-mail
• marcar “Notified”
• evitar duplicidade
• registrar logs de diagnóstico
• e continuar rápido mesmo com 200+ linhas

To view this discussion visit https://groups.google.com/d/msgid/google-apps-script-community/CAGSpW-DqdJK%3DVYNBU6%2BVXQWLgeKAc43Qkvi%2BjQLpcJQ8QMnS3g%40mail.gmail.com.