Google Sheets appscript functions for use in Generic Decision Matrix.

decision_matrix.js

/**
 * Utility function to remove empty values from an array
 * 
 * @param {array} arr - The array to clean.
 * 
 * @return {array} - An array with no empty values.
 **/
function removeEmptyCells(arr) {
  return arr.filter(v => v && v != "")
}

/**
 * Normalizes a given number within an array to a value between 0 and 1.
 * 
 * @param {number[]} array - Array of numbers.
 * @param {number} number - The number to normalize.
 * 
 * @return {number} - The normalized value between 0 and 1.
 */
function normalize(arr, number) {
    arr = removeEmptyCells(arr)

    const min = Math.min(...arr);
    const max = Math.max(...arr);
    
    if (max === min) {
        // Avoid division by zero if all numbers in the array are the same
        return 0.5;
    }
    
    return (number - min) / (max - min);
}

/**
 * Returns where a number is within an array to a value between 0 and 1.
 * Lower values return a number closer to 1.
 * 
 * @param {number} weight - The number to normalize.
 * @param {number} value - The number to normalize.
 * @param {number[]} array - Array of numbers to normalize.
 * 
 * @return {number} - The normalized value between 0 and 1.
 */
function lowHigh(weight, value, range) {
  return (1-normalize(range, value)) * weight
}

/**
 * Returns where a number is within an array to a value between 0 and 1.
 * Higher values return a number closer to 1.
 * 
 * @param {number} weight - The number to normalize.
 * @param {number} value - The number to normalize.
 * @param {number[]} array - Array of numbers to normalize.
 * 
 * @return {number} - The normalized value between 0 and 1.
 */
function highLow(weight, value, range) {
  return (normalize(range, value)) * weight
}

/**
 * Returns 1 if 'Yes' and 0 if 'No'.
 * 
 * @param {string} yesNo - The number to normalize.
 * 
 * @return {number} - The normalized value between 0 and 1.
 */
function yesOrNo(weight, yesNo) {
  if (typeof yesNo !== 'string' || !yesNo instanceof String) {
    throw new Error( "`yesNo` should be a string." )
  }

  return (yesNo.toUpperCase() == 'YES' ? 1 : 0) * weight
}

/**
 * Calls the function based on the `selType`. 
 * 
 * @param {string} selType - The function to call.
 * @param {number} weight - The number to normalize.
 * @param {number} value - The number to normalize.
 * @param {number[]} array - Array of numbers to normalize.
 * 
 * @return {number} - The result of the function.
 */
function select(selType, weight, value, range) {
  if (typeof selType !== 'string' || !selType instanceof String) {
    throw new Error( "`selType` should be a string." )
  }

  switch(selType.toUpperCase()) {
    case 'HIGH':
      return highLow(weight, value, range)

    case 'LOW':
      return lowHigh(weight, value, range)

    case 'Y/N':
      return yesOrNo(weight, value)

    default:
      throw new Error( "Invalid `selType`." )
  }
}