Property Grids

function propGrids() {
  const ssh = SpreadsheetApp.getActiveSpreadsheet();
  const _   = LodashGS.load();

  const sizeTable = { range: ssh.getRangeByName('size_table') };
  [ sizeTable.cols,
    sizeTable.rows,
    sizeTable.values
  ] = getXRefs_(sizeTable.range.getValues(), _);
  sizeTable.cols = deName(sizeTable.cols);
  sizeTable.rows = deName(sizeTable.rows);

  const glowTable = { range: ssh.getRangeByName('glow_table') };
  [ glowTable.cols,
    glowTable.rows,
    glowTable.values
  ] = getXRefs_(glowTable.range.getValues(), _);
  glowTable.cols = deName(glowTable.cols);
  glowTable.rows = deName(glowTable.rows);

  sizeTable.xRows = glowTable.rows;
  glowTable.xRows = sizeTable.rows;

  const recentTable = { range: ssh.getRangeByName('recent_data') };
  [ recentTable.cols,
    recentTable.values
  ] = prepRecent(recentTable.range.getValues());

  const distMatrix = distribAdjust_(
    recentTable,
    sizeTable.rows,
    glowTable.rows,
    _
  );

  const newValues = doFormulas_(sizeTable, glowTable, recentTable, distMatrix, _);

  sizeTable.range.offset(
    1,
    1,
    _.size(sizeTable.rows) / 2,
    _.size(sizeTable.cols) / 2
  ).setValues(newValues.size);

  glowTable.range.offset(
    1,
    1,
    _.size(glowTable.rows) / 2,
    _.size(glowTable.cols) / 2
  ).setValues(newValues.glow);

  function prepRecent(values) {
    var superHead;
    return [
      getColumnObj_(_.map(values[1], function(val, index) {
        if (values[0][index] != '') { superHead = values[0][index]; }
        return (superHead + " - " + val).toLowerCase();
      })),
      _.slice(values,2)
    ]
  }

  function deName(object) {
    const working = {};
    _.forOwn(object, function(val, key) {
      if (isFinite(val) && val != 0) { working[key]               = val-1; }
      if (isFinite(key) && key != 0) { working[_.toString(key-1)] = val;   }
    });
    return working;
  }
}

function doFormulas_(sizeTable, glowTable, recentTable, distMatrix, _) {
  const
    dataTables = {
      size: sizeTable,
      glow: glowTable
    },
    xProps = {
      size: 'glow',
      glow: 'size'
    },
    order = [
      'points'
      ,'% chance'
      ,'kept data points'
      ,'w-avg. value per point'
      ,'w-avg. value'
      ,'median value'
      ,'values trend'
    ],
    outp = {
      size: undefined,
      glow: undefined
    },
    mGetTrendData = _.memoize(getTrendData);

  _.forOwn(dataTables, function(propObj, prop) {
    const rows  = propObj.rows;
    const cols  = propObj.cols;
    const xRows = propObj.xRows;

    var rowCount = 0, colCount = 0;
    while (!_.isUndefined(rows[rowCount])) { rowCount++; }
    while (!_.isUndefined(cols[colCount])) { colCount++; }
    outp[prop] = Array(rowCount);
    for (var i = 0; i < rowCount; i++) {
      outp[prop][i] = Array(colCount);
    }

    _.forEach(order, function(colKey) {
      for (var i = 0; i < rowCount; i++) {
        outp[prop][i][cols[colKey]] = doMath(
          colKey,
          rows[i],
          prop,
          xRows,
          xProps,
          rowCount,
          rows,
          outp
        );
      }
    });
  });

  return outp;

  function doMath(colKey, rowKey, prop, xRows, xProps, rowCount, rows, outp) {
    switch(colKey) {
      case 'points':
        return points_(getSGLvl_(rowKey));

      case '% chance':
        return getOddsSG_(getSGLvl_(rowKey));

      case 'kept data points':
        return keptDataPoints_(distMatrix[prop][rowKey], _);

      case 'w-avg. value per point':
      case 'w-avg. value':
      case 'median value':
        return valueCalc_(
          prop,
          xProps[prop],
          rowKey,
          xRows,
          distMatrix[prop][rowKey],
          recentTable,
          points_(getSGLvl_(rowKey)),
          _
        )[colKey];

      case 'values trend':
        //return null;
        const getTrend = mGetTrendData(prop, outp, rowCount, 5, _);
        return getTrend(_.unzip(outp[prop])[dataTables[prop].cols['points']][rows[rowKey]]);
    }
  }

  function getTrendData(prop, outp, rowCount, minSampleSize, _) {
    const outpProp = _.unzip(outp[prop]);
    return LeastSquares_(
      _.filter(_.times(rowCount, function(i) {
        return outpLookup(outpProp, 'points')[i];
      }), function(value, i) {
        if (outpLookup(outpProp, 'kept data points')[i] > minSampleSize) { return true; }
      }),
      _.filter(_.map(_.unzip([
        outpLookup(outpProp, 'w-avg. value'),
        outpLookup(outpProp, 'median value')
      ]), function (pair) {
        return _.mean(pair);
      }), function(value, i) {
        if (outpLookup(outpProp, 'kept data points')[i] > minSampleSize) { return true; }
      })
    )

    function outpLookup(outpProp, key) { return outpProp[dataTables[prop].cols[key]]; }
  }
};

var oddsSG_ = [
  0.500488759,
  0.250244379,
  0.12512219,
  0.062561095,
  0.031280547,
  0.015640274,
  0.007820137,
  0.003910068,
  0.001955034,
  0.000977517
];

function points_(level) {
  return Math.pow(level, 2);
}

function getOddsSG_(level) {
  if (level < 1) { level = 1; }
  return oddsSG_[level-1];
}

function multiByOdds_(odds, _) {
  _ = _ || LodashGS.load();

  return _.map(odds, function(value) {
    return _.map(odds, function(value2) {
      return (value2 / value);
    })
  });
}

function getSGLvl_(name) {
  return {
    'none':        0,
    'small':       1,
    'dusky':       2,
    'average':     2,
    'lucent':      3,
    'large':       3,
    'bright':      4,
    'massive':     4,
    'brilliant':   5,
    'enormous':    5,
    'radiant':     6,
    'gigantic':    6,
    'dazzling':    7,
    'staggering':  7,
    'starlike':    8,
    'monumental':  8,
    'crown jewel': 9,
    'colossal':    9,
    'titanic':     10
  }[name.toLowerCase()];
}

function keptDataPoints_(dist, _) {
  _ = _ || LodashGS.load();

  return _.sum(dist);
}

var valueCalc_ = (function() {
  const cache = {};

  function memoized(prop, xProp, name, xRows, dist, recent, points, _) {
    _ = _ || LodashGS.load();

    var outp;
    cache[prop] = cache[prop] || {};

    if (prop in cache && name in cache[prop]) {
      // USE CACHE

      return output(cache[prop][name]);

    } else if (_.sum(dist) <= 0) {
      // NEW PARAMS - ZERO USABLE LISTINGS

      return output({
        'w-avg. value per point': null,
        'w-avg. value': null,
        'median value': null
      });

    } else {
      // NEW PARAMS - CALCULATE VALUES

      const xnames = [];
      for (var i = 0; !_.isUndefined(xRows[i]); i++) { xnames.push(xRows[i]); }

      const verifyKeys = [
        prop  + ' sd (5th pass) - kept data',
        xProp + ' sd (5th pass) - kept data'
      ]

      const lookupKeys = {};
      lookupKeys['recent listings - ' + prop ] = name;

      const filteredMed = _.flatten(_.map(xnames, function(xname) {
        lookupKeys['recent listings - ' + xProp] = xname;

        return _.slice(filterRecent_(
          recent,
          lookupKeys,
          ['overall sd (4th pass) - kept data'],
          _
        ), 0, dist[xRows[xname]]);
      }));

      const filtered = _.flatten(_.map(xnames, function(xname) {
        lookupKeys['recent listings - ' + xProp] = xname;

        return _.slice(filterRecent_(
          {
            values: filteredMed,
            cols: recent.cols
          },
          lookupKeys,
          verifyKeys,
          _
        ), 0, dist[xRows[xname]]);
      }));

      var weight = 0, sum = 0;
      _.forEach(filtered, function(rowArray) {
        const compiled = [
          rowArray[recent.cols['total weight - ']],
          rowArray[recent.cols[verifyKeys[0]    ]]
        ]
        weight += _.toNumber(compiled[0]);
        sum    += _.toNumber(compiled[0] * compiled[1]);
      });

      var medianArr = [];
      _.forEach(filtered, function(rowArray) {
        medianArr.push(rowArray[recent.cols['overall sd (4th pass) - kept data']]);
      });
      medianArr = _.sortBy(medianArr);

      return output({
        'w-avg. value per point': (sum / weight),
        'w-avg. value': (sum / weight) * points,
        'median value': ((
          medianArr[Math.floor((medianArr.length - 1) / 2)] +
          medianArr[Math.ceil(( medianArr.length - 1) / 2)]
        ) / 2) * points
      });

    }

    function output(obj) {
      cache[prop][name] = obj;
      return obj;
    }
  }

  return memoized;
})();

function filterRecent_(recent, lookupKeys, verifyKeys, _) {
  return _.filter(recent.values, function(listing) {
    for (var key in lookupKeys) { if (lookupKeys.hasOwnProperty(key)) {
      if (listing[recent.cols[key]].toLowerCase() === lookupKeys[key]) { return true; }
    }}
    for (var key in verifyKeys) { if (lookupKeys.hasOwnProperty(key)) {
      if (_.isNumber(recent.cols[key])) { return true; }
    }}
  });
}

function distribAdjust_(recent, sizeObj, glowObj, _) {
  _ = _ || LodashGS.load();
  const
    multipliers  = multiByOdds_(oddsSG_, _),
    recentValues = recent.values,
    recentCols   = recent.cols,
    sizes        = _.filter(_.values(sizeObj), function(value) { return _.isString(value); }),
    glows        = _.filter(_.values(glowObj), function(value) { return _.isString(value); });

  return {

    size: labelBy(makeRepresent(mapCounts(
      sizes,
      glows,
      'recent listings - size',
      'recent listings - glow',
      'size sd (5th pass) - kept data'
    ), multipliers), sizes),

    glow: labelBy(makeRepresent(mapCounts(
      glows,
      sizes,
      'recent listings - glow',
      'recent listings - size',
      'glow sd (5th pass) - kept data'
    ), multipliers), glows)

  }

  function mapCounts(mains, secondaries, mainHead, secondHead, valueHead) {
    return _.map(mains, function(main) {
      return _.map(secondaries, function(secondary) {
        return _.filter(recent.values, function(listing) {
          if (
            listing[recent.cols[  mainHead]].toLowerCase() === main      &&
            listing[recent.cols[secondHead]].toLowerCase() === secondary &&
            _.isNumber(listing[recent.cols[valueHead]])
          ) { return true; }
        }).length;
      });
    });
  }

  function makeRepresent(countMapProperty, multipliers) {
    return _.map(countMapProperty, function(propRow) {
      var newCounts;

      // itterate over multiplier rows
      main: for (var i = 0; i < 10; i++) {

        // reset newCounts to array of 10 zeros
        newCounts = _.fill(Array(10), 0, 0, 10);

        // get row from multiplier matrix
        var multiRow = multipliers[i];

        // itterate over property values and matching multipliers
        for (var ii = 0; ii < 10; ii++) {
          var propCount = propRow[ii];
          if (propCount > 0) {
            repCount = _.round(multiRow[ii] * propRow[i]);

            /*
             * if the representative count would be more than the available count,
             * restart from 'main'; else add repCount to new
             */
            if (repCount > propCount) { continue main; }
            newCounts[ii] = repCount;
          } else {
            // we have found 0, all remaining counts are 0.
            break;
          }
        }
        break;
      }
      return newCounts;
    });
  }

  function labelBy(countMatrix, labels) {
    if (countMatrix.length !== labels.length) { throw new TypeError('mismatched inputs'); }
    const outp = {};
    _.forEach(countMatrix, function(row, i) {
      outp[labels[i]] = row;
    });
    return outp;
  }
}

// https://github.com/jprichardson/least-squares
function LeastSquares_(X, Y, computeError, ret) {
  if (typeof computeError == 'object') {
    ret = computeError
    computeError = false
  }

  if (typeof ret == 'undefined') ret = {}

  var sumX = 0
  var sumY = 0
  var sumXY = 0
  var sumXSq = 0
  var N = X.length

  for(var i = 0; i < N; ++i) {
    sumX += X[i]
    sumY += Y[i]
    sumXY += X[i] * Y[i]
    sumXSq += X[i] * X[i]
  }

  ret.m = ((sumXY - sumX * sumY / N) ) / (sumXSq - sumX * sumX / N)
  ret.b = sumY / N - ret.m * sumX / N

  if (computeError) {
    var varSum = 0
    for (var j = 0; j < N; ++j) {
      varSum += (Y[j] - ret.b - ret.m*X[j]) * (Y[j] - ret.b - ret.m*X[j])
    }

    var delta = N * sumXSq - sumX*sumX
    var vari = 1.0 / (N - 2.0) * varSum

    ret.bErr = Math.sqrt(vari / delta * sumXSq)
    ret.mErr = Math.sqrt(N / delta * vari)
  }

  return function(x) {
    return ret.m * x + ret.b
  }
}