根据单元格值设置行颜色的格式 [英] Format row color based on cell value

问题描述

我正在尝试修改示例脚本这个以前的相关问题。对于列K中的单元格值为零的行，我想让这一行变为黄色。

这是我现在改编的代码：

  function colorAll（）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var startRow = 3; 
 var endRow = sheet.getLastRow（）; 
 
 for（var r = startRow; r <= endRow; r ++）{
 colorRow（r）; 
 
 
 
 function colorRow（r）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var c = sheet.getLastColumn（）; 
 var dataRange = sheet.getRange（r，1，1，c）; 
 
 var data = dataRange.getValue（）; 
 var row = data [0]; 
 
 if（row [0] ===0）{
 dataRange.setBackground（white）; 
} else {
 dataRange.setBackground（yellow）; 
} 
 
 SpreadsheetApp.flush（）; 
 
 
 function onEdit（event）
 {
 var r = event.source.getActiveRange（）。getRowIndex（）; 
 if（r> = 3）{
 colorRow（r）; 
 
 
 
函数onOpen（）{
 colorAll（）; 
 
 $ / code> 

我的问题是，我无法弄清楚如何引用K列。在上面的链接答案中，该脚本的创建者声称： [h] ere是一个基于列A中的值更改整行的背景颜色的Google Apps脚本示例。首先，最重要的是，我不知道他在哪里引用列A.我认为改变var dataRange = sheet.getRange（r，1，1，c）;到var dataRange = sheet.getRange（r， 11 ，1，c）;会做到这一点，但只是在我的工作表的末尾添加了10个空白列，然后脚本崩溃了。第二，但更重要的是，他声称脚本影响整行是不准确的，因为他原来的var dataRange = sheet.getRange （r，1,1，3）;只为前三列着色 - 这就是为什么我添加var c并将3更改为c。

此外，当我播放/调试脚本，或者从电子表格脚本管理器运行onEdit，我会得到 TypeError：无法从undefined 读取属性source。我可以看到源是未定义的 - 我错误地认为这是一种方法 - 但我不知道如何解决这个问题。

最后，K列并不总是引用列，因为我的意思是在其左侧添加更多列。我假设每次添加列时都必须更新脚本，但第2行中的列标题永远不会更改，所以如果有人可以帮助我设计一些代码来查找行中的特定字符串2，然后获取在colorRow（）函数中使用的列引用，我将不胜感激。

我无法判断这个脚本的结构是否有效，但理想情况下，我希望我的电子表格是被动的 - 我不想在这个脚本之后重新运行这个脚本编辑驾驶室，或者打开时;它看起来像它应该这样做（是不是越野车），但这是我第一次尝试使用谷歌应用程序脚本，我不觉得确定任何东西。

我对脚本编程并不擅长，但我在2006年修读了一所编程基础/ Python课程，并在Excel&在此之后不久，通常会创建和调整宏。我不能从头开始设计，但我理解基本原则和概念，即使我不能翻译所有内容（例如，我不明白for中第三个参数中++意味着什么，我使用的语句是：for（var r = startRow; r <= endRow; r ++ ）。我认为我的寓意等同于一位有文字的西班牙语演讲者试图阅读意大利文。 p>

帮助和教育解释/例子，将会非常感谢，非常感谢您阅读/撇取/跳过这句话。

解决方案

与其重写你已经获得一些帮助的代码，我会尝试给你解释你提出的具体问题。我看到你有一些答案已经存在，但我正在把事情完全放进去，因为它有助于理解。

我的问题是，我无法弄清楚如何引用列K 。

列A = 1，B = 2，... K = 10.

 我不知道他在哪里引用了列A. 
  

当您更改.getRange时，您已经很近了。 .getRange根据（）中有多少个参数来做不同的事情。有4个参数，它是getRange（row，column，numRows，numColumns）。

$ p $ sheet.getRange（r，1， c）//第一个'1'引用列从行（r）开始，它是最初的行（r）行（3）和列（1）。所以这是细胞（A3）。范围延伸1行和（c）列。由于c = sheet.getLastColumn（），这意味着您已将范围设置为1行和所有列。

当您将此更改为

  var dataRange = sheet.getRange（r，11，1，c）//'11'引用列L 
 如果你超出范围，这将会造成奇怪的结果。 
您可能已将它推入无限循环，这会导致脚本崩溃。其次，但更多的是作为除此之外，他声称脚本影响整行是不准确的，因为他的原始var dataRange = sheet.getRange（r，1，3，）; 
仅列出前三列 - 这就是为什么我添加了var c并将3更改为c。
 
 
 
 
你是对的。 （3）表示范围扩展为3列。
 TypeError：无法从undefined读取属性source。 
  
这里发生的事情并不直观。您无法从电子表格脚本管理器运行函数onEdit（event），因为它期望发生事件。
 
 
 
 
 
 onEdit是一个每次编辑电子表格时都会运行一个特殊的google触发器。
 
 
传递激活它的（event）和
 
 event.source。指事件发生的表单。
 
 
 var r = event.source.getActiveRange（）。getRowIndex（）;获取发生编辑的行号，即将要更改其颜色的行。
 
 
 
 
 
如果您在经理没有事件要阅读，因此未定义。你不能调试它，要么出于同样的原因。
 
 
最后，K列并不总是参考列，因为我的意思是
在其左侧添加更多列。我假设每次添加列时都必须更新
脚本，但行
 2中的列标题永远不会更改，所以如果有人可以帮助我设计一些
代码，它将在第2行中查找特定的字符串，然后获得用于函数colorRow（）中的
列引用，我将非常感谢
 it。 
  blockquote> 
 
 
在我给你提供代码帮助之前，我有一个替代建议，因为你也在讨论效率，在电子表格中运行函数通常比使用脚本更快。您可以尝试将列A作为列ColumnA（Row＃）= ColumnK（Row＃）的索引列。如果将以下内容放入单元格（A1）中，ColumnA将与列K完全匹配。
  = ArrayFormula（K： K）
  
更好的是，如果添加/删除A和K之间的列，则公式将更改其没有你做任何事情的参考。现在只需隐藏columnA，然后您的工作表返回到其创建者外观。
 
 
 
这是您的代码帮助，利用您自己的代码。
 函数findSearchColumn（）{
 var colNo; //这是我们正在寻找的。 
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var c = sheet.getLastColumn（）; 
 
 //以数组格式获取第二行的值
 var values = sheet.getRange（2，1，1，c）.getValues（）; 
 //返回一个二维数组值，按行索引，然后按列索引。 
 
 //我们将通过值[0] [col]进行搜索，因为只有一行
（var col = 0; col< data [0] .length;如果（data [0] [col] == value）{
 colNo = col; col ++）{// data [0] .length should = c 
 if 
休息; //我们不需要在这里做更多的事情。 
} 
} 
 return（colNo）; 
} 
  
如果break给你一个问题，只需删除它并让外观完整或替换它与col = data [0] .length; 
 
 
 
 
我不能说这个脚本的结构是否有效，但理想情况下，I 
希望我的电子表格被反应 - 我不希望在编辑驱动单元格后或打开时重新运行
此脚本;它读取
就像它应该这样做（它不是越野车），但这是我的
首次尝试使用Google Apps脚本，并且我不确定任何
的东西。 / p> 
 
效果的微调取决于电子表格。函数onEdit（event）
将在每次编辑表单时运行，对此您无能为力。然而，它应该做的第一件事是检查相关范围已被编辑。 
这条线if（r> = 3）好像在做这个。您可以根据需要制定具体的细节。 
我对隐藏索引列的建议旨在提高效率，并且更容易实现。
 
 
 
 我不是很棒使用脚本， 
 
 
 
你做得不错，但可以做一些背景阅读，只需查看循环等内容即可。不幸的Python在语法上与许多其他语言不同。谷歌脚本中的for循环与VBA，C，JAVA等相同。所以阅读这些基本的操作实际上是教你关于许多语言。
 
 
 
 我不明白++意味着什么for语句中的参数 
这就是C ++语言作为程序员笑话获取其名称的原因。
 
 
 
 r ++是就像r = r + 1 
 
 r--意味着r = r-1 
 
 r + 2意味着r = r + 2  
 
 
因此 
 
 
  for（var r = startRow; r <= endRow; r ++）
  
 
 
 
 
 
表示r开始为startRow，在这种情况下为3。
 
 
循环将一直运行，直到r <= endRow，在本例中为sheet.getLastRow（）
 
 
每次循环运行后r增加1，因此如果endRow == 10，循环将从r = 3运行到r = 10 => 8次。
 
 
I am trying to adapt the example script from this previous, related question.  For rows where the cell value in column K is zero, I want to make the row yellow.

Here is my current adapted code:
function colorAll() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var startRow = 3;
  var endRow = sheet.getLastRow();

  for (var r = startRow; r <= endRow; r++) {
    colorRow(r);
  }
}

function colorRow(r){
  var sheet = SpreadsheetApp.getActiveSheet();
  var c = sheet.getLastColumn();
  var dataRange = sheet.getRange(r, 1, 1, c);

  var data = dataRange.getValue();
  var row = data[0];

  if(row[0] === "0"){
    dataRange.setBackground("white");
  }else{
    dataRange.setBackground("yellow");
  }

  SpreadsheetApp.flush(); 
}

function onEdit(event)
{
  var r = event.source.getActiveRange().getRowIndex();
  if (r >= 3) {
    colorRow(r);
  }
}

function onOpen(){
  colorAll();
}
My problem is, I can't figure out how to reference column K.  In the linked answer above, the script's creator claims, "[h]ere is a Google Apps Script example of changing the background color of an entire row based on the value in column A."  First, and most importantly, I can't figure out where he's referencing column A.  I thought changing "var dataRange = sheet.getRange(r, 1, 1, c);" to "var dataRange = sheet.getRange(r, 11, 1, c);" would do it, but that just added 10 blank columns to the end of my sheet, and then the script crashed.  I do not understand why.

Secondly, but more as an aside, his claim that the script affects entire rows is inaccurate, as his original "var dataRange = sheet.getRange(r, 1, 1, 3);" only colored the first three columns - which is why I added "var c" and changed "3" to "c".

Furthermore, when I play/debug the script, or run "onEdit" from the spreadsheet script manager, I get "TypeError: Cannot read property "source" from undefined."  I can see that "source" is undefined - I had mistakenly assumed it was a Method at first - but I'm not sure how to fix this issue either.  

Lastly, column K will not always be the reference column, as I mean to add more columns to the left of it.  I assume I'll have to update the script every time I add columns, but there is a column heading in row 2 that will never change, so if someone can help me devise a bit of code that will look for a specific string in row 2, then get that column reference for use in function colorRow(), I would appreciate it. 

I can't tell if this script is structured efficiently, but ideally, I want my spreadsheet to be reactive - I don't want to have to rerun this script after editing a driving cell, or upon opening; it reads like it's supposed to do that (were it not buggy), but this is my first attempt at using Google Apps Script, and I don't feel certain of anything.

I'm not great with scripting, but I took a programming fundamentals/Python class in grad school back in 2006, and spent 4 years working with Excel & Access shortly after that, often creating and adapting Macros.  I can't really design from scratch, but I understand the basic principles and concepts, even if I can't translate everything (e.g., I don't understand what the "++" means in the third argument in the "for" statement I'm using: "for (var r = startRow; r <= endRow; r++)."  I think I'm allegorically equivalent to a literate Spanish speaker trying to read Italian.

Help, and educational explanations/examples, will be much appreciated.  Thank you kindly for reading/skimming/skipping to this sentence.
解决方案
Rather than rewriting the code which you have already got some help with, I will try to give you explanations to the specific questions you asked.  I see that you have some of the answers already but I am putting thing in completely as it helps understanding.

My problem is, I can't figure out how to reference column K.

Column A = 1, B = 2,... K = 10. 
I can't figure out where he's referencing column A.
You were close when you altered the .getRange.  .getRange does different things depending on how many arguments are in the ().  With 4 arguments it is getRange(row, column, numRows, numColumns).
sheet.getRange(r, 1, 1, c)  // the first '1' references column A
starts at row(r) which is initially row(3), and column(1).  So this is cell(A3).  The range extends for 1 row and (c) columns.  As c = sheet.getLastColumn(), this means you have taken the range to be 1 row and all the columns.

When you changed this to
var dataRange = sheet.getRange(r, 11, 1, c)  // the '11' references column L
You have got a range starting at row(3) column(L) as 11 = L.  This runs to row(3) column(getLastColumn()).
This is going to do weird things if you have gone out of range.
You may have pushed it in to an infinite for loop which would cause the script to crash

  
Secondly, but more as an aside, his claim that the script affects entire rows is inaccurate, as his original "var dataRange = sheet.getRange(r, 1, 1, 3);"
   only colored the first three columns - which is why I added "var c" and changed "3" to "c".
You are correct.  The (3) says that the range extend for 3 columns.
"TypeError: Cannot read property "source" from undefined."
What is happening here is not intuitively clear.  You can't run the function onEdit(event) from the spreadsheet script manager because it is expecting an "event".


onEdit is a special google trigger that runs whenever any edits the spreadsheet.
it is passed the (event) that activated it and 
event.source. refers to the sheet where the event happened so
var r = event.source.getActiveRange().getRowIndex(); gets the row number where the edit happened, which is the row that is going to have its color changed.


If you run this in the manager there is no event for it to read, hence undefined.  You can't debug it either for the same reasons.

  
Lastly, column K will not always be the reference column, as I mean to
  add more columns to the left of it. I assume I'll have to update the
  script every time I add columns, but there is a column heading in row
  2 that will never change, so if someone can help me devise a bit of
  code that will look for a specific string in row 2, then get that
  column reference for use in function colorRow(), I would appreciate
  it.
Before I give you code help her, I have an alternative suggestion because you are also talking about efficiency and it is often faster to run functions in the spreadsheet than using scripts.  You could try having column A as an index columns where ColumnA(Row#) = ColumnK(Row#).  If you put the following into cell(A1), ColumnA will be an exact match of Column K. 
=ArrayFormula(K:K) 
Even better, if you add/remove Columns between A and K, the formula will change its reference without you doing anything. Now just hide columnA and your sheet is back to its originator appearance.

Here is your code help, utilizing some of your own code.
function findSearchColumn () {
  var colNo;  // This is what we are looking for.
  var sheet = SpreadsheetApp.getActiveSheet();
  var c = sheet.getLastColumn();

  // gets the values form the 2nd row in array format
  var values = sheet.getRange(2, 1, 1, c).getValues();
  // Returns a two-dimensional array of values, indexed by row, then by column.

  // we are going to search through values[0][col] as there is only one row
  for (var col = 0; col < data[0].length; col++) { // data[0].length should = c
    if (data[0][col] == value) {
      colNo = col;
      break; // we don't need to do any more here.
    }
  }
  return(colNo);
}
If break gives you a problem just delete it and let the look complete or replace it with col = data[0].length;

  
I can't tell if this script is structured efficiently, but ideally, I
  want my spreadsheet to be reactive - I don't want to have to rerun
  this script after editing a driving cell, or upon opening; it reads
  like it's supposed to do that (were it not buggy), but this is my
  first attempt at using Google Apps Script, and I don't feel certain of
  anything.
It is ok, the fine tuning of efficiency depends on the spreadsheet.  function onEdit(event)
 is going to run every time the sheet is edited, there is nothing you can do about that.  However the first thing it should do is check that a relevant range has been edited.
The line if (r >= 3) seems to be doing that.  You can make this as specific as you need.
My suggestion on a hidden index column was aimed a efficiency as well as being much easier to implement.

I'm not great with scripting,

You are doing ok but could do with some background reading, just look up things like for loops.  Unfortunate Python is grammatically different from many other languages.  A for loop in google script is the same as VBA, C, JAVA, and many more.  So reading about these basic operations is actually teaching you about many languages.

I don't understand what the "++" means in the third argument in the "for" statement
It is why the language C++ gets its name, as a programmer joke.

r++ is the same as saying r = r+1

r-- means r = r-1

r+2 means r = r+2

So 
for (var r = startRow; r <= endRow; r++)



means r begins as startRow, which in this case is 3.
the loop will run until r <= endRow, which in this case is sheet.getLastRow()
after each time the loop runs r increments by 1, so if endRow == 10, the loop will run from r = 3 to r = 10 => 8 times


                        
这篇关于根据单元格值设置行颜色的格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！