CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Solana_Station
Solana_Station

Reputation: 321

How to automatically sorting continuously only if a certain value is set for a certain column in Google Apps Script/Google Sheet?

[How My Current Google Sheet works] I have a table that has the following headers in the exact order and it has all sorts of data.

Date, Ticket ID, Ticket Link, Ticket Type, Category, Subcategory, Status, Customer

What the sheet looks like (Certain information has been removed)

[Goal] I want to be able to bring all the rows to the top that has a status with anything except for "Finished." And at the same time sort the entire table with the "Date" column. So I want this to be continuous and want the rows with the "Finished" "Status" to all be under the rows with the rest of the statuses.

[What's the issue] I've constructed the following script, to sort the data, however there are two main issues.

  1. I don't know how to have the system sort the rows whenever the user makes changes to the cell of the "Status" column.
  2. I was able to sort the data with the following script by "Date" and "Status," however the rows with the "Finished" status are in between the other statuses. What I want to do is, if there are rows with the "Status" as "Finished" then I want them to be below any other statuses. And within those rows with the "Finished" status, they should be sorted by the "Date" column.

// Global veriables
var app = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Status");
var dateColumn = 1;
var statusColumn = 7;
var lastRow = app.getLastRow();
var lastColumn = app.getLastColumn();

function dynamicDataSorting() {
  var sortingRange = app.getRange(16, 1, lastRow - 1, lastColumn)
  sortingRange.sort([{column: dateColumn, ascending: false}, {column: statusColumn, ascending: false}]);
}

Upvotes: 0

Views: 124

Answers (2)

Rocky
Rocky

Reputation: 990

If you want to try Arrayformula:

You can create a separate sheet and apply below formula:

=Arrayformula(SUBSTITUTE(query(Arrayformula(SUBSTITUTE(A2:E8,"Finished","ZzFinished")),"Select * Order by Col5,Col1 asc"),"ZzFinished","Finished"))

Change the data Range and 'Col' number per your need!

Upvotes: 0

fullfine
fullfine

Reputation: 1461

You were very close to the final solution. This is how I would approach it:

  1. Create an auxiliary column (statusAux) with this formula: =if(C2="finished",0,1) where column C is your statusColumn. You can hide it with hideColumn(colmun), hideColumns(columnIndex) or from the web.
  2. Use the following function to sort your data:

function onEdit(e)  {
  range.sort([{column: statusAux, ascending: false}, {column: dateColumn, ascending: true}]);
}

You can define your range inside or outside of the function, it has the same effect.

To understand how triggers work check out Simple Triggers and onEdit(e)

Upvotes: 1

Related Questions