Tamotsu(保つ) is an object-spreadsheet mapping library like ActiveRecord for google apps script.
Tamotsu.initialize();
var Agent = Tamotsu.Table.define({ sheetName: 'Agents' });
var agent = Agent.find(1);
Logger.log(agent); //=> {#=1.0, First Name=Charles, Last Name=Bartowski, Gender=Male, Salary=100.0, ...}Tamotsu is made available as a script library. This is how you add it to your project:
- Select "Resources" > "Libraries..." in the Google Apps Script editor.
- Enter the project key (
1OiJIgWlrg_DFHFYX_SoaEzhFJPCmwbbfEHEqYEfLEEhKRloTNVJ-3U4s) in the "Find a Library" field, and choose "Select". - Select the highest version number, and choose Tamotsu as the identifier. (Do not turn on Development Mode unless you know what you are doing. The development version may not work.)
- Press Save. You can now use the Tamotsu library in your code.
When your spreadsheet is the following sheet.
NOTICE: Column # is used as id column.
// You have to invoke this first.
Tamotsu.initialize();
// Define your table class
var Agent = Tamotsu.Table.define({ sheetName: 'Agents' }, {
fullName: function() {
return [this['First Name'], this['Last Name']].join(' ');
},
});
Agent.first(); //=> {#=1.0, First Name=Charles, ...}
Agent.find(2); //=> {#=2.0, First Name=Sarah, ...}
Agent.last(); //=> {#=3.0, First Name=John, ...}
Agent.find(1).fullName(); //=> "Charles Bartowski"
Agent.where({ Gender: 'Male' })
.order('Salary DESC')
.all(); //=> [{#=3.0, First Name=John, ...}, {#=1.0, First Name=Charles}]
Agent.sum('Salary'); //=> 600
Agent.create({
'First Name': 'Morgan',
'Last Name': 'Grimes',
'Gender': 'Male',
'Salary': 50,
}); //=> {#=4.0, First Name=Morgan, ...}
// and the data will be appended to the sheet.
var agent = Agent.where(function(agent) { return agent['Salary'] > 150; })
.first(); //=> {#=2.0, First Name=Sarah, ...}
agent['Salary'] = 250;
agent.save(); //=> The salary on the sheet will be updated.| Param | Type | Description |
|---|---|---|
| Spreadsheet (optional) | Spreadsheet | A spreadsheet object you will handle. If nothing is given, it uses SpreadsheetApp.getActive() instead. |
You have to invoke this method before using Tamotsu.Table.
| Param | Type | Description |
|---|---|---|
| callback | function | A function that is to be added to the callback list. |
You can isolate table definitions from main code with this method.
var Agent;
Tamotsu.onInitialized(function() {
Agent = Tamotsu.Table.define({ sheetName: 'Agents' });
});
function main() {
Tamotsu.initialize();
Logger.log(Agent.first());
}Returns a table class extended from Tamotsu.Table.
| Param | Type | Description |
|---|---|---|
| classProperties | object | An object that is to be defined on your table class as class properties |
| instanceProperties (optional) | object | An object that is to be defined on your table class's instance as instance properties |
| Key | Type | Description |
|---|---|---|
| sheetName (necessary) | string | A sheet name you will use as a table |
| idColumn | string | Id column used as a key (Default: '#') |
| autoIncrement | boolean | If true, id is automatically incremented when its value is blank (Default: true) |
| rowShift | number | Number of rows before the start of the table (Default: 0) |
| columnShift | number | Number of columns before the start of the table (Default: 0) |
| Key | Type | Description |
|---|---|---|
| validate | function | A validation function that is to be called before save.The callback will be given on param. |
...
validate: function(on) {
// on === 'create' or 'update'
if (on === 'create') {
if (!this['First Name']) this.errors['First Name'] = "can't be blank";
} else if (on === 'update') {
if (this['Salary'] > 500) this.errors['Salary'] = "is too much";
}
if (this['Gender'] !== 'Male' && this['Gender'] !== 'Female') {
this.errors['Gender'] = "must be 'Male' or 'Female'";
}
},
...An example of Tamotsu.Table.define:
Tamotsu.initialize();
var Agent = Tamotsu.Table.define({
// classProperties
sheetName: 'Agents2',
idColumn: 'Agent No', // the column used as id
aClassProp: 'A class property',
rowShift: 1,
columnShift: 0,
}, {
// instanceProperties
isMale: function() {
return this['Gender'] === 'Male';
},
isFemale: function() {
return this['Gender'] === 'Female';
},
validate: function(on) {
if (!this['First Name']) {
this.errors['First Name'] = "can't be blank";
}
},
});
Logger.log(Agent.aClassProp); //=> 'A class property'
var agent = Agent.where({ Gender: 'Male' }).first();
Logger.log(agent.isMale()); //=> true
Logger.log(agent.isFemale()); //=> false
Logger.log(Agent.create({ 'First Name': '' })); //=> false
Logger.log(Agent.create({ 'First Name': 'James' })); //=> {Agent No=4.0, First Name=James, ...}Retunrs the first model in the table.
Retunrs the last model in the table.
Returns the model found by the given id.
| Param | Type | Description |
|---|---|---|
| id | any | An id to find |
Returns the all model in the table.
Returns an array of the given column's values.
| Param | Type | Description |
|---|---|---|
| column | string | a column name to pluck |
var firstNames = Agent.pluck('First Name');
Logger.log(firstNames); //=> ['Charles', 'Sarah', 'John']Returns the summed up number of the given column's values.
| Param | Type | Description |
|---|---|---|
| column | string | a column name to sum up |
var total = Agent.sum('Salary');
Logger.log(total); //=> 600Returns the maximum value of the given column's values.
| Param | Type | Description |
|---|---|---|
| column | string | a column name to examine |
var max = Agent.max('Salary');
Logger.log(max); //=> 300Returns the minimum value of the given column's values.
| Param | Type | Description |
|---|---|---|
| column | string | a column name to examine |
var min = Agent.min('Salary');
Logger.log(min); //=> 100Returns the relation object which meets the given conditions.
| Param | Type | Description |
|---|---|---|
| conditions | object or function | a condition object or predicate function |
// Object condition
var men = Agent.where({ Gender: 'Male' }).all();
Logger.log(men); //=> [{#=1.0, First Name=Charles...}, {#=3.0, First Name=John...}
// Function condition
var highPay = Agent.where(function(agent) { return agent['Salary'] > 150; })
.all();
Logger.log(highPay); //=> [{#=2.0, First Name=Sarah...}, {#=3.0, First Name=John...}]For sure where returns not models but a relation object that is to be chained with other scope functions, so you can get the records in such an elegant way.
Agent.where(condition1).where(condition2).order(comparator).all();Returns the relation object which meets the given sort order.
| Param | Type | Description |
|---|---|---|
| comparator | string or function | a column name or comparator function |
// Object comparator
var asc = Agent.order('Salary').all();
Logger.log(asc); //=> [{#=1.0, Salary=100...}, {#=3.0, Salary=200...}, {#=2.0, Salary=300}]
// Supports ASC/DESC
var desc = Agent.order('Salary DESC').all();
Logger.log(desc); //=> [{#=2.0, Salary=300...}, {#=3.0, Salary=200...}, {#=1.0, Salary=100}]
// Function comparator
Agent.order(function(agent1, agent2) {
// complex comparator
return agent2['First Name'].length - agent1['First Name'].length;
}).all();Creates new record in the spreadsheet with the given model or attributes and returns the new model if created successfully.
| Param | Type | Description |
|---|---|---|
| modelOrAttributes | model or object | Tamotsu.Table model or attribtues object |
var agent = new Agent({
'First Name': 'Morgan',
'Last Name': 'Grimes',
'Gender': 'Male',
'Salary': 50,
});
Agent.create(agent); //=> {#=4.0, First Name=Morgan, ...}
// and the data will be appended to the sheet.
// or
Agent.create({
'First Name': 'Morgan',
'Last Name': 'Grimes',
'Gender': 'Male',
'Salary': 50,
});Creates new recoreds in the spreadsheet with the given models or attributes and returns the new models if created successfully.
Better performace than create one by one
| Param | Type | Description |
|---|---|---|
| modelsOrAttributes | array of model or object | Array of Tamotsu.Table model or attribtues object |
var agents = [
new Agent({
'First Name': 'Morgan',
'Last Name': 'Grimes',
'Gender': 'Male',
'Salary': 50,
}),
new Agent({
'First Name': 'Bryce',
'Last Name': 'Larkin',
'Gender': 'Male',
'Salary': 400,
}),
];
Agent.batchCreate(agents); //=> [{#=4.0, First Name=Morgan, ...}, {#=5.0, First Name=Bryce, ...}]
// and the data will be appended to the sheet.
// or
Agent.batchCreate([
{
'First Name': 'Morgan',
'Last Name': 'Grimes',
'Gender': 'Male',
'Salary': 50,
},
{
'First Name': 'Bryce',
'Last Name': 'Larkin',
'Gender': 'Male',
'Salary': 400,
},
]);[with NOT existing id] Creates new record in the spreadsheet with the given model or attributes and returns the new model if created successfully.
[with existing id] Updates existing record in the spreadsheet with the given model or attributes and returns true if updated successfully.
| Param | Type | Description |
|---|---|---|
| modelOrAttributes | model or object | Tamotsu.Table model or attribtues object |
var agent = new Agent({
'#': '999',
'First Name': 'Morgan',
'Last Name': 'Grimes',
'Gender': 'Male',
'Salary': 50,
});
// If there is no record with id=999, the data will be appended to the sheet. Otherwise, the row with id=999 will be updated with the given data.
Agent.createOrUpdate(agent); //=> {#=999.0, First Name=Morgan, ...}
// with attributes
Agent.createOrUpdate({
'#': '999',
'First Name': 'Morgan',
'Last Name': 'Grimes',
'Gender': 'Male',
'Salary': 50,
});Creates or updates on the spreadsheet with the model attributes.
// Creates
var newAgent = new Agent({
'First Name': 'Morgan',
'Last Name': 'Grimes',
'Gender': 'Male',
'Salary': 50,
});
newAgent.save(); // The data will be appended to the last of the sheet.
// Updates
var agent = Agent.last();
agent['Salary'] = 10;
agent.save(); // The data on the sheet will be updated.Updates model and spreadsheet with the given attributes and returns true if updated successfully.
| Param | Type | Description |
|---|---|---|
| attributes | object | attribtues object (column to value) |
var agent = Agent.first();
agent.updateAttributes({ 'First Name': 'Chuck', 'Salary': 500 }); // The data on the sheet will be updated.
Logger.log(agent); //=> {#=1.0, First Name=Chuck, Last Name=Bartowski, Gender=Male, Salary=500.0, ...}Delete the model away from the spreadsheet.
var fired = Agent.first();
fired.destroy(); // The data will be removed away from the sheet.Returns boolean of the model being valid/invalid.
Tamotsu.initialize();
var Agent = Tamotsu.Table.define({ sheetName: 'Agents' }, {
validate: function(on) {
if (!this['First Name']) {
this.errors['First Name'] = "can't be blank";
}
},
});
var agent = new Agent();
Logger.log(agent.isValid()); //=> false
agent['First Name'] = 'Morgan';
Logger.log(agent.isValid()); //=> trueReturns boolean of the model being new record or not.
var agent = new Agent();
Logger.log(agent.isNewRecord()); //=> true
agent = Agent.first();
Logger.log(agent.isNewRecord()); //=> falseReturns an object of the model attributes. (column to value)
var agent = new Agent();
Logger.log(agent.getAttributes()); //=> {'#': 1, 'First Name': 'Charles', 'Last Name': 'Bartowski', ...}Set attributes to a model.
| Param | Type | Description |
|---|---|---|
| attributes | object | attribtues object (column to value) |
var agent = Agent.first();
agent.setAttributes({ 'First Name': 'Morgan', 'Last Name': 'Grimes' });Here is the specs.
https://github.com/itmammoth/Tamotsu_Test
MIT Licence.
