Adding borders based on grouped rows and conditional formatting in Google Sheets

Question

I have a sheet that looks like this:

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:

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.

EDIT:

Links to the spreadsheets:

Before (What I have): https://docs.google.com/spreadsheets/d/1r3ybiy5Gaw7SYDZlYA68HmvPCCoxI-Bz7qpPCSK146A/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:

• If a row does not have a name, we assume it to be a variant of the closest row going up that has a product name.
• If two rows have the same product name, we obviously assume them to be variants of the same product.

Answer 1

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. Script is automatically run by OnEdit event trigger, when the cell is edited.
2. Retrieve the sheet name.
3. Clear all borders.
4. Create a range list for adding the border.
5. Add the border using the range list.

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

Sample script:

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);
  }
}

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") {.

References:

• Simple Triggers
• Class RangeList
• setBorder()

Edit:

• Your 1st shared Spreadsheet is different from your actual Spreadsheet.
  • The shared Spreadsheet in your reply comment is the actual Spreadsheet.
• You want to check the column "C" and add the border from the column "A" to "P".
• Data starts from row 3.

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

From:

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

To:

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

And

From:

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

To:

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