Created
September 7, 2012 08:06
-
-
Save brucemcpherson/3664271 to your computer and use it in GitHub Desktop.
Google apps script equivalent for cdataset
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
/** @description | |
* javaScript/Google Apps script functions for abstracting data from sheets | |
* See http://ramblings.mcpher.com/Home/excelquirks/codeuse for more details | |
* @author <a href="mailto:bruce@mcpher.com">Bruce McPherson</a><a href="http://ramblings.mcpher.com"> ramblings.mcpher.com</a> | |
*/ | |
/** | |
* a cDataSet | |
* @class | |
* @implements {cDataSet} | |
* @return {cDataSet} a new cDataSet | |
*/ | |
function cDataSet () { | |
//-------private properties | |
var pCollect = new collection(); | |
var pCollectColumns = new collection(); | |
var pHeadingRow = new cHeadingRow(); | |
//-------public properties ->methods | |
this.rows = function() { | |
return pCollect; | |
}; | |
this.columns = function() { | |
return pCollectColumns; | |
}; | |
this.headingRow = function() { | |
return pHeadingRow; | |
}; | |
return this; | |
}; | |
/** | |
* return the cCell indexed | |
* @param {string|number} rowId the key or dataset row number of the cell | |
* @param {string|number} sid the key (column heading) or dataset column of the cell | |
* @return {cCell} the requested cCell | |
*/ | |
cDataSet.prototype.cell = function(rowId, sid, complain) { | |
if (! (dr = this.row(rowId,complain))){ | |
if (fixOptional ( complain , false)) { | |
MsgBox (rowId.toString() + " is not a known row"); | |
} | |
} | |
else | |
return dr.cell(sid,complain); | |
}; | |
/** | |
* return whether the cell contains a 'true like' value (yes/1/true etc..) | |
* @param {string|number} rowId the key or dataset row number of the cell | |
* @param {string|number} sid the key (column heading) or dataset column of the cell | |
* @return {boolean} whether cell is true | |
*/ | |
cDataSet.prototype.isCellTrue = function(rowId, sid) { | |
return isStringTrue(this.toString(rowId, sid)) ; | |
}; | |
/** | |
* convert the cell value to a string | |
* @param {string|number} rowId the key or dataset row number of the cell | |
* @param {string|number} sid the key (column heading) or dataset column of the cell | |
* @param {string=} sFormat an optional format to use | |
* @return {string} cell converted to use | |
*/ | |
cDataSet.prototype.toString = function(rowId, sid,sFormat) { | |
return this.cell(rowId, sid).toString() ; | |
}; | |
/** | |
* return the cell value | |
* @param {string|number} rowId the key or dataset row number of the cell | |
* @param {string|number} sid the key (column heading) or dataset column of the cell | |
* @param {boolean=} complain whether to complain if cell doesnt exist | |
* @return {*|undefined} cell value | |
*/ | |
cDataSet.prototype.value = function(rowId, sid,complain) { | |
var cc = this.cell(rowId, sid,complain); | |
return cc ? cc.value() : undefined; | |
}; | |
/** | |
* return the cDatarow indexed | |
* @param {string|number} rowId the key or dataset row number of the cell | |
* @param {boolean=} complain whether to complain if row doesnt exist | |
* @return {cDatarow|null} cDatarow | |
*/ | |
cDataSet.prototype.row = function(rowId,complain) { | |
if (!this.isLab()) { | |
if (!isTypeNumber(rowId) ) { | |
MsgBox ("Dataset " + this.name() + " must have labels enabled to use non-numeric labels"); | |
return null; | |
} | |
} | |
return this.exists(rowId,complain); | |
}; | |
/** | |
* return the cDataColumn indexed | |
* @param {string|number} sid the key or dataset column number of the cell | |
* @return {cDataColumn|null} cDataColumn | |
*/ | |
cDataSet.prototype.column = function(sid) { | |
return this.columns().item(sid); | |
}; | |
/** | |
* return the collection of headings for this cDataSet | |
* @return {collection} collection of cCell | |
*/ | |
cDataSet.prototype.headings = function() { | |
return this.headingRow().headings(); | |
}; | |
/** | |
* return the cDataRow indexed | |
* @param {string|number} sid the key or dataset row number of the cell | |
* @param {boolean=} complain whether to complain if row doesnt exist | |
* @return {cDataColumn|null} cDataColumn | |
*/ | |
cDataSet.prototype.exists = function(sid,complain) { | |
return this.rows().item(sid,complain); | |
}; | |
/** | |
* refresh the indexed cells | |
* @param {string|number=} rowId the key or dataset row number of the cell. Default all rows | |
* @param {string|number=} sId the key or dataset column number of the cell. Default all columns | |
* @return {cDataColumn|cDataRow|cCell} whatever was refreshed | |
*/ | |
cDataSet.prototype.refresh = function(rowId,sid) { | |
if (isUndefined(rowID) && isUndefined(sid)) { | |
for ( var i =0; i < this.rows().count(); i ++) { | |
this.row(i+1).refresh(); | |
} | |
} | |
else if (isUndefined(rowID)) { | |
return this.column(sid).refresh(); | |
} | |
else if (isUndefined(sid)){ | |
return this.row(sid).refresh(); | |
} | |
else { | |
return this.cell(rowId,sid).refresh(); | |
} | |
}; | |
/** | |
* commit the contents of the indexed cCells | |
* @param {*=} p a value to first enter in the indexed cell | |
* @param {string|number=} rowId the key or dataset row number of the cell. Default all rows | |
* @param {string|number=} sId the key or dataset column number of the cell. Default all columns | |
* @return {cDataColumn|cDataRow|cCell} whatever was committed | |
*/ | |
cDataSet.prototype.commit = function(p,rowId,sid) { | |
if (isUndefined(rowID) && isUndefined(sid)) { | |
for ( var i =0; i < this.rows().count(); i ++) { | |
this.row(i+1).commit(p); | |
} | |
} | |
else if (isUndefined(rowID)) { | |
return this.column(sid).commit(p); | |
} | |
else if (isUndefined(sid)){ | |
return this.row(sid).commit(p); | |
} | |
else { | |
return this.cell(rowId,sid).commit(p); | |
} | |
}; | |
/** | |
* create a cDataSet - normally called automatically by populateData. | |
* @param {string=} sn a name for the cDataSet - default the same as the source worksheet | |
* @param {boolean=} blab whether to allow rows to have keys- default false | |
* @param {boolean=} keepFresh whether to keep the source sheet cache continually up to date- default false. | |
* @param {boolean=} stopAtFirstEmptyRow whether input data is delimited by a blank row- default true. | |
* @param {string=} sKey if blab (label keys allowed) this specifies the column name of the key - default the name of column 1. | |
* @param {number=} maxDataRows the maximum number of rows to use - default all rows. | |
* @return {cDataSet} the cDataSet | |
*/ | |
cDataSet.prototype.create = function(sn , blab, | |
keepFresh , stopAtFirstEmptyRow , | |
sKey, maxDataRows ) { | |
var rCurrent = vOffset(this.headingRow().where(),1); | |
this.xWhere = rCurrent; | |
this.where = function () { | |
return this.xWhere; | |
} | |
this.xKeepFresh = fixOptional(keepFresh,false); | |
this.keepFresh = function() { | |
return this.xKeepFresh; | |
}; | |
this.xName = makeKey(isUndefined(sn) ? this.xWhere.getSheet().getName() : sn); | |
this.name = function() { | |
return this.xName; | |
}; | |
if ( this.xIsLab = fixOptional ( blab, false)) { | |
if (isUndefined(sKey) ) | |
this.xKeyColumn = 1; | |
else if (this.headingRow().exists(sKey)) { | |
this.xKeyColumn = this.headingRow().exists(sKey).column(); | |
} | |
} | |
this.isLab = function() { | |
return this.xIsLab; | |
}; | |
this.keyColumn = function() { | |
return this.xKeyColumn; | |
}; | |
var m = maxDataRows; | |
if (!m) m = wholeWs(WorkSheet(this.xWhere)).getNumRows(); | |
// create the columns | |
for (var i = 0; i < this.headings().count() ; i++ ) { | |
var hCell = this.headings().item(i+1); | |
var dCol = new cDataColumn(); | |
dCol.create (this, hCell, i+1) ; | |
this.columns().add( dCol, makeKey (hCell.toString())); | |
} | |
//get the shape of a blank delimited table | |
if (m > 0) { | |
this.xWhere = vResize(this.xWhere,m,this.headings().count()); | |
if (stopAtFirstEmptyRow) this.xWhere = toEmptyRow(this.xWhere); | |
//read in the whole lot at once | |
if (this.xWhere) { | |
var rv = sheetCache(this.xWhere).getValues(this.xWhere); | |
var xw = vResize(this.xWhere,1); // avoid repeating making it one row | |
for (var i = 0 ; i < rv.length ; i++) { | |
var dRow = new cDataRow(); | |
dRow.create (this, vOffset(xw,i), i+1 , rv[i]); | |
if (this.isLab()) { | |
var k = makeKey(dRow.cell(this.xKeyColumn).toString()); | |
if (!this.rows().add (dRow, k, false)){ | |
MsgBox ("Could not add duplicate key " + k + | |
" in data set " + this.name() + " column " + | |
this.headings().item(this.xKeyColumn).toString()); | |
} | |
} | |
else { | |
this.rows().add (dRow); | |
} | |
for (var j = 0; j < this.columns().count() ; j++ ) { | |
var dCol = this.columns().item(j+1); | |
dCol.rows().add (dRow.cell(dCol.column())); | |
} | |
} | |
} | |
} | |
else { | |
this.xWhere = Nothing; | |
} | |
return this; | |
}; | |
/** | |
* repopulate and potentially resize a cDataSet - will return a new cDataSet | |
* @return {cDataSet} the cDataSet | |
*/ | |
cDataSet.prototype.rePopulate = function(){ | |
//this repopulates and creates a new cdataset | |
var s = this.xKeyColumn > 0 ? this.headingRow().headings(this.xKeyColumn) : '' ; | |
var newSet = new cDataSet(); | |
//delete it from parent collection | |
if(this.parent()) this.parent().dataSets.remove(this.name()); | |
// recreate it with the same parameters as before | |
return newSet.populateData(firstcell(this.headingRow().where()), undefined , | |
this.name(), this.isLab(), undefined , this.parent(), true, s); | |
}; | |
// TODO | |
cDataSet.prototype.populateDataOptions = function(options) { | |
var jOptions = optionsExtend(options, | |
{ | |
rStart : undefined, | |
keepFresh : false, | |
sn : undefined, | |
blab : false, | |
blockstarts : undefined, | |
ps : undefined, | |
bLikely : true, // different default | |
sKey : undefined, | |
maxDataRows : undefined, | |
stopAtFirstEmptyRow : true | |
} | |
); | |
return this.populateData( | |
jOptions.cValue("rStart"), | |
jOptions.cValue("keepFresh"), | |
jOptions.cValue("sn"), | |
jOptions.cValue("blab"), | |
jOptions.cValue("blockstarts"), | |
jOptions.cValue("ps"), | |
jOptions.cValue("bLikely"), | |
jOptions.cValue("sKey"), | |
jOptions.cValue("maxDataRows"), | |
jOptions.cValue("stopAtFirstEmptyRow") | |
); | |
} | |
/** | |
* create and populate a cDataSet. | |
* @param {Range} rStart a range specifying either the start of or all of the input data set | |
* @param {boolean=} keepFresh whether to keep the source sheet cache continually up to date- default false. | |
* @param {string=} sn a name for the cDataSet - default the same as the source worksheet | |
* @param {boolean=} blab whether to allow rows to have keys- default false | |
* @param {string=} blockstarts the column 1 text at which a cDataSet starts - default - no text search | |
* @param {cDataSets=} ps the cDataSets collection of which this is a part - default - no collection | |
* @param {boolean=} bLikely whether to try to figure out the likely start of a cDataSet if beginning of range is blank - default - false | |
* @param {string=} sKey if blab (label keys allowed) this specifies the column name of the key - default the name of column 1. | |
* @param {boolean=} stopAtFirstEmptyRow whether input data is delimited by a blank row- default true. | |
* @param {number=} maxDataRows the maximum number of rows to use - default all rows. | |
* @return {cDataSet} the cDataSet | |
*/ | |
cDataSet.prototype.populateData = function(rStart, keepFresh, sn, | |
blab,blockstarts, | |
ps, | |
bLikely, | |
sKey, | |
maxDataRows, | |
stopAtFirstEmptyRow) { | |
this.xParent = fixOptional(ps,null); | |
this.parent = function() { | |
return this.xParent; | |
} | |
// the table dimensions to look at | |
var rInput = rStart; | |
if(isUndefined(rInput)) { | |
rInput = getLikelyColumnRange(); | |
} | |
else if (fixOptional (bLikely, false)) { | |
rInput = getLikelyColumnRange(rStart.getSheet()); | |
} | |
// set up name and get actual table range start row | |
var blockName = makeKey(fixOptional (sn, '')); | |
var rp = vResize(rInput,1); | |
if (!isUndefined(blockstarts)) { | |
if (!blockName) blockName = makeKey(blockstarts); | |
rp = cleanFind(blockstarts, vResize(rInput,undefined,1), true, true); | |
if (rp) rp = toEmptyCol (rp); | |
else return rp; | |
} | |
this.headingRow().create (this, rp); | |
this.create (blockName, fixOptional(blab,false), fixOptional(keepFresh,false), | |
fixOptional(stopAtFirstEmptyRow,true), sKey, fixOptional(maxDataRows,0)); | |
return this; | |
}; | |
/** | |
* Commit or Clone a cDataSet to cache and commit the cache. | |
* @param {Range} optrOut a range specifying either the start of or all of the output data. Default is original source | |
* @param {boolean=} optClearWs whether to clear the entire worksheet first- default false. | |
* @param {<Array>.*=} optHeadOrderArray An array of the column headings to be written. Default is all of them | |
* @param {string=} optFilterHead the name of a column to use to selectively output data - default '' | |
* @param {*=} optFilterValue if specified, only rows with values matching this value in optFilterHead column will be output | |
* @param {boolean=} optFilterApproximate if filtering, then whether approximate or exact matching is required- default true | |
* @param {boolean=} optOutputHeadings whether to output a heading row - default true | |
* @return {number} the number of rows written | |
*/ | |
cDataSet.prototype.bigCommit = function (optrOut, | |
optClearWs, | |
optHeadOrderArray, | |
optFilterHead, | |
optFilterValue, | |
optFilterApproximate, | |
optOutputHeadings) { | |
// this one does a quick bulk commit | |
var rTarget = fixOptional( optrOut, this.headingRow().where()); | |
var targetCache = sheetCache(rTarget); | |
//possible that we clear the target worksheet frst | |
if(fixOptional(optClearWs,false)){ | |
targetCache.clear(); | |
} | |
if (IsMissing(optHeadOrderArray)) { | |
// its possible to specify only a subset of columns, or reorder them | |
var headOrder = this.headings(); | |
} | |
else { | |
var headOrder = new collection(); | |
var s = ''; | |
for (var nHeads = 0; nHeads < optHeadOrderArray.length; nHeads++ ){ | |
var hcell = this.headingRow().exists(optHeadOrderArray[nHeads]); | |
if (hcell) { | |
headOrder.add (hcell, hcell.toString()); | |
} | |
else { | |
s += (s ? ',' : '') + optHeadOrderArray[nHeads].toString() ; | |
} | |
} | |
if(s) MsgBox ("These fields do not exist " + s); | |
} | |
//is there a filter ? | |
var filterCol = 0; | |
if(fixOptional(optFilterHead,'')) { | |
var hcell = this.headingRow().exists(optFilterHead); | |
if (hcell) { | |
filterCol = hcell.column(); | |
} | |
else { | |
MsgBox (optFilterHead + " does not exist to filter on..ignoring"); | |
} | |
} | |
// now output to cache | |
var n=0; | |
if(headOrder.count()) { | |
var outputHeadings = fixOptional (optOutputHeadings, true) ; | |
if (outputHeadings) n =1; | |
// we're using cache so no problem stepping through this one by one. | |
var self = this; | |
var i = 0; | |
if (outputHeadings) { | |
headOrder.forEach( | |
function (hcell) { | |
i++; | |
targetCache.setValue(hcell.value(),1,i); | |
} | |
); | |
} | |
this.rows().forEach( | |
function (dr) { | |
if(self.filterOk(dr, filterCol, optFilterValue, optFilterApproximate)) { | |
n++; | |
i=0; | |
headOrder.forEach( | |
function (hcell) { | |
i++; | |
targetCache.setValue(dr.cell(hcell.column()).value(),n,i); | |
} | |
); | |
} | |
} | |
); | |
} | |
targetCache.commit(); | |
return n; | |
}; | |
/** | |
* check to see if a row matches filter specified in bigcommit - used internally | |
* @param {cDataRow} dr the row to consider | |
* @param {number} filterCol the column number of column to check | |
* @param {*} filterValue the value to check against | |
* @param {boolean} optFilterApproximate if filtering, then whether approximate or exact matching is required | |
* @return {boolean} whether this row passes the filter test | |
*/ | |
cDataSet.prototype.filterOk = function(dr, filterCol,filterValue, filterApproximate){ | |
return filterCol ? | |
(filterApproximate ? | |
Like (dr.cell(filterCol),filterValue) : | |
dr.cell(filterCol) == filterValue ) | |
: | |
true; | |
}; | |
cDataSet.prototype.logIt = function(){ | |
DebugPrint(' datasset',this.name(),sad(this.where())); | |
this.rows().forEach ( | |
function(drItem,drIndex) { // for each row | |
DebugPrint(' row',drItem.row(),sad(drItem.where())); | |
drItem.columns().forEach ( | |
function(dcItem,dcIndex) { // for each cell | |
DebugPrint (' item r,c',dcItem.row(),dcItem.column(),dcItem.value(),sad(dcItem.where())); | |
} | |
) | |
} | |
) | |
DebugPrint(' ncols', this.columns().count()); | |
var self = this; | |
this.columns().forEach ( | |
function(doItem,doIndex) { // for each column | |
DebugPrint(' column',doItem.column(),sad(doItem.where())); | |
} | |
) | |
this.headings().forEach ( | |
function(dhItem,dhIndex) { // for each heading | |
DebugPrint(' heading',dhItem.value(),sad(dhItem.where())); | |
} | |
) | |
}; | |
/** | |
* a cCell | |
* @class | |
* @implements {cCell} | |
* @return {cCell} a new cCell | |
*/ | |
function cCell () { | |
}; | |
/** | |
* construct a cCell | |
* @param {cDataRow} par the row this belongs in | |
* @param {number} colNum the column number | |
* @param {Range} rCell it's address | |
* @param {*=} v the value (if not given, will look up the rCell | |
* @return {cCell} for chaining | |
*/ | |
cCell.prototype.create = function(par, colNum , rCell, v ) { | |
this.xColumn = colNum; | |
this.column= function() { | |
return this.xColumn; | |
}; | |
this.xParent = par; | |
this.parent= function() { | |
return this.xParent; | |
}; | |
this.xWhere = rCell; | |
this.where= function() { | |
return this.xWhere; | |
}; | |
this.row = function () { | |
return this.parent().row(); | |
}; | |
if (isUndefined(v)) | |
this.refresh(); | |
else | |
this.xValue = v ; | |
return this; | |
}; | |
/** | |
* refresh a cCell from it's cache | |
* @return {*} the value | |
*/ | |
cCell.prototype.refresh = function(){ | |
return (this.xValue = | |
sheetCache(this.where()).getFirstValueOfRange(this.where())); | |
} | |
/** | |
* whether or not we are supposed to be keeping the range for this ccell constatly refreshed | |
* @return {boolean} whether it needs to be kept fresh | |
*/ | |
cCell.prototype.keepFresh = function() { | |
return this.parent().parent().keepFresh(); | |
}; | |
/** | |
* convert to string | |
* @param {string=} sFormat the format to use (optional - not yet implemented) | |
* @return {string} the cCell value converted to a string | |
*/ | |
cCell.prototype.toString = function(sFormat) { | |
if (isUndefined(sFormat)) { | |
return this.xValue.toString(); | |
} | |
else { | |
//TODO | |
} | |
}; | |
/** | |
* the cCell value | |
* @return {*} the cCell value | |
*/ | |
cCell.prototype.value = function() { | |
return this.keepFresh() ? this.refresh() : this.xValue; | |
}; | |
/** | |
* set the cCell value | |
* @param {*} p the value to set | |
* @return {*} the cCell value | |
*/ | |
cCell.prototype.letValue = function(p) { | |
return this.keepFresh() ? this.commit(p) : (this.xValue = p); | |
}; | |
/** | |
* say whether this needs swapped in a sort | |
* @param {cCell} cc the cell to compare with | |
* @param {ESORT=} es the sort order from the ESORT enum | |
* @return {boolean} swap is needed | |
*/ | |
cCell.prototype.needSwap = function( cc,es){ | |
switch (es) { | |
case ESORT.ESORTAscending: | |
return LCase(this.toString()) > LCase(cc.toString()); | |
case ESORT.ESORTDescending: | |
return LCase(this.toString()) < LCase(cc.toString()); | |
default: | |
return false; | |
} | |
}; | |
/** | |
* commit the cCell value to the sheet cache | |
* @param {*=} p the value to set and then commit (default, the current ccell value) | |
* @return {*} the cCell value | |
*/ | |
cCell.prototype.commit = function (p) { | |
if (!isUndefined(p)) { | |
this.xValue = p; | |
} | |
this.where().setValue(this.xValue); | |
sheetCache(this.where()).makeDirty(); | |
return this.refresh(); | |
}; | |
//--------------------------------------- | |
// conversion of cDataColumn VBA class to Google Apps Script | |
// a collection of data Cells representing one column of data | |
/** | |
* a cDataColumn | |
* @class | |
* @implements {cDataColumn} | |
* @return {cDataColumn} a new cDataColumn | |
*/ | |
function cDataColumn () { | |
//-------private properties | |
var pCollect = new collection(); | |
this.rows = function () { | |
return pCollect; | |
} | |
this.xTypeofColumn = ETYPEOFCOLUMN.eTCunknown; | |
}; | |
/** | |
* return the type of values to be found in this column | |
* @return {ETYPEOFCOLUMN} the type of column from ETYPEOFCOLUMN enum | |
*/ | |
cDataColumn.prototype.typeOfColumn = function (){ | |
return this.xTypeofColumn; | |
} | |
/** | |
* return the type of values to be found in this column - GoogleWire names | |
* @return {string} the type of column | |
*/ | |
cDataColumn.prototype.googleType = function() { | |
switch (this.xTypeofColumn) { | |
case ETYPEOFCOLUMN.eTCnumeric: | |
return "number"; | |
case ETYPEOFCOLUMN.eTCdate: | |
return "date"; | |
default: | |
return "string"; | |
} | |
}; | |
/** | |
* return the column number for this column | |
* @return {number} the column number | |
*/ | |
cDataColumn.prototype.column= function() { | |
return this.xColumn; | |
}; | |
/** | |
* return the cDataSet to which this cDataColumn belongs | |
* @return {cDataSet} the column number | |
*/ | |
cDataColumn.prototype.parent= function() { | |
return this.xParent; | |
}; | |
/** | |
* return the cCell where the heading value for this column can be found | |
* @return {cCell} the column number | |
*/ | |
cDataColumn.prototype.headingCell= function() { | |
return this.xHeadingCell; | |
}; | |
/** | |
* return the Range that describes this column's range | |
* @return {Range} the range for this column and its data | |
*/ | |
cDataColumn.prototype.where = function () { | |
return this.headingCell().where().offset(1,0,this.parent().where().getNumRows(),1); | |
}; | |
/** | |
* return the cCell at a particular row for this column | |
* @param (number|string} rowId the string or row number identifying the required row | |
* @return {Range} the ccell at the given row | |
*/ | |
cDataColumn.prototype.cell = function(rowId){ | |
return this.parent().cell(rowId, this.headingCell().column()); | |
}; | |
/** | |
* refresh the cCell or entire column | |
* @param (number|string=} rowId the string or row number identifying the required row(default- all rows) | |
* @return {*} the value of the ccell at the given row, or null if the whole column | |
*/ | |
cDataColumn.prototype.refresh = function(rowId){ | |
if (isUndefined(rowId)) { | |
for ( var i=0; i < this.rows().count() ; i ++){ | |
this.rows().item(i+1).refresh(); | |
} | |
return null; | |
} | |
else { | |
return (this.cell(rowId)).refresh(); | |
} | |
}; | |
/** | |
* return a collection of cCells containing unique values for this column | |
* @param {ESORT=} es the sort order from the ESORT enum | |
* @return {collection} collection of cCells | |
*/ | |
cDataColumn.prototype.uniqueValues = function(es){ | |
var self = this; | |
var vUnique = new collection(); | |
self.rows().forEach( | |
function (cc) { | |
// just try to add and dont complain if it doesnt work | |
vUnique.add (cc,cc.toString(),false); | |
} | |
); | |
return es == ESORT.ESORTNone ? | |
vUnique : | |
( vUnique.sort ( | |
es == ESORT.ESORTAscending ? undefined : | |
function (a,b) { return (a<b) }) | |
); | |
}; | |
// -- max | |
cDataColumn.prototype.max = function(){ | |
//toDo | |
}; | |
// -- min | |
cDataColumn.prototype.min = function(){ | |
//toDo | |
}; | |
/** | |
* commit the cDataColumn or cCell value to the sheet cache | |
* @param {*=} p the value to set and then commit (default, the current ccell value) | |
* @param {*=} rowId the row to apply this to (default, the whole column) | |
* @return {*} the value of the ccell at the given row, or null if the whole column | |
*/ | |
cDataColumn.prototype.commit = function(p,rowId){ | |
if (isUndefined(rowId)) { | |
for ( var i=0; i < this.rows().count() ; i ++){ | |
this.rows().item(i+1).commit(p); | |
} | |
return null; | |
} | |
else { | |
return (this.cell(rowId)).commit(p); | |
} | |
}; | |
/** | |
* convert cCell to to string | |
* @param {number|string} rowNum the rowId to get the ccell from | |
* @param {string} sFormat the format to use TODO | |
* @return {*} the formated string | |
*/ | |
cDataColumn.prototype.toString = function(rowNum, sFormat) { | |
return (this.cell(rowNum).toString(sFormat)); | |
}; | |
/** | |
* construct a cDataColumn | |
* @param {cDataSet} dSet the cDataSet to which this column belongs | |
* @param {cCell} hCell the cCell holding the header value for this column | |
* @param {hCell} colNum the column number of this cell | |
* @return {cDataColumn} the cDataColumn | |
*/ | |
cDataColumn.prototype.create = function(dSet, hCell, colNum ) { | |
this.xColumn = colNum; | |
this.xParent = dSet; | |
this.xHeadingCell = hCell; | |
return this; | |
}; | |
//--------------------------------------- | |
// conversion of cDataRow VBA class to Google Apps Script | |
/** | |
* cDataRow a collection of cCells representing one row of data | |
* @class | |
* @implements {cDataRow} | |
* @return {cDataRow} a new cDataRow | |
*/ | |
var cDataRow = function () { | |
//-------private properties | |
var pCollect = new collection(); | |
//-------public properties ->methods | |
this.columns = function() { | |
return pCollect; | |
}; | |
}; | |
/** | |
* construct a cDataRow | |
* @param {cDataSet} dSet the cDataSet to which this row belongs | |
* @param {Range} rDataRow the spreadsheet range for this row | |
* @param {number} nRow the row number of this cell in the cDataSet | |
* @param {<Array>.*=} vArray an array of width nRow with the values for this row if known(if not given will be taken from the range) | |
* @return {cDataRow} the cDataRow | |
*/ | |
cDataRow.prototype.create = function (dSet, rDataRow, nRow, vArray) { | |
this.xWhere = rDataRow; | |
this.where = function () { | |
return this.xWhere; | |
}; | |
this.xParent = dSet; | |
this.parent = function () { | |
return this.xParent; | |
}; | |
this.xRow = nRow; | |
this.row = function () { | |
return this.xRow; | |
}; | |
var n = 0; | |
if( !this.xRow) { | |
var r = firstcell(this.xWhere); | |
var nx = this.xWhere.getNumColumns(); | |
for (var i = 0; i < nx ; i++) { | |
var dCell = new cCell(); | |
dCell.create (this,i+1,r); | |
this.columns().add (dCell, makeKey(dCell.toString())); | |
r=vOffset(r,undefined,1); | |
} | |
} | |
else { | |
var hr = this.parent().headingRow(); | |
for ( var i=0; i < hr.headings().count() ; i++ ){ | |
var hCell = hr.headings().item(i+1); | |
var dCell = new cCell(); | |
var rCell = this.where().offset(0,hCell.column()-1,1,1); | |
var v = vArray[i]; | |
dCell.create ( this,hCell.column(),rCell,v); | |
this.columns().add ( dCell) ; | |
// set the type of data | |
var dc = this.parent().columns().item(hCell.column(),true); | |
if (!IsEmpty(v)){ | |
if (dc.xTypeofColumn != ETYPEOFCOLUMN.eTCmixed) { | |
if (IsDate(v)) { | |
if (dc.xTypeofColumn != ETYPEOFCOLUMN.eTCdate) { | |
dc.xTypeofColumn = | |
( dc.xTypeofColumn == ETYPEOFCOLUMN.eTCunknown ? | |
ETYPEOFCOLUMN.eTCdate : ETYPEOFCOLUMN.eTCmixed); | |
} | |
} | |
else if (IsNumeric(v)) { | |
if (dc.xTypeofColumn != ETYPEOFCOLUMN.eTCnumeric) { | |
dc.xTypeofColumn = | |
( dc.xTypeofColumn == ETYPEOFCOLUMN.eTCunknown ? | |
ETYPEOFCOLUMN.eTCnumeric : ETYPEOFCOLUMN.eTCmixed); | |
} | |
} | |
else { | |
if (dc.xTypeofColumn != ETYPEOFCOLUMN.eTCtext) { | |
dc.xTypeofColumn = | |
( dc.xTypeofColumn == ETYPEOFCOLUMN.eTCunknown ? | |
ETYPEOFCOLUMN.eTCtext : ETYPEOFCOLUMN.eTCmixed); | |
} | |
} | |
} | |
} | |
} | |
} | |
return this; | |
}; | |
/** | |
* get the cCell at given column ID | |
* @param {string|number} sid the column name or number to get the cCell from | |
* @param {boolean} complain whether to complain if this is an invalid address | |
* @return {cCell} the cCell | |
*/ | |
cDataRow.prototype.cell = function(sid,complain) { | |
var cc = null; | |
if (! (cc = this.exists(sid))){ | |
if (fixOptional ( complain , true)) { | |
MsgBox (sid.toString() + " is not a known column heading"); | |
} | |
} | |
return cc; | |
}; | |
/** | |
* get the value at given column ID | |
* @param {string|number} sid the column name or number to get the value from | |
* @return {*} the value | |
*/ | |
cDataRow.prototype.value = function(sid) { | |
var cc = this.cell(sid); | |
if (cc) { | |
return cc.value(); | |
} | |
}; | |
/** | |
* refresh the cCell at given column ID | |
* @param {string|number=} sid the column name or number to refresh(default the whole row) | |
* @return {cCell|null} the cCell if a single cCell specified | |
*/ | |
cDataRow.prototype.refresh = function(sid) { | |
if (isUndefined(sid)) { | |
this.columns().forEach( | |
function (cItem,cIndex) { | |
cItem.refresh(); | |
} | |
) | |
} | |
else{ | |
return this.cell(sid).refresh(); | |
} | |
}; | |
/** | |
* commit the cCell at given column ID | |
* @param {*=} p the value to commit (default the current cCell value) | |
* @param {string|number=} sid the column name or number to refresh(default the whole row) | |
* @return {cCell|null} the cCell if a single cCell specified | |
*/ | |
cDataRow.prototype.commit = function(p, sid) { | |
if (isUndefined(sid)) { | |
this.columns().forEach( | |
function (cItem,cIndex) { | |
cItem.commit(p); | |
} | |
) | |
} | |
else{ | |
return this.cell(sid).commit(p); | |
} | |
}; | |
/** | |
* convert the cCell at given column ID to a string | |
* @param {string|number} sid the column name or number to format | |
* @param {string=} sFormat the format to use (TODO) | |
* @return {string} the converted string | |
*/ | |
cDataRow.prototype.toString = function(sid, sFormat) { | |
return (this.cell(sid).toString(sFormat)); | |
}; | |
/** | |
* return the cCell at the given column if it exists | |
* @param {string|number} sid the column name or number to format | |
* @param {boolean=} complain whether or not to complain if ccel doesnt exist | |
* @return {cCell} the cCell | |
*/ | |
cDataRow.prototype.exists = function(sid,complain){ | |
var c = fixOptional (complain, false); | |
return isTypeNumber (sid) ? | |
this.columns().item(sid,c) : | |
this.columns().item(this.parent().headings().item(sid,c).column(), c ) ; | |
}; | |
//---------------------------------------------------------- | |
// conversion of cHeadingRow VBA class to Google Apps Script | |
// a data rows representing headings in a table | |
var cHeadingRow = function () { | |
this.xDataRow = new cDataRow(); | |
}; | |
cHeadingRow.prototype.create = function(dSet, rHeading, keepFresh){ | |
this.xDataRow.create ( dSet, rHeading,0, keepFresh); | |
return this; | |
}; | |
cHeadingRow.prototype.dataRow = function(){ | |
return this.xDataRow; | |
}; | |
cHeadingRow.prototype.parent = function(){ | |
return this.xDataRow.parent(); | |
}; | |
cHeadingRow.prototype.headings = function(){ | |
return this.xDataRow.columns(); | |
}; | |
cHeadingRow.prototype.where = function(){ | |
return this.xDataRow.where(); | |
}; | |
cHeadingRow.prototype.exists = function(s,complain){ | |
return this.headings().item(makeKey(s),complain); | |
}; | |
cHeadingRow.prototype.validate = function(complain,args){ | |
var s =''; | |
for( var i = 1; i < arguments.length; i++ ) { | |
if (!this.exists(arguments[i],false)) { | |
if (s) s += ","; | |
s += arguments[i].toString() ; | |
} | |
} | |
if (s) { | |
if (complain) { | |
MsgBox("The following required columns are missing from dataset " + | |
this.parent().name() + ":" + s); | |
} | |
return false; | |
} | |
else | |
return true; | |
}; | |
//--------------------------------------------- | |
// conversion of cDataSets VBA class to Google Apps Script | |
// a collection of cDataSets | |
var cDataSets = function () { | |
//-------private properties | |
var pCollect = new collection(); | |
var pName ='DataSets'; | |
//-------public properties ->methods | |
this.dataSets = function() { | |
return pCollect; | |
}; | |
this.dataSet = function(n,complain) { | |
return pCollect.item(n,fixOptional(complain,false)); | |
}; | |
this.name = function(){ | |
return pName; | |
} | |
this.create = function(sName) { | |
if (!isUndefined(sName)) pName = sName; | |
return this; | |
}; | |
this.init = function(rInput, keepFresh, | |
sn , | |
blab , blockstarts , | |
bLikely , | |
sKey) { | |
ds = new cDataSet(); | |
ds.populateData (rInput, keepFresh, sn, blab, blockstarts, this, bLikely, sKey); | |
return pCollect.add(ds,ds.name()); | |
}; | |
}; | |
cDataSets.prototype.logIt=function(){ | |
DebugPrint('datassets',this.name(),this.dataSets().count() + ' datasets'); | |
this.dataSets().forEach( // for each dataset | |
function(dsItem,dsIndex) { | |
dsItem.logIt(); | |
} | |
) | |
}; | |
//------------------------------------------ |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment