Vfrdtyky

I am trying to do exactly what this other post is asking, but the accepted answer simply does not work. The original post was very clear, so here it is once more:

Excel apparently has this type of conditional formatting built-in: https://sites.google.com/site/techyhelp85/excel-tips/group-borders

I'd like to accomplish the same in Google Sheets via Google Apps Script. The following should demonstrate the before and after conditions:

Example Dataset

        A           B           C

  1   apple     Macintosh

  2   apple     Granny Smith

  3   orange    Florida

  4   orange    Valencia

  5   pear      Garden

  6   banana    Chiquita

Resulting Example Dataset

        A           B           C

  1   apple     Macintosh

  2   apple     Granny Smith

  -----------------------------------

  3   orange    Florida

  4   orange    Valencia

  -----------------------------------

  5   pear      Garden

  -----------------------------------

  6   banana    Chiquita

  -----------------------------------

The script/answer to this question should display a bottom border under the entire row (of columns 'A', 'B' & 'C') for rows: 2, 4, 5, & 6.

The accepted answer to this original post provides the following script:

function onOpen() {

   GroupMyData(); // trigger this function on sheet opening

}



function GroupMyData() {

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var sheet = ss.getSheetByName('Sheet 1'); // apply to sheet name only

  var rows = sheet.getRange('a1:g'); // range to apply formatting to

  var numRows = rows.getNumRows(); // no. of rows in the range named above

  var values = rows.getValues(); // array of values in the range named above

  var testvalues = sheet.getRange('a1:a').getValues(); // array of values to be tested (1st column of the range named above)



  rows.setBorder(false, false, false, false, false, false, "red", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // remove existing borders before applying rule below

      //Logger.log(numRows);



  for (var i = 0; i <= numRows - 1; i++) {

      var n = i + 1;

      //Logger.log(n);

      //Logger.log(testvalues[i] > 0);

      //Logger.log(testvalues[i]);

      if (testvalues[i] > 0) { // test applied to array of values

        sheet.getRange('a' + n + ':g' + n).setBorder(null, null, true, null, null, null, "red", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // format if true

      }

  }

};

That script that functions like so:
A border will appear along the bottom of each row where column A's cell has a number in it (and no letters), regardless of whether the number is the same as the one in the cell above it or different.

However the goal is for A to have text, and wherever consecutive text down column A changes from a repeated string to a differing string, there should be a border between the differing strings.

Thanks!

The Questioner refers to a previous Q&A which was based on evaluating a number. In the questioners case, the evaluation is based on a string.

I referred to the question noted by the Questionner. I also referred to How to compare strings in google apps script which contains a very elegant and efficient approach by Harold which I have carried through in this answer. This uses indexof to detect new values, and so underlining can be linked with each new value.

function so53053492() {



    //setup spreadsheet

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getSheetByName("Sheet1");



    // get key variables

    var LastRow = sheet.getLastRow();

    var LastColumn = sheet.getLastColumn();



    //erase any current formatting 

    var ClearRange = sheet.getRange(1, 1, LastRow, LastColumn).setBorder(false, false, false, false, false, false); // clear all formatting



    // get the data

    var data = sheet.getRange(1, 1, sheet.getLastRow(), sheet.getLastColumn()).getValues();



    // setup new array  

    var ListofFruits = new Array();



    // Loop through the fruits (Column A)

    for (var i in data) {

        var row = data[i][0].toString();





        // search for unqiue values

        if (ListofFruits.indexOf(row) == -1) { // if value =-1, then the variable is unique



            // Logger.log(row+" is not referenced. Adding it");//DEBUG

            // underline the previous row

            var range = sheet.getRange((+i + 1), 1, 1, 2).setBorder(true, false, false, false, false, false, "red", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // format if true



            // continue to build array

            ListofFruits.push(row);

        }



    }

    // underline the last row of the fruits column

    var range = sheet.getRange(LastRow, 1, 1, LastColumn).setBorder(null, null, true, null, false, false, "red", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // format if true

    // Logger.log(ListofFruits);// DEBUG

}

UPDATE - Accounting for Headers

The previous version assumed no headers. This version takes headers into account. There are several lines of code affected, but the key variable is headerRows which allows the user to nominate the depth of the headers.

function so53053492_01() {



    //setup spreadsheet

    var ss = SpreadsheetApp.getActiveSpreadsheet();

    var sheet = ss.getSheetByName("Sheet3");



    // get key variables

    var LastRow = sheet.getLastRow();

    var LastColumn = sheet.getLastColumn();

    var NumColumns = sheet.getMaxColumns();



    // allow for headers

    var headerRows = 2;



    //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 ListofFruits = new Array();



    // Loop through the fruits (Column A)

    for (var i in data) {



        var row = data[i][0].toString();

        // Logger.log("Inside LOOP: i = "+i+", value = "+ row);// DEBUG



        // search for unqiue values

        if (ListofFruits.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, "red", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // format if true

            }

            // continue to build array

            ListofFruits.push(row);

        }



    }

    // underline the last row of the fruits column

    var range = sheet.getRange(LastRow, 1, 1, NumColumns).setBorder(null, null, true, null, false, false, "red", SpreadsheetApp.BorderStyle.SOLID_MEDIUM); // format if true

    //Logger.log(ListofFruits);// DEBUG

}

Screenshot showing headers: