.
101 Apps Script: ObjDb Sheet Setup and Init App
Introduction
This tutorial uses ObjDb Library to setup and initialize an app.
We will run the setup function to create spreadsheet database with 2 record sheets; admin and user.
Every time the app runs, we will call the init function to set the script id, spreadsheet id and folder id.
Objective
1. Create script with setup and init functions.
2. When the web app runs, it calls init function.
3. Init function checks if script properties exist, else it calls setup function.
To check for empty object (eg script properties), we use the following logic
function isObjectEmpty(r){for(var n in r)if(r.hasOwnProperty(n))return!1;return!0}
|
1. Create Script.
Script name=_101-initapp-startapp.
Copy and paste the following codes:
function setupApp(){
/*script id*/
var SCPID = ScriptApp.getScriptId();
var file = DriveApp.getFileById(SCPID);
var folders = file.getParents();
while (folders.hasNext()){
FOLID=folders.next().getId();
}
FOLDER = DriveApp.getFolderById(FOLID);
var d = new Date();
var t = d.getTime();
var NEWSST = SpreadsheetApp.create("_appdata-"+t);
var TEMP = DriveApp.getFileById(NEWSST.getId());
FOLDER.addFile(TEMP);
DriveApp.getRootFolder().removeFile(TEMP);
var scriptProperties = PropertiesService.getScriptProperties();
scriptProperties.setProperties({
scpid:SCPID,
folid:FOLID,
sstid:NEWSST.getId()
});
/*record headers*/
var arrRecordTitles=["admin","user"];
var arrRecordHeaders=[
["tid","timestamp","name","gmail"],
["tid","timestamp","name","gmail","passcode"]
];
for (i in arrRecordTitles){
var Sheet = NEWSST.getSheetByName(arrRecordTitles[i]);
if (Sheet != null) {
NEWSST.setActiveSheet(NEWSST.getSheetByName(arrRecordTitles[i]));
NEWSST.deleteActiveSheet();
}
NewSheet = NEWSST.insertSheet();
NewSheet.setName(arrRecordTitles[i]);
NewSheet.appendRow(arrRecordHeaders[i]);
NewSheet.getRange("A2:A").setNumberFormat('@STRING@');
}
}
function isObjectEmpty(r){for(var n in r)if(r.hasOwnProperty(n))return!1;return!0}
function initApp(){
SCRIPTPROP = PropertiesService.getScriptProperties();
if (isObjectEmpty(SCRIPTPROP.getProperties())){
setupApp();
SCRIPTPROP = PropertiesService.getScriptProperties();
}
Logger.log(SCRIPTPROP.getProperties());
/* projectkey=MJMF2lqsgWV-I-dlyqJN6OrljYCrJdQKl */
DB1 = objDB.open(SCRIPTPROP.getProperty("sstid"));
}
/* web request listeners */
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);
}
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) {
initApp();
var output = "";
switch (cmd) {
case "app":
/*test with a call to get rows from admin sheet*/
output=objDB.getRows( DB1, 'admin' );
return output;
break;
}/*swith*/
}
function test(){
Logger.log(taskManager("app")) ;
}
|
2. Test
2.1. Run test()
2. Run As Web App
Running either test() or web app will cause the app to create a spreadsheet with two sheets ie admin and user.
Sheet: admin
tid
|
timestamp
|
name
|
gmail
|
Sheet: user
tid
|
timestamp
|
name
|
gmail
|
passcode
|
At the moment the sheets contain no records. So , we will get an empty array when we run the test.
No comments:
Post a Comment