1

I have two custom functions bound to a Google spreadsheet with an onEdit() simple trigger that are returning errors.

The first function sortStatus() sorts the columns in range A2:R by I2:I (col 9) ascending and F2:F (Col 6) descending.

The second projectMove moves the edited row from range A2:R to the sheet named in the edited cell, if the edited cell is in I2:I (col 9) and the name is different than the current sheet.

Example Script

function onEdit(e) {
  sortStatus(e);
  projectMove(e);
}

function sortStatus(e) {
  var sheet = e.source.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 9;
  var tableRange = "A2:R";

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort([{column: columnToSortBy, descending: false}, {column: 6, descending: true}]);
  }
}

function projectMove(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  if (r.columnStart != 9 || r.rowStart == 1 || e.value == src.getName()) return;
  const dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(e.value);
  src.getRange(r.rowStart,1,1,18).moveTo(dest.getRange(dest.getLastRow()+1,1,1,18));
  src.deleteRow(r.rowStart);
}

This is the error I'm getting in the execution log:

Execution log error

What am I doing wrong?

0

2 Answers 2

0

Updated Script

function onEdit(e) {

  const ss = e.source;
  const range=e.range;
  const row = range.getRow();
  const value=e.value;
  const sheets=ss.getSheets();
  const sheet=range.getSheet();
  const name=sheet.getName();
  const dest = ss.getSheetByName(value);
  
  const columnToSortBy = 9;
  const tableRange = "A2:R";
  
  // Only continue if the right column 
  if (range.getColumn() == columnToSortBy){

    // move if needed  
    if ((dest) && (name != value) && (row != 1)) {
      
      sheet.getRange(row,1,1,18).moveTo(dest.getRange(dest.getLastRow()+1,1));
      sheet.deleteRow(row);
    
    }  
       
  // sort 

  sheet.getRange(tableRange).sort([
      { column: columnToSortBy, descending: false},
      { column: 6, descending: true}

    ]); 
  
  }  
    
}

Explanation

  1. First condition checks if the edited cell is in column 9. Both the sort and the move are dependent on this, so no point continuing if that condition isn't met.
  2. The next condition ensures the cell value is a valid sheet name, is not in row 1, and doesn't match the current sheet name, which are all preconditions for moving the row.
  3. If the conditions were met, the row is moved to the new sheet along with any formatting, and then the original row (which is now empty and unformatted following the move) is deleted.
  4. Following that, the remaining rows (or the original rows, if the conditions for the move were not met) are sorted.

So, if the edited cell is in column 9, the row might be moved, but the rows will always be sorted.

1
  • 1
    This is incredible and has solved all my issues, thank you! And noted on the columns - I don't intend to add any more but know I can change that section of the code if need be to accommodate an addition. Thanks again!!
    – rellawp
    Dec 12, 2023 at 10:44
0

The sortStatus(e) function changes the location of rows in the active sheet, which means that values such as e.range.rowStart will not point to the correct cells by the time projectMove(e) gets executed. To make it work, change the order of the two function calls, like this:

function onEdit(e) {
  if (!e) throw new Error('Please do not run the script in the script editor window. It runs automatically when you hand edit the spreadsheet.');
  projectMove(e);
  sortStatus(e);
}

The onEdit(e) function is a simple trigger that is designed to run automatically when you manually edit the spreadsheet. In that context, the event object e is properly populated.

Do not run the code through the ▷ Run button in the script editor. If you do, the event parameter e is not populated, causing the error you quote.

6
  • If I don't run the code through the Run button, how else do I run it? Sorry if that's a really basic question!
    – rellawp
    Nov 28, 2023 at 10:25
  • The onEdit(e) function will run automatically when you manually edit the spreadsheet. See onEdit(e) best practices. Nov 28, 2023 at 10:39
  • Oh, right, I see what you mean. It still didn't work, unfortunately.
    – rellawp
    Nov 30, 2023 at 10:17
  • "Didn't work" is not the most stellar of problem descriptions. If the two functions work when separately triggered in the order projectMove(e), sortStatus(e), they will work the same when executed sequentially by the onEdit(e) above. Nov 30, 2023 at 11:16
  • Sorry, let me explain. The functions don't work together. The sortStatus function only works when I abandon using projectMove. I have tried: a new script per function, changing the order of the functions (as suggested by you, given they are onEdit), and the other commenter's new code as well, to no avail. The projectMove function always seems to override sortStatus. I've just tried my OG code again and weirdly that seems to work now, however it moves the edited row + the 1 row below it to the nominated sheet, instead of just the edited row.
    – rellawp
    Dec 1, 2023 at 12:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.