Function to extract numeric data from mixed content cells and apply a heatmap to them in Google Sheets

highlightcells.js

function highlightcells() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var allData = sheet.getDataRange();
  var dataRange = sheet.getRange(2,3,allData.getLastRow(),allData.getLastColumn());
  var data = dataRange.getValues();
  
  // iterate over data
  var values = [];
  
  for (var i = 0; i < data.length; i++) {
    for (var j = 0; j < data[i].length; j++) {
      if (data[i][j] != "") {
        // just get numbers
        values.push(Number(data[i][j].match(/\d+/).pop()));
      }
    }
  }
  
  // sort data
  function sortNumber(a,b) {
    return a - b;
  };
  
  values.sort(sortNumber);
  
  // find min and max values
  function arrayMin(arr) {
    var len = arr.length, min = Infinity;
    while (len--) {
      if (arr[len] < min) {
        min = arr[len];
      }
    }
    return min;
  };
  
  function arrayMax(arr) {
    var len = arr.length, max = -Infinity;
    while (len--) {
      if (arr[len] > max) {
        max = arr[len];
      }
    }
    return max;
  };
  var maxVal = arrayMax(values), minVal = arrayMin(values);
  var range = maxVal - minVal;
  
  // colour functions
  function Interpolate(start, end, steps, count) {
    var s = start,
        e = end,
        final = s + (((e - s) / steps) * count);
    return Math.floor(final);
  }
  
  function Color(r, g, b) {
    this.getColors = function() {
      var colors = {
        r: r,
        g: g,
        b: b
      };
      return colors;
    };
  }

  // set colours
  var red = new Color(230, 124, 115),
      yellow = new Color(255, 214, 102),
      green = new Color(87, 187, 138),
      start = green,
      end = yellow;
  
  var steps = 100, max = 100;
  var step = (max/steps), val = 0;

  var colours = [];
  
  // generate gradient
  while(max > 0) {
    if (val > steps/2) {
      start = yellow,
      end = red;
      val = val % steps/2;
    }

    var startColors = start.getColors(),
      endColors = end.getColors();
      
    var r = Interpolate(startColors.r, endColors.r, steps/2, val);
    var g = Interpolate(startColors.g, endColors.g, steps/2, val);
    var b = Interpolate(startColors.b, endColors.b, steps/2, val);
    
    colours.push({ r:r, g:g, b:b});

    max -= step;
    val += step;
  }
    
  var gradientSteps = [];
  for (var i=0; i<steps; i++) {
    gradientSteps.push(((range/steps)*i)+minVal);
  }
    
  // iterate over cells
  for (var i=data.length-1; i>=0; i--) {
    for (var j=0; j<data[i].length; j++) {
      var cell = dataRange.getCell(i+1,j+1);
      
      Logger.log(cell.getA1Notation());

      if (data[i][j] != "") {
        var value = Number(data[i][j].match(/\d+/).pop());
        
        // iterate over gradient steps to find matching colour
        for (var z=0; z<gradientSteps.length; z++) {
          if (value > gradientSteps[z]) {
            cell.setBackgroundRGB(colours[z].r, colours[z].g, colours[z].b);
          }
        }
      }
    }
  }
}