2

I'd like to have a medium black top border across each row whose A column cell contains text in a Google Spreadsheet.

I'd also like to have the same top border across each row whose whole row is blank.

I understand Conditional Formatting doesn't include border formatting as it does in Excel, and I am as of yet unfamiliar with scripting in this web app.

Would someone be able to explain the process to generate this please?

2

The process that would generate a script that makes mentioned conditional formatting includes of course what you mentioned you are unfamiliar with: scripting.

A) Learn the basics of google apps script bound to google sheets. This is just a small part of apps script and this is good starting point. Most of the basics can be learned while trying to build your formatting script but you have to want to learn scripting.

B) In the sheets document, open the script editor and define the following function:

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = event.range;
  var adress= range.getA1Notation();
  Logger.log("change cell %s", adress);
  sheet.getRange('F2').setValue(adress);
}

C) Install a trigger on your spreadsheet that triggers our 'onEdit' function when the sheet is edited. See the official HOWTO. Test that this simple trigger and code works. Also make sure you understand what the code expresses.

D) Write the code corresponding to the logic:

FOR EACH ROW in event.range() {
  IF( your condition for formatting) THEN 
    var myRange= sheet.getRange(rownr, 1,1,nbrOfColumns)
    myRange.setBorder();
  ENDIF
}

Note that the former is not apps script but pseudocode explaining the logic. This translates into the following apps script:

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = event.range;

  var startingRow=range.getRow();
  var numRows = range.getNumRows();
  for (var i = 1; i <= numRows; i++) {
    var rowNbr=startingRow+i-1;
    Logger.log("changed row %s",rowNbr );
    //Now check the WHOLE row
    var colLeft=1; //1=A
    var colRight=10; //10=J
    var row = sheet.getRange(rowNbr,colLeft, 1, colRight-colLeft+1);
    if(row.isBlank()) {
      row.setBorder(false, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
    } else {
      row.setBorder(true, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
    }
  }
}

Small variation: iso 1 row the code underneath simply formats a single cell:

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSheet();
  var range = event.range;
  var adress= range.getA1Notation();
  Logger.log("changed cell %s", adress);
  sheet.getRange('F2').setValue(adress);

  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      var currentValue = range.getCell(i,j).getValue();
      Logger.log("changed cell %s", range.getCell(i,j).getA1Notation() );
      if(currentValue != "") {
        Logger.log("positive on %s", range.getCell(i,j).getA1Notation());
        range.getCell(i,j).setBorder(true, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
      } else {
        range.getCell(i,j).setBorder(false, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM);
      }
    }
  }
}
  • Admittedly I did ask for help on the process, because I'm interested in how things like this work. But at this point, I'm just trying to improve a workflow of a job I'm doing as fast as I can instead of trying to learn script in depth, which this requires. I gave some of that article and the corresponding example a read, but it's too far deep for me to be able to grasp or spend time on currently. Any help on a functional script would be really appreciated. Thank you! – etudes Oct 16 '17 at 7:24
  • Actually, I did do some more reading and tried to fill in the blanks, but I just don't grasp how this all works. But here's what I filled in (and failed to yield an executable result): { IF(NOT(ISBLANK(A2))) THEN var myRange = sheet.getRange(1, 1, 1, 13) myRange.setBorder(true, null, null, null, null, null, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); ENDIF } – etudes Oct 16 '17 at 10:07
  • @etudes I updated the answer with more details. This should allow you to copy paste the necessary code, – Maarten Troost Oct 17 '17 at 16:14
  • 2
    thank you so much for your help. this doesn't quite work how I intended – this seems to simply add a top border across the row of any cell that gets edited – but in the end, I was able to work out a solution that didn't require borders. This did help me understand the scripting process a bit better, too, so I really appreciate it. I'll go ahead and green checkmark your solution. – etudes Oct 17 '17 at 17:48

Your Answer

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

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