Alternate colors and add a separator on column value change. Google Sheets Google Apps Script

AlternatingColorsOnRowGroups.gs

/**
 * @OnlyCurrentDoc  Limits the script to only accessing the current spreadsheet.
 */

/**
 * After installing, select a column where the values repeat on each row, for example
 * a flattened table with dates, invoice numbers, and invoice lines. like this:
 * 
 * Date       |Invoice#| PartNo       |Descr ....
 * 2017-01-15 | 123456 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1
 * 2017-01-15 | 123456 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2
 * 2017-01-15 | 123456 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3
 * 2017-01-15 | 123457 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1
 * 2017-01-15 | 123457 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2
 * 2017-01-16 | 123458 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3
 * 2017-01-16 | 123459 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1
 * 2017-01-16 | 123459 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2
 * 2017-01-16 | 123459 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3
 * 2017-01-16 | 123460 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1
 * 2017-01-17 | 123461 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2
 * 2017-01-17 | 123461 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3
 * 
 * Select column Date, then from Addons menu select "apply separators.." 
 * will give you:
 * Date       |Invoice#| PartNo       |Descr ....
 * 2017-01-15 | 123456 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1 # BACKGROUND1
 * 2017-01-15 | 123456 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2 # BACKGROUND1
 * 2017-01-15 | 123456 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3 # BACKGROUND1
 *-------------------------------------------------------------
 * 2017-01-15 | 123457 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1
 * 2017-01-15 | 123457 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2
 *-------------------------------------------------------------
 * 2017-01-16 | 123458 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3 # BACKGROUND1
 * 2017-01-16 | 123459 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1 # BACKGROUND1
 * 2017-01-16 | 123459 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2 # BACKGROUND1
 * 2017-01-16 | 123459 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3 # BACKGROUND1
 * 2017-01-16 | 123460 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1 # BACKGROUND1
 *-------------------------------------------------------------
 * 2017-01-17 | 123461 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2
 * 2017-01-17 | 123461 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3
 *
 * Select column Invoice, then from Addons menu select "apply separators.." 
 * will give you:
 * Date       |Invoice#| PartNo       |Descr ....
 * 2017-01-15 | 123456 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1 # BACKGROUND1
 * 2017-01-15 | 123456 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2 # BACKGROUND1
 * 2017-01-15 | 123456 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3 # BACKGROUND1
 *-------------------------------------------------------------
 * 2017-01-15 | 123457 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1
 * 2017-01-15 | 123457 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2
 *-------------------------------------------------------------
 * 2017-01-16 | 123458 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3 # BACKGROUND1
 *-------------------------------------------------------------
 * 2017-01-16 | 123459 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1
 * 2017-01-16 | 123459 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2
 * 2017-01-16 | 123459 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3
 *-------------------------------------------------------------
 * 2017-01-16 | 123460 | ACME-WCMCI-1 | ACME WHATCHAMACALLIT 1 # BACKGROUND1
 *-------------------------------------------------------------
 * 2017-01-17 | 123461 | ACME-WCMCI-2 | ACME WHATCHAMACALLIT 2
 * 2017-01-17 | 123461 | ACME-WCMCI-3 | ACME WHATCHAMACALLIT 3
 *
 * Pretty dumb but inmensely useful to have around.
 * easely modifiable to get a subtotals like feature like excel (sucks)
 */

function onInstall(e) { //trigger install
  onOpen(e);
};

function onOpen(e) { // trigger open
  buildMenu();
};

function buildMenu(){ // add menu to sheets ui
  SpreadsheetApp.getUi().createAddonMenu()
  .addItem("Apply Separators on selected column value change", "applySeparator")
  .addSubMenu(SpreadsheetApp.getUi().createMenu("Developer tools")
              .addItem("Reset Separators", "resetSeparator")
              .addItem("Rebuild menu","buildMenu")
  ).addToUi();
};

function applySeparator(){ // self describing
  var sheet = SpreadsheetApp.getActiveSheet();
  var SOLID = SpreadsheetApp.BorderStyle.SOLID;

  var valuerange = sheet.getActiveRange();
  var values = valuerange.getValues();
  var rows = values.length;
  var maxcol = sheet.getMaxColumns();

  var bg_A = "#efefef"; // change to your favorite rgb hex triplet
  var bg_B = "#ffffff"; // #ffffff = white #000000 = black. Also, color names work ("grey") 
  var row = 2;
  var curr_color = bg_A;

  for (row ; row <= rows - 1; ++row ) { // walk the column    
    var cd = values[row][0];   // current row
    var pd = values[row-1][0]; // previous row
    
    sheet.getRange(row,1,1,maxcol).setBackground(curr_color); // apply background on each row

    if ( cd != pd ) { // detect value change
      if (curr_color == bg_A){ // switch color 
        curr_color = bg_B;     
      } else {                 
        curr_color = bg_A;
      };
      sheet.getRange(row, 1, 1, maxcol)
        .setBorder(null, null, true, null, false, false, "black", SOLID); // apply separator 
      //          (top , left, bott, rght, vert,  hor,   "color", style
    };
  };
};

function resetSeparator(){   
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var r = sheet.getDataRange();
  r.setBackground("#ffffff");
  r.setBorder(false, false, false, false, false, false);
}