Why is my Sorting script works inconsistently?

Question

Hi helpful contributors,

I have a response google sheet that has a set of datarange with different type of data values (screenshot) as below. Col2 Timestamp values are auto-generated upon user input in gform. I moved it as col2 (default is in col1) because I want to put the Quote ID as primary key col. Quote ID values are auto-generated based on google script that is triggered upon submission of gform:-
gresponse sheet with inconsistent sort

I supposed we would have the data range autosort by timestamp (and I need it significantly to sort by timestamp) but this is not happening consistently with mine. I already tried the below codes but still not working consistently too. But when I sort it manually in the sheet using the feature Filter to choose Timestamp column and then sort A->Z, it works fine. But that won't be effective for my requirements

Method 1 :

// this resulted as in the screenshot.

SortRange = "A2:AC";
SortOrder = [{column:2, ascending: true}];
function multiSortColumns(){
  var range = msheet.getRange(SortRange);
  range.sort(SortOrder);
}

Method 2 :

// some sorted, some not but still wrong and not ascending order.

SortRange = 2,1,sheet.getLastRow() - 1, sheet.getLastColumn();
SortOrder = [{column:2, ascending: true}];
function multiSortColumns(){
   var range = sheet.getRange(SortRange);
   range.sort(SortOrder);
}

Method 3

var sheet=SpreadsheetApp.getActiveSheet().sort(2);
// this doesn't sort by specific column we want and it includes sorting the header.

Please help to correct my code. Tqvm in advance.

user_11225291 · Answer

Potential Issues:

The first two methods in your question don't seem to define the sheet object.
Also I am not even sure if you execute multiSortColumns at all.
The third method will also sort the header as well.
Another potential issue would be when using:
```
const sheet = ss.getActiveSheet();
```
The latter considers the active sheet which can be any of the selected sheets in the spreadsheet file.

Instead I would advice you to use:

const sheet = ss.getSheetByName("Form Responses 1");

assuming the name of the sheet you want to sort is Form Responses 1

Solution:

Here is a modified version of your second method which makes sure that everything is executed when you execute myFunction():

function myFunction() {
  const ss = SpreadsheetApp.getActive();
  // const sheet = ss.getActiveSheet(); // not recommended
  const sheet = ss.getSheetByName("Form Responses 1"); // recommended
  sheet.getRange(2, 1, sheet.getLastRow() -1, sheet.getLastColumn()).sort({column: 2, ascending: true});   
}

Why is my Sorting script works inconsistently?

1 Answers

Potential Issues:

Solution: