CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
Diego Kersjes
Diego Kersjes

Reputation: 47

Google Sheets Conditional formatting borders - One cell based on another cell value

I am trying to create script that automatically adds "blue" lines to an entire row until column "BY" based on the value of a cell in a specific column.

Conditions

When a cell value in Column A (example A4) is different to the value below (example A5), a top thick blue line should be added to cell A6. This conditional formatting starts at row 4 all the way to the end. And should only be applied to specific sheets in the spreadsheet. in Excel I would create the following condition: =A5<>A4

Example

enter image description here

SCRIPT THAT WORKS FOR ME

function myFunction() {
  const sheetNames = ["FASHION NL", "FASHION BE","KIDS & UNDERWEAR BNL" ,"NEW BUSINESS BNL" ,"SPORTS & SHOES BNL", "HD&E BNL"] // Please set the sheet names.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  sheetNames.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    const range = sheet.getRange("H5:H" + sheet.getLastRow());
    const { ranges } = range.getValues().reduce((o, [a], i) => {
      if (i == 0) {
        o.temp = a;
      } else if (i > 0 && o.temp != a) {
        o.ranges.push(`A${i + 5}:BY${i + 5}`);
        o.temp = a;
      }
      return o;
    }, { ranges: [], temp: "" });
    sheet.getRange("A5:BY").setBorder(false, null, false, null, null, false);
    sheet.getRangeList(ranges).setBorder(true, null, null, null, null, null, "GREY", SpreadsheetApp.BorderStyle.SOLID_THICK);
  });
}

What if I want to integrate the script above in an existing script?

Goal is to have the conditional formatting run after my autoSortonEdit() script has finished AND it should only impact the activesheet from the sorting code. IMPORTANT TO KNOW: the autoSortonEdit() function is triggered by edit through Google Sheets.

Existing script

function autoSortonEdit(e){

  if (e.range.columnStart == 3 && e.range.getValue() != '') {
    var sheets = ["FASHION NL", "FASHION BE","KIDS & UNDERWEAR BNL" ,"NEW BUSINESS BNL" ,"SPORTS & SHOES BNL", "HD&E BNL"]; // Please set your expected sheet names.
    var sheet = e.range.getSheet();
    if (sheets.includes(sheet.getSheetName())) {
      var range = sheet.getRange("A5:bY600");
      range.sort({ column: 11, ascending: true });
      e.source.toast('Sort complete.');

    }
  }
}

Upvotes: 0

Views: 695

Answers (1)

Tanaike
Tanaike

Reputation: 201643

I believe your goal is as follows.

  • You want to put the blue border line from column "A" to column "BY", when the value of column "A" is changed. And, you want to start from row 5, and also, first, you want to put the borderline to the top of row 5.
  • You want to use this script for the specific sheets you expect.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const sheetNames = ["Sheet1", "Sheet2",,,]; // Please set the sheet names.

  const ss = SpreadsheetApp.getActiveSpreadsheet();
  sheetNames.forEach(sheetName => {
    const sheet = ss.getSheetByName(sheetName);
    const range = sheet.getRange("A5:A" + sheet.getLastRow());
    const { ranges } = range.getValues().reduce((o, [a], i) => {
      if (i == 0) {
        o.temp = a;
      } else if (i > 0 && o.temp != a) {
        o.ranges.push(`A${i + 5}:BY${i + 5}`);
        o.temp = a;
      }
      return o;
    }, { ranges: [], temp: "" });
    sheet.getRange("A5:BY").setBorder(false, null, false, null, null, false);
    sheet.getRangeList(ranges).setBorder(true, null, null, null, null, null, "blue", SpreadsheetApp.BorderStyle.SOLID);
  });
}

  • In your sample Spreadsheet image, the border is put from column "A" to column "C". But, from your question of I am trying to create script that automatically adds "blue" lines to an entire row until column "BY" based on the value of a cell in a specific column., when this script is run your sample Spreadsheet, the same borders are put from column "A" to column "BY".

  • In this script, by sheet.getRange("A5:BY").setBorder(false, false, false, false, false, false);, the initial borders of "A5:BY" are cleared and new borders are put. If you don't want to clear the existing borders, please remove this line.

References:

Upvotes: 1

Related Questions