Automatically add borders around nonempty cells

Question

I'm looking to have cell borders automatically applied when a cell is not empty. I thought I could use conditional formatting for this, but it doesn't seem to be an option.

Answer 1

This is possible with a script ("Tools > Script editor"), using setBorder method. Here is a little demo script

function onEdit() {
  var cell = SpreadsheetApp.getActiveRange();
  if (cell.getValue() !== "") {
    cell.setBorder(true, true, true, true, true, true);
  }
  else {
    cell.setBorder(false, false, false, false, false, false);
  }
}

This runs on every edit, puts borders on the active cell/range if it's nonempty, removes them otherwise.

However, the above logic is too simple. If a cell is made blank by an edit, but has non-blank neighbors, some borders should be kept. Here is a version that addresses this by removing all borders and repainting them around non-blank cells.

function onEdit() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  range.setBorder(false, false, false, false, false, false);
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (values[i][j] !== "") {
        range.getCell(i + 1, j + 1).setBorder(true, true, true, true, true, true);
      }
    }
  }
}

But the constant flashing of all borders is annoying and slow and annoying. It's better to have this painting job invoked manually, via a menu. Here's my final version of the script: it adds a menu item "Custom > Borders for nonempty cells" whenever the spreadsheet is opened.

function onOpen() {
 var menu = [{name: "Borders for nonempty cells", functionName: "borders"}];
 SpreadsheetApp.getActiveSpreadsheet().addMenu("Custom", menu);
}

function borders() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  range.setBorder(false, false, false, false, false, false);
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    for (var j = 0; j < values[i].length; j++) {
      if (values[i][j] !== "") {
        range.getCell(i + 1, j + 1).setBorder(true, true, true, true, true, true);
      }
    }
  }
}

Answer 2

I could do it just by using conditional formatting in MS Excel and then importing the book to Google sheets.

It keeps the conditional formatting of the borders as long as I don't touch conditions for formatting. In such a case, conditional formatting stops working for the border lines.

It's a implementation Google should correct.

ADDED: I confirm the above and have put a simple example here That sheet is view-only, so make a copy first into your own Google account so you can then experiment with it as follows:

• Cell is empty: no border, no fill
• Cell contains the word "dotted": dotted black border, no fill
• Cell contains the word "solid": solid black border, no fill
• Cell contains an ODD numeric: solid blue border, yellow fill
• Cell contains an EVEN numeric: solid red border, green fill

I first set it up in Excel with the above four (well, five, if you count the null case) conditional formatting options, and then simply loaded it into Google Sheets where it continued to obey the formatting rules (despite the fact that when I looked at the rules in Google Sheets, there was, as per usual, no mention of borders!)

Answer 3

I could Modify the code to the following:

 var menu = [{name: "Draw Borders", functionName: "borders"}];
 SpreadsheetApp.getActiveSpreadsheet().addMenu("Borders", menu);
}

function borders() {
  var range = SpreadsheetApp.getActive().getSheetByName('Tab_Name').getDataRange();
  var cell = SpreadsheetApp.getActive().getSheetByName('Tab_Name').getRange("B50:H200")
  cell.setBorder(false, false, false, false, false, false);
  var values = range.getValues();
  for (var i = 2; i < values.length; i++) {
    for (var j = 1; j < values[i].length; j++) {
      
        range.getCell(i + 1, j + 1).setBorder(true, true, true, true, true, true);
      }
    }
 
  }

cell variable is to clear borders of cells every time the tab is changed. Problem I found when you don't set "cell" when your initial data has 100 rows for example then you edit and change the data to 40 rows, The borders will be cleared for the new data range only while the remaining range from rows 41 to 100 will still have the borders. So I set the cell value to the minimum that worked for me because I will never get data less than 50 rows. you could modify that according to your need.

Variable i is the rows. I don't want the borders to start from row 1 because I have preformatted cell which is unchangeable (title of page). Variable j is column number which I have column 1 is static as well.