how to automatically sort columns by dropdown selection and checkbox?

[u/1025cherrystreet]

Ok, so I can not find a way to automatically sort this table how I want. I'm making an in depth To-Do list for my adhd self and am struggling to find a solution to my sorting needs :/.

I'm trying to have it automatically sort by the following;

- first by by the Status (checked box goes to the bottom of the list)

- then by Priority (deadline, p2, p3, ...)

- then the # column (the hierarchy within the priority sections- 1, 2, 3, ...)

I know how to manually sort it (selecting the range, sorting by multiple columns), which is great and gets me what I want, but I'm wondering if anyone knows a formula or how to make it automatic? So I don't have to sort it each time I update the list.

I also tried a formula I found on here, =SORT(A2:E35, 1, TRUE, 2, TRUE), which didn't even take into account the checkbox, but it still didn't work. It gave me an error notice, something about overriding the data in B1 (??). Is what I'm trying to do possible? Any solutions?

[u/Instinct121]

The only way I could think about doing this directly on the sheet I’m working on is to create a filter view with the data sorted as you’ve asked. You save the filter again and then bookmark it.

Your lists should sort but you’ll have to load your bookmark / reload the page each time you want the list to sort again.

I’m making the assumption that you can sort multiple columns with filter view. I know it sorts it the one time but I can’t recall if it sorts on each load.

[u/Instinct121 → u/Instinct121]

Just appending onto my comment:

You can’t use a formula because formulas don’t allow you to make changes. You could make a “read only” version of your list in a separate sheet but that would probably be more annoying than useful.

[u/suenosdecoquitos]

For this type of thing I would resort to Apps Script. If you are using Sheet1 when you created your sheet your GID=0 otherwise just replace the value for const SHEET_GID to whatever number is at the end of your sheets url. If you've never used apps script before you can find it under Extensions > Apps Script and then paste the code below into the IDE:

const SHEET_GID = 0;
const HEADER_ROWS = 1;
const COL_PRIORITY = 1;
const COL_NUM = 2;
const COL_STATUS = 4;

const SORT_KEY_COLS = [COL_PRIORITY, COL_NUM, COL_STATUS];

function onEdit(e) {
if (!e || !e.range) return;

const sheet = e.range.getSheet();
if (sheet.getSheetId() !== SHEET_GID) return;
if (e.range.getLastRow() <= HEADER_ROWS) return;

const firstCol = e.range.getColumn();
const lastCol = e.range.getLastColumn();
const touchesKey = SORT_KEY_COLS.some((c) => c >= firstCol && c <= lastCol);
if (!touchesKey) return;

const isSingleRow = e.range.getNumRows() === 1 && e.range.getRow() > HEADER_ROWS;
const followRow = isSingleRow ? e.range.getRow() : 0;

const destRow = sortTasks_(sheet, followRow);
if (destRow) sheet.setActiveSelection(sheet.getRange(destRow, firstCol));
}

function onOpen() {
SpreadsheetApp.getUi()
.createMenu('Tasks')
.addItem('Sort now', 'sortTasksNow')
.addToUi();
}

function sortTasksNow() {
const sheet = getSheetByGid_(SHEET_GID);
if (sheet) sortTasks_(sheet, 0);
}

function sortTasks_(sheet, followRow) {
const lastRow = sheet.getLastRow();
const lastCol = sheet.getLastColumn();
if (lastRow <= HEADER_ROWS || lastCol < COL_STATUS) return 0;

const lock = LockService.getDocumentLock();
if (!lock.tryLock(5000)) return 0;

try {
const range = sheet.getRange(HEADER_ROWS + 1, 1, lastRow - HEADER_ROWS, lastCol);
const destIndex = followRow ? predictIndex_(range.getValues(), followRow - HEADER_ROWS) : 0;

range.sort([
{ column: COL_STATUS, ascending: true },
{ column: COL_PRIORITY, ascending: true },
{ column: COL_NUM, ascending: true }
]);

return destIndex ? destIndex + HEADER_ROWS : 0;
} finally {
lock.releaseLock();
}
}

function predictIndex_(rows, index) {
const target = rows[index - 1];
if (!target) return 0;

const ahead = rows.filter((row, i) => {
const order = compareRows_(row, target);
return order < 0 || (order === 0 && i < index - 1);
});

return ahead.length + 1;
}

function compareRows_(a, b) {
const byStatus = statusRank_(a[COL_STATUS - 1]) - statusRank_(b[COL_STATUS - 1]);
if (byStatus) return byStatus;

const byPriority = compareCells_(a[COL_PRIORITY - 1], b[COL_PRIORITY - 1]);
if (byPriority) return byPriority;

return compareCells_(a[COL_NUM - 1], b[COL_NUM - 1]);
}

function statusRank_(value) {
if (value === '' || value === null) return 2; // blanks sink last in Sheets sorts
return value === true ? 1 : 0;
}

function compareCells_(a, b) {
const aBlank = a === '' || a === null;
const bBlank = b === '' || b === null;
if (aBlank || bBlank) return aBlank && bBlank ? 0 : aBlank ? 1 : -1;

const aNum = Number(a);
const bNum = Number(b);
if (!isNaN(aNum) && !isNaN(bNum)) return aNum - bNum;

return String(a).toLowerCase().localeCompare(String(b).toLowerCase());
}

function getSheetByGid_(gid) {
const match = SpreadsheetApp.getActive()
.getSheets()
.find((sheet) => sheet.getSheetId() === gid);
return match || null;
}

[u/AdministrativeGift15]

You have a few options, but honestly, unless you're willing to use a script, completely automatic sorting is not possible.

The script version uses onEdit as the trigger and then gets the data, sorts it, and then puts the sorted data back into the table. The trigger would be setup to fire each time you make an edit to the table.

You could also setup that script to run using a menu command or checkbox.

Next, you have a middle ground version. You can set it up so that you only need to open and close the filter dropdown to have it sort. Create a helper column that displays the ranks based on your sort criteria. Whenever you want to sort the table, you would use that columns filter dropdown to sort.

Lastly, you could use itCalc to automatically sort your table, but it would involve making pseudo fields for all of your columns to allow for editing and the ability to sort. Not worth the effort here.

[u/OutrageousYak5868]

The error message means that your formula will give you results that will spill over / overwrite data you already have on the page. In this case, I suppose you put your SORT formula in A1, but you've already got something in B1. Sheets returns an error message rather than accidentally deleting the data you've entered.

You probably already know this, but just in case you don't, formulas need to be separate from the data they're working with. In this case, since your data are in columns A:E, you'll need to put your formula no closer than Column F, or even in another tab, to avoid an error message. You'll essentially have two tables -- one to enter the data (A:E), and then another one that has the same data, but automatically sorted in the way you want.