Geek Answers Handbook

Google Apps Script Additional Sort Rules

I am working on a Google Apps Script application, and one of the features that I would like the program to have is to automatically sort a series of form responses based on data from two different columns. Therefore, I would like to sort it by the data in column 16, and then sort by column 1. I can execute this function manually using the method: https://drive.googleblog.com/2010/06/tips-tricks-advanced-sorting -rules-in.html

I am currently running the Spreadsheet.sort(column, ascending) function with the first column, but I cannot sort it so that it accepts the second column as an additional sort rule. Is there a method in Google Apps Script that I could use to emulate this feature?

+4
source share
2 answers

See document: https://developers.google.com/apps-script/reference/spreadsheet/range#sort(Object)

 function sortFormResponses() { var ss = SpreadsheetApp.getActiveSpreadsheet(); // change name of sheet to your sheet name var s = ss.getSheetByName("Form Responses"); var lastCol = s.getLastColumn(); var lastRow = s.getLastRow(); // assumes headers in row 1 var r = s.getRange(2, 1, lastRow - 1, lastCol); // Note the use of an array r.sort([{ column: 1, ascending: true }, { column: 16, ascending: true}]); }
+11
source

You can sort at the array level, just get the data from the sheet into the matrix and sort the matrix in several passes by selecting the column that you want to sort.

something like that:

 function test(){ sortSheetOnColumn(2,3) } function sortSheetOnColumn(col1,col2){ var sh = SpreadsheetApp.getActiveSheet(); var data = sh.getDataRange().getValues();// get all data var header = data.shift(); data.sort(function(x,y){ // Note: sort method changes the original array // var xp = Number(x[col2-1]);// use these to sort on numeric values // var yp = Number(y[col2-1]); var xp = x[col2-1].toLowerCase();// use these for non-numeric values var yp = y[col2-1].toLowerCase(); // I used toLowerCase() for my use case but you can remove it or change it to whatever you need Logger.log(xp+' '+yp); // just to check the sort is OK return xp == yp ? 0 : xp < yp ? -1 : 1;// sort on column col ascending }); data.sort(function(x,y){ // Note: sort method changes the original array // var xp = Number(x[col1-1]);// use these to sort on numeric values // var yp = Number(y[col1-1]); var xp = x[col1-1].toLowerCase();// use these for non-numeric values var yp = y[col1-1].toLowerCase();// Logger.log(xp+' '+yp); // just to check the sort is OK return xp == yp ? 0 : xp < yp ? -1 : 1;// sort on column col ascending }); // and at the end take back the headers data.unshift(header); sh.getDataRange().setValues(data); }

or better, after Adam's comment:

 function sortSheetOnColumn2(col1, col2) { var sh = SpreadsheetApp.getActiveSheet(); var data = sh.getDataRange().getValues();// get all data var header = data.shift(), x1, y1, x2, y2; col1--; col2--; data.sort(function(x, y) { x1 = x[col1].toLowerCase(); y1 = y[col1].toLowerCase(); x2 = x[col2].toLowerCase(); y2 = y[col2].toLowerCase(); return x1 == y1 ? (x2 == y2 ? 0 : x2 < y2 ? -1 : 1) : x1 < y1 ? -1 : 1; }); data.unshift(header); sh.getDataRange().setValues(data); }

but Michael answers, if smarter , using the built-in Range.sort method that I did not know about (at least about its advanced features).

0
source

More articles:


All Articles