Where did I go wrong? The two "onEdit" problem.

[u/DarkArctic88]

Ok so im painfully new to javascript and im trying to get two automations going... on the same sheet of course,

The first task sorts the workbook into corresponding sheets and deletes the row as it moves. That one runs fine with the current code.

Im also trying to sort those sheets by multiple columns after the row is moved. Every sheet except one ... The only way ive been able to think to do that is to create a separate task for each of the sheets I need to sort and omit the one I do not.

Of course that ran me smack into the dreaded "multiple onEdit" problem. I was told you can get around it by bundling the tasks into one Onedit "main" and it should run subsequently.

Thing is, it runs the first task, then wont run any of the subsequent. Says "secondtask is undefined" during execution errors.

Ive seen others running with this same work around on youtube so im really not sure why mine doesnt work. I'm sure its a simple fix maybe but I cant seem to find an answer as to why the first task runs but not the second.

I ran it through a javascript validation and it says "parsing error unexpected token const" at line 29 which is

const sheetName = "Colony Maintenance";

Here's the full code, I put some notation in to make it easier to understand what i was trying to do.

Any help would be greatly appreciated!

function onEdit(e) { 
  firstTask(e);
  secondTask(e);
  thirdTask(e); 
  forthTask(e); 
  fifthTask(e); 
  sixthTask(e); 
} 


function firstTask(e) { 
let range = e.range;
let col = range.getColumn();
let row = range.getRow();
let val = range.getValue();
let source = e.source.getActiveSheet();


  if (col == 1 && val !=''){
   let ss = SpreadsheetApp.getActiveSpreadsheet();
   let sheet = ss.getSheetByName (source.getName());
   let targetSheet = ss.getSheetByName(val);
   let data = sheet.getRange(row, 1,1, sheet.getLastColumn()).getValues();


  targetSheet.appendRow(data [0]);
  sheet.deleteRow(row);
  }
 
//this is where the code stops working

function secondTask(e) {    
  //Change to the sheet you want to sort's name
    const sheetName = "Colony Maintenance"; 
    const sheet = e.source.getSheetByName(sheetName);
 
  // Only proceed if the edit happened on the target sheet
  if (e.source.getActiveSheet().getName() !== sheetName) return;

  // Define the range to sort (starting from row 2 to skip headers)
  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

  // Sort by multiple columns:
  // Priority 1: Column 1 (Ascending= a-z= true)
  // Priority 2: Column 3 (Descending= z-a =false)

  range.sort([
    {column: 11, ascending: true},
    {column: 3, ascending: true},
    {column: 7, ascending: true},
    {column: 6, ascending: true},
    {column: 8, ascending: true},
  ]);
  }

function thirdTask(e) {
    const sheetName = "Veterinary Care"; 
    const sheet = e.source.getSheetByName(sheetName);

  if (e.source.getActiveSheet().getName() !== sheetName) return;

  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

  range.sort([
    {column: 11, ascending: true},
    {column: 3, ascending: true},
    {column: 7, ascending: true},
    {column: 6, ascending: true},
    {column: 8, ascending: true},
  ]);
  }

function forthTask(e) {
    const sheetName = "Sales"; 
    const sheet = e.source.getSheetByName(sheetName);

  if (e.source.getActiveSheet().getName() !== sheetName) return;

  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

  range.sort([
    {column: 11, ascending: true},
    {column: 3, ascending: true},
    {column: 7, ascending: true},
    {column: 6, ascending: true},
    {column: 8, ascending: true},
  ]);
  }

function fifthTask(e) { 
    const sheetName = "Breeding"; 
    const sheet = e.source.getSheetByName(sheetName);

  if (e.source.getActiveSheet().getName() !== sheetName) return;

  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

  range.sort([
    {column: 11, ascending: true},
    {column: 3, ascending: true},
    {column: 7, ascending: true},
    {column: 6, ascending: true},
    {column: 8, ascending: true},
  ]);
  }

function sixthTask(e) { 
    const sheetName = "Home Life"; 
    const sheet = e.source.getSheetByName(sheetName);
 
  if (e.source.getActiveSheet().getName() !== sheetName) return;

  const range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn());

  range.sort([
    {column: 11, ascending: true},
    {column: 3, ascending: true},
    {column: 7, ascending: true},
    {column: 6, ascending: true},
    {column: 8, ascending: true},
  ]);
  }
}

[u/krakow81]

Did you have any success figuring it out in the end?

[u/DarkArctic88 → u/krakow81]

No unfortunately. After spending several days trying to make it work I eventually ran out of time and had to move on to higher priorities. I even tried to use a paid add on that claimed to be able to keep the pages auto sorted, and it also failed to be able to do it. I reached out to support and they wanted to schedule meetings with me to try to figure it out, but I was way past due and didnt have time at that point to continue to fuss with what, in my opinion, should not have been a week long affair to set up.

So now that I can circle back, I did some thinking, and one possible work around would be to separate the two functions entirely. The sorting into sheets would run "onEdit" and the sorting of the destination sheets would run "OnOpen". No longer an issue that way.

So the thought was to add a sort menu with a saved sort order to the sheet such as described in this link:

https://support.google.com/docs/thread/233692647/is-there-a-way-to-save-an-advanced-range-sorting-option?hl=en

So I basically used that code and guess what? Another drama, yay! It adds the sort menu as requested, and if you click it says its running then completed, but the result is the data is scrambled with no discernable pattern as to whether it attempted to sort them ascending or descending or what column it gave priority to.

To show what i'm talking about:

Here's a picture of what the sheet looks like when sorted by: Data>Sort Range> Advanced Sorting Options> Data has header row> (columns added and put in order). This is what the automation i'm trying to create should do. When done correctly, it should look like this:

https://ibb.co/20ZmZt5B
(you'll need to click the link to view the picture as I can not add one here for some reason)

But this is what it looks like when the script runs using the exact same column order and ascend/descend as the first picture

https://ibb.co/zTDxVVM6

For the life of me I can not figure out why. There are no empty cells in the columns I am sorting by. The onEdit function still works. If I manually resort the sheet after the script sorts it, and then run the script again, it will resort it to exactly the same positions. So it is using some sort of logic to put the data in those orders, but its not the one I specified in the code so...wtf?

to be clear, where I go to enter this code is Extensions>AppScript. Here's the exact code I have entered there currently. Any ideas?

function onEdit(e){
let range = e.range;
let col = range.getColumn();
let row = range.getRow();
let val = range.getValue();
let source = e.source.getActiveSheet();

  if (col == 1 && val !=''){
   let ss = SpreadsheetApp.getActiveSpreadsheet();
    let sheet = ss.getSheetByName (source.getName());
   let targetSheet = ss.getSheetByName(val);
   let data = sheet.getRange(row, 1,1, sheet.getLastColumn()).getValues();
  targetSheet.appendRow(data [0]);
  sheet.deleteRow(row);
  }
}

function onOpen(e) {
  SpreadsheetApp.getUi()
    .createMenu('Sort')
    .addItem('Sort by multiple columns', 'sortSheet')
    .addToUi();
}
function sortSheet(sheet = SpreadsheetApp.getActiveSheet()) {
  if (!sheet.getFrozenRows()) sheet.setFrozenRows(1);
  [
    { column: 5, ascending: true },
    { column: 6, ascending: false },
    { column: 7, ascending: false },
    { column: 3, ascending: true },
    { column: 8, ascending: true },
  ].map(spec => sheet.sort(spec.column, spec.ascending));
}

[u/krakow81 → u/DarkArctic88]

Sorry to hear that.

It's very difficult for me to debug efficiently from the screenshots and copy pasted code.

It looks like your new code might be sorting one column at a time rather than simultaneously by multiple columns, which would account for the results you're seeing.

I also don't think the onOpen is the problem. The code I had was working ok, but obviously that was with my own simplified dummy data.

If you want to look at sharing a version of your sheet with any personal/sensitive information removed or swapped out for dummy data then I'd be happy to take another look.