8

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.

4

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

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.

1
  • Thanks. I tried to adapt that code for my sheet. Basically I want all the rows to be bordered whenever a single cell of that row was edited or nonempty. <docs.google.com/spreadsheets/d/…>. Could you please figure it out – Hassan Almaateeq Mar 3 at 6:09
0

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy