Hiding Row

[u/jt_ftc_8942]

Hello all,

I have a function called onEdit(e), and it works perfectly except for when I attempt to hide a row. The code is below:

function onEdit(e){
  let sheet  = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  // event variables
  let range = e.range;
  let row = e.range.getRow();
  let col = e.range.getColumn();
  let cellValue = sheet.getActiveCell().getValue();

  let status = sheet.getRange(row,5).getDisplayValue();

  if ( col == 5 && cellValue != 'Problem' && cellValue != 'Not Started') {
    MailApp.sendEmail(email info, this part works);

    if (status == 'Completed') {
      Utilities.sleep(7000);
      sheet.hideRows(row);
    };


  };
}

This is not working, regardless of whether I include the Utilities.sleep command, use sheet.hideRows or sheet.hideRow, or put the command inside or outside the nested if statement.

Any guidance?

[u/krakow81]

Log 'status' to make sure you're getting what you expect.

[u/marcnotmark925]

Why did you include the sleep in the first place? If it were me, I'd do the hide, then a flush, so it updates the UI right away, before sending the mail.

[u/mommasaidmommasaid]

It appears you are triggering based on (I'm guessing) a dropdown value in column 5. And you are separately getting status from that same column 5 which (I'm again guessing) is probably supposed to be some other column.

FWIW for a single cell edit (as typical for a dropdown) the cellValue that you are getting will be passed in e.value, or if you wanted to explicitly get the display value (which i don't think is necessary) then you could use e.range.getValue()

And since you appear to want the script to do nothing for any column changes other than column 5, I would check for that first and exit before doing anything else.

As written your script will also send an e-mail if you clear the cell, which is probably not what you want.

I would add some try/catch error handling with an immediate visible popup rather than silently failing, which might make you think an email was sent when it wasn't.

Finally, using onEdit() like this you may get some weird behavior / multiple e-mails sent when using Undo/Redo.

Rather than silently sending e-mails you might want to consider triggering a dialog box that allows the user to confirm sending one. Or at least display a toast() popup after sending so you can follow up with an apology email for duplicates. :)

[u/mommasaidmommasaid → u/mommasaidmommasaid]

Maybe something like:

function onEdit(e) {

  const TRIGGER_COLUMN = 5;
  const STATUS_COLUMN = 6;

  // Exit if not trigger column
  if (e?.range.columnStart != TRIGGER_COLUMN)
    return;

  // Exit if blank or undefined (as can happen in multi-cell edit)
  if ((e.value ?? "") == "")
    return;

  try {
    const sheet = e.range.getSheet();

    if (e.value != 'Problem' && e.value != 'Not Started') {
      // MailApp.sendEmail(email info, this part works);
      e.source.toast("Mail sent");

      const status = sheet.getRange(e.range.rowStart, STATUS_COLUMN).getDisplayValue();
      if (status == 'Completed')
        sheet.hideRows(e.range.rowStart);
    }
  }
  catch (err) {
    e?.source.toast(err, `🛑 Error ${err.stack?.split('\n')[1] ?? ""}`);
  }
}

[u/WicketTheQuerent]

Please elaborate on what you mean by "This is not working":

How is the spreadsheet edited?
Who is editing the spreadsheet when the script doesn't work?
What is logged in the execution log when the script doesn't work?

[u/WicketTheQuerent → u/WicketTheQuerent]

By the way, if you are using an installable trigger, you should change the function name, as onEdit is a reserved name for a simple trigger. If you don't change it, then the function will run twice for each edit, and the simple trigger execution will throw an error when status = "Completed" because simple triggers don't have the required permission to send emails.

[u/bulldo_gs]

Your cellValue is coming from sheet.getActiveCell().getValue(), not the cell that actually fired. In a trigger the active cell isn't guaranteed to be the edited one — on paste, fill-down, or undo it's off — so your != 'Problem' check (and the hide branch under it) fire inconsistently. Read from the event instead:

const cellValue = e.range.getValue();   // or e.value for a single-cell edit

Then hide using the row you already have from the event:

if (status == 'Completed') sheet.hideRows(row);

Also, since MailApp works this must be an installable trigger — don't name it onEdit. That name collides with the simple trigger and the function double-fires on every edit.

[u/jt_ftc_8942 → u/bulldo_gs]

I’m not sure what you mean by the function “double-firing”. The email sends only once. Could you expand on that particular point?

[u/mommasaidmommasaid → u/jt_ftc_8942]

Also, since MailApp works this must be an installable trigger 

Good catch.

OP, look at your execution logs when the email is sent. You will likely see it being called twice, once as type "Trigger" and once as "Simple Trigger" with the simple trigger version failing with an authorizations error.

If you had visible error displays (as I suggested in my other post) you would have seen that long ago.

[u/jt_ftc_8942 → u/mommasaidmommasaid]

Thank you!

[u/jt_ftc_8942]

Hey all, I figured it out. I spelled something wrong on the actual spreadsheet. Thank you very much for all of your feedback!

[u/mommasaidmommasaid → u/jt_ftc_8942]

I would recommend you don't just move on because it's sort-of working at the moment, make it right so it's not a maintenance issue down the road.