Conditional borders troubleshooting (Google sheets)

Question

I have a dataset with four different treatment types in blocks of 5 or 7 rows. I want to add borders between each treatment set, so I tried modifying Tedinoz's code. The problem is that this code only adds a border to unique values, and I want to add a border whenever the value is different from the previous row. How can I modify the if statement if (Treatments.indexOf(row) == -1) to search instead for rows whose value for 'Treatment' is different from the previous row?

Here is the full code:

function lineBetween() {

    //setup spreadsheet
    var ss = SpreadsheetApp.getActiveSpreadsheet();
    var sheet = ss.getSheetByName("Sheet1");

    // get key variables
    var LastRow = 417
    var LastColumn = sheet.getLastColumn();
    var NumColumns = 8;

    // allow for headers
    var headerRows = 1;

    //erase any current formatting 
    var ClearRange = sheet.getRange(1, 1, LastRow, NumColumns).setBorder(false, false, false, false, false, false); // clear all formatting

    // get the data
    var data = sheet.getRange((+1 + headerRows), 1, (LastRow - headerRows), LastColumn).getValues();

    // setup new array  
    var Treatments = new Array();

    // Loop through treatments(Column C)
    for (var i in data) {

        var row = data[i][2].toString();
        // Logger.log("Inside LOOP: i = "+i+", value = "+ row);// DEBUG

        // search for unqiue values
        if (Treatments.indexOf(row) == -1) { // if value =-1, then the variable is unique

            // Logger.log("Inside IF#1: i = "+i+", "+row+" is not referenced. Adding it");//DEBUG
            // underline the previous row

            if (i != 0) {
                // This IF statement to avoid underlining the Header row
                var range = sheet.getRange((+i + 1 + headerRows), 1, 1, NumColumns).setBorder(true, false, false, false, false, false, "black", SpreadsheetApp.BorderStyle.SOLID); // format if true
            }
            // continue to build array
            Treatments.push(row);
        }

    }
    // underline the last row of the treatments column
    var range = sheet.getRange(LastRow, 1, 1, NumColumns).setBorder(null, null, true, null, false, false, "black", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // format if true
    //Logger.log(Treatments);// DEBUG
}


  [1]: https://stackoverflow.com/questions/53053492/conditional-borders-in-google-sheets

Answer 1

Try this:

function lineBetween() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName("Sheet1");
  sh.getDataRange().setBorder(false, false, false, false, false, false); // clear all formatting
  let sr = 2;//data start row
  const data = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn()).getValues();
  data.forEach((r, i, arr) => {
    if (i > 0) {
      if (r[2] != arr[i - 1][2]) {
        sh.getRange(i + sr, 1, 1, sh.getLastColumn()).setBorder(true, true, true, true, false, false);
      }
    }
  });
}

Actually I think this works better for me:

function lineBetween() {
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const sh = ss.getSheetByName("Sheet0");
  sh.getDataRange().setBorder(false, false, false, false, false, false); // clear all formatting
  let sr = 2;//data start row
  const data = sh.getRange(sr, 1, sh.getLastRow() - sr + 1, sh.getLastColumn()).getValues();
  let uA = [];
  let dA = [];
  data.forEach((r, i, arr) => {
    if (i > 0) {
      if (r[2] != arr[i - 1][2]) {
        sh.getRange(i + sr, 1, 1, sh.getLastColumn()).setBorder(true, false, false, false, false, false);
      }
    }
  });
}