google sheet script border by color

Question

I would like to have borders on rows where the first cell of a row has a specific color.

I found this script, and tried modifying to to my needs.. but without success.


function border1() {
  var sheet1 = SpreadsheetApp.getActiveSheet();
  var range = sheet1.getRange("A1:B50");
  var backgrounds = range.getBackgrounds();
  var counter = 0;
  var gray = "#efefef";
  for (var i = 0; i < backgrounds.length; i++){
    if(backgrounds[i]== gray)
     //counter++;
      range.setBorder(true, false, true, true, true, true);      
  }
  Logger.log(counter);  
     //range.setBorder(true, false, true, true, true, true);  

}

The problem is that when I run this, all my cells are borderd instead of the row with the specific color. I think the command range.setborder has to refer to the cells with the background -->> (backgrounds[i]) ???

score 2 · Answer 1 · answered Jul 28 '22 at 18:47

Set Border to solid red when color of first cell is yellow

function border1() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const bgs = sh.getRange("A1:A" + sh.getLastRow()).getBackgrounds().flat();
  bgs.forEach((b, i) => {
    if (b == "#ffff00") {
      sh.getRange(i + 1,1,1,sh.getLastColumn()).setBorder(true,true,true,true,false,false,"#ff0000",SpreadsheetApp.BorderStyle.SOLID_THICK);
    }
  })
}

score 1 · Accepted Answer · answered Jul 29 '22 at 00:25

I believe your goal is as follows.

You want to apply the borders to the cells when the background color of column "A" is var gray = "#efefef".

Modification points:

From The problem is that when I run this, all my cells are borderd instead of the row with the specific color., I thought that in your showing script, by backgrounds[i]== gray, no borders are written. Because backgrounds is 2-dimensional array. So, I'm worried that your showing script might be different from your tested script.
And, even when backgrounds[i]== gray is modified to backgrounds[i][0] == gray, from range.setBorder(true, false, true, true, true, true), in this case, the borders are written to all cells of var range = sheet1.getRange("A1:B50"). I thought that these are the reason for your current issue with your script.
In your situation, RangeList might be able to be used for writing the borders.

When these points are reflected in your script, how about the following modification?

Modified script:

function border1() {
  var sheet1 = SpreadsheetApp.getActiveSheet();
  var colors = sheet1.getRange("A1:A" + sheet1.getLastRow()).getBackgrounds();
  var gray = "#efefef";
  var rangeList = colors.reduce((ar, [a], i) => {
    var row = i + 1;
    if (a == gray) ar.push(`A${row}:B${row}`);
    return ar;
  }, []);
  sheet1.getRangeList(rangeList).setBorder(true, false, true, true, true, true);
}

When this script is run, the background color of column "A" is checked, and when the background color is #efefef, the border is applied by sheet1.getRangeList(rangeList).setBorder(true, false, true, true, true, true).
In this modification, from your showing script, the borders are applied to the columns "A" and "B". If you want to set the borders to the whole row, please modify A${row}:B${row} to A${row}:${row}.

If you want to apply the border to the data range, please modify the above script as follows.

  function border1() {
    // https://stackoverflow.com/a/53678158
    const columnIndexToLetter_ = index =>
      (a = Math.floor(index / 26)) >= 0 ? columnIndexToLetter_(a - 1) + String.fromCharCode(65 + (index % 26)) : "";

    var sheet1 = SpreadsheetApp.getActiveSheet();
    var colors = sheet1.getRange("A1:A" + sheet1.getLastRow()).getBackgrounds();
    var gray = "#efefef";
    var lastCol = columnIndexToLetter_(sheet1.getLastColumn() - 1);
    var rangeList = colors.reduce((ar, [a], i) => {
      var row = i + 1;
      if (a == gray) ar.push(`A${row}:${lastCol}${row}`);
      return ar;
    }, []);
    sheet1.getRangeList(rangeList).setBorder(true, false, true, true, true, true);
  }

Note:

About the condition of border, in this modification, your showing condition of true, false, true, true, true, true is used. If you want to use other conditions, please modify this for your actual situation.

References:

Thank you. So much to learn. I managed to modify your script to my needs. I added a check for white background, so the border is deleted when the color is removed. — wjp79, Jul 30 '22 at 14:13