Google Sheets Conditional Formatting of Borders Only for Visible Rows (not filtered rows)

Question

I'm trying to add dynamic horizontal borders to visually group rows. Here is an example sheet that shows what I'm trying to do. Basically, I need to apply a horizontal border whenever the value in column A changes, but I want to apply this to only the visible rows.

I used this function which works well assuming all rows are visible, but doesn't work if you filter out or hide one of the rows where the value changes:

function myFunction() {
  const sheetNames = ["Grade 6","Grade 7","Grade 8","Algebra 1","Algebra 2","Geometry"]; // Please set the sheet names.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  sheetNames.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    const range = sheet.getRange("A2:A" + sheet.getLastRow());
    const { ranges } = range.getValues().reduce((o, [a], i) => {
      if (i == 0) {
        o.temp = a;
      } else if (i > 0 && o.temp != a) {
        o.ranges.push(`A${i + 2}:BY${i + 2}`);
        o.temp = a;
      }
      return o;
    }, { ranges: [], temp: "" });
    sheet.getRange("A2:BY").setBorder(false, null, false, null, null, false);
    sheet.getRangeList(ranges).setBorder(true, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID);
  });
}

I'd like the border to appear between the visible rows, even when filtered or hidden (for example, in the "Desired Output" sheet where rows with "C" are filtered). Does anyone have any reasonably simple way to help achieve this? I'm pretty new with scripts so I'm trying to piece it together in a way that allows me to edit it myself.

Answer 1

From your showing script and your provided Spreadsheet, I guessed that you might have used my sample script. If my understanding is correct, in order to achieve your expected result, how about the following modification?

Modified script:

function myFunction() {
  const sheetNames = ["Grade 6","Grade 7","Grade 8","Algebra 1","Algebra 2","Geometry"]; // Please set the sheet names.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  sheetNames.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    const range = sheet.getRange("A2:A" + sheet.getLastRow());
    const { ranges } = range.getValues().reduce((o, [a], i) => {
      if (i == 0) {
        o.temp = a;
      } else if (i > 0 && o.temp != a && !sheet.isRowHiddenByFilter(i + 2)) { // Modified
        o.ranges.push(`A${i + 2}:BY${i + 2}`);
        o.temp = a;
      }
      return o;
    }, { ranges: [], temp: "" });
    sheet.getRange("A2:BY").setBorder(false, null, false, null, null, false);
    sheet.getRangeList(ranges).setBorder(true, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID);
  });
}

• When I tested this modified script to your provided Spreadsheet, I confirmed that the same result with "Desired Output" could be obtained as follows.

  

• In your showing script, sheet.getRange("A2:BY").setBorder(false, null, false, null, null, false); is used. If you want to achieve the same situation with "Desired Output" sheet, you might want to remove sheet.getRange("A2:BY").setBorder(false, null, false, null, null, false);.

Reference:

• isRowHiddenByFilter(rowPosition)