Google App Script - 基于另一个单元格的条件格式 [英] Google App Script - Conditional Formatting Based on Another Cell
问题描述
我正在尝试弄清楚如何通过 google 电子表格上的脚本使用条件格式,类似于您可以使用条件格式功能执行的操作.
I am trying to figure out how to use conditional formatting via script on a google spreadsheet similar to what you can do with the conditional formatting feature.
我有两列分别标为国家"和州".如果一个单元格中的值为美国",我希望脚本检查相邻的州列以确保它不是空白的.如果是,我希望单元格背景变为红色.
I have two columns labeled 'Country' and 'State.' If the value in one cell is 'United States,' I want the script to check the adjacent state column to make sure it's not blank. If it is, I want the cell background to change to red.
有没有办法在脚本中做到这一点?我不想使用内置功能,因为它不会复制到电子表格中新创建的工作表,因为我会让其他用户创建新工作表.
Is there any way to do this in a script? I don't want to use the built in feature as it doesn't copy over to newly create sheets within the spreadsheet as I'll be having other users creating new sheets.
我找到了一些参考资料,但我无法根据自己的需要定制它们.
I found some references, but I'm having trouble tailoring them to my needs.
推荐答案
尝试将其复制并粘贴到空白脚本文件中.这取决于 A 列是 Country,B 列是 State,但您可以根据需要制作任意数量的工作表,这将自动工作.
Try copy and pasting this into a blank script file. This depends on column A being Country, and column B being State, but you can make as many sheets as you want, and this will work automatically.
如果您需要对特定部分的解释,请告诉我,因为我不确定您的脚本背景是什么.我在代码中添加了一些注释.
Let me know if you want an explanation of a specific part, as I'm not sure what your scripting background is. I've included some comments in the code.
function onEdit(e) {
var ss = e.source;
var sheet = ss.getActiveSheet();
var range = sheet.getRange("A:B");
var values = range.getValues();
//for each row that data is present
for(var i = 0; i < values.length; i++) {
var cell = sheet.getRange(i + 1, 2);
//check if the first value of that row is exactly "United States"
if(values[i][0] === "United States") {
//if it is, check for a blank cell. If so, make it red.
if(values[i][1] === "") {
cell.setBackground('red');
} else {
cell.setBackground('white');
}
} else {
//In any other case, make it white.
cell.setBackground('white');
}
}
}
这篇关于Google App Script - 基于另一个单元格的条件格式的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!