CODEWITHSUNDEEP
sortinggoogle-apps-scriptgoogle-sheetsfilteringgoogle-sheets-formula
Luponius
Luponius

Reputation: 87

Google App Script: Remove blank rows from range selection for sorting

I want to sort real-time when a number is calculated in a "Total" column, which is a sum based on other cells, inputted by the user. The sort should be descending and I did achieve this functionality using the following:

function onEdit(event){
  var sheet = event.source.getActiveSheet();
  var range = sheet.getDataRange();
  var columnToSortBy = 6;
  range.sort( { column : columnToSortBy, ascending: false } );

}

It's short and sweet, however empty cells in the total column which contain the following formula, blanking itself if the sum result is a zero, otherwise printing the result:

=IF(SUM(C2:E2)=0,"",SUM(C2:E2))

It causes these rows with an invisible formula to be included in the range selection and upon descending sort, they get slapped up top for some reason. I want these blank rows either sorted to the bottom, or in an ideal scenario removed from the range itself (Without deleting them and the formula they contain from the sheet) prior to sorting.

Or maybe some better way which doesn't require me dragging a formula across an entire column of mostly empty rows. I've currently resorted to adding the formula manually one by one as new entries come in, but I'd rather avoid this.

EDIT: Upon request find below a screenshot of the sheet. As per below image, the 6th column of total points needs to be sorted descending, with winner on top. This should have a pre-pasted formula running lengthwise which sums up the preceding columns for each participant.

The column preceding it (Points for Tiers) is automatically calculated by multiplying the "Tiers" column by 10 to get final points. This column could be eliminated and everything shifted once left, but it's nice to maintain a visual of the actual points awarded. User input is entered in the 3 white columns.

Sheet sort

Upvotes: 4

Views: 2879

Answers (2)

Adam
Adam

Reputation: 2802

A much simpler way to fix this is to just change

=IF(SUM(C2:E2)=0,"",SUM(C2:E2))

to

=IF(SUM(C2:E2)=0,,SUM(C2:E2))

The cells that are made blank when the sum is zero will then be treated as truly empty and they will be excluded from sort, so only cells with content will appear sorted at the top of the sheet.

Why your original formula doesn't work that way is because using "" actually causes the cell contain content so it's not treated as a blank cell anymore. You can test this by entering ISBLANK(F1) into another cell and check the difference between the two formulas.

Upvotes: 1

Tanaike
Tanaike

Reputation: 201553

  • You want to sort the sheet by the column "F" as the descending order.
  • You want to sort the sheet by ignoring the empty cells in the column "F".
  • You want to move the empty rows to the bottom of row.
  • You don't want to change the formulas at the column "F".
  • You want to achieve this using Google Apps Script.

If my understanding is correct, how about this answer?

Issue and workaround:

In the current stage, when the empty cells are scattered at the column "F", I think that the built-in method of "sort" of Class Range cannot be directly used. The empty cells are moved to the top of row like your issue. So in this answer, I would like to propose to use the sort method of JavaScript for this situation.

Modified script:

In order to run this function, please edit a cell.

function onEdit(event){
  const columnToSortBy = 6; // Column "F"
  const headerRow = 1; // 1st header is the header row.
  
  const sheet = event.source.getActiveSheet();
  const values = sheet.getRange(1 + headerRow, 1, sheet.getLastRow() - headerRow, sheet.getLastColumn())
    .getValues()
    .sort((a, b) => a[columnToSortBy - 1] > b[columnToSortBy - 1] ? -1 : 1)
    .reduce((o, e) => {
      o.a.push(e.splice(0, columnToSortBy - 1));
      e.splice(0, 1);
      if (e.length > 0) o.b.push(e);
      return o;
    }, {a: [], b: []});
  sheet.getRange(1 + headerRow, 1, values.a.length, values.a[0].length).setValues(values.a);
  if (values.b.length > 0) {
    sheet.getRange(1 + headerRow, columnToSortBy + 1, values.b.length, values.b[0].length).setValues(values.b);
  }
}
  • In this sample script, it supposes that the header row is the 1st row. If in your situation, no header row is used, please modify to const headerRow = 0;.
  • From your question, I couldn't understand about the columns except for the column "F". So in this sample script, all columns in the data range except for the column "F" is replaced by sorting. Please be careful this.

Note:

  • Please use this sample script with enabling V8.

References:

Added:

  • You want to sort the sheet by the column "F" as the descending order.
  • You want to sort the sheet by ignoring the empty cells in the column "F".
    • You want to move the empty rows to the bottom of row.
  • In your situation, there are the values in the column "A" to "F".
  • The formulas are included in not only the column "F", but also other columns.
  • You don't want to change the formulas.
  • You want to achieve this using Google Apps Script.

From your replying and updated question, I could understand like above. Try this sample script:

Sample script:

function onEdit(event){
  const columnToSortBy = 6; // Column "F"
  const headerRow = 1; // 1st header is the header row.

  const sheet = event.source.getActiveSheet();
  const range = sheet.getRange(1 + headerRow, 1, sheet.getLastRow() - headerRow, 6);
  const formulas = range.getFormulas();
  const values = range.getValues().sort((a, b) => a[columnToSortBy - 1] > b[columnToSortBy - 1] ? -1 : 1);
  range.setValues(values.map((r, i) => r.map((c, j) => formulas[i][j] || c)));
}

Upvotes: 1

Related Questions