如果在Google表格中满足条件，则将边框格式添加到行 [英] Add border format to row if condition met in Google Sheets

问题描述

Excel显然具有这种类型的条件格式内置。

我想通过 Google Apps脚本。以下内容应证明前后条件：

示例数据集

  ABC 
 1苹果Macintosh 
 2苹果格兰尼史密斯
 3橙色佛罗里达州
 4橙色巴伦西亚
 5梨园
 6香蕉Chiquita 
  

结果示例数据集

  ABC 
 1苹果Macintosh 
 2苹果Granny Smith 
 --------------- ------------ 
 3橙色佛罗里达州
 4橙色巴伦西亚
 -------------------- ------- 
 5梨园
 --------------------------- 
 6香蕉Chiquita 
 --------------------------- 
  

此问题的脚本/答案应在以下行的整个行（ A， B和 C列）的下方显示底边框。 ，5和6.电子表格的逻辑非常简单：

1. 在单元格 C1 中， = IF（A1 = A2， NOBORDER， BORDER）


2. 然后将上面的内容包装在 = IF中（C1 = BORDER，addBorder（A1：C1）， NOBORDER）

较大的数据集，这种格式将帮助最终用户更轻松地查看逻辑分组。必须使用Google Apps脚本，因为数据将动态更新。

 函数onEdit（event）{
 var sheet = SpreadsheetApp.getActiveSheet（）; 
 var range = event.range; 
 
 var startingRow = range.getRow（）; 
 var numRows = range.getNumRows（）; 
 for（var i = 1; i< = numRows; i ++）{
 var rowNbr = startingRow + i -1; 
 Logger.log（已更改的行％s，rowNbr）; 
 //现在检查整个行
 var colLeft = 1; // 1 = A 
 var colRight = 11; // 11 = K 
 var row = sheet.getRange（rowNbr，colLeft，1，colRight-colLeft + 1）; 
 if（row.isBlank（））{
 row.setBorder（false，null，true，null，null，null， red，SpreadsheetApp.BorderStyle.SOLID_MEDIUM）； 
} else {
 row.setBorder（false，null，null，null，null，null，null，黑色，SpreadsheetApp.BorderStyle.SOLID_MEDIUM）; 
} 
} 
} 
  

解决方案

在此问题与解答的帮助下找到了答案： https://webapps.stackexchange.com/questions/59484/changing-row-colour-if-value-set?rq=1

此代码测试A列中的值。如果result = TRUE，则在整个行上应用边框：

  function onOpen（）{ 
 GroupMyData（）; //在表单打开
时触发此函数
 
函数GroupMyData（）{
 var ss = SpreadsheetApp.getActiveSpreadsheet（）; 
 var sheet = ss.getSheetByName（’shared’）; //仅适用于工作表名称
 var rows = sheet.getRange（’a1：g’）; //将格式应用于
的范围var numRows = rows.getNumRows（）; //不。 
以上命名范围内的行数var values = rows.getValues（）; //在
以上命名的范围内的值数组var testvalues = sheet.getRange（’a1：a’）。getValues（）; //要测试的值的数组（上述范围的第一列）
 
 rows.setBorder（false，false，false，false，false，false， red，SpreadsheetApp.BorderStyle.SOLID_MEDIUM ）; //在将规则应用到
下面之前删除现有边框//Logger.log(numRows）; 
 
 for（var i = 0; i< = numRows-1; i ++）{
 var n = i + 1; 
 //Logger.log(n）; 
 //Logger.log(testvalues[i]> 0）; 
 //Logger.log(testvalues[i]）; 
 if（testvalues [i]> 0）{//测试应用于值数组
 sheet.getRange（'a'+ n +'：g'+ n）.setBorder（null，null ，true，null，null，null， red，SpreadsheetApp.BorderStyle.SOLID_MEDIUM）； //如果为true，则格式为
} 
} 
}; 
  

Excel apparently has this type of conditional formatting built-in.

I'd like to accomplish the same in Google Sheets via Google Apps Script. The following should demonstrate the before and after conditions:

Example Dataset

        A           B           C
  1   apple     Macintosh
  2   apple     Granny Smith
  3   orange    Florida
  4   orange    Valencia
  5   pear      Garden
  6   banana    Chiquita

Resulting Example Dataset

        A           B           C
  1   apple     Macintosh
  2   apple     Granny Smith
  ---------------------------
  3   orange    Florida
  4   orange    Valencia
  ---------------------------
  5   pear      Garden
  ---------------------------
  6   banana    Chiquita
  ---------------------------

The script/answer to this question should display a bottom border under the entire row (of columns 'A', 'B' & 'C') for rows: 2, 4, 5, & 6. The spreadsheet logic is fairly straightforward:

1. In cell C1, =IF(A1=A2,"NOBORDER","BORDER")
2. Then wrap the above in =IF(C1="BORDER", addBorder(A1:C1), "NOBORDER")

In a larger dataset, this formating will help the end user see logical groupings more easily. A Google Apps Script is required because the data will be updating dynamically.

Below is some sample Google Apps Script code, but it's not working

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = event.range;

  var startingRow = range.getRow();
  var numRows = range.getNumRows();
  for (var i = 1; i <= numRows; i++) {
    var rowNbr = startingRow + i - 1;
    Logger.log("changed row %s",rowNbr );
    //Now check the WHOLE row
    var colLeft=1; //1=A
    var colRight=11; //11=K
    var row = sheet.getRange(rowNbr,colLeft, 1, colRight - colLeft + 1);
    if(row.isBlank()) {
      row.setBorder(false, null, true, null, null, null, "red", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
    } else {
      row.setBorder(false, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
    }
  }
}

解决方案

Figured it out with help from this Q&A: https://webapps.stackexchange.com/questions/59484/changing-row-colour-if-value-set?rq=1

This code tests the values in column A. If result = TRUE, applies a border across that entire row:

function onOpen() {
   GroupMyData(); // trigger this function on sheet opening
}

function GroupMyData() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('shared'); // apply to sheet name only
  var rows = sheet.getRange('a1:g'); // range to apply formatting to
  var numRows = rows.getNumRows(); // no. of rows in the range named above
  var values = rows.getValues(); // array of values in the range named above
  var testvalues = sheet.getRange('a1:a').getValues(); // array of values to be tested (1st column of the range named above)

  rows.setBorder(false, false, false, false, false, false, "red", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // remove existing borders before applying rule below
      //Logger.log(numRows);

  for (var i = 0; i <= numRows - 1; i++) {
      var n = i + 1;
      //Logger.log(n);
      //Logger.log(testvalues[i] > 0);
      //Logger.log(testvalues[i]);
      if (testvalues[i] > 0) { // test applied to array of values
        sheet.getRange('a' + n + ':g' + n).setBorder(null, null, true, null, null, null, "red", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // format if true
      }
  }
};

这篇关于如果在Google表格中满足条件，则将边框格式添加到行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！