当前位置： 文江博客话题详情

Google表脚本以从列的另一个选项卡中导入的行排序行

发布于 2025-01-23 12:45:39 字数 2527 浏览 2 评论 0原文

我是在Google表中脚本编写的新手，我正在尝试在脚本中添加一个函数，以按列顺序按列值对范围进行排序。

我正在使用一个允许我根据付款状态将行从一个选项卡移到另一个选项卡的脚本。所有导入的行均放在第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客户” Je Voudrais Trier par Colonne g et dans l'Onglet“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);
    }
原文

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

如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

扫码二维码加入Web技术交流群

发布评论

需要 登录 才能够评论， 你可以免费 注册 一个本站的账号。

评论（2）

请别遗忘我 2025-01-30 12:45:39

尝试

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

FYI，完成脚本

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

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

FYI, complete 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())
      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();
  }
};
回复 原文
陌若浮生 2025-01-30 12:45:39

排序范围上升

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将值放回电子表格中。如果您在此范围内使用公式，则可能会损坏公式。它还假设该范围内的第7列中的值都是日期。

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

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);
}
回复 原文
~没有更多了~

关于作者

独享拥抱

暂无简介

文章
评论
28 人气
发私信

相关话题

更多

热门标签

操作系统 程序设计 IT运维 Linux系统管理 JavaScript 服务器应用 solaris C/C++ PHP Shell BSD Vue.js aix Oracle Python HTML 系统管理 HTML5 CSS 前端
更多

推荐作者

櫻之舞

文章 0 评论 0

弥枳

文章 0 评论 0

m2429

文章 0 评论 0

寻找一个思念的角度

文章 0 评论 0

野却迷人

文章 0 评论 0

我怀念的。

文章 0 评论 0

更多

友情链接

文江博客
    我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站，即表示您同意使用 Cookies 和您的相关数据。
    原文