I am new to scripting in google sheets and I am trying to add a function in a script to sort a range by column value in ascending order.

I'm using a script that allows me to move rows from one tab to another based on payment status. All imported rows are placed in row 7, just below the header. As a result, the lines are completely messy.

I have tried so many things that I am lost. I don't know if I've tried the wrong function or if I'm placing them in the wrong place in the script.

function onEdit(e) {
  var sh = e.source.getActiveSheet();
  var rng = e.source.getActiveRange();

  if (sh.getName()=='Suivi Clients' && rng.getColumn()==21){
    if(rng.getValue()=='OUI' || rng.getValue()=='ANNULÉ'){

      var dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Clients Finalisé')
      dest.insertRowBefore(7)

      var plage = sh.getRange('C'   rng.getRow()   ':G'    rng.getRow())
      plage.copyTo(dest.getRange("C7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('L'   rng.getRow()   ':N'    rng.getRow())
      plage.copyTo(dest.getRange("L7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('P'   rng.getRow())
      plage.copyTo(dest.getRange("P7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('U'   rng.getRow()   ':W'    rng.getRow())
      plage.copyTo(dest.getRange("U7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)

      sh.deleteRow(rng.getRow())
    }
  }

  if (sh.getName()=='Clients Finalisé' && rng.getColumn()==21){
    if(rng.getValue()=='NON'){

      var dest = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Suivi Clients')
      dest.insertRowBefore(7)

      var plage = sh.getRange('C'   rng.getRow()   ':G'    rng.getRow())
      plage.copyTo(dest.getRange("C7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('L'   rng.getRow()   ':N'    rng.getRow())
      plage.copyTo(dest.getRange("L7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('P'   rng.getRow())
      plage.copyTo(dest.getRange("P7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
      var plage = sh.getRange('U'   rng.getRow()   ':W'    rng.getRow())
      plage.copyTo(dest.getRange("U7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)

      sh.deleteRow(rng.getRow())
    }
  }

  // other onEdit here
}

Dans l'onglet "Suivi Clients" je voudrais trier par colonne G et dans l'onglet "Clients Finalisé" je voudrais trier par colonne P.

That's one of the functions I tried:

function sort() {
       var ss = SpreadsheetApp.getActiveSpreadsheet();
       var sheet = ss.getSheetByName("Suivi Clients");
       var range = sheet.getRange("B6:W");
       range.sort(7);
    }

CodePudding user response：

Sort a range ascending

function sort() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Suivi Clients");
  var range = sheet.getRange("B6:W"   (sheet.getLastRow()-5));
  range.sort({column:7,ascending:true});
}

function onEdit(e) {
  var sh = e.range.getSheet();
  if (sh.getName() == 'Suivi Clients' && e.range.getColumn() == 21) {
    if (e.range.getValue() == 'OUI' || e.range.getValue() == 'ANNULÉ') {
      var dest = e.source.getSheetByName('Clients Finalisé');
      dest.insertRowBefore(7)
      sh.getRange('C'   e.range.rowStart   ':G'   e.range.rowStart).copyTo(dest.getRange("C7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      sh.getRange('L'   e.range.rowStart   ':N'   e.range.rowStart).copyTo(dest.getRange("L7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      sh.getRange('P'   e.range.rowStart).copyTo(dest.getRange("P7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      sh.getRange('U'   e.range.rowStart   ':W'   e.range.rowStart).copyTo(dest.getRange("U7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
      sh.deleteRow(e.range.rowStart);
    }
  }
  if (sh.getName() == 'Clients Finalisé' && e.range.getColumn() == 21 && e.value == 'NON') {
    var dest = e.source.getSheetByName('Suivi Clients')
    dest.insertRowBefore(7);
    sh.getRange('C'   e.range.rowStart   ':G'   e.range.rowStart).copyTo(dest.getRange("C7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    sh.getRange('L'   e.range.rowStart   ':N'   e.range.rowStart).copyTo(dest.getRange("L7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    sh.getRange('P'   e.range.rowStart).copyTo(dest.getRange("P7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false);
    sh.getRange('U'   e.range.rowStart   ':W'   e.range.rowStart).copyTo(dest.getRange("U7"), SpreadsheetApp.CopyPasteType.PASTE_VALUES, false)
    sh.deleteRow(e.range.rowStart);
  }
}

This sort sorts the values in the array and then using setValues it places the values back into the spreadsheet. If you are using formulas in this range then it could damage the formulas. It also assumes that the values in column7 within the range are all dates.

function sort() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName("Sheet0");
  var vs = sheet.getRange("B6:W"   sheet.getLastRow()).getValues();
  vs.sort((a, b) => {
    let vA = new Date(a[5]).valueOf();
    let vB = new Date(b[5]).valueOf();
    return vA - vB;
  })
  sheet.getRange("B6:W"   sheet.getLastRow()).setValues(vs);
}

CodePudding user response：

Try

function sortSC() { // Suivi Clients
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Suivi Clients");
  var range = sheet.getRange("B6:W"   (sheet.getLastDataRow(3)));
  range.sort({column:7,ascending:true});
}

function sortCF() { // Clients Finalisé
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName("Clients Finalisé");
  var range = sheet.getRange("B6:W"   (sheet.getLastDataRow(3)));
  range.sort({column:16,ascending:true});
}

Object.prototype.getLastDataRow = function (col) { 
  var lastRow = this.getLastRow();
  if (col == null) { col = 1 }
  var range = this.getRange(lastRow, col);
  if (range.getValue() !== "") {
    return lastRow;
  } else {
    return range.getNextDataCell(SpreadsheetApp.Direction.UP).getRow();
  }
};