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 cellF3
- notE
, if you want to retrieve the columnE
- it corresponds to the array index4
(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, sorangeData[0][4]
- When you use the method
getRange()
- the row indices start with1
(unlike array elements!!!), so array elementi
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屋!