How to generate very sharp color scale for below zero and above zero?

Question

I'm encountering a big problem when using the number 0 (zero) as a factor for the colors to generate scales, the numbers close to 0 (zero) end up becoming almost white, impossible to see a difference.

The idea is that above 0 (zero) it starts green and gets even stronger and below 0 (zero) starting with a red one and getting stronger.

I really need any number, even if it's 0.000001 already has a visible green and the -0.000001 has a visible red.

Link to SpreadSheet:
https://docs.google.com/spreadsheets/d/1uN5rDEeR10m3EFw29vM_nVXGMqhLcNilYrFOQfcC97s/edit?usp=sharing

Note to help with image translation and visualization:

Número = Number  
Nenhum = None  
Valor Máx. = Max Value
Valor Min. = Min Value

Current Result / Expected Result

Answer 1

A very small improvement to acques-Guzel Heron

I made it skip all non numeric values, beforehand it just errored out.

I added an option in the menu to use a custom range.

Thank you very much acques-Guzel Heron

function onOpen() {
    const ui = SpreadsheetApp.getUi();

    ui.createMenu('Extra')
  .addItem('Generate gradient', 'parseData')
  .addItem('Custom Range', 'customRange')
  .addToUi();
}

function parseData(customRange = null) {
    const darkestGreen = '#009000';
    const lighestGreen = '#B8F4B8';
    const darkestRed = '#893F45';
    const lighestRed = '#FEBFC4';
    let range = SpreadsheetApp.getActiveRange();
  if (customRange) {
    range = SpreadsheetApp.getActiveSpreadsheet().getRange(customRange);
  }

    const data = range.getValues();
    const biggestPositive = Math.max.apply(null, data.filter(a => !isNaN([a])));
    const biggestNegative = Math.min.apply(null, data.filter(a => !isNaN([a])));
    const greenPalette = colorPalette(darkestGreen, lighestGreen, biggestPositive);
    const redPalette = colorPalette(darkestRed, lighestRed, Math.abs(biggestNegative) + 1);

    const fullPalette = [];
    for (const datum of data) {
        if (datum > 0) {
            fullPalette.push([greenPalette[datum - 1]]);
        } else if (datum < 0) {    
            fullPalette.push([redPalette[Math.abs(datum) - 1]]);
        } else if (datum == 0 || isNaN(datum)) {
            fullPalette.push(['#ffffff']);
        }
    }
    range.setBackgrounds(fullPalette);
}

function customRange() {
    const ui = SpreadsheetApp.getUi();
    result = ui.prompt("Please enter a range");
    parseData(result.getResponseText());
}

function colorPalette(darkestColor, lightestColor, colorSteps) {
    const firstColor = hexToRGB(darkestColor);
    const lastColor = hexToRGB(lightestColor);
    let blending = 0;
    const gradientColors = [];

    for (i = 0; i < colorSteps; i++) {
        const color = [];

        blending += (1 / colorSteps);
        color[0] = firstColor[0] * blending + (1 - blending) * lastColor[0];
        color[1] = firstColor[1] * blending + (1 - blending) * lastColor[1];
        color[2] = firstColor[2] * blending + (1 - blending) * lastColor[2];
        gradientColors.push(rgbToHex(color));
    }

    return gradientColors;
}

function hexToRGB(hex) {
    const color = [];

    color[0] = Number.parseInt((removeNumeralSymbol(hex)).slice(0, 2), 16);
    color[1] = Number.parseInt((removeNumeralSymbol(hex)).slice(2, 4), 16);
    color[2] = Number.parseInt((removeNumeralSymbol(hex)).slice(4, 6), 16);

    return color;
}

function removeNumeralSymbol(hex) {
    return (hex.charAt(0) == '#') ? hex.slice(1, 7) : hex;
}

function rgbToHex(rgb) {
    return '#' + hex(rgb[0]) + hex(rgb[1]) + hex(rgb[2]);
}

function hex(c) {
    const pool = '0123456789abcdef';
    let integer = Number.parseInt(c, 10);

    if (integer === 0 || isNaN(c)) {
        return '00';
    }

    integer = Math.round(Math.min(Math.max(0, integer), 255));

    return pool.charAt((integer - integer % 16) / 16) + pool.charAt(integer % 16);
}

Answer 2

After reading your new comments I understand that these are the requisites:

• The values above zero should be green (with increased intensity the further beyond zero).
• The values below zero should be red (with increased intensity the further beyond zero).
• Values near zero should be coloured (not almost white).

Given those requisites, I developed an Apps Script project that would be useful in your scenario. This is the full project:

function onOpen() {
  var ui = SpreadsheetApp.getUi();

  ui.createMenu("Extra").addItem("Generate gradient", "parseData").addToUi();
}

function parseData() {
  var darkestGreen = "#009000";
  var lighestGreen = "#B8F4B8";
  var darkestRed = "#893F45";
  var lighestRed = "#FEBFC4";
  var range = SpreadsheetApp.getActiveRange();
  var data = range.getValues();
  var biggestPositive = Math.max.apply(null, data);
  var biggestNegative = Math.min.apply(null, data);
  var greenPalette = colourPalette(darkestGreen, lighestGreen, biggestPositive);
  var redPalette = colourPalette(darkestRed, lighestRed, Math.abs(
    biggestNegative) + 1);
  var fullPalette = [];

  for (var i = 0; i < data.length; i++) {
    if (data[i] > 0) {
      var cellColour = [];

      cellColour[0] = greenPalette[data[i] - 1];
      fullPalette.push(cellColour);

    } else if (data[i] < 0) {
      var cellColour = [];

      cellColour[0] = redPalette[Math.abs(data[i]) - 1];
      fullPalette.push(cellColour);

    } else if (data[i] == 0) {
      var cellColour = [];

      cellColour[0] = null;
      fullPalette.push(cellColour);
    }
  }
  range.setBackgrounds(fullPalette);
}

function colourPalette(darkestColour, lightestColour, colourSteps) {
  var firstColour = hexToRGB(darkestColour);
  var lastColour = hexToRGB(lightestColour);
  var blending = 0.0;
  var gradientColours = [];

  for (i = 0; i < colourSteps; i++) {
    var colour = [];

    blending += (1.0 / colourSteps);
    colour[0] = firstColour[0] * blending + (1 - blending) * lastColour[0];
    colour[1] = firstColour[1] * blending + (1 - blending) * lastColour[1];
    colour[2] = firstColour[2] * blending + (1 - blending) * lastColour[2];
    gradientColours.push(rgbToHex(colour));
  }
  return gradientColours;
}

function hexToRGB(hex) {
  var colour = [];

  colour[0] = parseInt((removeNumeralSymbol(hex)).substring(0, 2), 16);
  colour[1] = parseInt((removeNumeralSymbol(hex)).substring(2, 4), 16);
  colour[2] = parseInt((removeNumeralSymbol(hex)).substring(4, 6), 16);

  return colour;
}

function removeNumeralSymbol(hex) {
  return (hex.charAt(0) == '#') ? hex.substring(1, 7) : hex
}

function rgbToHex(rgb) {
  return "#" + hex(rgb[0]) + hex(rgb[1]) + hex(rgb[2]);
}

function hex(c) {
  var pool = "0123456789abcdef";
  var integer = parseInt(c);

  if (integer == 0 || isNaN(c)) {
    return "00";
  }

  integer = Math.round(Math.min(Math.max(0, integer), 255));

  return pool.charAt((integer - integer % 16) / 16) + pool.charAt(integer % 16);
}

First of all the script will use the Ui class to show a customised menu called Extra. That menu calls the main function parseData, that reads the whole selection data with getValues. That function holds the darkest/lightest green/red colours. I used some colours for my example, but I advise you to edit them as you wish. Based on those colours, the function colourPalette will use graphical linear interpolation between the two colours (lightest and darkest). That interpolation will return an array with colours from darkest to lightest, with as many in-betweens as the maximum integer in the column. Please notice how the function uses many minimal functions to run repetitive tasks (converting from hexadecimal to RGB, formatting, etc…). When the palette is ready, the main function will create an array with all the used colours (meaning that it will skip unused colours, to give sharp contrast between big and small numbers). Finally, it will apply the palette using the setBackgrounds method. Here you can see some sample results:

In that picture you can see one set of colours per column. Varying between random small and big numbers, numerical series and mixed small/big numbers. Please feel free to ask any doubt about this approach.