Skip to content

Instantly share code, notes, and snippets.

Embed
What would you like to do?
Burn-up chart script
/**
* 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;
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
You can’t perform that action at this time.