CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetsgoogle-sheets-formula
Dev
Dev

Reputation: 33

What kind of changes do I need to this so I can auto sort two columns in Google sheets (date columns)?

I have two columns - column 4 is a date, column 5 is a time field (HH:MM). I would like it to auto sort by both (ascending).

The code below works if both columns are numbers but doesn't work when I do data validation and convert column 4 into a date field and 5 into time (HH:MM).

I'm not adamant on using a modification of the script below, couldn't think of a clean formula that would get the job done (using one sheet).

var SORT_COLUMN_INDEX = 4;
var SORT_COLUMN_SECOND = 5;
var ASCENDING = true;
var NUMBER_OF_HEADER_ROWS = 1;
var activeSheet;
function autoSort(sheet) {
  var range = sheet.getDataRange();
  if (NUMBER_OF_HEADER_ROWS > 0) {
    range = range.offset(NUMBER_OF_HEADER_ROWS, 0);
  }

  // Perform the actual sort.
  range.sort( {
    column: SORT_COLUMN_SECOND,
    ascending: ASCENDING
  });
  range.sort( {
    column: SORT_COLUMN_INDEX,
    ascending: ASCENDING
  } );
}
function onEdit(event) {
  var editedCell;
  activeSheet = SpreadsheetApp.getActiveSheet();
  editedCell = activeSheet.getActiveCell();
  if (editedCell.getColumn() == SORT_COLUMN_INDEX) {
    autoSort(activeSheet);
  }
}
function onOpen(event) {
  activeSheet = SpreadsheetApp.getActiveSheet();
  autoSort(activeSheet);
}
function onInstall(event) {
  onOpen(event);
}

Upvotes: 0

Views: 32

Answers (1)

Cooper
Cooper

Reputation: 64100

This function combines COL3 and COL4 using the column 3 as the day and column 4 as the hour and replaces column 3 with a datetime. It also deletes column 4 and writes the data back into the spreadsheet and sorts on column 3 which is now a datetime.

function myfunc() {
  const ss=SpreadsheetApp.getActive();
  const sh=ss.getSheetByName('Sheet1');
  const [hA, ...rows]=sh.getDataRange().getValues();
  let idx={};
  hA.forEach((h,i)=>{idx[h]=i;});
  let vA=rows.map((r,i)=>{
    r[idx['COL3']]= Utilities.formatDate(new Date(2021,0,r[idx['COL3']-1],r[idx['COL4']]),Session.getScriptTimeZone(),"E MMM dd, yyyy HH:mm:ss");

    return [r[idx['COL1']],r[idx['COL2']],r[idx['COL3']],r[idx['COL5']],r[idx['COL6']],r[idx['COL7']],r[idx['COL8']],r[idx['COL9']],r[idx['COL10']]];
  });
  hA.splice(3,1);
  sh.clearContents();
  sh.getRange(1,1,1,hA.length).setValues([hA]);
  sh.getRange(2,1,vA.length,vA[0].length).setValues(vA);
  sh.getRange(2,1,sh.getLastRow()-1,sh.getLastRow()).sort({column:3,ascending:true});;
}

Starting Data with random numbers:

COL1 COL2 COL3 COL4 COL5 COL6 COL7 COL8 COL9 COL10
8 14 18 9 5 16 18 13 4 3
5 6 12 6 4 10 15 14 15 11
0 9 3 12 9 14 17 6 8 5
16 15 17 17 12 9 14 15 5 16
3 6 15 6 0 9 15 8 0 8
13 8 13 8 15 0 4 1 16 13
2 10 11 3 14 14 1 19 5 1
12 5 3 14 5 2 5 7 8 3
12 4 6 9 8 15 15 16 4 18
3 18 15 15 7 3 15 18 5 2
11 5 3 6 13 15 15 18 9 14
2 10 4 4 8 16 13 8 0 11
0 7 3 10 9 2 6 11 11 11
17 9 17 10 11 4 5 1 12 19
11 8 19 10 7 2 5 4 1 6
8 3 7 4 17 3 11 1 19 13
9 17 16 15 17 4 16 13 15 10
7 13 1 15 8 17 6 4 6 12
11 10 5 19 10 6 8 12 17 12
16 18 2 0 4 11 3 19 2 8

And then after running the function:

COL1 COL2 COL3 COL5 COL6 COL7 COL8 COL9 COL10
8 3 Sun Jan 03 2021 04:00:00 GMT-0700 (Mountain Standard Time) 17 3 11 1 19 13
12 4 Mon Jan 04 2021 09:00:00 GMT-0700 (Mountain Standard Time) 8 15 15 16 4 18
11 5 Tue Jan 05 2021 06:00:00 GMT-0700 (Mountain Standard Time) 13 15 15 18 9 14
12 5 Tue Jan 05 2021 14:00:00 GMT-0700 (Mountain Standard Time) 5 2 5 7 8 3
5 6 Wed Jan 06 2021 06:00:00 GMT-0700 (Mountain Standard Time) 4 10 15 14 15 11
3 6 Wed Jan 06 2021 06:00:00 GMT-0700 (Mountain Standard Time) 0 9 15 8 0 8
0 7 Thu Jan 07 2021 10:00:00 GMT-0700 (Mountain Standard Time) 9 2 6 11 11 11
13 8 Fri Jan 08 2021 08:00:00 GMT-0700 (Mountain Standard Time) 15 0 4 1 16 13
11 8 Fri Jan 08 2021 10:00:00 GMT-0700 (Mountain Standard Time) 7 2 5 4 1 6
17 9 Sat Jan 09 2021 10:00:00 GMT-0700 (Mountain Standard Time) 11 4 5 1 12 19
0 9 Sat Jan 09 2021 12:00:00 GMT-0700 (Mountain Standard Time) 9 14 17 6 8 5
2 10 Sun Jan 10 2021 03:00:00 GMT-0700 (Mountain Standard Time) 14 14 1 19 5 1
2 10 Sun Jan 10 2021 04:00:00 GMT-0700 (Mountain Standard Time) 8 16 13 8 0 11
11 10 Sun Jan 10 2021 19:00:00 GMT-0700 (Mountain Standard Time) 10 6 8 12 17 12
7 13 Wed Jan 13 2021 15:00:00 GMT-0700 (Mountain Standard Time) 8 17 6 4 6 12
8 14 Thu Jan 14 2021 09:00:00 GMT-0700 (Mountain Standard Time) 5 16 18 13 4 3
16 15 Fri Jan 15 2021 17:00:00 GMT-0700 (Mountain Standard Time) 12 9 14 15 5 16
9 17 Sun Jan 17 2021 15:00:00 GMT-0700 (Mountain Standard Time) 17 4 16 13 15 10
16 18 Mon Jan 18 2021 00:00:00 GMT-0700 (Mountain Standard Time) 4 11 3 19 2 8
3 18 Mon Jan 18 2021 15:00:00 GMT-0700 (Mountain Standard Time) 7 3 15 18 5 2

Upvotes: 1

Related Questions