I want to color cells with the same value in different columns. Is there any way to do that in app script?

CodePudding user response：

Please define distinctColors yourself and make sure there are sufficient colors for your data.

function test() {
  const sheet = SpreadsheetApp.getActiveSheet();
  const range = sheet.getDataRange();
  const values = range.getValues();
  const rows = values.length;
  const cols = values[0].length;
  const colors = new Array(rows).fill().map(row => new Array(cols));
  
  const distinctColors = ['red', 'blue', 'green', 'yellow'];
  const distinctValues = {};
  for (let i = 0; i < rows; i  ) {
    for (let j = 0; j < cols; j  ) {
      const value = values[i][j];
      if (!distinctValues[value]) { distinctValues[value] = []; }
      distinctValues[value].push({i: i, j:j});
    }
  }
  
  let k = 0;
  for (const array of Object.values(distinctValues)) {
    if (array.length == 1) { continue; }
    for (const v of array) {
      colors[v.i][v.j] = distinctColors[k];
    }
    k  ;
  }
  range.setBackgrounds(colors);
}

CodePudding user response：

• Create a Set of unique values
• Create a value to random color Map
• Map the range values to the color using Array.map
• Set back the mapped colors array to range

const colorSameValues = () => {
  const sheet = SpreadsheetApp.getActive().getSheetByName('Sheet1'),
    range = sheet.getDataRange(),
    values = range.getValues(),
    set /* unique set */ = new Set(values.flat()),
    map /* values to color map */ = new Map(),
    hex /* random hex color code */ = () =>
      '#'  
      Array(3)
        .fill()
        .map(() =>
          Math.floor(Math.random() * 256)
            .toString(16)
            .padStart(2, '0')
        )
        .join('');
  set.delete('');
  set.forEach((val) => map.set(val, hex()));
  range.setBackgrounds(values.map((row) => row.map(map.get.bind(map))));
};