0

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.

3

2 Answers 2

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

Resources

1
  • Thanks. I tried to adapt that code for my sheet. Basically I want all the columns of any row to be bordered whenever a single cell of that row is nonblank. <docs.google.com/spreadsheets/d/…>. For example the range is (B5:H). If any cell within row number 5 is nonblank, then each cell within that row will have borders. Could you please figure it out and help me accomplish that. Thanks Commented Mar 3, 2021 at 6:13
1

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 and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.