Google Sheets Script - Sort By Multiple, Dynamic Ranges

Question

*TL:DR I would like to sort an entire sheet automatically with the following conditions.

1. Sort entire sheet by columns 1 2 and 3
2. Move any rows with "Z Shipped" in column 1 to the bottom of the populated list (there are many rows with blank data in column 1, so I want "other data, Z Shipped data, blank rows".*

Hi there, I as the title states, I am trying to sort my google sheet through multiple ranges. I have pasted my current code below that I have found gets me close, however I keep getting a

"The coordinates of the range are outside the dimensions of the sheet."

error on the second "newRange.sort(newSortOrder)" line.

I am not sure if my current code is what I need to have or not. I have no expertise in this and just try to find functions that work online and am getting stuck on this one as it does not seem to be a common request.

function autosort() {


  var ss        = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var sortRange = "A3:E";
  var range     = ss.getRange(sortRange);
  var sortOrder = [
                  {column: 1, ascending: true},
                  {column: 2, ascending: true},
                  {column: 3, ascending: true}
                  ];

  range.sort(sortOrder);


  var status = ss.getDataRange().getLastRow();

  for (var i=status;i>=1;i--){
    if(ss.getRange(i,1).getValue()!=='Z Shipped') {break;}
  }

  var newSortRow   = i+1;
  var newSortRange = "A3:E"+newSortRow;
  var newRange     = ss.getRange(newSortRange);
  var newSortOrder = [
                     {column: 2, ascending: true},
                     {column: 3, ascending: true}
                     ];

  newRange.sort(newSortOrder);
}

Answer 1

EDIT: Code has been updated so that it can work around the Array Formulas I have calculating certain columns. Basically just had to identify the ranges around each array column.

Awesome thanks to @Boris Baublys for his help on this!

I did tweak the code a little bit as I was giving sample data, and my real-life data didn't work exactly with the code as it was supplied. There may be a way to clean up my tweaked code a bit more, but it worked for me!

Basically the new code does a final sort of the "non Z Shipped" data to put it into the proper order from there, as well as specifying the sheet you want sorted.

//  Sort entire sheet by a specified sort order
function autosort() {
  let ss        = SpreadsheetApp.getActiveSpreadsheet();
  var sheet     = ss.getSheetByName("Sheet Name");
  let sortRange = "A3:BH";
  var range     = sheet.getRange(sortRange);
  let sortOrder = [
    {column: 1, ascending: true},
    {column: 11, ascending: true},
  ];
  range.sort(sortOrder);
  
//  Find the boundaries of a specified block of entries ("Z Shipped" here).
  let Avals  = sheet.getRange("A3:A").getValues();
  let fAvals = [].concat(...Avals); // Make the array flat
  let start  = fAvals.indexOf("Z Shipped");
  let end    = fAvals.lastIndexOf("Z Shipped");
  
/////

//  Load data with non-empty column A into an array
  let Alast = Avals.filter(String).length + 2;
  let vals1  = sheet.getRange("A3:O" + Alast).getValues();
  
//  Move the "Z Shipped" block to the end of the array
  let removed1 = vals1.splice(start, end - start + 1);
  let sorted1  = vals1.concat(removed1);
  
  sheet.getRange("A3:O" + Alast).setValues(sorted1);

/////

//  Load data with non-empty column A into an array
  let vals2  = sheet.getRange("S3:AW" + Alast).getValues();
  
//  Move the "Z Shipped" block to the end of the array
  let removed2 = vals2.splice(start, end - start + 1);
  let sorted2  = vals2.concat(removed2);
  
  sheet.getRange("S3:AW" + Alast).setValues(sorted2);

/////

//  Load data with non-empty column A into an array
  let vals3  = sheet.getRange("AY3:BD" + Alast).getValues();
  
//  Move the "Z Shipped" block to the end of the array
  let removed3 = vals3.splice(start, end - start + 1);
  let sorted3  = vals3.concat(removed3);
  
  sheet.getRange("AY3:BD" + Alast).setValues(sorted3);

/////

//  Load data with non-empty column A into an array
  let vals4  = sheet.getRange("BF3:BF" + Alast).getValues();
  
//  Move the "Z Shipped" block to the end of the array
  let removed4 = vals4.splice(start, end - start + 1);
  let sorted4  = vals4.concat(removed4);
  
  sheet.getRange("BF3:BF" + Alast).setValues(sorted4);

/////

//  Load data with non-empty column A into an array
  let vals5  = sheet.getRange("BH3:BH" + Alast).getValues();
  
//  Move the "Z Shipped" block to the end of the array
  let removed5 = vals5.splice(start, end - start + 1);
  let sorted5  = vals5.concat(removed5);
  
  sheet.getRange("BH3:BH" + Alast).setValues(sorted5);

/////

//  Sort all the non "Z Shipped" items with a new sort order
  let sortRange2 = ("A3:BH" + start);
  var range2 = sheet.getRange(sortRange2);
  let sortOrder2 = [
    {column: 6, ascending: true},
    {column: 11, ascending: true},
    {column: 4, ascending: true}
  ];
  range2.sort(sortOrder2);
}

Huge thanks again @Boris Baublys

Answer 2

"I want "other data, Z Shipped data, blank rows"

We sort in 2 stages. First, sort entire sheet by columns 1, 2 and 3. Then find the boundaries of the "Z Shipped" block and move it to the bottom of the populated list.

function autosort() {
  let ss        = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  let sortRange = "A3:E";
  var range     = ss.getRange(sortRange);
  let sortOrder = [
    {column: 1, ascending: true},
    {column: 2, ascending: true},
    {column: 3, ascending: true}
  ];
  range.sort(sortOrder);
  
//  Find the boundaries of the "Z Shipped" block
  let Avals = ss.getRange("A3:A").getValues();
  let fAvals = [].concat(...Avals); // Make the array flat
  let start = fAvals.indexOf("Z Shipped");
  let end = fAvals.lastIndexOf("Z Shipped");
  
//  Load data with non-empty column A into an array
  let Alast = Avals.filter(String).length + 2;
  let vals = ss.getRange("A3:E" + Alast).getValues();
  
//  Move the "Z Shipped" block to the end of the array
  let removed = vals.splice(start, end - start + 1);
  let sorted = vals.concat(removed);
  
  ss.getRange("A3:E" + Alast).setValues(sorted);
}