How to sort range using Apps script based on a column

Question

I have this demo sheet https://docs.google.com/spreadsheets/d/1pGkTrudeDfv7Xkm7ZGM0fmQzSCqU05hFVhuczJ4cWCQ/edit?usp=sharing

I'm using this code to sort my data,

function SortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Ventas");
  var range = sheet.getRange("A4:S");
  var SORT_ORDER = [
{column: 19, ascending: false},  
{column: 18, ascending: false},
{column: 13, ascending: false},
{column: 7 , ascending: false},
{column: 2 , ascending: true},
{column: 1 , ascending: true},
{column: 3 , ascending: true}
]
  range.sort(SORT_ORDER);
}

which works ok

What I need is:
if Column M contains 'Entregado' to sort in this way,
if contains 'Cobrar' sort in different order,
if contains 'Apartado' sort in different order,
if contains 'Preventa' sort in different order,
and if contains any other value to sort them in a different way

is this possible?

thanks!

Edit: I'm trying this code, but its not working as expected

function ColumnContainString(column,string) {
  var column=column || 13;//used for initial testing
  var string=string || 'test';//used for initial testing
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName("Ventas");
  var cA=sheet.getRange(4,column,sheet.getLastRow(),1).getValues();
  for(var i=0;i<cA.length;i++) {
    if(cA[i][0].toString().indexOf(string)>-1) {//you may wish to change this line to some regular expression to find only words
      Logger.log(i+1);
      return(i+1);
    }
  }
  Logger.log('-1');
  return -1;
}

function SortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Ventas");
  var range = sheet.getRange("A4:S");
 
 if(ColumnContainString(13,'Entregado')==-1) {
    var SORT_ORDER = [
    {column: 19, ascending: false},  // M
    {column: 18, ascending: true},   // H
    {column: 1 , ascending: true},   // Fecha
    {column: 2 , ascending: true},   // Cliente
    {column: 3 , ascending: true}    // Sabor
    ]   
      range.sort(SORT_ORDER);
  }
  
  if(ColumnContainString(13,'Cobrar')==-1) {
    var SORT_ORDER = [
    {column: 18, ascending: true},   // H
    {column: 13, ascending: true},   // Estatus
    {column: 7 , ascending: false},  // Vendio
    {column: 2 , ascending: true},   // Cliente
    {column: 3 , ascending: true},   // Sabor
    {column: 1 , ascending: true}    // Fecha
    ]  
      range.sort(SORT_ORDER);
  }
  
  if(ColumnContainString(13,'Pendiente')==-1) {
    var SORT_ORDER = [
    {column: 18, ascending: true},   // H
    {column: 13, ascending: true},   // Estatus
    {column: 7 , ascending: false},  // Vendio
    {column: 2 , ascending: true},   // Cliente
    {column: 3 , ascending: true},   // Sabor
    {column: 1 , ascending: true}    // Fecha
    ]  
      range.sort(SORT_ORDER);
  }
  
  if(ColumnContainString(13,'Preventa')==-1) {
    var SORT_ORDER = [
    {column: 18, ascending: true},   // H
    {column: 13, ascending: true},   // Estatus
    {column: 7 , ascending: false},  // Vendio
    {column: 2 , ascending: true},   // Cliente
    {column: 3 , ascending: true},   // Sabor
    {column: 1 , ascending: true}    // Fecha
    ]  
      range.sort(SORT_ORDER);
  }
  
  if(ColumnContainString(13,'Apartado')==-1) {
    var SORT_ORDER = [
    {column: 18, ascending: true},   // H
    {column: 13, ascending: true},   // Estatus
    {column: 2 , ascending: true},   // Cliente
    {column: 3 , ascending: true},   // Sabor
    {column: 1 , ascending: true}    // Fecha
    ]  
      range.sort(SORT_ORDER);
  }
}

In order to correctly understand about What I need is, can I ask you about your question? If your shared Spreadsheet is the situation before the script, which you want, is run, can you provide the result after the script was run? — Tanaike
– Tanaike, Commented Jun 17, 2019 at 3:42

Cooper · Accepted Answer · 2019-06-17 04:27:47Z

4

try this:

function ColumnContainString(column,string) {
  var column=column || 13;//used for initial testing
  var string=string || 'test';//used for initial testing
  var ss=SpreadsheetApp.getActive();
  var sheet=ss.getSheetByName("Ventas");
  var cA=sheet.getRange(4,column,sheet.getLastRow(),1).getValues();
  for(var i=0;i<cA.length;i++) {
    if(cA[i][0].toString().indexOf(string)>-1) {//you may wish to change this line to some regular expression to find only words
      Logger.log(i+1);
      return(i+1);
    }
  }
  Logger.log('-1');
  return -1;
}

The main function will have expressions like this:

if(ColumnContainString(13,'Entregado')==-1) {
//Then put your sort functions in here
}

So you might rewrite you current function in this way:

function SortColumns(){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Ventas");
  var range = sheet.getRange("A4:S");
  if(ColumnContainString(13,'Entregado')==-1) {
    var SORT_ORDER = [
      {column: 19, ascending: false},  
      {column: 18, ascending: false},
      {column: 13, ascending: false},
      {column: 7 , ascending: false},
      {column: 2 , ascending: true},
      {column: 1 , ascending: true},
      {column: 3 , ascending: true}];
      range.sort(SORT_ORDER);
  }
  if(ColumnContainString(13,'Apartado')==-1) {
    var SORT_ORDER = [
      {column: 19, ascending: false},  
      {column: 18, ascending: false},
      {column: 13, ascending: false},
      {column: 7 , ascending: false},
      {column: 2 , ascending: true},
      {column: 1 , ascending: true},
      {column: 3 , ascending: true}];//change sort order
      range.sort(SORT_ORDER);
  }
}

edited Jun 17, 2019 at 4:27

answered Jun 17, 2019 at 3:51

Cooper

64.2k7 gold badges29 silver badges65 bronze badges

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

3 Comments

SATH59 Over a year ago

Sorry, I didn't understand where I have to put the main function inside your function

Cooper Over a year ago

The main function or whatever you want to call it will use ColumnContainString() function to test if a column contains a string. Please note that I changed the answer just a bit.

SATH59 Over a year ago

im trying the code I paste on the question, but is not working if you can see the shared spreadsheet, I don't know what im doing wrong

Collectives™ on Stack Overflow

How to sort range using Apps script based on a column

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related