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

问题描述：

我有一个 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;
    } 
  }
}

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

相关推荐