Using this script which writes the color numbering in a sequence for the all colored cell except the default one.

I want to write value for the default color as well so i can sort it later.

But it should not write the value for the those rows which are empty but it is writing values for the empty row as well.

any help will be appreciated.

function numberColors4() {
  
  var colorCol = 1,     
      numberCol = 11,    
      firstDataRow = 2, 

      palette = [
        "#ffffff",  // Default 1
        "#ff9900",  // Orange 2
        "#00b0f0", // Light Blue 3
        "#ffff00", // yellow 4 
        "#93c47d", // Green 5
        "#00ff00", // Light Green 6
        "#9900ff", // Purple 7
        "#ff0000", // Red 8
        "#5f5f5f" // Grey 9     
      ];

  var numbers = [],
      activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1')

      dataNumRows = activeSheet.getMaxRows()-(firstDataRow-1),
      colorColColors = activeSheet.getRange(2, colorCol, dataNumRows, 1).getBackgrounds();

  for (var rowIndex in colorColColors) {
    numbers[rowIndex] = [];

    var rowColor = colorColColors[rowIndex].toString(),
        colorIndex = palette.indexOf(rowColor);

    if (colorIndex == -1) {
      numbers[rowIndex].push(rowColor); 

    } else if (colorIndex == 0) {
      numbers[rowIndex].push(""); 

    } else {
      numbers[rowIndex].push(colorIndex);
    }
  }
  activeSheet.getRange(2, numberCol, dataNumRows, 1).setValues(numbers);

}

CodePudding user response：

This code will output the background color if the color is on the palette and the cell is not empty:

function numberColors4(){
  var colorCol = 1;
  var numberCol = 11;
  var firstDataRow = 2;


  var palette = [
    "#ffffff",  // Default 
    "#ff9900",  // Orange 8
    "#00b0f0", // Light Blue 1
    "#ffff00", // yellow 4 
    "#93c47d", // Green 7
    "#00ff00", // Light Green 5
    "#9900ff", // Purple 6
    "#ff0000", // Red 3
    "#5f5f5f" // Grey 2     
  ];

  var numbers = [];
  var activeSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet2');
  var dataNumRows = activeSheet.getMaxRows()-(firstDataRow-1);
  var colorColColors = activeSheet.getRange(2, colorCol, dataNumRows, 1).getBackgrounds();
  var colorColTexts = activeSheet.getRange(2, colorCol, dataNumRows, 1).getValues();

  for (var rowIndex=0; rowIndex<colorColColors.length; rowIndex  ){

    var rowColor = colorColColors[rowIndex].toString();
    var rowText = colorColTexts[rowIndex].toString();

    if (palette.includes(rowColor) && rowText != ""){
      numbers.push([palette.indexOf(rowColor)]);
    }
    else{
      numbers.push([""]);
    }
  }
  activeSheet.getRange(2, numberCol, dataNumRows, 1).setValues(numbers);
}

Main Changes

• Apart from the colorColColors, it is also grabbing the texts (**colorColTexts) inside the cells with the getValues() method.
• There is only one if statement: it checks whether the background color of the cell is inside the palette and that the cell is not empty. If the conditions are met, the color code will be written into the spreadsheet. If either of those fail, an empty string will be written into the spreadsheet.