Google脚本根据数据变化对行颜色进行了更改，对工作表进行了排序 [英] Google script change row color based on data change, sheet sorted

问题描述

链接到演示表

我有一个按E列排序的工作表.E列中的值可以重复，这意味着几行在E列中可以具有相同的值. 现在，我想给表示列E中的更改的行上色. 这是我基于StackOverflow的一些出色答案而构建的代码:

I have a sheet which is sorted by column E. Values in column E can be duplicate, which means that several rows can have the same value in column E. Now I want to color rows which signify a change in column E. Here's the code that I've built based on some great answers on StackOverflow:

function quicktest() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var rangeData = spreadsheet.getDataRange().getValues();
  var lastRow = spreadsheet.getLastRow();
  //  var searchRange = spreadsheet.getRange(1, 1, lastRow-1, 5);
//  var rangeValues = searchRange.getValues();
  var previousclient = rangeData[2][5];
  console.log(previousclient);
  for ( j = 1 ; j < lastRow - 1; j++){
    var currentclient = rangeData[j][5];
    console.log(previousclient," ",currentclient);
    if (previousclient != currentclient) {
      spreadsheet.getRange(j,1,1,5).setBackground("#cc4125");
      previousclient = currentclient;  
    }; 
}

代码运行，但是结果是它正在绘画".错误的单元格，它也没有绘制整行(A到H列)，而只是绘制A到C. 我在这里也阅读了一些建议将值发送到rangeValues的答案，但这对我没有用.这就是为什么它在代码中被列为注释.也许我确实需要使用它. 这是我运行示例代码后想要演示工作表的样子:

The code runs, but the result is that it's "painting" the wrong cell and it's also not painting the entire row (columns A to H), it's painting just A to C. I've also read some answers here that recommend sending the values to rangeValues, but that didn't work me. That's why it's listed as a comment in the code. Perhaps I do need to use it. Here's how I'd like to demo sheet to look like after running this code:

谢谢

推荐答案

小心索引-数组以[0]开头

各自的混乱会导致代码中出现多个问题:

Be careful with indices - arrays start with [0]

The respective confusion leads to several problems in your code:

• rangeData[2][5]表示单元格F3-而不是E，如果要检索列E-它对应于数组索引4(既适用于先前客户端，也适用于当前客户端！)
• 鉴于最初的previousclient不应设置为彩色(仅以下一个)，应将其设置为标题行，因此rangeData[0][4]
• 使用方法getRange()时-行索引以1开头(与数组元素不同！)，因此数组元素i对应于行i+1

• rangeData[2][5] means cell F3 - not E, if you want to retrieve the column E - it corresponds to the array index 4 (both for previous and current client!)
• Given that the initial previousclient is not suposed to be colored (only the following onew) you should set it to the header row, so rangeData[0][4]
• When you use the method getRange() - the row indices start with 1 (unlike array elements!!!), so array element i corresponds to the rowi+1

要解决这些问题，请按如下所示修改您的代码:

To fix those issue modify your code as following:

function change_row_color() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var rangeData = spreadsheet.getDataRange().getValues();
  var lastRow = spreadsheet.getLastRow();
  var previousclient = rangeData[0][4];
  console.log(previousclient);
  for ( j = 1 ; j < lastRow - 1; j++){
    var currentclient = rangeData[j][4];
    console.log(previousclient," ",currentclient);
    if (previousclient != currentclient) {
      spreadsheet.getRange(j+1,1,1,5).setBackground("#cc4125");
      previousclient = currentclient;  
    };
  }}

更新

对包括SpreadsheetApp方法在内的外部服务的调用应当最小化，因为它们会使您的代码变慢.

Calls to external services including SpreadsheetApp methods should be minimized because they make your code slower.

因此，例如在每个循环内调用setBackground会导致更长的执行时间.
如果您只想使用 setBackgrounds 设置一次颜色，的setBackground，您可以这样操作:

So, for example calling setBackground within each loop will result in a longer execution time.
If you want to set the colors only once with setBackgrounds instead of setBackground, you can do it like this:

function change_row_color() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');
  var range = spreadsheet.getDataRange();
  var rangeData = range.getValues();
  var lastRow = spreadsheet.getLastRow();
  var previousclient = rangeData[0][4];
  console.log(previousclient);
  var colors = [[0,0,0,0,0]];
  for ( j = 1 ; j < lastRow; j++){
    var currentclient = rangeData[j][4];
    console.log(previousclient," ",currentclient);
    if (previousclient != currentclient) {
      colors.push(["#cc4125","#cc4125","#cc4125","#cc4125","#cc4125"]);
      previousclient = currentclient;  
    } else{
      colors.push([0,0,0,0,0]);
    }
  }
range.setBackgrounds(colors);
}

哪种方法会更快取决于您的数据大小和重复项的数量.

Either this approach will be faster strongly depends on your data size and amount of duplicates.

这篇关于Google脚本根据数据变化对行颜色进行了更改，对工作表进行了排序的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！