当单元格更改文本时更改行颜色的脚本

发布于 2024-09-19 05:54:34 字数 187 浏览 6 评论 0原文

我有一个 Google 电子表格,其中保存了错误列表,每当我修复错误时,我都会将状态从“未开始”更改为“完成”。我想为 Google Docs 电子表格编写一个脚本,这样每当我将状态更改为“完成”时,整行都会以某种颜色突出显示。

我已经知道 Google 电子表格已经具有“更改文本颜色”功能,但该功能仅更改单元格的颜色,而不会更改整行的颜色。

I have a Google spreadsheet where I keep a list of bugs and whenever I fix a bug I change the status from "Not Started" to "Complete". I want to write a script for the Google Docs spreadsheet such that whenever I change the status to "Complete" the entire row gets highlighted in a certain color.

I already know that Google spreadsheet already has "change color on text" but that function only changes the color of the cell and does not change the color of the entire row.

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

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

发布评论

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

评论(5

爱的十字路口 2024-09-26 05:55:56

我认为假设 Status 列是 ColumnS 会更简单(尽管没有脚本)。

选择 ColumnS 并从中清除格式。选择要格式化的整个范围,然后“格式”、“条件格式...”、“如果...则设置单元格格式”自定义公式为 和:

=and($S1<>"",search("Complete",$S1)>0)

选择填充并完成

这不区分大小写(将 search 更改为 find),并且会突出显示 ColumnS 包含类似 Now Complete 的行(尽管也尚未完成)。

I think simpler (though without a script) assuming the Status column is ColumnS.

Select ColumnS and clear formatting from it. Select entire range to be formatted and Format, Conditional formatting..., Format cells if... Custom formula is and:

=and($S1<>"",search("Complete",$S1)>0)

with fill of choice and Done.

This is not case sensitive (change search to find for that) and will highlight a row where ColumnS contains the likes of Now complete (though also Not yet complete).

骄兵必败 2024-09-26 05:55:45

user2532030的答案是正确且最简单的答案。

我只是想补充一点,在确定单元格的值不适合正则表达式匹配的情况下,我发现以下语法的工作原理相同,仅适用于数值、关系等:

[Custom formula is]
=$B$2:$B = "Complete"
Range: A2:Z1000

如果列任何行的 2(脚本中的第 2 行,但前导 $ 表示,这可以是任何行)文本上等于“完整”,对整个工作表的范围执行 X(不包括标题行(即从 A2 而不是 A1 开始) )。

但显然,此方法还允许进行数值运算(即使这不适用于 op 的问题),例如:

=$B$2:$B > $C$2:$C

因此,如果任何行中的 col B 值高于 col C 值,则执行一些操作。

最后一件事:
最有可能的是,这仅适用于我,但我很愚蠢,反复忘记在下拉列表中选择自定义公式,而将其保留在文本包含。显然,这不会浮...

user2532030's answer is the correct and most simple answer.

I just want to add, that in the case, where the value of the determining cell is not suitable for a RegEx-match, I found the following syntax to work the same, only with numerical values, relations et.c.:

[Custom formula is]
=$B$2:$B = "Complete"
Range: A2:Z1000

If column 2 of any row (row 2 in script, but the leading $ means, this could be any row) textually equals "Complete", do X for the Range of the entire sheet (excluding header row (i.e. starting from A2 instead of A1)).

But obviously, this method allows also for numerical operations (even though this does not apply for op's question), like:

=$B$2:$B > $C$2:$C

So, do stuff, if the value of col B in any row is higher than col C value.

One last thing:
Most likely, this applies only to me, but I was stupid enough to repeatedly forget to choose Custom formula is in the drop-down, leaving it at Text contains. Obviously, this won't float...

哆啦不做梦 2024-09-26 05:55:34

我使用了GENEGC的脚本,但我发现它很慢。

它很慢,因为它在每次编辑时都会扫描整张纸。

所以我为自己写了更快、更干净的方法,我想分享它。

function onEdit(e) {
    if (e) { 
        var ss = e.source.getActiveSheet();
        var r = e.source.getActiveRange(); 

        // If you want to be specific
        // do not work in first row
        // do not work in other sheets except "MySheet"
        if (r.getRow() != 1 && ss.getName() == "MySheet") {

            // E.g. status column is 2nd (B)
            status = ss.getRange(r.getRow(), 2).getValue();

            // Specify the range with which You want to highlight
            // with some reading of API you can easily modify the range selection properties
            // (e.g. to automatically select all columns)
            rowRange = ss.getRange(r.getRow(),1,1,19);

            // This changes font color
            if (status == 'YES') {
                rowRange.setFontColor("#999999");
            } else if (status == 'N/A') {
                rowRange.setFontColor("#999999");
            // DEFAULT
            } else if (status == '') { 
                rowRange.setFontColor("#000000");
            }   
        }
    }
}

I used GENEGC's script, but I found it quite slow.

It is slow because it scans whole sheet on every edit.

So I wrote way faster and cleaner method for myself and I wanted to share it.

function onEdit(e) {
    if (e) { 
        var ss = e.source.getActiveSheet();
        var r = e.source.getActiveRange(); 

        // If you want to be specific
        // do not work in first row
        // do not work in other sheets except "MySheet"
        if (r.getRow() != 1 && ss.getName() == "MySheet") {

            // E.g. status column is 2nd (B)
            status = ss.getRange(r.getRow(), 2).getValue();

            // Specify the range with which You want to highlight
            // with some reading of API you can easily modify the range selection properties
            // (e.g. to automatically select all columns)
            rowRange = ss.getRange(r.getRow(),1,1,19);

            // This changes font color
            if (status == 'YES') {
                rowRange.setFontColor("#999999");
            } else if (status == 'N/A') {
                rowRange.setFontColor("#999999");
            // DEFAULT
            } else if (status == '') { 
                rowRange.setFontColor("#000000");
            }   
        }
    }
}
荒路情人 2024-09-26 05:55:24

意识到这是一个旧线程,但在看到很多这样的脚本后,我注意到您可以仅使用条件格式来完成此操作。

假设“状态”是D列:

突出显示单元格>右键单击>条件格式。
选择“自定义公式为”并将公式设置为

=RegExMatch($D2,"Complete")

=OR(RegExMatch($D2,"Complete"),RegExMatch( $D2,"complete"))

编辑(感谢 Frederik Schøning)

=RegExMatch($D2,"(?i)Complete") 然后设置范围以覆盖所有行,例如 A2:Z10。这不区分大小写,因此将匹配complete、Complete 或CoMpLeTe。

然后您可以添加“未开始”等其他规则。$ 非常重要。它表示绝对引用。如果没有它,单元格 A2 将查看 D2,但 B2 将查看 E2,因此任何给定行上的格式都会不一致。

Realise this is an old thread, but after seeing lots of scripts like this I noticed that you can do this just using conditional formatting.

Assuming the "Status" was Column D:

Highlight cells > right click > conditional formatting.
Select "Custom Formula Is" and set the formula as

=RegExMatch($D2,"Complete")

or

=OR(RegExMatch($D2,"Complete"),RegExMatch($D2,"complete"))

Edit (thanks to Frederik Schøning)

=RegExMatch($D2,"(?i)Complete") then set the range to cover all the rows e.g. A2:Z10. This is case insensitive, so will match complete, Complete or CoMpLeTe.

You could then add other rules for "Not Started" etc. The $ is very important. It denotes an absolute reference. Without it cell A2 would look at D2, but B2 would look at E2, so you'd get inconsistent formatting on any given row.

单身狗的梦 2024-09-26 05:55:12
//Sets the row color depending on the value in the "Status" column.
function setRowColors() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var statusColumnOffset = getStatusColumnOffset();

  for (var i = range.getRow(); i < range.getLastRow(); i++) {
    rowRange = range.offset(i, 0, 1);
    status = rowRange.offset(0, statusColumnOffset).getValue();
    if (status == 'Completed') {
      rowRange.setBackgroundColor("#99CC99");
    } else if (status == 'In Progress') {
      rowRange.setBackgroundColor("#FFDD88");    
    } else if (status == 'Not Started') {
      rowRange.setBackgroundColor("#CC6666");          
    }
  }
}

//Returns the offset value of the column titled "Status"
//(eg, if the 7th column is labeled "Status", this function returns 6)
function getStatusColumnOffset() {
  lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
  var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);

  for (var i = 0; i < range.getLastColumn(); i++) {
    if (range.offset(0, i, 1, 1).getValue() == "Status") {
      return i;
    } 
  }
}
//Sets the row color depending on the value in the "Status" column.
function setRowColors() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var statusColumnOffset = getStatusColumnOffset();

  for (var i = range.getRow(); i < range.getLastRow(); i++) {
    rowRange = range.offset(i, 0, 1);
    status = rowRange.offset(0, statusColumnOffset).getValue();
    if (status == 'Completed') {
      rowRange.setBackgroundColor("#99CC99");
    } else if (status == 'In Progress') {
      rowRange.setBackgroundColor("#FFDD88");    
    } else if (status == 'Not Started') {
      rowRange.setBackgroundColor("#CC6666");          
    }
  }
}

//Returns the offset value of the column titled "Status"
//(eg, if the 7th column is labeled "Status", this function returns 6)
function getStatusColumnOffset() {
  lastColumn = SpreadsheetApp.getActiveSheet().getLastColumn();
  var range = SpreadsheetApp.getActiveSheet().getRange(1,1,1,lastColumn);

  for (var i = 0; i < range.getLastColumn(); i++) {
    if (range.offset(0, i, 1, 1).getValue() == "Status") {
      return i;
    } 
  }
}
~没有更多了~
我们使用 Cookies 和其他技术来定制您的体验包括您的登录状态等。通过阅读我们的 隐私政策 了解更多相关信息。 单击 接受 或继续使用网站,即表示您同意使用 Cookies 和您的相关数据。
原文