101 Apps Script ObjDb Sheet Setup and Init App

101 Apps Script ObjDb Sheet Setup and Init App

.

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")) ; }

link download