google-sheets - 具有合并下拉菜单的 Google 电子表格动态条件格式

这个问题的核心是合并单元格的操作。当一个单元格被合并时，比如多行，只有合并单元格左上角的单元格可以包含数据、响应条件格式等等。从某种意义上说，其他单元格不再存在，并且不能为它们分配值。

提问者问：
问：有没有办法以有效的方式为合并单元格的所有单元格赋予合并单元格的值？
答：不。不仅仅是“高效”的方式；这是不可能的。

问：有没有更好的方法让我的条件格式代码适用于合并的单元格？
答：否，是的；）
否。就合并单元格而言，一切都由合并范围顶部单元格中的值驱动。合并单元格的“其余”没有其他选项。
是的。我会在 F 列中创建一个“帮助”单元格，如此屏幕截图所示

实现这一点的代码是动态的——它会自动适应添加更多产品、更多项目等。

逻辑相当简单：从F2开始，测试E2是否有值（即是否是合并单元格的顶部？）。如果是，则将 E2 的值分配给 F2 并将该值放入以下单元格的变量中。如果否，则 E 列中的单元格必须是合并单元格的一部分，因此将 F 列的值分配给之前保存的变量。

function so5270705902() {

    // basic declarations
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    // note this is going to work on the second sheet in the spreadsheet - this can be edited.
    var sheet = ss.getSheets()[1];

    // Column B contains no merged cells, and always contains data (it is the BOM for the Products).
    // so we'll use it to established the last row of data.
    var Bvals = sheet.getRange("B1:B").getValues();
    var Blast = Bvals.filter(String).length;

    // Row 1 is a header row, so data commences in Row 2 - this can be edited
    var dataStart = 2;
    // Logger.log("the last row in column D = "+Blast);// DEBUG


    // set up to loop through the rows of Column F
    var mergedcellvalue = "";

    for (i = dataStart; i < (Blast + 1); i++) {

        // set the range for the row
        var range = sheet.getRange(i, 6);
        //Logger.log("row#"+i+" = "+range.getA1Notation()); DEBUG


        // get the value in column E
        var ECell = range.offset(0, -1);
        var ECellVal = ECell.getValue();
        //Logger.log("offsetrange#"+i+" range value = "+ECellVal);
        //Logger.log("Column E, row#"+i+", value = "+ECell.getA1Notation()+" range value = "+ECellVal);//DEBUG

        // when a row is merged, on the top row contains any data
        // so we'll evaluate to see whether there is any value in this row in Column E
        if (ECell.isBlank()) {

            //Logger.log("ECell is blank. We're in the middle of the Merged Cell"); ??DEBUG

            // Set the value to the lastes value of "mergedcellvalue"
            range.setValue(mergedcellvalue);

        } else {

            //Logger.log("ECell has a value. We're at the top of the merged cell");//DEBUG

            // paste the ECellVal into this range
            range.setValue(ECellVal);

            // Update the "mergedcellvalue" variable so that it can be applied against lower cells of this merged cell
            mergedcellvalue = ECellVal;

        } // end of the if isblank

    } // end of the loop through column F

}

2018 年 10 月 22 日更新

出于开发目的，我在 E 列中仅使用了 14 行的一小部分。但是提问者的数据涵盖了 250 多行，因此我将开发测试扩展到了 336 行（是的，我知道，但我是复制/粘贴并结束了最多 336 并且懒得删除任何行。好吗？）。我发现代码处理时间超过 81 秒。不好。

处理时间长的主要原因（大约 80 秒）是getValue循环中有一条语句 - var ECellVal = ECell.getValue();. 每个实例花费大约 0.2 秒。包含getValue在循环中是一个典型的性能错误。我的错。所以我修改了代码以在循环之前
var Evals = sheet.getRange("e2:E").getValues();获取 E 列的值。

当执行时间保持在相同的标记附近时，我感到很惊讶。原因是isBlank评估 - if (ECell.isBlank()) {以前根本不需要时间，现在每个实例消耗 @0.2 秒。不好++。所以在搜索 Stack Overflow 后，我将这一行修改如下
if (!Evals[(i-dataStart)][0]) {：

包含setValues在循环中也是自找麻烦。一种选择是将值写入数组，然后在循环之后用数组更新列 E 的值。但是在这种情况下，执行时间似乎没有受到影响，我将离开setValues循环内部。

通过这两项更改，总执行时间现在为 1.158 秒。那是减少的百分比，嗯，很多。

function so5270705903() {

    // basic declarations
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    // note this is going to work on the second sheet in the spreadsheet - this can be edited.
    var sheet = ss.getSheets()[2];

    // Column B contains no merged cells, and always contains data (it is the BOM for the Products).
    // so we'll use it to established the last row of data.
    var Bvals = sheet.getRange("B1:B").getValues();
    var Blast = Bvals.filter(String).length;

    // Row 1 is a header row, so data commences in Row 2 - this can be edited
    var dataStart = 2;
    // Logger.log("the last row in column D = "+Blast);// DEBUG


    // set up to loop through the rows of Column F
    var mergedcellvalue = "";

    // get the values for Column E BEFORE the loop
    var Evals = sheet.getRange("e2:E").getValues();

    for (i = dataStart; i < (Blast + 1); i++) {



        // set the range for the row
        var range = sheet.getRange(i, 6);
        //Logger.log("row#"+i+" = "+range.getA1Notation()); DEBUG


        // get the value in column E
        var ECell = range.offset(0, -1);

        var ECellVal = Evals[(i - dataStart)][0];

        //Logger.log("Column E, row#"+i+", value = "+ECell.getA1Notation()+" range value = "+ECellVal);//DEBU

        // when a row is merged, on the top row contains any data
        // so we'll evaluate to see whether there is any value in this row in Column E
        // instead is isblank, which was talking 0.2 seconds to evaluate, this if is more simple
        if (!Evals[(i - dataStart)][0]) {

            //Logger.log("ECell is blank. We're in the middle of the Merged Cell"); //DEBUG

            // Set the value to the lastes value of "mergedcellvalue"
            range.setValue(mergedcellvalue);

        } else {

            //Logger.log("ECell has a value. We're at the top of the merged cell");//DEBUG

            // paste the ECellVal into this range
            range.setValue(ECellVal);

            // Update the "mergedcellvalue" variable so that it can be applied against lower cells of this merged cell
            mergedcellvalue = ECellVal;

        } // end of the if isblank

    } // end of the loop through column F

}

2019 年 3 月 3 日更新

提问者对代码进行了最后的修改。此代码是最终解决方案。

function reloadCreatedCells() {

  // Basic declarations.
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  // Note this is going to work on the second sheet in the spreadsheet - this can be edited.
  var sheet = ss.getSheets()[1];

  // Column B contains no merged cells, and always contains data (it is the BOM for the Products).
  // so we'll use it to established the last row of data.
  var D_vals = sheet.getRange("D1:D").getValues();
  var D_last = D_vals.filter(String).length;

  // First row with data.
  var dataStart = 2;

  // Set up to loop through the rows of Column H - K.
  var mergedcellvalue = "";

  // Get the values for Column H - K BEFORE the loop.
  var H_K_vals = sheet.getRange("H2:K").getValues();

  // How many people we have.
  var people = 4;

  // The first vertical row.
  var rowStart = 12;

  // Horizontal rows.
  for (var h = 0; h < people; h++) {

    // Vertical rows.
    for (var v = dataStart; v < D_last; v++) {

      // Set the range for the row.
      var range = sheet.getRange(v, rowStart + h);
      // Logger.log(range.getA1Notation()); //DEBUG

      // Get the value in column H - K.
      var H_K_Cell = range.offset(0, -people);

      // Adding Created and not created values inside L - O.
      var H_K_CellVal = H_K_vals[(v - dataStart)][h];
      // Logger.log(H_K_Cell.getA1Notation() + ': ' + H_K_CellVal); //DEBUG

      // When a row is merged, the value is only inside the top row.
      // Therefore, you need to check if the value is empty or not.
      // If the value is empty. Place the top value of the merged cell inside the empty cell.
      if (!H_K_vals[(v - dataStart)][h]) {
        // Logger.log(H_K_Cell.getA1Notation() + ": is blank. We're below the top cell of the merged cell."); //DEBUG

        // Set the value to the top cell of the merged cell with "mergedcellvalue".
        range.setValue(mergedcellvalue);

      } else {
        // Logger.log(H_K_Cell.getA1Notation() + ": has a value. We're at the top of the merged cell."); //DEBUG

        // Paste the H_K_CellVal into this range.
        range.setValue(H_K_CellVal);

        // Update the "mergedcellvalue" variable, so that it can be applied against lower cells of this merged cell.
        mergedcellvalue = H_K_CellVal;

      } // end of the if isblank.

    } // End of the vertical row loop.
  } // End of the horizontal row loop.
}

google-sheets - 具有合并下拉菜单的 Google 电子表格动态条件格式

问题描述

解决方案

2018 年 10 月 22 日更新

2019 年 3 月 3 日更新

推荐阅读