单元格更改文本时更改行颜色的脚本 [英] Script to Change Row Color when a cell changes text

问题描述

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

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.

我已经知道Google电子表格已经具有更改文本颜色"的功能，但是该功能仅会更改单元格的颜色，而不会更改整个行的颜色.

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.

推荐答案

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

这篇关于单元格更改文本时更改行颜色的脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！