CODEWITHSUNDEEP
javascriptgoogle-apps-scriptgoogle-sheets
cocky2012
cocky2012

Reputation: 13

Sort ranges in an array

I have a timesheet spreadsheet for our company and I need to sort the employees by each timesheet block (15 rows by 20 columns). I have the following code which I had help with, but the array quits sorting once it comes to a block without an employee name (I would like these to be shuffled to the bottom). Another complication I am having is there are numerous formulas in these cells and when I run it as is, it removes them. I would like to keep these intact if at all possible. Here's the code:

function sortSections() 
{
  var activeSheet = SpreadsheetApp.getActiveSheet();
  //SETTINGS
  var sheetName = activeSheet.getSheetName(); //name of sheet to be sorted
  var headerRows = 53; //number of header rows
  var pageHeaderRows = 5; //page totals to top of next emp section
  var sortColumn = 11; //index of column to be sorted by; 1 = column A
  var pageSize = 65;
  var sectionSize = 15; //number of rows in each section

  var col = sortColumn-1;
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var data = sheet.getRange(headerRows+1, 1, sheet.getMaxRows()-headerRows, sheet.getLastColumn()).getValues();
  var data3d = [];
  var dataLength = data.length/sectionSize;
  for (var i = 0; i < dataLength; i++) {
    data3d[i] = data.splice(0, sectionSize);
  }
  data3d.sort(function(a,b){return(((a[0][col]<b[0][col])&&a[0][col])?-1:((a[0][col]>b[0][col])?1:0))});
  var sortedData = [];
  for (var k in data3d) {
    for (var l in data3d[k]) {
      sortedData.push(data3d[k][l]);
    }
  }
  sheet.getRange(headerRows+1, 1, sortedData.length, sortedData[0].length).setValues(sortedData);

Upvotes: 1

Views: 9397

Answers (1)

megabyte1024
megabyte1024

Reputation: 8650

I think to solve your problems is possible to use the Range.sort function instead of the custom code. The sort function relocates also the formulas but in a tricky way - if a cell formula contains a cell reference, the sort function changes the row index in relocated cell to have the new cell row index, for instance, initially the cell C1 contains the =A1*B1 formula, after the sort operation the row 1 relocated to the row 3 and the cell 'C3' will contain not =A1*B1, but =A3*B3.

With this modification your code should looks something like this

function sortSections() 
{
  var activeSheet = SpreadsheetApp.getActiveSheet();
  //SETTINGS
  var sheetName = activeSheet.getSheetName(); //name of sheet to be sorted
  var headerRows = 53; //number of header rows
  var pageHeaderRows = 5; //page totals to top of next emp section
  var sortColumn = 11; //index of column to be sorted by; 1 = column A
  var pageSize = 65;
  var sectionSize = 15; //number of rows in each section

  var col = sortColumn-1;
  var sheet = SpreadsheetApp.getActive().getSheetByName(sheetName);
  var range = sheet.getRange(headerRows+1, 1, sheet.getMaxRows()-headerRows, sheet.getLastColumn());
  range.sort({column: sortColumn, ascending: true});
  ...
}

Upvotes: 1

Related Questions