Apply multiple different border styles to a cell

[Va no]

Hello.

I'm struggling to understand how `setBorder()` works. What I'm trying to do change border style based on conditions:

1) If a cell is empty, left border should be green, right = red, top/bottom should be none unless there is a non-empty cell above/below (it should not remove border from non-empty cell above/below)

2) if cell is NOT empty, then right = red and top, left, bottom should be black;

I have the following code:

```javascript

function onEdit(e)

{

  const range = e.range,

        ss = e.source,

        sheet = ss.getActiveSheet(),

        col = range.getColumn(),

        row = range.getRow(),

        bool = Boolean(range.getValue()),

        boolTop = Boolean(sheet.getRange(row-1, col).getValue()),

        boolBot = Boolean(sheet.getRange(row+1, col).getValue());

  range.setBorder(bool||boolTop /*top*/, bool /*left*/, bool||boolBot /*bottom*/, bool /*right*/, false /*vertical*/, false /*horizontal*/, "black" /*default color*/, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  if (!bool) /* range is empty */

    range.setBorder(null /*top*/, true /*left*/, null /*bottom*/, null /*right*/, false  /*vertical*/, false  /*horizontal*/, "green" /*color*/, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  range.setBorder(null /*top*/, null /*left*/, null /*bottom*/, true /*right*/, false  /*vertical*/, false  /*horizontal*/, "red" /*color*/, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

}

```

It works fine on non-modified cells, however when I try clear a non-empty cell, it doesn't remove top/bottom borders.

Any suggestions?

Thank you.

Test sheet

[Tanaike]

Although I'm not sure whether I could correctly understand your question, how about the following modification?

- In order to retrieve the boolean type by checking whether a cell is empty, you can use "isBlank()".
- In order to retrieve the above and the below cells, you can use `offset()`.
- In your situation, for example, after it sets "false" to the top and bottom at the 1st "range.setBorder", it is required to use "SpreadsheetApp.flush()". By this, the 2nd and 3rd "range.setBorder" can be worked as you expect.
If these modification points are reflected in your script, it becomes as follows.

function onEdit(e) {
  const range = e.range,

    bool = range.isBlank(),
    boolTop = range.offset(-1, 0).isBlank(),
    boolBot = range.offset(1, 0).isBlank();

  range.setBorder(!bool && boolTop /*top*/, null /*left*/, !bool && boolBot /*bottom*/, null /*right*/, false /*vertical*/, false /*horizontal*/, "black" /*default color*/, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);

  SpreadsheetApp.flush(); // This is an important point.

  range.setBorder(null /*top*/, true /*left*/, null /*bottom*/, null /*right*/, null  /*vertical*/, null  /*horizontal*/, "green" /*color*/, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
 
  SpreadsheetApp.flush(); // This is an important point. // In this case, this line might not be required to be used.

  range.setBorder(null /*top*/, null /*left*/, null /*bottom*/, true /*right*/, null  /*vertical*/, null  /*horizontal*/, "red" /*color*/, SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}

For example, when your script is modified, please put "SpreadsheetApp.flush();" between "range.setBorder" lines.

If I misunderstood your question and that was not useful, I apologize.

[Va no]

Thank you!  SpreadsheetApp.flush() works! So, just out of curiosity, if I want to make 4 sides in different color, I'd need use  SpreadsheetApp.flush() after for each side? It seems to have a side effect, though - the cell border "blinks", and feels slower execution.

P.S. also thank you for the tips

[Tanaike]

Thank you for replying. About `It seems to have a side effect, though - the cell border "blinks", and feels slower execution.`, it seems that this is the current specification for using this. The reason that I cannot propose your expected result is due to my poor skill. I deeply apologize for my poor skill again. I think that I have to study more.