2

I have the below script running to sort column E in an ascending order. Each row is a different task and column E is the deadline. I have another piece of script running which deletes the deadline in column E once 'Complete' has been selected in another column. Hence, completed tasks will show empty cells in column E.

The deadlines are currently sorting from closest to furthest away, however E cells that are now empty are not moving to the bottom. I am a Scripts newbie and cannot see the issue. Any help to resolve this issue would be much appreciated.

function onEdit(event){
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sh0 = ss.getSheetByName("2020");
  var sheet = ss.getActiveSheet();
  var editedCell = sheet.getActiveCell();

  var columnToSortBy = 5;
  var tableRange = "A2:I998";

  if(editedCell.getColumn() == columnToSortBy){   
    var range = sheet.getRange(tableRange);
    range.sort( { column : columnToSortBy, ascending: true } );
  }
}
0
1

Issue:

The issue is not the code.

As stated in the official documentation:

The onEdit(e) trigger runs automatically when a user changes the value of any cell in a spreadsheet.

  • Namely, the onEdit trigger is activated only when the user edits the value of a particular sheet. If the edit comes from scripts or formulas then the onEdit function won't be triggered.

  • In your case, you have a script that deletes the content of cells in column E and hence onEdit is not triggered and as a result there no sorting is being done.

Workaround Solution 1:

  • Since you can't take advantage of the onEdit since the deletion of a value happens by a script.
  • An obvious workaround would be to incorporate the sorting functionality inside the script that deletes a cell in column E.

Let's say that the deleteCell function is responsible for deleting the content of a cell in column E. As soon as this function runs, after deleting the cell, also sort the data range right after that by calling the sortE function:

function deleteCell(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName("2020");
  sh.getRange("E3").clearContent();
  sortE();
}

function sortE(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("2020"); // assuming you want to sort sheet "2020"
  const columnToSortBy = 5;
  const tableRange = "A2:I998";  
  const range = sheet.getRange(tableRange);
  range.sort( { column : columnToSortBy, ascending: true } );
}

In this case, sortE won't be manually called by you. You only need to make sure that deleteCell is executed.

Workaround Solution 2:

You can create a time trigger to check (every minute or so) if column E contains an empty cell. If it does, then run sortE of the script above (in workaround solution 1). To create such a trigger for sortE, you can run createTrigger once (just make sure sortE is somewhere in the script editor):

function sortE(){
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sheet = ss.getSheetByName("2020"); // assuming you want to sort sheet "2020"
  const columnToSortBy = 5;
  const tableRange = "A2:I998";  
  const range = sheet.getRange(tableRange);
  range.sort( { column : columnToSortBy, ascending: true } );
}

function createTrigger(){
   ScriptApp.newTrigger("sortE").timeBased().everyMinutes(1).create();
}

and execute only and once createTrigger to initiate the trigger:

enter image description here

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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