Google App-script Autosort on edit

Question

Ive been working on automatically sorting my data (ascending based on 2nd row 1st column data) and I found some tips through searching online but encountered an error which seems I cant find an answer through the net.

so heres the scenario:

I have 2 sheets, Sheet1 & Sheet2, the data from sheet1 is linked through sheet2 although sheet2 has additional columns,

this is sheet1

and this is sheet2

notice that the Column lastname and code in both sheets are thesame, the difference is the column Gender (Formatted on drop-down list) & Bdate(cell formatted as date)

I found a script that seems to work but I does not properly work completely, here is the output after I run the script.

notice the columns that inside the red box, it seems gender and bdate didnt follow the auto sort.

here is my code:

function autosortOnEdit() {
var sheetNames = ["Sheet1", "Sheet2"];
var ss = SpreadsheetApp.getActiveSpreadsheet();
sheetNames.forEach(function(name) {
var sheet = ss.getSheetByName(name);
var range = sheet.getRange(2, 1, sheet.getLastRow() - 1, sheet.getLastColumn() -1);
range.sort({column: 1, ascending: true});
});
}

my observation is I think this script does not work on cells that are formatted like the example above.

I want to sort this automatically based on column A "last name".

how can i make this script work even on formatted cells?

Thanks in Advance, I will continue searching through the net.

Shyam Kansagra Shyam Kansagra · Accepted Answer · 2017-01-20T07:29:39

Not sure how to use range.sort({column: 1, ascending: true}); or how does it work, but whenever I want to sort sheet values, I do the following:

function myFunction() 
{
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
  var rows = sheet.getLastRow();
  var columns = sheet.getLastColumn();

  var sortedValues = sheet.getRange(2, 1, rows-1, columns).getValues().sort();

  sheet.getRange(2, 1, rows-1, columns).setValues(sortedValues);
}

Try this instead of your code, hope this helps as it is successfully sorting all the values when I tested.

EDIT

To apply the same to all sheets inside a spreadsheet, you can get sheet names and iterate it in for loop one by one. Check the code:

function myFunction() 
{
  var sheets = SpreadsheetApp.getActiveSpreadsheet().getSheets();
  var currentSheetName, currentSheet;

  for(var i=0; i<sheets.length; i++)
  {
    currentSheetName = sheets[i].getName();

    currentSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(currentSheetName);
    var rows = currentSheet.getLastRow();
    var columns = currentSheet.getLastColumn();

    var sortedValues = currentSheet.getRange(2, 1, rows-1, columns).getValues().sort();

    currentSheet.getRange(2, 1, rows-1, columns).setValues(sortedValues);
  }    
}

Google App-script Autosort on edit

1 Answers