CODEWITHSUNDEEP
sortinggoogle-sheetsgoogle-sheets-formula
Liliya Kostadinova
Liliya Kostadinova

Reputation: 15

How to lock cell movement with row movement when sorting Google Sheets

I have this script for automatic custom sorting, however, for some reason it only works when the chosen range includes only the column I actually want to sort and not the whole table. In this case I want to sort people by their current status (which is a dropdown with all options for each person) - when they are "Ready" I want them to appear on top, when "DISPATCHED" to appear second, "ENROUTE" - third, etc. The status column sorts nicely but it doesn't move the rows with itself.

Is there a way to group and lock a row so that if one cell of that row is re-sorted then the whole row should be re-sorted? This is the script I currently use:

SHEET_NAME = "Zars";
SORT_DATA_RANGE = "F3:F100";

function onEdit(e){
  multiSortColumns();
}
function multiSortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet(); 
  var sheet = ss.getSheetByName(SHEET_NAME);
  var range = sheet.getRange(SORT_DATA_RANGE); 
  var values = range.getValues(); 
  var order = ['READY', 'DISPATCHED', 'ENROUTE', 'REST', 'COVERED', 'NO ANSWER', '']; //CUSTOM ORDER LIST
  values.sort(function (a, b) {
    if (a[0] === 'READY' && b[0] !== 'READY') { return -1; }
    else if (a[0] !== 'READY' && b[0] === 'READY') { return 1; }
    else { return order.indexOf(a[0]) - order.indexOf(b[0]); }
  });
  range.setValues(values);
  ss.toast('Sort complete.');
}

All help is greatly appreciated! Thank you lots

Upvotes: 1

Views: 327

Answers (1)

idfurw
idfurw

Reputation: 5862

You have modified it wrongly.

SHEET_NAME = "Zars";
SORT_DATA_ROWSTART = 3; // Data starts from Row 3
SORT_DATA_ROWEND = 100; // Data ends by Row 100
SORT_DATA_COL = 6; // Status in Col F

function onEdit(e){
  multiSortColumns(e);
}
function multiSortColumns(e){
  const r = e.range.rowStart;
  if (r < SORT_DATA_ROWSTART) { return; }
  if (r > SORT_DATA_ROWEND) { return; }
  var sheet = e.source.getActiveSheet();
  if (sheet.getName() !== SHEET_NAME) { return; }
  var rows = SORT_DATA_ROWEND - SORT_DATA_ROWSTART + 1;
  var range = sheet.getRange(SORT_DATA_STARTROW, 1, rows, sheet.getLastColumn()); 
  var values = range.getValues(); 
  var order = ['READY', 'DISPATCHED', 'ENROUTE', 'REST', 'COVERED', 'NO ANSWER', '']; //CUSTOM ORDER LIST
  var i = SORT_DATA_COL - 1;
  values.sort(function (a, b) {
    return order.indexOf(a[i]) - order.indexOf(b[i]);
  });
  range.setValues(values);
  ss.toast('Sort complete.');
}

Upvotes: 1

Related Questions