How to make the cells of query function results with borders that are dynamic according to rows/column numbers?

Question

https://docs.google.com/spreadsheets/d/1VsEvt2KnYGnlN_qHjkjPohVHF5MUqfyHD7tSk0VpkqA/edit?usp=sharing

I would like to make the cells that have query results in All_Patients tab with borders instead of just plain results with no formatting. If I preformat the cells with borders, I will have problem with printing because it will print all the cells even empty cells. While printing query results on plain cells will print only the filled cells with results. So when I have query results of 10 rows, I will see 10 rows bordered cells. and the remaining are empty with no borders.

Answer 1

The closest built-in feature is Conditional Formatting unfortunately it doesn't support cell borders.

One option is to use Google Apps Script.

Related

• Automatically add borders around nonempty cells
• Conditionally format row borders

Resources

• https://developers.google.com/apps-script/guides/sheets

Answer 2

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.