Burn-up chart script

gistfile1.js

/**
 * Google Spreadsheet script to create simple burn-up charts.
 * See spreadsheet at
 * https://docs.google.com/spreadsheet/ccc?key=0AgpNfbjg-OFhdGRPRjNSejFWS1dpN1VoSk5ZbkctcGc&usp=sharing
 * and blog post at
 * http://niksilver.com/2014/08/08/burn-up-charts-in-google-spreadsheets/
 * 
 */

/**
 * Is the input a string? True or false.
 */
function isString(input) {
  return typeof(input) == "string";
}

/**
 * Is the input a number? True or false.
 * Note that due to ISNUMBER function in Spreadsheets, this
 * cannot be called from a spreadsheet; it's only useful in this script.
 */
function isNumber(input) {
  return typeof(input) == "number";
}

/** Is the input an array? True or false
 */
function isArray(input) {
  return (input instanceof Array);
}

/**
 * Is the input a date? True or false.
 */
function isDate(input) {
  return (input instanceof Date);
}

/**
 * Is this input a basic type:
 * number, string, date. True or false
 */
function isBasicType(input) {
  return isNumber(input) || isString(input) || isDate(input);
}

/**
 * Return the index of a named column in a given array,
 * or throw an error if column not found.
 * @param colname   Name of column
 * @param arraz     Array with a header row
 */
function indexOfColumn(colname, arraz) {
  if (!isString(colname)) {
    throw "First argument, column name, must be a string";
  }
  if (!isArray(arraz)) {
    throw "Second argument must be an array";
  }
  var header = arraz[0];
  var idx = header.indexOf(colname);
  if (idx == -1) {
    throw "Colname '" + colname + "' not found";
  }
  return idx;
}

/**
 * Force the input to be an array of arrays.
 */
function make2DArray(input) {
  if (isBasicType(input)) {
    return [[input]];
  }
  if (isBasicType(input[0])) {
    return [input];
  }
  return input;
}

/**
 * Get the value from a 2D array.
 * @param arr  Array to look at; will be made into a 2D array if not already
 * @param row  Index of row to choose, starting at zero.
 * @param col  Index of column to choose, starting at zero.
 */
function indexBy0(arr, row, col) {
  var forced_array = make2DArray(arr);
  return forced_array[row][col];
}

/**
 * Extract the named columns from an array.
 * This is almost the same as Spreadsheet's built in INDEX()
 * function, but it ensures we're using proper Javascript
 * and bypassing any clever auto-conversion that might go
 * on with that built in function.
 * @param colname  Name of columns to extract
 * @param arraz    Array with header row
 */
function extractColumns(colnames, arraz) {
  colnames = make2DArray(colnames);
  if (colnames.length > 1) {
    throw "Column names should be a string or a single row";
  }

  var indexes = [];
  colnames[0].forEach(function (colname) {
    var idx = indexOfColumn(colname, arraz);
    indexes.push(idx);
  });
  
  var out = [];
  for (var i = 1; i < arraz.length; i++) {
    var row = [];
    indexes.forEach(function (idx) {
      row.push(arraz[i][idx]);
    });
    out.push(row);
  }
  return out;
}

/**
 * Is the given date between the two limit dates (inclusive)? True or false.
 * @param tdate  Date under test
 * @param lower  Lower date limit
 * @param upper  Upper date limit
 */
function isBetween(tdate, lower, upper) {
  if (!isDate(tdate)) {
    throw "Date to test (" + tdate + ") is not a date";
  }
  if (!isDate(lower)) {
    throw "Lower date bound (" + lower + ") is not a date";
  }
  if (!isDate(upper)) {
    throw "Upper date bound (" + upper + ") is not a date";
  }
  var tdateTime = tdate.getTime();
  var lowerTime = lower.getTime();
  var upperTime = upper.getTime();
  return lowerTime <= tdateTime && tdateTime <= upperTime;
}

/**
 * Aggregate data in a dimensional array.
 * @param idate     The date for which the valid rows are extracted.
 *                  Any row is extracted if the idate falls between
 *                  the "Valid from" and "Valid to" dates (inclusive).
 * @param arraz     The dimension table. It requires a header row
 *                  and it must have columns "Valid from" and "Valid to"
 * @param datacols  The column names of the data to work with.
 * @param fname     Name of the function to apply to the data.
 *                  Current values are: "sum", "sumproduct", "count", "counta".
 */
function aggregateDimension(idate, arraz, datacols, fname) {
  var limitArray = extractColumns(["Valid from", "Valid to"], arraz);
  var dataArray = extractColumns(datacols, arraz);
  
  var filteredDataArray = [];
  for (var i = 0; i < limitArray.length; i++) {
    if (isBetween(idate, limitArray[i][0], limitArray[i][1])) {
      filteredDataArray.push(dataArray[i]);
    }
  }

  if (fname == "sum") {
    return aggSum(filteredDataArray);
  }
  if (fname == "sumproduct") {
    return aggSumProduct(filteredDataArray);
  }
  if (fname == "count") {
    return aggCount(filteredDataArray);
  }
  if (fname == "counta") {
    return aggCountA(filteredDataArray);
  }
  throw "Do not recognise function '" + fname + "'";
}

/** Sum all the numbers in the given array
 */
function aggSum(arraz) {
  var sum = 0;
  for (var i = 0; i < arraz.length; i++) {
    var row = arraz[i];
    for (var j = 0; j < row.length; j++) {
      if (isNumber(row[j])) {
        sum += row[j];
      }
    }
  }
  
  return sum;
}

/**
 * Work out the product of each row, and sum them together.
 */
function aggSumProduct(arraz) {
  arraz = make2DArray(arraz);
  var sumProd = 0;
  for (var i = 0; i < arraz.length; i++) {
    var row = arraz[i];
    var prod = 1;
    for (var j = 0; j < row.length; j++) {
      if (isNumber(row[j])) {
        prod = prod * row[j];
      }
    }
    sumProd += prod;
  }
  
  return sumProd;
}

/** Count all the numbers in the given array.
 */
function aggCount(arraz) {
  var count = 0;
  for (var i = 0; i < arraz.length; i++) {
    var row = arraz[i];
    for (var j = 0; j < row.length; j++) {
      if (isNumber(row[j])) {
        count += 1;
      }
    }
  }
  
  return count;
}

/** Count all the non-empty cells in the given array.
 */
function aggCountA(arraz) {
  var count = 0;
  for (var i = 0; i < arraz.length; i++) {
    var row = arraz[i];
    for (var j = 0; j < row.length; j++) {
      if (row[j] != "") {
        count += 1;
      }
    }
  }
  
  return count;
}