Google-sheets – Google Script Sort Column Ascending Order with Blank Cells at Bottom

google sheetsgoogle-apps-script

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 } );
  }
}

Best Answer

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:

Related Solutions

Google Sheets – How to Sort Randomly Using Google Script

Here is the function shuffleSheet which randomly shuffles the rows of the current sheet. The actual shuffling algorithm (which is classical) is taken from this answer by Laurens Holst.

I imagine this is something you will not want to do on every edit, but only when the script is explicitly invoked.

function shuffleSheet() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getActiveSheet();
  var range = sheet.getDataRange();
  range.setValues(shuffleArray(range.getValues()));    
}    

function shuffleArray(array) {
  var i, j, temp;
  for (i = array.length - 1; i > 0; i--) {
    j = Math.floor(Math.random() * (i + 1));
    temp = array[i];
    array[i] = array[j];
    array[j] = temp;
  }
  return array;
}

Google-sheets – Dropdown list based on the result of another cell’s dropdown

If you need multiple validation rules for multiple cells, you should also have multiple ranges for them, so that the rules are tracked independently of each other.

But the second issue, with an illegal choice staying in F after an edit to D, can't be solved with formulas alone. So I wrote a script that keeps better track of validation in column F. Note that it does not set the initial validation rules (before first use); that you can do manually. It also doesn't set rules for column D, which are static.

What it does is the following: when a cell in column D is edited (header row excluded), it adjusts the validation rule of the cell two steps to the right accordingly. Additionally, if that cell contained a value such as OptionB that is no longer allowed when D is blank, the value in F is removed.

In the script, e.range is the cell edited, e.value is its new value.

function onEdit(e) {
  if (e.range.getColumn() === 4 && e.range.getRow() > 1 ) {  // only Column D, not header 
    var optionA = SpreadsheetApp.newDataValidation()
      .requireValueInList(['OptionA'], true).build();
    var optionsABC = SpreadsheetApp.newDataValidation()
      .requireValueInList(['OptionA', 'OptionB', 'OptionC'], true).build();

    var affectedCell = e.range.offset(0,2,1,1);  // two steps to the right
    if (e.value === '') {
      if (affectedCell.getValue() !== 'OptionA') {
        affectedCell.setValue('');    // reset if current choice is no longer legal
      }
      affectedCell.setDataValidation(optionA); 
    }
    else {
      affectedCell.setDataValidation(optionsABC);      
    }
  }
}