根据Google表格中的分组行和条件格式添加边框 [英] Adding borders based on grouped rows and conditional formatting in Google Sheets

问题描述

我有一张看起来像这样的工作表:

I have a sheet that looks like this:

如您所见，它是一个简单的库存跟踪系统.现在，我有3个产品.某些产品实际上并没有不同的颜色(例如Tacky Design Tee)，因此对于这些产品，变体(颜色)保持为空.我面临的一个问题是，由于某些单元格保持为空，因此很难真正理解我所在的行.理想情况下，我希望电子表格看起来像这样:

As you can see, it is a simple inventory tracking system. Right now, I have 3 products. Some products don't really have different colors (like Tacky Design Tee), so for those, the variant (color) is kept empty. One issue I faced is that it gets a bit difficult to actually understand which row I'm in because some cells are kept empty. Ideally, I would want the spreadsheet to look like this:

带有边框的产品之间有明显的分隔.我希望这是动态的.逻辑可能来自B列不为空，并且紧随其后的所有行，其中B为空，这些行被视为一组并在底部加边框.

With the borders, there is a clear separation between the products. I want this to be dynamic. The logic might come from the B column not being empty, and all the rows right after it where B is empty, those rows are treated as a group and bordered on the bottom.

是否有一种方法可以使我每次添加新产品时都进行相应的分组?感谢您的帮助.

Is there a way to do this in a way so that whenever I add a new product, it gets grouped accordingly? Thanks for any help.

链接到电子表格:

之前(我所拥有的): https://docs.google.com/spreadsheets/d/1r3ybiy5Gaw7SYDZlYA68HmvPCCoxI-Bz7qpPCSK146A/edit?usp = sharing

Before (What I have): https://docs.google.com/spreadsheets/d/1r3ybiy5Gaw7SYDZlYA68HmvPCCoxI-Bz7qpPCSK146A/edit?usp=sharing

之后(基本上是我想要的): https://docs.google.com/spreadsheets/d/1sS3Y_MH4DaYD4QW19vjwExd7jc4H-eB5OAGb3J4njUQ/edit?usp = sharing

After (Basically what I want): https://docs.google.com/spreadsheets/d/1sS3Y_MH4DaYD4QW19vjwExd7jc4H-eB5OAGb3J4njUQ/edit?usp=sharing

简而言之，我想根据以下两个条件根据产品名称对产品进行分组:

In short, I want to group products based on their names based on these 2 conditions:

• 如果某行没有名称，我们认为它是具有产品名称的最近一行的变体.
• 如果两行具有相同的产品名称，我们显然会认为它们是同一产品的变体.

推荐答案

该示例脚本如何?在此示例脚本中，当您在"Sheet1"工作表中编辑单元格时，OnEdit事件触发器将动态添加边框.请认为这只是几个答案之一.该脚本的流程如下.

How about this sample script? In this sample script, when you edit a cell in the sheet of "Sheet1", the border is dynamically added by the OnEdit event trigger. Please think of this as just one of several answers. The flow of this script is as follows.

1. 在编辑单元格时，脚本会由OnEdit事件触发器自动运行.
2. 获取工作表名称.
3. 清除所有边界.
4. 创建一个范围列表以添加边框.
5. 使用范围列表添加边框.

我使用上面的流程是因为我不确定编辑单元格的情况.

I used above flow, because I'm not sure about the situation of editing cells.

请复制以下脚本并将其粘贴到电子表格的容器绑定脚本中(在这种情况下，请将其粘贴到之前"电子表格中.).当您编辑单元格时，会添加边框.

Please copy and paste the following script to the container-bound script of the Spreadsheet (in this case, please do this to "Before" Spreadsheet.). When you edit a cell, the border is added.

function onEdit(e) {
  if (e.source.getActiveSheet().getSheetName() == "Sheet1") {
    var sheet = e.source.getActiveSheet();
    sheet.getRange(1, 1, sheet.getMaxRows(), sheet.getMaxColumns()).setBorder(null, null, null, null, false, false);
    var values = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1).getValues();
    var rangeList = values.reduce(function(ar, e, i) {
      if (i > 0 && values[i - 1][0] != e[0] && e[0] != "") {
        ar.push("A" + (i + 1) + ":D" + (i + 1));
      }
      return ar;
    }, [])
    rangeList.push(sheet.getRange(sheet.getLastRow(), 1, 1, sheet.getLastColumn()).getA1Notation());
    sheet.getRangeList(rangeList).setBorder(null, null, true, null, false, false, "black", SpreadsheetApp.BorderStyle.SOLID_THICK);
  }
}

注意:

• 在此示例脚本中，当编辑的工作表名称为"Sheet1"时，将运行该脚本.如果要修改此内容，请修改if (e.source.getActiveSheet().getSheetName() == "Sheet1") {.

Note:

• In this sample script, when the edited sheet name is "Sheet1", the script is run. If you want to modify this, please modify if (e.source.getActiveSheet().getSheetName() == "Sheet1") {.
• 简单触发器
• Class RangeList
• setBorder()
• Simple Triggers
• Class RangeList
• setBorder()
• 您的第一个共享电子表格不同于您的实际电子表格.
  • 您的回复评论中共享的电子表格就是实际的电子表格.
  • Your 1st shared Spreadsheet is different from your actual Spreadsheet.
    • The shared Spreadsheet in your reply comment is the actual Spreadsheet.

    如果我的理解正确，请按如下方式修改上面的脚本.

    If my understanding is correct, please modify above script as follows.

    var values = sheet.getRange(2, 2, sheet.getLastRow() - 1, 1).getValues();
    

    收件人:

    var values = sheet.getRange(3, 3, sheet.getLastRow() - 1, 1).getValues();
    

    还有

    if (i > 0 && values[i - 1][0] != e[0] && e[0] != "") {
      ar.push("A" + (i + 1) + ":D" + (i + 1));
    }
    

    收件人:

    if (i > 0 && values[i - 1][0] != e[0] && e[0] != "") {
      ar.push("A" + (i + 2) + ":P" + (i + 2));
    }
    

    这篇关于根据Google表格中的分组行和条件格式添加边框的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！