SHARE
TWEET

Untitled

a guest Jun 25th, 2019 62 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
  1. function onOpen() {SpreadsheetApp.getUi().createMenu("Scripts").addItem("Number by Colors", "numberColors").addToUi();}
  2.      
  3. function numberColors() {
  4.   // ⬇ script 'Settings' below. UPDATE IF RESTRUCTURING OR RECOLORING SHEET! ⬇
  5.  
  6.   var colorCol = 6,     // 6 is F
  7.       numberCol = 7,    // 7 is G
  8.       firstDataRow = 2, // first row in which to begin color checks (to skip headers)
  9.       palette = [
  10.         "#ffffff",      // white (or your spreadsheet default)
  11.         "#d9ead3",      // light green 3
  12.         "#d9d9d9",      // light gray 1
  13.         "#f4cccc"       // light red 3
  14.       ];
  15.  
  16.   // ⬆ script 'Settings' above. UPDATE IF RESTRUCTURING OR RECOLORING SHEET! ⬆
  17.  
  18.   var numbers = [],
  19.       activeSheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet(),
  20.       dataNumRows = activeSheet.getMaxRows()-(firstDataRow-1),
  21.       colorColColors = activeSheet.getRange(2, colorCol, dataNumRows, 1).getBackgrounds();
  22.   for (var rowIndex in colorColColors) {
  23.     numbers[rowIndex] = [];
  24.     var rowColor = colorColColors[rowIndex].toString(),
  25.         colorIndex = palette.indexOf(rowColor);
  26.     if (colorIndex == -1) {
  27.       numbers[rowIndex].push(rowColor); // custom output when cell color is missing from script palette.
  28.     } else if (colorIndex == 0) {
  29.       numbers[rowIndex].push(""); // custom output for first palette color (spreadsheet default). "" means blank.
  30.     } else {
  31.       numbers[rowIndex].push(colorIndex);
  32.     }
  33.   }
  34.   activeSheet.getRange(2, numberCol, dataNumRows, 1).setValues(numbers);
  35. }
RAW Paste Data
We use cookies for various purposes including analytics. By continuing to use Pastebin, you agree to our use of cookies as described in the Cookies Policy. OK, I Understand
 
Top