谷歌表格腳本按列對從另一個選項卡匯入的行進行排序-有解無憂

我是谷歌表格腳本的新手，我正在嘗試在腳本中添加一個函式，以按列值按升序對范圍進行排序。

我正在使用一個腳本，該腳本允許我根據付款狀態將行從一個選項卡移動到另一個選項卡。所有匯入的行都放在第 7 行，就在標題下方。結果，線條完全凌亂。

我已經嘗試了很多事情，以至于我迷失了。我不知道我是否嘗試了錯誤的功能，或者我是否將它們放在腳本中的錯誤位置。

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.

這是我嘗試過的功能之一：

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

uj5u.com熱心網友回復：

嘗試

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

僅供參考，完整的腳本

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())
      SpreadsheetApp.flush()
      sortCF()
    }
  }

  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())
      SpreadsheetApp.flush()
      sortSC()
    }
  }

  if (sh.getName() != 'Suivi Clients') return;
  var editRange = {
    top: 6,
    left: 3,
    right: 7
  };
  var thisRow = e.range.getRow();
  if (thisRow < editRange.top || thisRow > editRange.bottom) return;
  var thisCol = e.range.getColumn();
  if (thisCol < editRange.left || thisCol > editRange.right) return;
  removeEmpty()
}

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

uj5u.com熱心網友回復：

對范圍進行升序排序

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

此排序對陣列中的值進行排序，然后使用 setValues 將值放回電子表格中。如果您在此范圍內使用公式，則可能會損壞公式。它還假定該范圍內 column7 中的值都是日期。

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

轉載請註明出處，本文鏈接：https://www.uj5u.com/houduan/463909.html

標籤：排序谷歌应用脚本谷歌表格排脚本

上一篇：創建UrlFetchApp腳本以替換GoogleSheetimportHTML函式

下一篇：如何用帶有谷歌腳本的字串替換谷歌檔案中的多行文本