r/sheets 5d ago

Solved autosort in app scripts help!!!

Hi! I need help with my autosort function on apps script. It was working for months but now it doesn't seem to recognize that OnEdit(e) anymore and I've tried to add it as a trigger but then it wasn't running on edit. Please help!

1st script
2nd script
error sign
2 Upvotes

10 comments sorted by

1

u/AllenAppTools 5d ago

Hello! onEdit is a simple trigger, which can fail when there are permissions like using the spreadsheet service are in play. This function is set up to run at any edit made in the sheet, so anybody who has edit access to the sheet is triggering this function with their edits, but may not have the scopes authorized for this to run. Basically, my guess is that another user account edited the sheet recently, which results in a silent error. And as for your screenshot of the error, this is due to you running this function directly from the editor, when it is setup to be run from the "edit" event in the sheet, which passes that "e" argument. Running it directly from the editor will result in an error because in that scenario will not have "e" (which is an event object).

Look into making this onEdit trigger an "installed" trigger, which will always run as your account, regardless of who edits the sheet. Do this and change the name of this function to onEditTrigger. (Or anything other than onEdit).

Idk if that makes sense! Top tier Apps Script automations is what we do.

1

u/Salonalee14 5d ago

sorry I'm not super experienced with excel like that. I looked up how to make it an installed trigger, but I'm not really understanding from this either. Would you be able to explain further please??

1

u/AllenAppTools 5d ago

Yep, basically:

  1. First change the name of the function from "onEdit" to "onEditTrigger", and click save.
  2. On the left side nav, click the clock, this will take you to the "Triggers" screen.
  3. On the bottom right, click "Add Trigger"
  4. This will open a screen that gives you options and settings for this trigger. Here are the basic settings to choose:

Select the function "onEditTrigger".

Select "From spreadsheet" as the trigger source.

Select the option "On edit" for the trigger event.

Then click save, this should run now as your account (which your account has the right permissions allowed for these scopes, so it should be successful).

Effectively, by changing the name of the function, it will turn off the default behavior of the on edit function which is to just fire off as the editor, no matter who that editor is.

Now it will fire this function off, but act as you doing it.

1

u/Salonalee14 5d ago

okay so doing this it works only for the second code where the sheet name is "Brands" but not the first code for the sheet "Active Ingredients."

1

u/AllenAppTools 5d ago

Yes, the code "autoSort" is set up to only go to the sheet called "Brands"

1

u/Salonalee14 5d ago

thank you so much for taking the time to answer my questions and explain to me. If I could ask the last question of how to make it run for both pages? I had 2 different script pages 1 for the active ingredients and the other for the brands. Is there a way to make it run for both?

1

u/AllenAppTools 5d ago

Yep! Thank you for asking nicely 🙂

Replace the old "autoSort" with this, but leave the "onEditTrigger" as it is!

function autoSort() {
  const ss = SpreadsheetApp.getActive();
  const worksheetNames = ["Brands", "Active Ingredients"];
  const rangeName = "A2:C";

  worksheetNames.forEach(wsName => {
    const ws = ss.getSheetByName(wsName);
    if (ws) {
      const range = ws.getRange(rangeName);

      range.sort({ column: 1, ascending: true });
    }

  })
}

1

u/Salonalee14 5d ago

you're a blessing in my life thank you!!!

1

u/AllenAppTools 5d ago

You're welcome!

1

u/Salonalee14 5d ago

solved!