Model & Controller Template for Google Apps Script
Using spreadsheet as a database
Set the ID of a default spreadsheet to the Config.gs file
Get Data from Spreadsheet
const mdl = new Model ( ) ;
const conditions = [
{ key : "ColumnName1" , value : "value1" } ,
{ key : "ColumnName2" , value : "value2" } ,
// etc.
] ;
const offset = 10 ;
const limit = 5 ;
const allData = mdl . getData ( "SheetName" ) ;
const dataWithAnd = mdl . getData ( "SheetName" , conditions ) ;
const dataWithOr = mdl . orGetData ( "SheetName" , conditions ) ;
const dataOffsetLimit = mdl . getData ( "SheetName" , null , offset , limit ) ;
Logger . log ( allData ) ;
Logger . log ( dataWithAnd ) ;
Logger . log ( dataWithOr ) ;
Logger . log ( dataOffsetLimit ) ;
const mdl = new Model ( "9Lnk1lZhQ9Hr8IMt60FIbDwLSlv5jlu9" ) ;
Insert Data into Spreadsheet
const mdl = new Model ( ) ;
const keyValuePairs = [
{
"ColumnName1" : "value1-1" ,
"ColumnName2" : "value2-1" ,
// etc.
} ,
{
"ColumnName1" : "value1-2" ,
"ColumnName2" : "value2-2" ,
// etc.
} ,
// etc.
] ;
const result = mdl . inserData ( "SheetName" , keyValuePairs ) ;
Logger . log ( result ) ;
Update Data of Spreadsheet
const mdl = new Model ( ) ;
const conditions = [
{ key : "ColumnName1" , value : "value1" } ,
{ key : "ColumnName2" , value : "value2" } ,
// etc.
] ;
const keyValuePair = {
"ColumnName1" : "new value 1" ,
"ColumnName2" : "new value 2" ,
// etc.
} ;
const resultOfUpdate = mdl . updateData ( "SheetName" , keyValuePair , conditions ) ;
const resultOfOrUpdate = mdl . orUpdateData ( "SheetName" , keyValuePair , conditions ) ;
Logger . log ( resultOfUpdate ) ;
Logger . log ( resultOfOrUpdate ) ;
Delete Data of Spreadsheet
const mdl = new Model ( ) ;
const conditions = [
{ key : "ColumnName1" , value : "value1" } ,
{ key : "ColumnName2" , value : "value2" } ,
// etc.
] ;
const resultOfDelete = mdl . deleteData ( "SheetName" , conditions ) ;
const resultOfOrDelete = mdl . orDeleteData ( "SheetName" , conditions ) ;
Logger . log ( resultOfDelete ) ;
Logger . log ( resultOfOrDelete ) ;
Truncate Data of Spreadsheet
Delete all rows of the selected sheet except headers
const mdl = new Model ( ) ;
const result = mdl . truncateData ( "SheetName" ) ;
Logger . log ( result ) ;