.color_detector.md

Color Detector

• Google Script row color detector [L5kSR16ycLQ] #docs
• https://docs.google.com/spreadsheets/d/1_UWv_75PYCtR5-j4GBCLX-G91IPTudlty_IEiyKS-OM/copy

code.gs

function onOpen(){
  SpreadsheetApp.getUi().createMenu('Color Detector').addItem('Detect colors', 'colorDetector').addToUi();
}

function colorDetector() {
  
  var currentsheet = 'Production';
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName(currentsheet);
  
  var startRow = 1;
  var numRows = sheet.getLastRow();
  var rightColumn = sheet.getLastColumn();
  
  sheet.activate();
  var range = sheet.getRange(startRow, 1, numRows, rightColumn);
  var bgcolors = range.getBackgrounds().map(function(row){
    return [row.getUnique().join(', ')];
  });
  
  sheet.getRange(startRow, 1, numRows).setValues(bgcolors);
  SpreadsheetApp.flush();
}

Array.prototype.getUnique = function(){
  var u = {}, a = [];
  for(var i = 0, l = this.length; i < l; ++i){
    if(u.hasOwnProperty(this[i])) {
      continue;
    }
    a.push(this[i]);
    u[this[i]] = 1;
  }
  return a;
}

Cool, thanks for this. I am trying to use this for a slightly different purpose, but am a bit stuck.

I have a spreadsheet with 2 sheets. one called "green" and one called "all". in sheet "all" the cells A1:Z1 are used to name each column and A2:A, B2:B etc. have different values that the user can either set the background to green or nothing. now when the user marked certain cells green in the "all" sheet , i want to have the function go through each cell of that sheet, detect if the cell is green and if yes, take that value und add it to the first free cell of the "green" sheet, in the same column - starting from row 2, because row one is again used to name the columns in the same way as in "all".

Now is my use case too different from this or does it make sense to work with this as a foundation? I don't have much experience yet with javaScript or GAS and the getUnique function confuses me still