1

*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);
}
Improve this question

2 Answers 2

Reset to default
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

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

"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);
}
Improve this answer

6 Comments

HUGE Thanks my friend!I did tweak it a bit to suit my exact needs (my question was surrounding a sample sheet I was testing with), but your help got me the info/code I needed.
Actually, I just noticed something. I have array formulas in my sheet that are getting blocked with this sorts (I assume this is because it copy/pastes data instead of just sorting? Is there a way to avoid this and have it strictly sort, or do I need to identify each range around the columns that have an array formula for the sort ranges?
"I have array formulas in my sheet that are getting blocked with this sorts" The general description of the problem does not allow me to understand it well, sorry. Could you please clarify which formulas stop working?
Certain columns have array formulas. So when the sort happens (moving "Z Shipped" to the bottom", I believe this script must be using some form of "copy/paste" as my array formula columns will have actual values pasted after the script is ran, instead of relying on the array formula that is part of my header (i.e. Cell R2 = {"Back Sheets";ARRAYFORMULA(IF(ISBLANK(O3:O),"",IF(ISBLANK(BB3:BB),"",E3:E/P3:P)))}
Thank you, it became a little clearer. Does it help if you use a combination of absolute and relative references in your formulas? It is difficult to advise without seeing the specific formulas on the sheet. In the original formulation of the question there was nothing about this, it was only sorting. Maybe you'd better create a new question and attach the problem spreadsheet link to it?
|

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.