Skip to content

Instantly share code, notes, and snippets.

@brainysmurf

brainysmurf/DBSheets.gs

Last active September 24, 2023 12:13
Show Gist options
  • Save brainysmurf/6f44c41766b16faa3d03878b32be2b25 to your computer and use it in GitHub Desktop.
Save brainysmurf/6f44c41766b16faa3d03878b32be2b25 to your computer and use it in GitHub Desktop.
Download ZIP
DBSheeets
Raw
DBSheets.gs
(function (global, Factory) {
global.pkg = global.pkg || {};
global.pkg.dbsheets = (function wrapper (args) {
var wrapped = function () { return Factory.apply(Factory, arguments); }
for (i in args) { wrapped[i] = args[i]; }
return wrapped;
}({
extend: {
registered: [],
registerInit: function (func) {
this.registered.push(func);
},
execInitCallbacks: function (dbObj) {
if (this.registered) {
this.registered.forEach(function (func) {
func.call(this, dbObj);
});
}
},
},
utils: { /* Utility functions */
contextManager: function () { return pkg.contextManager(); },
transpose: function (arr) {
return Object.keys(arr[0]).map(function(column) {
return arr.map(function(row) { return row[column]; });
});
},
zeroIndexedToColumnName: function (n) {
var ordA = 'A'.charCodeAt(0);
var ordZ = 'Z'.charCodeAt(0);
var len = ordZ - ordA + 1;
var s = "";
while(n >= 0) {
s = String.fromCharCode(n % len + ordA) + s;
n = Math.floor(n / len) - 1;
}
return s;
},
str_to10: function(str, base) {
var lvl = str.length - 1;
var val = (base || 0) + Math.pow(26, lvl) * (str[0].toUpperCase().charCodeAt() - 64 - (lvl ? 0 : 1));
return (str.length > 1) ? pkg.dbsheets.utils.str_to10(str.substr(1, str.length - 1), val) : val;
},
hexToColor: function (hex) {
var result = /^#?([a-f\d]{2})([a-f\d]{2})([a-f\d]{2})$/i.exec(hex);
return result ? {
red: Math.floor(parseInt(result[1], 16) * 255),
green: Math.floor(parseInt(result[2], 16) * 255),
blue: Math.floor(parseInt(result[3], 16) * 255),
alpha: 1,
} : null;
},
makeBandingProperties: function (headerHex, firstBandHex, secondBandHex, footerHex) {
return {
headerColor: pkg.dbsheets.utils.hexToColor(headerHex),
firstBandColor: pkg.dbsheets.utils.hexToColor(firstBandHex),
secondBandColor: pkg.dbsheets.utils.hexToColor(secondBandHex),
footerColor: pkg.dbsheets.utils.hexToColor(footerHex),
}
},
},})
); // end global.pkg.dbsheets
})(this,
function Package (options) {
options = options || {}; // options for defaults
options.dimension = options.dimension || 'ROWS';
options.keyHeaderRow = options.keyHeaderRow || 0;
options.destInfo = options.destInfo || []; // for the form!
/*
The private, main constructor
*/
var DbSheet = function (ss) {
this.ss = ss;
this.plugins = [];
}
var BuilderObj = pkg.dbsheets.builder(options);
/*
All errors that occur (except those at object creation) eventually go through here
Rewraps the calls into better wording errors
Does not return always throws an error
FIXME: Instead of using built-in objects, instead use UrlFetch which will streamline this
*/
var processApiCall = function (err) {
Logger.log(err);
switch (err.details.code) {
case 400: // At the moment all of them are error codes with 400 ... BOOO
throw Error(err.message)
break;
default:
throw Error("Unimplemented error " + err.detail.error + ", inspect log for details: ");
break;
}
};
var dbSheetPrototype = {
setDimensionAsColumns: function () {
options.dimension = 'COLUMNS';
},
setDimensionAsRows: function () {
options.dimension = 'ROWS';
},
/*
* Called when something changed
*/
updated: function () {
this.ss = Sheets.Spreadsheets.get(this.getId());
},
ssUpdaterWrapper: function (fn) {
return function () {
fn.apply(this, arguments);
this.updated();
};
},
getId: function () {
return this.ss.spreadsheetId;
},
api: {
/*
* Returns the resource, for obj.ranges is specified then that means we are
* attempting to retrieve values
*/
get: function (spreadsheetId, obj) {
try {
return Sheets.Spreadsheets.get(spreadsheetId, obj);
} catch (err) {
processApiCall(err);
}
},
batchUpdate: function (resource, spreadsheetId) {
var response;
try {
response = Sheets.Spreadsheets.batchUpdate(resource, spreadsheetId);
} catch (err) {
processApiCall(err);
}
/*
* updatedSpreadsheet is definiately not returned. Oi vey
*/
//this.ss = Sheets.Spreadsheets.get(spreadsheetId);
},
/*
This is a less efficient way of setting values, but supported
*/
values: {
batchUpdate: function (resource, spreadsheetId) {
var response;
try {
response = Sheets.Spreadsheets.Values.batchUpdate(resource, spreadsheetId);
} catch (err) {
processApiCall(err);
}
},
}
},
processBuilder: function (obj) {
var resolvedRequests;
if (obj.preSSRequests.length > 0) {
resolvedRequests = obj.preSSRequests.reduce(function (acc, item) {
var requestObj;
requestObj = item.call(this);
if (requestObj.commit) {
requestObj = null;
if (acc.length > 0) {
this.api.batchUpdate({requests:acc}, this.getId()); // TODO: What about "empty response" error
this.updated();
acc = [];
}
}
if (requestObj != null) acc.push(requestObj)
return acc;
}.bind(this), []);
if (resolvedRequests.length > 0) {
// Can end up with with simple commit
this.api.batchUpdate({requests:resolvedRequests}, this.getId()); // TODO: What about "empty response" error
}
}
if (obj.sRequests.length > 0) {
if (obj._tabsAutoClear) {
var allSheets = obj.sRequests.reduce(function (acc, item) {
acc.push(item.call(this).range.match(/(.*)!/)[1]);
return acc;
}.bind(this), []);
allSheets.filter(function (i, p, a) {
return a.indexOf(i) == p;
}).forEach(function (sheetName) {
this.clearTab(sheetName); // use the
}.bind(this));
}
// resolve the requests
resolvedRequests = obj.sRequests.reduce(function (acc, item) {
acc.push(item.call(this));
return acc;
}.bind(this), []);
this.api.values.batchUpdate({
valueInputOption: "USER_ENTERED",
data: resolvedRequests
}, this.getId());
}
if (obj.postSSRequests.length > 0) {
resolvedRequests = obj.postSSRequests.reduce(function (acc, item) {
acc.push(item.call(this));
return acc;
}, []);
this.api.batchUpdate({requests:resolvedRequests}, this.getId()); // TODO: What about "empty response" error
}
},
makeRequestBuilder: function () {
return new BuilderObj(this);
},
};
var dbSheetPrototype2 = {
valuesBatchUpdate: dbSheetPrototype.ssUpdaterWrapper(function (request) {
return this.api.values.batchUpdate(request, this.getId());
}),
getValues: function (range) {
var response = Sheets.Spreadsheets.Values.get(this.getId(), range, {
majorDimension: options.dimension,
valueRenderOption: "UNFORMATTED_VALUE"
});
return response.values || [[]];
},
getGridValues: function (a1Notation, mode) {
mode = mode || 'userEnteredValue';
// https://issuetracker.google.com/71334456
// If --^ gets fixed, this would be a whole lot better
var response;
// NOTE: This api call saves back to this.ss, so no need to get the reponse
response = this.api.get(this.getId(), {ranges: a1Notation, fields: "properties,sheets(data(startRow,startColumn,rowData(values("+ mode + "))))"});
if (!response.sheets) {
throw Error("No data found, does this sheet exist?");
}
if (!response.sheets[0].data[0].rowData) {
throw Error("No row data found!");
}
return response.sheets[0].data[0].rowData.reduce(function (acc, row) {
if (!row.values) return acc;
var obj;
obj = row.values.reduce(function (a, r) {
var o;
// from fields spec we know there will only be one property (stringValue or booleanValue or formulaValue)
// so whatever that is, return its value
for (var p in r[mode]) {
o = r[mode][p];
}
a.push(o);
return a;
}, []);
acc.push(obj);
return acc;
}, []);
},
getUserEnteredValues: function (a1Notation) {
return this.getGridValues(a1Notation, 'userEnteredValue');
},
clearRange: function (range) {
Sheets.Spreadsheets.Values.clear({}, this.getId(), range);
},
clearTab: function (tabTitle) {
var targetTab;
targetTab = this.getSheets().filter(function (sheet) {
return sheet.properties.title == tabTitle;
});
if (targetTab && targetTab.length == 1) {
this.clearRange(tabTitle + '!1:' + targetTab[0].properties.gridProperties.rowCount.toString());
}
},
/*
Converts a1notation used with "this" the spreadsheet so that it returns gridRange
https://developers.google.com/sheets/api/reference/rest/v4/spreadsheets#GridRange
Reminder that the endRowIndex and endColumnIndex are not defined if boundless, and are
the open part of the "half open" (beware one-off errors!)
*/
a1notation2gridrange: function (a1notation) {
var data, co1, co2, sheetId;
data = a1notation.match(/(^.+)!(.+):(.+$)/);
if (data == null) {
data = a1notation.match(/(.+):(.+$)/);
if (data == null) {
data = a1notation.match(/(^.+)!(.+$)/);
if (data == null) return { sheetId: this.getSheetId(a1notation),};
data.push(""); // make data[3] nothing so co2 will be null
} else {
data.splice(1, 0, null); // insert null for sheet, as first match worked
}
}
sheetId = (data[1] == null) ? null : this.getSheetId(data[1]);
co1 = data[2].match(/(\D+)(\d+)/);
co2 = data[3].match(/(\D+)(\d+)/);
var gridRange, startColumnIndex, endColumnIndex;
if (co1)
startColumnIndex = pkg.dbsheets.utils.str_to10(co1[1]);
else
startColumnIndex = pkg.dbsheets.utils.str_to10(data[2]);
if (co2)
endColumnIndex = pkg.dbsheets.utils.str_to10(co2[1], 1);
else
if (data[3])
endColumnIndex = pkg.dbsheets.utils.str_to10(data[3], 1);
else
endColumnIndex = null;
gridRange = {
sheetId: sheetId,
startRowIndex: co1 ? parseInt(co1[2], 10) - 1 : null,
endRowIndex: co2 ? parseInt(co2[2], 10) : null,
startColumnIndex: startColumnIndex,
endColumnIndex: endColumnIndex,
};
if (gridRange.startRowIndex == null) delete gridRange.startRowIndex;
if (gridRange.endRowIndex == null) delete gridRange.endRowIndex;
return gridRange;
},
/*
@param {Number,String} sheet if number, returns the sheet at index
if name, return the sheet that has that name
@throws {Error} if sheet is not a number or not a string
@return {Object} returns the target sheet object
@TODO: Use network call to update
*/
getSheet: function (sheet) {
if (typeof sheet == "number") {
for (var i = 0; i < this.ss.sheets.length; i++) {
if (this.ss.sheets[i].properties.sheetId == sheet) return this.ss.sheets[i];
}
return null;
}
if (typeof sheet == "string") {
var sheetName = sheet.split("!")[0]; // take out the
for (var i = 0; i < this.ss.sheets.length; i++) {
if (this.ss.sheets[i].properties.title == sheetName) return this.ss.sheets[i];
}
return null;
}
throw new Error("Passed in " + typeof sheet + " into getSheet");
},
getSheetId: function (sheet) {
var ret;
ret = this.getSheet(sheet);
if (!ret) throw Error("No sheet '" + sheet + "'");
return ret.properties.sheetId;
},
getSheets: function () {
return this.ss.sheets;
},
/*
toRange: Convenience function to convert variables into a A1Notation string
@return {String} Legal A1Notation
*/
toRange: function (title, left, right) {
if (title.indexOf(' ') !== -1)
title = "'" + title + "'";
if (typeof right === 'undefined')
return title + '!' + left.toString() + ':' + left.toString();
else
return title + '!' + left.toString() + ':' + right.toString();
},
/*
* Returns an object useful
*/
getActiveInfo: function () {
var ss, sheet, range, col, row;
ss = SpreadsheetApp.getActiveSpreadsheet();
sheet = SpreadsheetApp.getActiveSheet();
range = SpreadsheetApp.getActiveRange();
col = range.getA1Notation().match(/[A-Z]+/)[0];
row = range.getA1Notation().match(/[0-9]+/)[0];
return {
iAmActive: ss.getId() == this.getId(),
sheet: sheet,
range: range,
ss: ss,
activeSsId: ss.getId(),
activeRange: range.getA1Notation(),
activeA1Notation: sheet.getName() + '!' + range.getA1Notation(),
activeSheet: sheet.getName(),
activeRow: row,
activeColumn: col,
}
},
getHeaders: function (sheet) {
var sht, numHeaders, values
sht = this.getSheet(sheet);
if (!sht) // may be either undefined or null
return [[]];
numHeaders = sht.properties.gridProperties.frozenRowCount || 1;
if (numHeaders == 0)
return [[]];
// Fill in the remaining in case of empty spaces
values = this.getValues(this.toRange(sht.properties.title, 1, numHeaders));
if (values.length < numHeaders) {
var emptyRow = [], howManyColumns;
howManyColumns = Math.max(values.reduce(function (acc, row) {
acc.push(row.length);
return acc;
}, []));
for (var i = 0; i < howManyColumns; i++) {
emptyRow.push("");
}
for (var j = 0; j < (numHeaders - values.length); i++) {
values.push(emptyRow);
}
}
return values;
},
getRange: function ( ) {
var ss = SpreadsheetApp.openById(this.getId());
return ss.getRange.apply(ss, arguments);
},
argsToGrid: function () {
if (arguments.length == 1 && typeof arguments[0] == 'string')
return this.a1notation2gridrange(arguments[0]);
else if (arguments.length == 1 && typeof arguments[0] == 'object')
return arguments[0];
else
throw Error("Unknown args sent to argsToGrid");
},
gridToA1Notation: function (grid) {
var sheetDef, left, right;
if (Object.keys(grid).length == 1 && typeof grid.sheetId != undefined)
return this.getSheet(grid.sheetId).properties.title;
sheetDef = grid.sheetId == null ? "" : this.getSheet(grid.sheetId).properties.title;
// handle A:A and 1:2 types
if (grid.startRowIndex == null && grid.endRowIndex == null) {
var col = pkg.dbsheets.utils.zeroIndexedToColumnName(grid.startColumnIndex);
return sheetDef + '!' + col + ":" + col;
} else if (grid.startColumnIndex == undefined && grid.endColumnIndex == undefined) {
return sheetDef + '!' + grid.startRowIndex + ":" + grid.startRowIndex;
}
left = pkg.dbsheets.utils.zeroIndexedToColumnName(grid.startColumnIndex) + (grid.startRowIndex + 1).toString();
right = pkg.dbsheets.utils.zeroIndexedToColumnName(grid.endColumnIndex-1) + (grid.endRowIndex ? grid.endRowIndex : ""); // one off potential here...
return sheetDef + '!' + left + (right == "" || left == right ? "" : ":" + right);
},
expandGridToDataTable: function (grid) {
// FIXME: This doesn't do the same as getDataRange
var sheet;
sheet = this.getSheet(grid.sheetId);
grid.startColumnIndex = 0;
grid.endColumnIndex = sheet.properties.gridProperties.columnCount + 1;
var fr = sheet.properties.gridProperties.frozenRowCount;
grid.startRowIndex = fr != null ? fr : 0;
grid.endRowIndex = sheet.properties.gridProperties.rowCount + 1;
return grid;
},
toObject: function () {
var grid, a1notation, headers, obj, objGrid, heading;
grid = this.argsToGrid.apply(this, arguments);
a1notation = this.gridToA1Notation(grid);
headers = this.getHeaders(grid.sheetId);
headings = headers[options.keyHeaderRow]; // headings to use as keys
obj = {columns: {}};
for (var h = 0; h < headers.length; h++) {
heading = headings[h];
obj[heading] = {
value: this.getUserEnteredValues(a1notation),
a1Notation: a1notation,
grid: grid,
headers: headers[h],
c: grid.startColumnIndex,
r: grid.startRowIndex,
column: pkg.dbsheets.utils.zeroIndexedToColumnName(h),
row: (grid.startRowIndex+1).toString(),
}
obj.columns[heading] = pkg.dbsheets.utils.zeroIndexedToColumnName(h) + (grid.startRowIndex+1).toString();
}
return obj;
},
/*
Uses the sheet's headers and range values and converts them into the properties
@param {string} rangeA1Notation The range string
@param {object} sheet, left, right
@returns {List[Object]}
*/
toObjects: function () {
var grid, a1notation, headers, numHeaders, headings, values, range, rowOffset, columnOffset;
grid = this.argsToGrid.apply(this, arguments);
grid = this.expandGridToDataTable(grid);
a1notation = this.gridToA1Notation(grid);
headers = this.getHeaders(grid.sheetId);
numHeaders = headers.length;
headings = headers[options.keyHeaderRow]; // headings to use as keys
values = this.getUserEnteredValues(a1notation);
// Ensure to adjust the offset to enture it is after headers
if (grid.startRowIndex == undefined || grid.startRowIndex < headers.length) {
rowOffset = numHeaders;
values = values.slice(numHeaders);
} else {
rowOffset = grid.startRowIndex || numHeaders;
}
columnOffset = grid.startColumnIndex || 0;
headers = pkg.dbsheets.utils.transpose(headers); // transpose so we can refehence by column below
var ro, co, header, heading, obj, objGrid, ret = [];
// Loop through the values
// We need to use headings.length in nested loop to ensure that
// even columns at the end that are blank come through
for (var r = 0; r < values.length; r++) {
ro = r + rowOffset;
obj = {columns: {}};
for (var c = 0; c < headings.length; c++) {
co = c + columnOffset;
heading = headings[c];
objGrid = {
sheetId: grid.sheetId,
startRowIndex: ro,
startColumnIndex: c,
endRowIndex: ro + 1,
endColumnIndex: c + 1
},
obj[heading] = {
value: values[r][c],
a1Notation: this.gridToA1Notation(objGrid),
grid: objGrid,
headers: headers[c],
c: co,
r: ro,
column: pkg.dbsheets.utils.zeroIndexedToColumnName(c),
row: (ro+1).toString(), /* one-off errors are a real bleep */
};
obj.columns[heading] = pkg.dbsheets.utils.zeroIndexedToColumnName(c) + (ro+1).toString();
}
ret.push(obj);
}
return ret;
},
setKeyHeadingRow: function (value) {
options.keyHeaderRow = value - 1; // a1notation is 1-index, might as well keep that part consistent
},
registerPlugin: function (description, func) {
this.plugins.push({description: description, func: func});
},
clearPlugins: function () {
this.plugins = [];
},
/* FIXME: This is WRONG and confusing */
insertRow: function (range, row) {
return Sheets.Spreadsheets.Values.append({
majorDimension: options.dimension,
values: [row]
}, this.getId(), range, {
valueInputOption: "USER_ENTERED",
insertDataOption: "INSERT_ROWS",
});
},
getPluginsOverwriteBuildRequests: function (rangeA1Notation) {
var objs, grid, ret;
objs = this.toObjects(rangeA1Notation); // convert to A1
grid = this.a1notation2gridrange(rangeA1Notation);
// cycle through the plugins and build results array
var plugin, res, ret = [];
for (var pluginIndex = 0; pluginIndex < this.plugins.length; pluginIndex++) {
plugin = this.plugins[pluginIndex];
res = objs.reduce(function (a, obj) {
var prop, objValue, targetHeader, regexp, match, newValue;
for (prop in obj) {
if (prop == 'columns')
continue;
objValue = obj[prop];
targetHeader = objValue.headers[plugin.description.entryPoint.header - 1];
if (typeof targetHeader == 'undefined') {
throw Error("No target header found for headers: " + objValue.headers);
}
if (plugin.description.match)
regexp = new RegExp(plugin.description.match);
else
regexp = new RegExp('^' + plugin.description.name + '$');
match = targetHeader.match(regexp);
if (match) {
newValue = plugin.func(objValue, {header: targetHeader, objects: objs});
var type = newValue['typeof'];
if (['Number', 'Date', 'Boolean', 'String'].indexOf(type) == -1) {
newValue = newValue.stringify();
}
if (type === 'String') {
newValue = newValue.format(objValue); // overwrites
newValue = newValue.format(obj.columns);
}
a.push({
values: [[newValue]],
a1Notation: objValue.a1Notation,
grid: objValue.grid
});
}
}
return a;
}, []);
ret.push(res);
}
return ret.reduce(function (acc, row) {
var objs;
objs = row.filter(function (obj) { /* filter out those not within the range of a1notation */
if (grid.endColumnIndex === undefined && grid.endRowIndex === undefined)
return grid.startColumnIndex == grid.startColumnIndex && grid.startColumnIndex == grid.startRowIndex;
else
return ((grid.startColumnIndex === undefined) || (obj.grid.startColumnIndex >= grid.startColumnIndex)) &&
((grid.endColumnIndex === undefined) || (obj.grid.endColumnIndex <= grid.endColumnIndex)) &&
((grid.startRowIndex === undefined) || (obj.grid.startRowIndex >= grid.startRowIndex)) &&
((grid.endRowIndex === undefined) || (obj.grid.endRowIndex <= grid.endRowIndex));
});
if (objs.length > 0) acc.push(objs);
return acc;
}, []);
},
overwriteWithPlugins: function (rangeA1Notation) {
var requests = this.getPluginsOverwriteBuildRequests(rangeA1Notation);
// Add value requests from results and allow the sheet to update
this.withRequestBuilder(function (rb) {
requests.forEach(function (pluginItems) {
pluginItems.forEach(function (item) {
rb.setValues(item.a1Notation, item.values);
});
});
});
},
inputValues: function (rangeNotation, values) {
var request = {
valueInputOption: 'USER_ENTERED',
data: [
{
range: rangeNotation,
majorDimension: options.dimension,
values: values
}
]
};
return this.valuesBatchUpdate(request);
},
getEffectiveValues: function (range) {
return this.getValues(range);
},
getColumnValues: function (range, column) {
saved = options.dimension;
this.setDimensionAsColumns();
var values = this.getValues(range);
options.dimension = saved;
return values[column].slice();
},
addSheets: function (sheets) {
//Logger.log(_ss.sheets);
},
getDestinationInfo: function () {
return options.destInfo;
},
setDestinationForForm: function (formCreationFunc) {
var before = [];
var ctx = pkg.dbsheets.utils.contextManager()({
enter: function (form) {
this.getSheets().forEach(function (b) {
var id = b.properties.sheetId;
before.push(id);
});
return form;
},
exit: function (form) {
if (typeof form === 'undefined') {
options.destInfo.push({id: null, sheetId: null, error: "Did not pas form into exit"});
return;
}
form.setDestination(FormApp.DestinationType.SPREADSHEET, this.getId());
var after = null;
this.getSheets().forEach(function (a) {
if (before.indexOf(a.properties.sheetId) === -1) {
after = a;
}
});
if (after == null) {
options.destInfo.push({id: null, sheetId:null, error: "Could not detect after creation."});
} else {
options.destInfo.push({id: this.getId(), sheet: after, sheetId: after.properties.sheetId, index: after.properties.index, error: false});
}
}
});
ctx.call(this, formCreationFunc);
return options.destInfo;
},
withRequestBuilder: dbSheetPrototype.ssUpdaterWrapper(pkg.dbsheets.utils.contextManager().call(this, {
enter: function (obj) {
obj.preSSRequests = [];
obj.sRequests = [];
obj.postSSRequests = [];
return obj;
},
exit: dbSheetPrototype.processBuilder,
params: function () { return [this.makeRequestBuilder()]; }, // new BuilderObj(this)
})),
}; // DbSheet()
DbSheet.prototype = Object.assign(dbSheetPrototype, dbSheetPrototype2);
/*
customBuilder allows end user devs define a function that has 'this'
as the builder object
*/
pkg.dbsheets.extend.customBuilder = function (definition) {
var namespace;
for (namespace in definition) {
BuilderObj.prototype[namespace] = definition[namespace];
}
};
/*
* Main object exposes constructors which all return DbSheet instance
*/
var dbObj = {};
dbObj.fromId = function (spreadsheetId) {
return new DbSheet(Sheets.Spreadsheets.get(spreadsheetId));
};
dbObj.fromRange = function (range) {
return dbObj.fromId(range.getSheet().getParent().getId());
};
dbObj.createWithProperties = function (resource) {
return new DbSheet(Sheets.Spreadsheets.create(resource));
};
dbObj.createWithTitle = function (title) {
return dbObj.createWithProperties({properties: {title: title}});
};
dbObj.fromActive = function () {
var ss, dbsheet;
ss = SpreadsheetApp.getActiveSpreadsheet();
return dbObj.fromId(ss.getId());
};
pkg.dbsheets.extend.execInitCallbacks(dbObj);
return dbObj;
});
Raw
dev.gs
(function () {
return {
self: {"dbsheets": "6f44c41766b16faa3d03878b32be2b25"},
packages: [
{"utgs": "07eaf09769b450f1e0e7b6ae043c2ba5"},
{"dbsheetsTemp": "b0d6df8e1cda1139f856de4dbf073d6d"},
]
};
})();
Raw
setup.gs
(function () {
return {
version: 0,
packages: [
{"contextManager": "3d2064f689d3652e6c9bb5631868074b"},
{"stringFormat": "b4394974047428edccef27b2abcc4fb3"},
]
};
})();
Raw
unittestsMain.gs
function testing_dbsheets() {
var DBSheets;
DBSheets = pkg.dbsheets();
pkg.utgs.init();
DBSheets.withTempSpreadsheet(function (tmp) {
tmp.withRequestBuilder(function (rb) {
// Make tabs that are needed below
rb.newTab('newapi')
.newTab('calcrows')
.newTab('calccols')
.newTab('headers')
.newTab('Values')
.newTab('appends')
.newTab('autoclear')
.newTab('plugin')
.newTab('templates')
.newTab('formulatemplates')
.newTab('unique')
.newTab('uber')
.newTab('insert')
.newTab('updateCells');
});
describe("setValues api with rb.transpose and formulas", function () {
it("last definition holds, formulas resolve", function () {
tmp.withRequestBuilder(function (rb) {
rb.setValues('newapi!A1', [['hi', 'there', 'everyone']])
.setValues('newapi', 'A2', [['hello']])
.setValues('newapi', 4, [['d4', '5', '6', '7']])
.setValues('newapi', 'D4', 'D', rb.transpose([['100', '5', '6', '7']]))
.setValues('newapi', 'D8', [['=SUM(D4:D7)']]);
});
var data = tmp.getEffectiveValues('newapi!A1:D8');
assert.arrayEquals({
expected: [['hi', 'there', 'everyone'], ['hello'], [], ['d4', 5, 6, 100], ['','','',5], ['','','',6], ['', '','', 7], ['','','', 118]],
actual: data
});
});
});
describe("setValues api with new tab", function () {
it("new tabs can be managed with commit method", function () {
tmp.withRequestBuilder(function (rb) {
rb.newTab('NewTab')
.newTab('AnotherTab')
.commit()
.setValues('NewTab!A1', [['hi']])
.setNumColumns('NewTab', 2)
.setNumRows('NewTab', 1)
.setValues('AnotherTab!A1', [['hi']]);
});
var data = tmp.getEffectiveValues('NewTab!A1');
assert.arrayEquals({
expected: [['hi']],
actual: data
});
});
});
describe("Initialization", function () {
it("from range", function () {
var range = tmp.getRange('Sheet1!A1:A');
var result = DBSheets.fromRange(range);
assert.equals({expected: tmp.getId(), actual: result.getId()});
});
});
describe("Setting formulas", function () {
it("Calculates with row dimension", function () {
tmp.setDimensionAsRows();
var a1Notation = 'calcrows!A1:D1';
tmp.inputValues(a1Notation, [["Hey", "there", "=3+1"]]);
var values = tmp.getEffectiveValues(a1Notation);
assert.arrayEquals({expected: [["Hey", "there", 4]], actual: values});
tmp.clearRange(a1Notation);
});
it("Calculates with columns dimension", function () {
tmp.setDimensionAsColumns();
var a1Notation = 'calccols!A1:D1';
tmp.inputValues(a1Notation, [["Hey"], ["there"], ["=3+1"]]);
var values = tmp.getEffectiveValues(a1Notation);
assert.arrayEquals({expected: [["Hey"], ["there"], [4]], actual: values});
tmp.setDimensionAsRows(); // TODO: Make this a context manager?
});
it("appends by moving down row", function () {
tmp.inputValues('appends!A3:E3', [['this', 'is', 'the', 'third', 'row']]);
var a1Notation = 'appends!1:1';
var result = tmp.insertRow(a1Notation, ["Hey", "there", "=3+1"]);
var values = tmp.getEffectiveValues(a1Notation);
assert.arrayEquals({expected: [["Hey", "there", 4]], actual: values});
values = tmp.getEffectiveValues('appends!A4:E4');
assert.arrayEquals({expected: [['this', 'is', 'the', 'third', 'row']], actual: values});
});
});
describe("defineHeaders", function () {
it("Sets frozen columns and values", function () {
var headers = [['First', 'Second']];
tmp.withRequestBuilder(function (rb) {
rb.freezeRows('headers', headers.length)
.setValues('headers', 1, headers.length, headers)
});
var newHeaders = tmp.getHeaders('headers');
assert.arrayEquals({expected: headers, actual: newHeaders});
});
});
describe("requestBuilder", function () {
it("Adds rows, headers, and colors, changes sheet title", function () {
tmp.withRequestBuilder(function (rb) {
rb.newTab('several').commit()
.setValues('several', 1, [['Column1', 'Column2']])
.setValues('several', 2, [['Info1', 'Info2']])
.freezeRows('several', 1)
.setNumColumns('several', 4)
.setNumRows('several', 4)
.changeTabColor('several', 60, 0, 0, 1)
.newTab('othertab');
});
var headers = tmp.getHeaders('several');
assert.arrayEquals({expected: [['Column1', 'Column2']], actual: headers});
});
});
describe("Sort a column", function () {
it("sorts!", function () {
// make it sort
tmp.withRequestBuilder(function (rb) {
[
[1, 'One'],
[0, 'Zero'],
[500, 'Five hundred'],
[10, 'Ten']
].forEach(function (row, index) {
rb.updateCells('Values', 0, index, [row]);
});
rb.sort('Values!A:B')
});
//
// check that it is sorted
var data = tmp.getEffectiveValues('Values!A:A');
assert.arrayEquals({expected: [[0], [1], [10], [500]], actual: data});
var data = tmp.getEffectiveValues('Values!B:B');
assert.arrayEquals({expected: [['Zero'], ['One'], ['Ten'], ['Five hundred']], actual: data});
//
});
});
describe("autotab clears before writing", function () {
it("clears!", function () {
var data = null;
tmp.inputValues('autoclear!1:4', [[500, 'B1', 101], [0, 'B2', ''], [1, 'B3', ''], [10, 'B3', '']]);
tmp.withRequestBuilder(function (rb) {
rb.tabsAutoClear();
[["Just this and only this"]].forEach(function (row, index) {
rb.setValues('autoclear', index+1, [row]);
});
});
data = tmp.getEffectiveValues('autoclear!A:Z');
assert.arrayEquals({expected: [["Just this and only this"]], actual: data});
});
});
describe("Registering plugin", function () {
it("overwrites returned value", function () {
tmp.clearPlugins();
var values;
tmp.withRequestBuilder(function (rb ) {
rb.setValues('plugin!A1:2', [['Something'], ['Say Hello']])
.freezeRows('plugin', 2);
});
var description = {
entryPoint: {header: 2}, // second row
name: 'Say Hello'
};
tmp.registerPlugin(description, function () {
return 'Hello, world'; // should update
});
tmp.insertRow('plugin', ['overwrite me with Hello, World']);
tmp.overwriteWithPlugins('plugin!A3:B3');
values = tmp.getEffectiveValues('plugin!A3:B3');
assert.arrayEquals({expected: [["Hello, world"]], actual: values});
});
it("can be templates for formulas", function () {
tmp.clearPlugins();
tmp.withRequestBuilder(function (rb) {
rb.freezeRows('templates', 2)
.setValues('templates!A1:B2', [['Something', 'Wicked'], ['Calc', 'Base']]);
});
var description = {
entryPoint: {header: 2}, // second row
name: 'Calc'
};
tmp.registerPlugin(description, function () {
return '=B3+1'; // should update
});
tmp.insertRow('templates', ['overwrite me with 4', 3]);
tmp.overwriteWithPlugins('templates!A3:B3');
var values = tmp.getEffectiveValues('templates!A3:B3');
assert.arrayEquals({expected: [[4, 3]], actual: values});
});
it("formulas can be templated with custom functions", function () {
tmp.clearPlugins();
tmp.withRequestBuilder(function (rb) {
rb.setValues('formulatemplates!A1:C2', [['Something', 'Wicked', 'Thiswaycomes'], ['Inc', 'Base', 'IncBy10']])
.freezeRows('formulatemplates', 2);
});
var Inc = {
entryPoint: {header: 2}, // second row
name: 'Inc'
};
tmp.registerPlugin(Inc, function (obj, utils) {
obj.columnPlusOne = function () {
return pkg.dbsheets.utils.zeroIndexedToColumnName(obj.c + 1);
}
return '={columnPlusOne}{row} + 1'; // should update
});
var IncBy10 = {
entryPoint: {header: 2},
name: 'IncBy10'
}
tmp.registerPlugin(IncBy10, function (obj, utils) {
obj.columnMinusOne = function () {
return pkg.dbsheets.utils.zeroIndexedToColumnName(obj.c - 1);
}
return '={columnMinusOne}{row} + 10'; // should update
});
tmp.insertRow('formulatemplates', ['overwrite me with 4', 3, 'overwrite me with 13']);
tmp.insertRow('formulatemplates', ['overwrite me with 5', 4, 'overwrite me with 14']);
tmp.overwriteWithPlugins('formulatemplates!A3:C4');
var values = tmp.getEffectiveValues('formulatemplates!A3:C4');
assert.arrayEquals({expected: [[4, 3, 13], [5, 4, 14]], actual: values});
});
it("formulas can be templated from second row items", function () {
tmp.withRequestBuilder(function (rb) {
rb.setValues('uber!A1:2', [['Base', 'Inc'], ['base', 'inc']])
.freezeRows('uber', 2);
});
tmp.insertRow('uber', [100]);
var Inc = {
entryPoint: {header: 2}, // second row
name: 'inc'
};
tmp.registerPlugin(Inc, function (obj, utils) {
return '={{base}} + 1'; // should update based on "base" second header row
});
tmp.overwriteWithPlugins('uber!A3:C5');
var values = tmp.getEffectiveValues('uber!A3:C5');
assert.arrayEquals({expected:[[100, 101]], actual: values});
});
it("unique ID plugin handles empty and existing columns", function () {
tmp.clearPlugins();
tmp.withRequestBuilder(function (rb) {
rb.setValues('unique!A1:D1', [['Something', 'Wicked', 'id', 'anotherId']])
.freezeRows('unique', 1);
});
['id', 'anotherId'].forEach(function (name) {
var UniqueId = {
entryPoint: {header: 1},
name: name,
};
var _ids = []; // keep record of ids already used in this running
var _minMax = 1;
var colValuesCache = {}; // so we don't have to keep calling the same one
var colValues;
tmp.registerPlugin(UniqueId, function (obj, utils) {
if (obj.c in colValuesCache) {
colValues = colValuesCache[obj.c];
} else {
colValues = tmp.getColumnValues('unique', obj.c)
.filter(function (item) {
return typeof item == 'number';
});
colValuesCache[obj.c] = colValues;
}
var max = _minMax;
if (colValues.length != 0)
max = Math.max.apply(null, colValues);
while (_ids.indexOf(max) != -1) {
max += 1;
}
_ids.push(max);
return max;
});
});
tmp.inputValues('unique!2:4', [['A1', 'B1', 101], ['A2', 'B2', ''], ['A3', 'B3', '']]);
tmp.overwriteWithPlugins('unique!A2:D4');
var values = tmp.getUserEnteredValues('unique!A2:D4');
assert.arrayEquals({
comment: "id and anotherId",
expected: [["A1", "B1", 101, 1], ["A2", "B2", 102, 2], ["A3", "B3", 103, 3]],
actual: values
});
});
});
describe("extensions", function () {
it("rb extend", function () {
var headers, data;
headers = [['Header One', "Header Two"], ['info']];
// Make a thing that calls two others
pkg.dbsheets.extend.customBuilder({
'makeHeaders': function (range, headers) {
this.setValues(range, headers); // TODO: change column from "A" to number
this.freezeRows(range, headers.length);
return this;
},
});
tmp.withRequestBuilder(function (rb) {
rb.newTab('testExtensions').commit()
rb.makeHeaders('testExtensions!A1', headers);
});
data = tmp.getEffectiveValues('testExtensions!A1:Z');
assert.arrayEquals({
expected: headers,
actual: data
});
});
});
describe("update cells", function () {
it("updates with strings, numbers, boolean and formula", function () {
var data;
tmp.withRequestBuilder(function (rb) {
rb.updateCells('updateCells', 0, 0, [['headerA', 'headerB'], ['infoA', 'infoB'], [3, '=A3+1'], [true, "=NOT(A4)"]]);
});
tmp.withRequestBuilder(function (rb) {
rb.updateCells('updateCells', 0, 0, [['new', 'new'], [2, '=A2+1']]);
});
data = tmp.getEffectiveValues('updateCells!A1:B4');
assert.arrayEquals({
expected: [['new', 'new'], [2, 3], [3, 4], [true, false]],
actual: data,
});
});
});
describe("update cells with clear", function () {
it("updates with strings, numbers, boolean and formula", function () {
var data;
tmp.withRequestBuilder(function (rb) {
rb.updateCellsWithClear('updateCells', 0, 0, [['clear', 'bitches']]);
});
data = tmp.getEffectiveValues('updateCells!A1:B3');
assert.arrayEquals({
expected: [['clear', 'bitches']],
actual: data,
});
});
});
describe("insertrows", function () {
it("Inserts rows after", function () {
var data;
tmp.withRequestBuilder(function (rb) {
rb.setValues('insert!A1', [['headerA', 'headerB'], ['infoA', 'infoB']]);
});
tmp.withRequestBuilder(function (rb) {
rb.insertRows('insert', 1, 2);
});
data = tmp.getEffectiveValues('insert!A1:B3');
assert.arrayEquals({
expected: [['headerA', 'headerB'], [], ['infoA', 'infoB']],
actual: data
});
});
it("Inserts columns after", function () {
var data;
tmp.withRequestBuilder(function (rb) {
rb.setValues('insert!A1', [['headerA', 'headerB'], ['infoA', 'infoB'], ['infoA2', 'infoB2']]);
});
tmp.withRequestBuilder(function (rb) {
rb.insertColumns('insert', 1, 2);
});
data = tmp.getEffectiveValues('insert!A1:C3');
assert.arrayEquals({
expected: [['headerA', '', 'headerB'], ['infoA', '', 'infoB'], ['infoA2', '', 'infoB2']],
actual: data
});
});
it("Inserts one row", function () {
tmp.withRequestBuilder(function (rb) {
rb.setValues('insert!A10', [['headerA', 'headerB'], ['infoA', 'infoB'], ['infoA2', 'infoB2']]);
});
tmp.withRequestBuilder(function (rb) {
rb.insertRow('insert', 10);
});
});
});
});
}
Raw
unittestsMetadata.gs
function testing_dbsheets_metadata() {
var DBSheets;
DBSheets = pkg.dbsheets();
pkg.utgs.init();
DBSheets.withTempSpreadsheet(function (tmp) {
tmp.withRequestBuilder(function (rb) {
// Make tabs that are needed below
rb.newTab('students')
.newTab('comments');
});
describe("metadata workage", function () {
it("what happens when", function () {
tmp.withRequestBuilder(function (rb) {
rb.setValues('students!A1', [['id', 'name', 'grade', 'comments']])
.setValues('students!A2', [[1, 'Adam Morris', 10]])
.setValues('students!A3', [[2, 'Beth Morris', 8]])
.freezeRows('students', 1)
.setValues('comments!A1', [['studentId', 'id', 'subject', 'content']])
.setValues('comments!A2', [[1, 1, 'Naughty', 'He forgot his homework']])
.setValues('comments!A3', [[1, 2, 'Still Naughty', 'He never does his homework']])
.freezeRows('comments', 1);
});
var description = {
entryPoint: {header: 1}, // second row
name: 'comments'
};
tmp.registerPlugin(description, function (obj) {
pkg.formatLogger({
defaultTransformString: "{0}",
pprintNewLines: false,
});
var studentData;
studentData = tmp.toObject(obj.a1Notation);
return tmp.toObjects('comments').reduce(function (acc, row) {
if (row.studentId.value == studentData.id.value) {
acc.push({
id: row.id.value,
subject: row.subject.value,
content: row.content.value
});
}
return acc;
}, []);
});
tmp.overwriteWithPlugins('students');
var ret = tmp.getEffectiveValues('students');
ret.__pprint__;
});
});
});
}
Raw
unittestsUtils.gs
function testing_dbsheets_utils() {
var DBSheets, utgs;
DBSheets = pkg.dbsheets();
pkg.utgs.init();
DBSheets.withTempSpreadsheet(function (tmp) {
tmp.withRequestBuilder(function (rb) {
// Make tabs that are needed below
rb.newTab('test').newTab('userEnteredValues').newTab('getPlugsinOverwriteBuildRequests');
});
(function GridRange () {
describe("Converting a1Notation to grid ranges", function () {
it("Sheet1!A1:A1", function () {
var result = tmp.a1notation2gridrange('Sheet1!A1:A1');
assert.objectEquals({actual: result, expected:{
sheetId: 0,
startRowIndex: 0,
endRowIndex: 1,
startColumnIndex: 0,
endColumnIndex:1
}});
});
it("Sheet1!A3:B4", function () {
var result = tmp.a1notation2gridrange('Sheet1!A3:B4');
assert.objectEquals({
actual: result,
expected:{
sheetId: 0,
startRowIndex: 2,
endRowIndex: 4,
startColumnIndex: 0,
endColumnIndex:2
}
});
});
it("Sheet1!A:B", function () {
var result = tmp.a1notation2gridrange('Sheet1!A:B');
assert.objectEquals({actual: result, expected:{
sheetId: 0,
startColumnIndex: 0,
endColumnIndex:2
}});
});
it("Sheet1!A5:B", function () {
var result = tmp.a1notation2gridrange('Sheet1!A5:B');
assert.objectEquals({actual: result, expected:{
sheetId: 0,
startRowIndex: 4,
startColumnIndex: 0,
endColumnIndex: 2,
// no endRowIndex
}});
});
it("Just sheet", function () {
var result = tmp.a1notation2gridrange('Sheet1');
assert.objectEquals({actual: result, expected:{
sheetId: 0,
}});
});
it("NoSheet", function () {
var result = tmp.a1notation2gridrange('A1:B2');
assert.objectEquals({
actual: result,
expected: {
sheetId: null,
startRowIndex: 0,
startColumnIndex: 0,
endRowIndex: 2,
endColumnIndex: 2
}
});
});
});
})();
(function test () {
describe("toObjects", function () {
it("returns expected items", function () {
var result;
tmp.withRequestBuilder(function (rb) {
rb.setValues('test!A1:B4', [['BaseHeader', 'IncHeader'], ['base', 'inc'], ['CA R3', 'CB R3'], ['CA R4', 'CB R4']])
.freezeRows('test', 2);
});
result = tmp.toObjects('test!A1:B4');
var expected = [{
columns: {
base: "A3",
inc: "B3"
},
base: {
headers: ["BaseHeader", "base"],
row: "3",
column: "A",
c: 0.0,
a1Notation: "test!A3",
r: 2.0,
value: "CA R3",
grid: {
sheetId: tmp.getSheetId('test'),
startRowIndex: 2,
startColumnIndex: 0,
endRowIndex: 3,
endColumnIndex: 1
},
},
inc: {
headers: ["IncHeader", "inc"],
row: "3",
column: "B",
c: 1.0,
a1Notation: "test!B3",
r: 2.0,
value: "CB R3",
grid: {
sheetId: tmp.getSheetId('test'),
startRowIndex: 2,
startColumnIndex: 1,
endRowIndex: 3,
endColumnIndex: 2
},
},
},{
columns: {
base: "A4",
inc: "B4",
},
base: {
headers: ["BaseHeader", "base"],
row: "4",
column: "A",
c: 0.0,
a1Notation: "test!A4",
r: 3.0,
value: "CA R4",
grid: {
sheetId: tmp.getSheetId('test'),
startRowIndex: 3,
startColumnIndex: 0,
endRowIndex: 4,
endColumnIndex: 1
},
},
inc: {
headers: ["IncHeader", "inc"],
row: "4",
column: "B",
c: 1.0,
a1Notation: "test!B4",
r: 3.0,
value: "CB R4",
grid: {
sheetId: tmp.getSheetId('test'),
startRowIndex: 3,
startColumnIndex: 1,
endRowIndex: 4,
endColumnIndex: 2
},
}
}];
assert.arrayEquals({
comment: 'test!A1:B4',
expected: expected,
actual: result,
});
result = tmp.toObjects('test!B:B');
assert.arrayEquals({
comment: 'test!B:B',
expected: expected,
actual: result,
});
});
it("getPluginsOverwriteBuildRequests", function () {
var a1, data, result;
a1 = 'getPlugsinOverwriteBuildRequests!A1:B4';
data = [['First', 'Second'], ['no', 'hello'], ['CA R3', 'CB R3'], ['CA R4', 'CB R4'], ];
tmp.withRequestBuilder(function (rb) {
rb.setValues(a1, data)
.freezeRows('getPlugsinOverwriteBuildRequests', 2);
});
var description = {
entryPoint: {header: 2}, // second row
name: 'hello'
};
tmp.registerPlugin(description, function () {
return 'Hello, world'; // should update
});
var description = {
entryPoint: {header: 2}, // second row
name: 'no'
};
tmp.registerPlugin(description, function () {
return 'NO NO NO NO'; // should NOT update
});
result = tmp.getPluginsOverwriteBuildRequests('getPlugsinOverwriteBuildRequests!B3:B4');
var expected = [
[
{
"values": [
[
"Hello, world"
]
],
"a1Notation": "getPlugsinOverwriteBuildRequests!B3",
"grid": {
"sheetId": tmp.getSheetId('getPlugsinOverwriteBuildRequests'),
"startRowIndex": 2,
"startColumnIndex": 1,
"endRowIndex": 3,
"endColumnIndex": 2
}
},
{
"values": [
[
"Hello, world"
]
],
"a1Notation": "getPlugsinOverwriteBuildRequests!B4",
"grid": {
"sheetId": tmp.getSheetId('getPlugsinOverwriteBuildRequests'),
"startRowIndex": 3,
"startColumnIndex": 1,
"endRowIndex": 4,
"endColumnIndex": 2
}
}
]
];
assert.arrayEquals({
expected: expected,
actual: result,
});
});
it("getUserEnteredValues", function () {
var a1, data, result;
a1 = 'userEnteredValues!A1:B3';
data = [['Base', 'Inc'], ['base', 'inc'], ['A3', 4]];
tmp.withRequestBuilder(function (rb) {
rb.setValues('userEnteredValues!A1:B3', data)
.freezeRows('userEnteredValues', 2);
});
result = tmp.getUserEnteredValues(a1);
assert.arrayEquals({
expected: data,
actual: result,
});
});
it("argsToGrid and gridToA1Notation are compatible", function () {
var a1s = ['Sheet1', 'Sheet1!A1:B2', 'Sheet1!A1:A', 'Sheet1!A:A', 'Sheet1!A3:A', 'Sheet1!A3']; // FIXME: "Sheet1!1:1" does NOT work because a1notation2gridrange is not ready for it yet
a1s.forEach(function (a1) {
var result = tmp.argsToGrid(a1);
var result2 = tmp.gridToA1Notation(result);
assert.equals({
expected: a1,
actual: result2
});
});
});
});
describe("to object", function () {
it("works", function () {
var expected, actual;
actual = tmp.toObject('test!A4');
expected = {
"columns": {
"base": "A4",
"inc": "B4"
},
"base": {
"value": [
[
"CA R4"
]
],
"a1Notation": "test!A4",
"grid": {
"sheetId": tmp.getSheetId('test'),
"startRowIndex": 3,
"startColumnIndex": 0,
"endColumnIndex": null
},
"headers": [
"BaseHeader",
"IncHeader"
],
"c": 0,
"r": 3,
"column": "A",
"row": "4"
},
"inc": {
"value": [
[
"CA R4"
]
],
"a1Notation": "test!A4",
"grid": {
"sheetId": tmp.getSheetId('test'),
"startRowIndex": 3,
"startColumnIndex": 0,
"endColumnIndex": null
},
"headers": [
"base",
"inc"
],
"c": 0,
"r": 3,
"column": "B",
"row": "4"
}
}
assert.objectEquals({expected: expected, actual: actual});
});
}),
describe("over the cell limit", function () {
it("throw error if over the limit", function () {
var column = [];
for (var i = 0; i < 200001; i++) {
column.push(i.toString());
}
tmp.setDimensionAsColumns();
assert.throwsError(function () {
tmp.withRequestBuilder(function (rb) {
rb.setValues('test!A1', [column]);
});
});
});
});
})();
});
}
Raw
xBuilder.gs
(function (global, Factory) {
global.pkg = global.pkg || {};
if (typeof global.pkg.dbsheets == 'undefined') throw Error("Cannot import");
global.pkg.dbsheets.builder = (function wrapper (args) {
var wrapped = function () { return Factory.apply(Factory, arguments); }
for (i in args) { wrapped[i] = args[i]; }
return wrapped;
}({})
);
})(this,
function Package (options) {
var BuilderObj = function (dbsheet) {
this.dbsheet = dbsheet;
this.valuesSortBy = null;
this.preSSRequests = [];
this.sRequests = [];
this.postSSRequests = [];
this._tabsAutoClear = false;
};
var builderObj_prototype1 = {
transpose: pkg.dbsheets.utils.transpose,
rowArrayToUserEnteredValues: function (rowArray) {
return rowArray.reduce(function (acc, row) {
var obj;
obj = row.reduce(function (a, v) {
var o;
o = {
userEnteredValue: {},
}
var kind = null;
switch (typeof v) {
case "string":
if (v[0] == '=') {
kind = "formulaValue";
} else {
kind = "stringValue";
}
break;
case "number":
kind = "numberValue";
break;
case "boolean":
kind = "boolValue";
break;
case "object":
if (v == null) {
kind = "stringValue";
v = "";
} else {
// should I do stringify on this?
throw Error("Unknown type value " + typeof(v) + " sent to updateCell");
}
default:
throw Error("Unknown type value " + typeof(v) + " sent to updateCell");
}
o.userEnteredValue[kind] = v;
a.push(o);
return a;
}, []);
acc.push({values: obj});
return acc;
}, []);
},
addValueRequest: function (fn) {
return function () {
var request = fn.apply(this, arguments);
this.sRequests.push(request);
return this;
};
},
addPreRequest: function (fn) {
return function () {
var request = fn.apply(this, arguments);
this.preSSRequests.push(request);
return this;
}
},
addPostRequest: function (fn) {
return function () {
var request = fn.apply(this, arguments);
this.postSSRequests.push(request);
return this;
}
},
};
var builderObj_prototype2 = {
commit: builderObj_prototype1.addPreRequest(
function () {
return function () {
return {commit: true};
}
}
),
tabsAutoClear: function () {
this._tabsAutoClear = true;
},
setValuesSortByIndex: function (sortBy) {
this.valuesSortBy = sortBy;
},
toRange: function (title, left, right) {
if (title.indexOf(' ') !== -1)
title = "'" + title + "'";
if (typeof right === 'undefined')
return title + '!' + left.toString() + ':' + left.toString();
else
return title + '!' + left.toString() + ':' + right.toString();
},
/*
* columns first because that is same as a1Notation
*/
updateCells: builderObj_prototype1.addPreRequest(
function (sheet, colIndex, rowIndex, rowArray) {
return function () {
return {
updateCells: {
rows: builderObj_prototype1.rowArrayToUserEnteredValues(rowArray),
fields: '*',
start: {
sheetId: this.getSheet(sheet).properties.sheetId,
columnIndex: colIndex,
rowIndex: rowIndex,
},
}
}
};
}
),
updateCellsWithClear: builderObj_prototype1.addPreRequest(
function (sheet, rowIndex, colIndex, rowArray) {
return function () {
var sht;
sht = this.getSheet(sheet);
return {
updateCells: {
rows: builderObj_prototype1.rowArrayToUserEnteredValues(rowArray),
fields: '*',
range: {
sheetId: sht.properties.sheetId,
startRowIndex: rowIndex,
startColumnIndex: colIndex,
endRowIndex: sht.properties.gridProperties.rowCount,
endColumnIndex: sht.properties.gridProperties.columnCount,
}
}
}
};
}
),
setValues: builderObj_prototype1.addValueRequest(
function () {
if (arguments.length == 0) throw Error("Cannot have setValues with zero args");
var title, left, right, range;
if (arguments.length == 2) {
range = arguments[0];
} else {
title = arguments[0];
left = arguments[1];
if (arguments.length == 4) right = arguments[2];
else if (arguments.length == 3) right = undefined;
else throw Error("setValues must be either 2, 3, or 4 arguments");
range = this.toRange(title, left, right);
}
var values;
values = arguments[arguments.length-1];
return function () {
return {
majorDimension: options.dimension,
range: range,
values: values
}
}
}
),
insertRows: builderObj_prototype1.addPreRequest(
function (sheet, startRow, endRow) {
return function () {
return {
insertDimension: {
range: {
sheetId: this.getSheet(sheet).properties.sheetId,
dimension: "ROWS",
startIndex: startRow,
endIndex: endRow
},
inheritFromBefore: true,
}
}
}
}
),
insertRow: function (sheet, startRow) {
this.insertRows(sheet, startRow, startRow+1);
return this;
},
insertColumns: builderObj_prototype1.addPreRequest(
function (sheet, startCol, endCol) {
return function () {
return {
insertDimension: {
range: {
sheetId: this.getSheet(sheet).properties.sheetId,
dimension: "COLUMNS",
startIndex: startCol,
endIndex: endCol
},
inheritFromBefore: true,
}
}
}
}
),
setNumColumns: builderObj_prototype1.addPreRequest(
function (sheet, numCols) {
return function () {
return {
updateSheetProperties: {
properties: {
sheetId: this.dbsheet.getSheetId(sheet),
gridProperties: {
columnCount: numCols,
}
},
fields: 'gridProperties.columnCount',
}
};
}.bind(this);
}
),
hideGridlinesRequest: builderObj_prototype1.addPreRequest(
function (sheet, bool) {
return function () {
return {
updateSheetProperties: {
properties: {
sheetId: this.dbsheet.getSheet(sheet).properties.sheetId,
gridProperties: {
hideGridlines: bool,
}
},
fields: 'gridProperties.hideGridlines',
}
};
}.bind(this);
}
),
setNumRows: builderObj_prototype1.addPreRequest(
function (sheet, numRows) {
return function () {
return {
updateSheetProperties: {
properties: {
sheetId: this.dbsheet.getSheet(sheet).properties.sheetId,
gridProperties: {
rowCount: numRows,
}
},
fields: 'gridProperties.rowCount',
},
};
}.bind(this);
}
),
/*
In addition to a freezeRows request, it can set the keyHeadingRow which is an option
that allows us to define which row in the header to look at
*/
freezeRows: builderObj_prototype1.addPreRequest(
function (sheet, numRows, headerRow) {
headerRow = headerRow || numRows;
this.dbsheet.setKeyHeadingRow(headerRow);
return function () {
return {
updateSheetProperties: {
properties: {
sheetId: this.dbsheet.getSheet(sheet).properties.sheetId,
gridProperties: {
frozenRowCount: numRows,
}
},
fields: 'gridProperties.frozenRowCount',
},
};
}.bind(this);
}
),
freezeColumns: builderObj_prototype1.addPreRequest(
function (sheet, numCols) {
return function () {
return {
updateSheetProperties: {
properties: {
sheetId: this.dbsheet.getSheet(sheet).properties.sheetId,
gridProperties: {
frozenColumnCount: numCols,
}
},
fields: 'gridProperties.frozenColumnCount',
},
};
}.bind(this);
}
),
changeTabColor: builderObj_prototype1.addPreRequest(
function (sheet, red, green, blue, alpha) {
if (typeof alpha === 'undefined')
alpha = 1;
return function () {
return {
updateSheetProperties: {
properties: {
sheetId: this.dbsheet.getSheet(sheet).properties.sheetId,
tabColor: {
red: red,
green: green,
blue: blue,
alpha: alpha
}
},
fields: 'tabColor',
}
};
}.bind(this);
}
),
newTab: builderObj_prototype1.addPreRequest(
function (title) {
return function () {
return {
addSheet: {
properties: {
title: title
}
},
};
}.bind(this);
}
),
tabTitleRequest: builderObj_prototype1.addPreRequest(
function (sheet, title) {
return function () {
return {
updateSheetProperties: {
properties: {
sheetId: this.dbsheet.getSheet(sheet).properties.sheetId,
title: title,
},
fields: 'title',
},
}
}.bind(this);
}
),
/*
* range: a1notation | gridrange
*/
sort: builderObj_prototype1.addPreRequest(
function (range, dimensionIndex, sortOrder) {
return function () {
return {
sortRange: {
range: this.dbsheet.argsToGrid(range),
sortSpecs: {
dimensionIndex: dimensionIndex || 0,
sortOrder: sortOrder || 'ASCENDING',
},
}
}
}.bind(this);
}
),
addBand: builderObj_prototype1.addPreRequest(
function (range, first, second, third, fourth) {
return function () {
return {
addBanding: {
bandedRange: {
range: this.dbsheet.argsToGrid(range),
rowProperties: pkg.dbsheets.utils.makeBandingProperties(first, second, third, fourth),
},
fields: "*",
},
}
}.bind(this);
}
),
updateBand: builderObj_prototype1.addPreRequest(
function (bandId, range, first, second, third, fourth) {
return function () {
return {
updateBanding: {
bandedRange: {
bandedRangeId: bandId,
range: this.dbsheet.argsToGrid(range),
rowProperties: pkg.dbsheets.utils.makeBandingProperties(first, second, third, fourth),
},
fields: "*",
},
}
}.bind(this);
}
),
};
BuilderObj.prototype = Object.assign(builderObj_prototype1, builderObj_prototype2);
return BuilderObj;
}
);
Raw
xPolyfills.gs
// Object.assign polyfill
if (typeof Object.assign != 'function') {
// Must be writable: true, enumerable: false, configurable: true
Object.defineProperty(Object, "assign", {
value: function assign(target, varArgs) { // .length of function is 2
'use strict';
if (target == null) { // TypeError if undefined or null
throw new TypeError('Cannot convert undefined or null to object');
}
var to = Object(target);
for (var index = 1; index < arguments.length; index++) {
var nextSource = arguments[index];
if (nextSource != null) { // Skip over if undefined or null
for (var nextKey in nextSource) {
// Avoid bugs when hasOwnProperty is shadowed
if (Object.prototype.hasOwnProperty.call(nextSource, nextKey)) {
to[nextKey] = nextSource[nextKey];
}
}
}
}
return to;
},
writable: true,
configurable: true
});
}
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment