Google应用程序脚本 - 基于另一个单元格的条件格式 [英] Google App Script - Conditional Formatting Based on Another Cell

问题描述

我试图找出如何通过脚本在谷歌电子表格上使用条件格式，类似于你可以使用条件格式化功能。

我有两个标记为国家和州的列。如果一个单元格中的值是美国，我希望脚本检查相邻的状态列以确保它不为空。如果是这样，我希望单元格背景变成红色。

有没有办法在脚本中做到这一点？我不想使用内置功能，因为它不会复制到电子表格中新创建的工作表，因为我将让其他用户创建新工作表。

我找到了一些参考资料，但是我很难根据自己的需求来定制它们。

链接： Google Spreadsheet条件格式化脚本

https://webapps.stackexchange.com/questions/16745/google-spreadsheets-conditional-formatting-based-on-another-cells-content

尝试复制并粘贴到一个空白的脚本文件中。这取决于列A是国家，列B是国家，但你可以做任意多的工作表，这将自动工作。

让我知道如果你想要一个特定部分的解释，因为我不知道你的脚本背景是什么。

pre $ onEdit函数（e）{

var ss = e.source;
var sheet = ss.getActiveSheet（）;
var range = sheet.getRange（A：B）;
var values = range.getValues（）;

//对于每行存在的数据
for（var i = 0; i< values.length; i ++）{
var cell = sheet.getRange（i + 1,2）;

//检查该行的第一个值是否正好是United States
if（values [i] [0] ===United States）{

//如果是，请检查空白单元格。如果是的话，把它变成红色。
if（values [i] [1] ===）{
cell.setBackground（'red'）;
} else {
cell.setBackground（'white'）;
}

} else {

//在任何其他情况下，使其变为白色。
cell.setBackground（'white'）;
}
}
}


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.

Links: Google Spreadsheet conditional formatting script

https://webapps.stackexchange.com/questions/16745/google-spreadsheets-conditional-formatting-based-on-another-cells-content

解决方案

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应用程序脚本 - 基于另一个单元格的条件格式的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！