CODEWITHSUNDEEP
google-sheetsconditional-formatting
biahuosisnice
biahuosisnice

Reputation: 49

Automatically add border to row if cell isn't empty

I want to know please how to make a border conditional formatting in google sheets

if my sheet is like this :

enter image description here

I want it to become like this whenever something is edited

enter image description here

PS : My sheet is importing data from another sheet I hope this won't create a problem on the function applying

Upvotes: 1

Views: 3426

Answers (2)

carlesgg97
carlesgg97

Reputation: 4460

Using Google Apps Script, you can create a function that:

  1. Removes any borders in your sheet.

  2. Finds all the empty rows that should have borders applied.

  3. Sets the borders to those rows.

The following code does what is described above:

function setSheetBorders() {
  var ss = SpreadsheetApp.getActive();
  var sheet = ss.getSheetByName('Sheet1');

  // Remove all borders
  var dataRange = sheet.getDataRange();
  dataRange.setBorder(false, false, false, false, false, false);

  var firstColumn = sheet.getRange("A:A").getValues();
  var ranges = [];

  for (var i=1; i<firstColumn.length; i++) {
    if (firstColumn[i][0]) {
      ranges.push("A" + i + ":" + i);
    }
  }

  sheet.getRangeList(ranges).setBorder(false, false, true, false, false, false, "black",
                                       SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
}

Triggering the script's execution

In regards to executing this script, the ideal situation would be to have it automatically executed when the sheet is edited. This can be done using an onEdit() trigger. Setting it up would be as simple as creating the following function in the script bound to the sheet:

function onEdit(e) {
  setSheetBorders();
}

However, this will only be executed when the user is the one editing the sheet, and not when a script edits it.

Since the modification of the Sheets document is for visualisation purposes, I suggest you instead use an onOpen() trigger. This will cause the script above to be executed whenever a user accesses the document.

function onOpen(e) {
  setSheetBorders();
}

Furthermore, you can also consider creating a custom menu. This will allow you to manually trigger the script whenever you want to using the UI.

function onOpen() {
  var ui = SpreadsheetApp.getUi();
  ui.createMenu('Custom Menu')
      .addItem('Set Sheet Borders', 'setSheetBorders')
      .addToUi();

  setSheetBorders();
}

example menu

Upvotes: 2

leo
leo

Reputation: 88

As far as I know, there is no way to "detect" changes and use conditional formatting to reflect that. The closest thing to that would be to make an IF statement and do something like shown below in the custom formula section of the conditional formatting menu.

=B1=67890779

or this for checking multiple cells

=B1=67890779=B2=86687585

To my knowledge that is the closest way to "detect" changes

EDIT: I was reminded that there are no options to change borders in conditional formatting.
My only idea for that would be to make rows small enough to look like borders and use conditional formatting to change the background of the cells.

Hope This Helps

Upvotes: 0

Related Questions