这就是你可以用着色做的事情的范围。 您也许可以找到一个复杂的公式来找到单元格的最小值，然后如果它与单元格中的内容匹配，则将其着色，但如果这对您来说很重要，您可能需要使用 Excel 而不是 Google 文档。
In the dropdown menu, Format->Conditional formatting...
Then set your rules and your color. You can select multiple cells and do this also.
Edit:
That is the extent of what you can do with coloring. You can maybe find a complex formula to find the minimum of the cells, and then if that matches what is in the cell, then color it, but you may want to use Excel instead of Google docs if this is a critical thing for you.
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
var r = s.getActiveRange();
var row = r.getRow();
var cols = s.getDataRange().getNumColumns();
// crate a range for the row using getRange(row, column, numRows, numColumns)
var rowRange = s.getRange(row, 1, 1, cols);
var rowValues = rowRange.getValues();
// check all the values in the row
var val = 999;
for(var i = 0; i < cols; i++) {
if(val > rowValues[0][i] && rowValues[0][i] !== "") {
val = rowValues[0][i];
}
}
for(var j = 0; j < cols; j++) {
if(rowValues[0][j] === val) {
s.getRange(row,(j + 1)).setFontColor("blue");
} else {
s.getRange(row,(j + 1)).setFontColor("black");
}
}
}
The trick is to tap into the onEdit event trigger and add some intelligence
At first glance I thought conditional formatting would work but the minimum-per-row is a little too complex for the standard conditional formatting. It's a little tricky to figure out but it can be done.
Here's the full script (tested and working):
function onEdit() {
var s = SpreadsheetApp.getActiveSheet();
var r = s.getActiveRange();
var row = r.getRow();
var cols = s.getDataRange().getNumColumns();
// crate a range for the row using getRange(row, column, numRows, numColumns)
var rowRange = s.getRange(row, 1, 1, cols);
var rowValues = rowRange.getValues();
// check all the values in the row
var val = 999;
for(var i = 0; i < cols; i++) {
if(val > rowValues[0][i] && rowValues[0][i] !== "") {
val = rowValues[0][i];
}
}
for(var j = 0; j < cols; j++) {
if(rowValues[0][j] === val) {
s.getRange(row,(j + 1)).setFontColor("blue");
} else {
s.getRange(row,(j + 1)).setFontColor("black");
}
}
}
First you tap into the onEdit event handler to trigger the script with the spreadsheet changes.
function onEdit()
By naming a function onEdit it will automatically know that you want to override the onEdit action.
Note: Event handlers in Docs are a little tricky though. Because docs can handle multiple simultaneous edits by multiple users, the event handlers are handled server-side. The major issue with this structure is that when an event trigger script fails, it fails on the server. If you want to see the debug info you'll need to setup an explicit trigger under the triggers menu that emails you the debug info when the event fails or else it will fail silently.
Fetch the row number:
var r = s.getActiveRange();
var row = r.getRow();
Pretty self explanatory here. The active range is the cell being edited.
Grab the number of columns:
var cols = s.getDataRange().getNumColumns();
You need to check the data range for the whole spreadsheet for this.
Next you need to construct a data range containing the data for the row in question:
var rowRange = s.getRange(row, 1, 1, cols);
Read the comments in the code to see what the values should be.
Then we cache the results for testing of values:
var rowRange = s.getRange(row, 1, 1, cols);
Due to the nature of Google Docs scripting event callbacks run server-side so, to prevent abuse, Google puts a time limit on script execution. By caching the values, you save the server from making lots of unnecessary round trips to fetch the values from the spreadsheet.
The next two parts are where all the magic happens.
First, we make a pass through all the cells of the row to find the minimum value.
var val = 999;
for(var i = 0; i < cols; i++) {
if(val > rowValues[0][i] && rowValues[0][i] !== "") {
val = rowValues[0][i];
}
}
I set a default ceiling of 999 for the sake of simplicity. This could be change to a more appropriate value. The trick is to test the value vs the current low. If it's lower, then mark the new low value.
You could get off easy by marking the cell number with the lowest value and setting it explicitly but I wanted to cover cases where multiple cells have the lowest value.
Handling multiple cells containing the minimum requires a second pass:
The loop through all the cells in the row remains the same. This time we're just checking whether the cell values match the chosen minimum. If it matches, the font color changes to blue. Otherwise, the font color is changed to black.
That about sums it up. It's a little tricky to get used to the manner that Google Apps Scripting deals with referencing spreadsheets and data cells but there's not much that Docs can't do.
I have made a public link to the spreadsheet I used to write/test this code. Feel free to try it out.
发布评论
评论（2）
在下拉菜单中，格式->条件格式...
然后设置规则和颜色。 您也可以选择多个单元格并执行此操作。
编辑：
这就是你可以用着色做的事情的范围。 您也许可以找到一个复杂的公式来找到单元格的最小值，然后如果它与单元格中的内容匹配，则将其着色，但如果这对您来说很重要，您可能需要使用 Excel 而不是 Google 文档。
In the dropdown menu, Format->Conditional formatting...
Then set your rules and your color. You can select multiple cells and do this also.
Edit:
That is the extent of what you can do with coloring. You can maybe find a complex formula to find the minimum of the cells, and then if that matches what is in the cell, then color it, but you may want to use Excel instead of Google docs if this is a critical thing for you.
诀窍是利用 onEdit 事件触发器并添加一些智能
乍一看，我认为条件格式可以工作，但每行最小值对于标准条件格式来说有点太复杂了。 弄清楚有点棘手，但可以做到。
这是完整的脚本（已测试且有效）：
首先，您点击 onEdit 事件处理程序以通过电子表格更改触发脚本。
通过将函数命名为 onEdit，它会自动知道您想要覆盖 onEdit 操作。
注意：文档中的事件处理程序有点棘手。 由于文档可以处理多个用户同时进行的多个编辑，因此事件处理程序是在服务器端处理的。 这种结构的主要问题是，当事件触发脚本失败时，它在服务器上也会失败。 如果您想查看调试信息，则需要在触发器菜单下设置一个显式触发器，以便在事件失败时通过电子邮件向您发送调试信息，否则它将默默失败。
获取行号：
非常不言自明这里。 活动范围是正在编辑的单元格。
获取列数：
为此，您需要检查整个电子表格的数据范围。
接下来，您需要构建一个包含相关行数据的数据范围：
阅读代码中的注释以查看值应该是什么。
然后我们缓存结果以测试值：
由于 Google Docs 脚本事件回调在服务器端运行的性质，因此为了防止滥用，Google 对脚本执行设置了时间限制。 通过缓存这些值，您可以使服务器免于进行大量不必要的往返以从电子表格中获取值。
接下来的两部分是所有魔法发生的地方。
首先，我们遍历该行的所有单元格以找到最小值。
为了简单起见，我将默认上限设置为 999。 这可以更改为更合适的值。 诀窍是测试该值与当前低点的关系。 如果较低，则标记新的低值。
您可以通过将单元格编号标记为最低值并明确设置它来轻松完成，但我想涵盖多个单元格具有最低值的情况。
处理包含最小值的多个单元格需要第二遍：
遍历行中所有单元格的循环保持不变。 这次我们只是检查单元格值是否与所选的最小值匹配。 如果匹配，字体颜色将变为蓝色。 否则，字体颜色将更改为黑色。
这大约总结了这一点。 习惯 Google Apps 脚本处理引用电子表格和数据单元格的方式有点棘手，但 Docs 没有什么做不到的。
我已创建一个公共链接我用来编写/测试此代码的电子表格。 请随意尝试一下。
The trick is to tap into the onEdit event trigger and add some intelligence
At first glance I thought conditional formatting would work but the minimum-per-row is a little too complex for the standard conditional formatting. It's a little tricky to figure out but it can be done.
Here's the full script (tested and working):
First you tap into the onEdit event handler to trigger the script with the spreadsheet changes.
By naming a function onEdit it will automatically know that you want to override the onEdit action.
Note: Event handlers in Docs are a little tricky though. Because docs can handle multiple simultaneous edits by multiple users, the event handlers are handled server-side. The major issue with this structure is that when an event trigger script fails, it fails on the server. If you want to see the debug info you'll need to setup an explicit trigger under the triggers menu that emails you the debug info when the event fails or else it will fail silently.
Fetch the row number:
Pretty self explanatory here. The active range is the cell being edited.
Grab the number of columns:
You need to check the data range for the whole spreadsheet for this.
Next you need to construct a data range containing the data for the row in question:
Read the comments in the code to see what the values should be.
Then we cache the results for testing of values:
Due to the nature of Google Docs scripting event callbacks run server-side so, to prevent abuse, Google puts a time limit on script execution. By caching the values, you save the server from making lots of unnecessary round trips to fetch the values from the spreadsheet.
The next two parts are where all the magic happens.
First, we make a pass through all the cells of the row to find the minimum value.
I set a default ceiling of 999 for the sake of simplicity. This could be change to a more appropriate value. The trick is to test the value vs the current low. If it's lower, then mark the new low value.
You could get off easy by marking the cell number with the lowest value and setting it explicitly but I wanted to cover cases where multiple cells have the lowest value.
Handling multiple cells containing the minimum requires a second pass:
The loop through all the cells in the row remains the same. This time we're just checking whether the cell values match the chosen minimum. If it matches, the font color changes to blue. Otherwise, the font color is changed to black.
That about sums it up. It's a little tricky to get used to the manner that Google Apps Scripting deals with referencing spreadsheets and data cells but there's not much that Docs can't do.
I have made a public link to the spreadsheet I used to write/test this code. Feel free to try it out.