CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
SATH59
SATH59

Reputation: 159

How to sort range using Apps script based on a column

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);
  }
}

Upvotes: 1

Views: 4255

Answers (1)

Cooper
Cooper

Reputation: 64040

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);
  }
}

Upvotes: 4

Related Questions