.
108 Apps Script: Google Sheet With ObjDb Library
This tutorial imports a library to manage data operations with the sheet (and jdbc as well). Instead of writing our own codes for these operations, we use the objdb library.
Thus, we save development time here and could shift more focus to problem solving.
Objective:
1. Parameter cmd for getting commands from clients.
2. Function taskManager() to handle app-specific commands.
3. Function/Command as follows:
No
|
Task
|
PARAMETERS
| ||||
1
| ||||||
2
|
getadmin
|
Sheet
|
{objRecord}
|
tid
| ||
3
|
addadmin
|
Sheet
|
{objRecord}
| |||
4
|
setadmin
|
Sheet
|
{objRecord}
|
tid
| ||
5
|
deladmin
|
Sheet
|
{objRecord}
|
tid
| ||
The items in strikethrough texts are suggestions for future development.
1. Create Spreadsheet and Script.
Spreadsheet name: 108appdatacrud.
Dummy Data:
A
|
B
|
C
|
D
| |
1
|
tid
|
timestamp
|
name
|
gmail
|
2
|
123
|
notarazi
|
notarazi@gmail.com
| |
3
|
456
|
notaraza
|
notaraza@gmail.com
| |
4
|
789
|
notarazu
|
notarazu@gmail.com
| |
5
| ||||
6
| ||||
7
|
Sheet name: admins
Don’t forget to rename your sheet as admins
|
Script name: 108appdatacrudscript.
2. Add ObjDb Library.
Import Library
Use Project Key: MJMF2lqsgWV-I-dlyqJN6OrljYCrJdQKl
Or SCript ID: 1o_O_ZkZm1GuTF5J1LYkfntUpiaT0sxDHyhZXL4fQma89mNIB65epbL6H
Reference: http://googlescripts.harryonline.net/objdb
3. Add codes
/*script id*/
var SCPID = ScriptApp.getScriptId();
/*active spreadsheet */
var SST = SpreadsheetApp.getActiveSpreadsheet();
/*active spreadsheet id*/
var SSTID = SpreadsheetApp.getActiveSpreadsheet().getId();
/*OR if you are writing a standalone script*/
/*var SSTID=1FhDxI7j0PFtQYF0gCsiAQauQ1QZ-nDTWNPydpQPD3GE*/
/* web request listeners */
/* pass request to handleResponse */
/* projectkey=MJMF2lqsgWV-I-dlyqJN6OrljYCrJdQKl */
var DB1 = objDB.open( SSTID );
function doGet(e) {
return handleResponse(e);
}
function doPost(e) {
return handleResponse(e);
}
/* handle action request */
function handleResponse(e) {
var lock = LockService.getPublicLock();
lock.waitLock(30000); // wait 30 seconds before conceding defeat.
try {
var cmd = e.parameter.cmd;
var output = [];
if (cmd == "app") {
output = taskManager("app", e);
} else if (cmd == "getadmin") {
output = taskManager("getadmin", e);
} else if (cmd == "addadmin") {
output = taskManager("addadmin", e);
} else if (cmd == "setadmin") {
output = taskManager("setadmin", e);
} else if (cmd == "deladmin") {
output = taskManager("deladmin", e);
}
return ContentService.createTextOutput(JSON.stringify({
"result": "success",
"data": output
})).setMimeType(ContentService.MimeType.JSON);
//return output
} catch (e) { /*if error return this*/
return ContentService.createTextOutput(JSON.stringify({
"result": "error",
"error": e
})).setMimeType(ContentService.MimeType.JSON);
} finally { /*release lock*/
lock.releaseLock();
}
}
/*taskManager*/
function taskManager(cmd, e) {
var output = "";
switch (cmd) {
case "app":
return output;
break;
case "getadmin":
var tid = e.parameter["tid"] || "";
if(tid!=""){output=objDB.getRows(DB1,'admins',[],{tid:tid});}
else{output=objDB.getRows( DB1, 'admins' );}
return output;
break;
case "addadmin":
var objRecord={tid:0,timestamp:"",name:"",gmail:""};
for(var key in objRecord) {objRecord[key]=e.parameter[key];}
var d = new Date();
objRecord.timestamp = d;
objRecord.tid = String(d.getTime());
var newrecord=objDB.insertRow( DB1, 'admins',objRecord );
//Logger.log(newrecord);
if (newrecord==1){
var f = {parameter:{tid:objRecord.tid}};
output= taskManager("getadmin",f);
}
return output;
break;
case "setadmin":
var objRecord={tid:0,timestamp:"",name:"",gmail:""};
for(var key in objRecord) {objRecord[key]=e.parameter[key];}
var d = new Date();
objRecord.timestamp = d;
var newrecord=objDB.updateRow( DB1, 'admins',objRecord,{tid:objRecord.tid} );
//Logger.log(newrecord);
if (newrecord==1){
var f = {parameter:{tid:objRecord.tid}};
output= taskManager("getadmin",f);
}
return output;
break;
case "deladmin":
var objRecord={tid:0,timestamp:"",name:"",gmail:""};
for(var key in objRecord) {objRecord[key]=e.parameter[key];}
var delrecord=objDB.deleteRow(DB1, 'admins', {tid:objRecord.tid} );
//Logger.log(delrecord);
if (delrecord==1) {output= [{tid:0}];}
else {output= [];}
return output;
break;
} /*switch*/
} /*taskManager*/
function testGetRecord() {
var e = {parameter: {cmd: "getadmin"}};Logger.log(taskManager("getadmin",e));
}
function testGetRecord() {
var e = {parameter: {cmd: "getadmin",tid:"123"}};Logger.log(taskManager("getadmin",e));
}
function testAddRecord() {
var e = {parameter: {cmd: "addadmin",name: "aba",gmail: "aba@gmail.com"}};Logger.log(taskManager("addadmin",e));
}
function testSetRecord() {
var e = {parameter: {cmd: "setadmin",tid:123,name: "vivi",gmail: "vovoi@gmail.com"}};Logger.log(taskManager("setadmin",e));
}
function testDelRecord() {
var e = {parameter: {cmd: "deladmin",tid: "789"}};Logger.log(taskManager("deladmin",e));
}
|
4. Test
Test URL: https://script.google.com/macros/s/AKfycbxCL6cshZ1hpPnNehRghH9y-3SB-FniAovskeTovWhd5eN1amo/exec
Test Platform:
4.1. getadmin with no param
4.2. getadmin with param
4.3. setadmin with param
4.4. deladmin with param
4.5. deladmin without param or invalid param
.
No comments:
Post a Comment