Learn the powerful enterprise adaptable database:

Getting Started With ADABAS & Natural

Monday, October 10, 2016

Google Sheet Data CRUD + App Script + jQuery + objDB


.
Google Sheet Data CRUD + App Script +  jQuery + objDB

However, for CRUD operations, it uses objDB Library (read here).

The codes are still in development. All functions are working but not extensively tested.

HTML
<script  data-cfasync="false" src="//ajax.googleapis.com/ajax/libs/jquery/1.11.1/jquery.min.js"></script>

<form id="formCreate" >
   <h3>Create Record</h3>
   <p><label for="name">Name:</label>
   <input id="name" name="name" type="text" value="" /></p>
   <p><label for="comment">Comment:</label><br/>
   <textarea id="comment" name="comment" cols="40"></textarea></p>
   <input type="hidden" name="action" value="create">
   <p id="result"></p>
   <input type="submit" value="Create" />
</form>
<hr/>
<form id="formRetrieve" >
 <h3>Retrieve Records</h3>
   <input type="hidden" name="action" value="retrieve">  
   <input id="btnRetrieve" type="submit" value="Retrieve" />
 </form>
<br/>
<table id="datatable" border='1'>
</table>
 <div id="messageRetrieve"></div>
<hr/>
<form id="formUpdate" >
   <h3>Update Record</h3>
   <p><label for="tid">TID:</label>
   <input id="tid" name="tid" type="text" value="" disabled="true"/></p>  
   <p><label for="name">Name:</label>
   <input id="name" name="name" type="text" value="" /></p>
   <p><label for="comment">Comment:</label><br/>
   <textarea id="comment" name="comment" cols="40"></textarea></p>
   <input type="hidden" name="action" value="update">
   <input type="submit" value="Update" />
   <p id="messageUpdate"></p>
</form>
<hr/>
<form id="formDelete" >
     <h3>Delete Record</h3>
     <p><label for="dtid">TID:</label>
   <input id="dtid" name="tid" type="text" value="" disabled="true"/></p>  
   <p><label for="dname">Name:</label>
   <input id="dname" name="name" type="text" value="" disabled="true"/></p>  
   <input type="hidden" name="action" value="delete">
   <input type="submit" value="Delete" />
   <p id="messageDelete"></p>  
</form>




JS

jQuery(document).ready(function($) {
   // variable to hold request
   var request;
   // bind to the submit event of our form
   $("#formCreate").submit(function(event) {
       // abort any pending request
       if (request) {
           request.abort();
       }
       // setup some local variables
       var $form = $(this);
       // let's select and cache all the fields
       var $inputs = $form.find("input, select, button, textarea");
       // serialize the data in the form
       var serializedData = $form.serialize();

       // let's disable the inputs for the duration of the ajax request
       // Note: we disable elements AFTER the form data has been serialized.
       // Disabled form elements will not be serialized.
       $inputs.prop("disabled", true);
       $('#result').text('Sending data...');

       // fire off the request to /form.php
       request = $.ajax({
           url: "https://script.google.com/macros/s/AKfycbyRSm1mcIhcehIYg7YaUlLILgpx7bv0z_B8ry6ebDiTqeoPQ3E/exec",
           type: "post",
           data: serializedData
       });

       // callback handler that will be called on success
       request.done(function(response, textStatus, jqXHR) {
           // log a message to the console
           $('#result').html('<a href="https://docs.google.com/spreadsheets/d/1aRNbMHyhUuXOVAWhw5enBIptceKTTJckW1LJPBw3fhA?usp=sharing" target="_blank">Success - see Google Sheet</a>');
           console.log("Created.");
           //console.log(response);
           //console.log(textStatus);
           //console.log(jqXHR);
           resetForm();
           $("#btnRetrieve").click();
       });

       // callback handler that will be called on failure
       request.fail(function(jqXHR, textStatus, errorThrown) {
           // log the error to the console
           console.error(
               "The following error occured: " +
               textStatus, errorThrown
           );
       });

       // callback handler that will be called regardless
       // if the request failed or succeeded
       request.always(function() {
           // reenable the inputs
           $inputs.prop("disabled", false);
       });

       // prevent default posting of form
       event.preventDefault();
   });


   $("#formRetrieve").submit(function(event) {
       // abort any pending request
       if (request) {
           request.abort();
       }
       // setup some local variables
       var $form = $(this);
       // let's select and cache all the fields
       var $inputs = $form.find("input, select, button, textarea");
       // serialize the data in the form
       var serializedData = $form.serialize();

       // let's disable the inputs for the duration of the ajax request
       // Note: we disable elements AFTER the form data has been serialized.
       // Disabled form elements will not be serialized.
       $inputs.prop("disabled", true);
       resetForm();
       $('#messageRetrieve').text('Requesting data...');
       $('#datatable').text("");


       // fire off the request to /form.php
       request = $.ajax({
           url: "https://script.google.com/macros/s/AKfycbyRSm1mcIhcehIYg7YaUlLILgpx7bv0z_B8ry6ebDiTqeoPQ3E/exec",
           type: "post",
           data: serializedData
       });

       // callback handler that will be called on success
       request.done(function(response, textStatus, jqXHR) {
           // log a message to the console
           $('#result').html('<a href="https://docs.google.com/spreadsheets/d/1PIJYBTjol_tEBuJMk5YvTRiePzqkdLMlRaZsEpC_ljg?usp=sharing" target="_blank">Success - see Google Sheet</a>');
           console.log("Retrieved.");
           //console.log(response);
           //console.log(textStatus);
           //console.log(jqXHR);
           var data = response.values;
           //console.log(data);      

           var trHTML = "";

           trHTML = "<tr><th>Delete</th><th>Edit</th>";
           $.each(data[0], function(key, value) {
               trHTML += "<th>" + key + "</th>";
           });
           trHTML += "</tr>";


           $.each(data, function(key, item) {
               var tdHTML = "";
               tdHTML += "<td><button onclick='recordDelete(this)' >delete</button></td>";
               tdHTML += "<td><button onclick='recordEdit(this)' >edit</button></td>";
               $.each(item, function(key, value) {
                   tdHTML += "<td>" + value + "</td>";
               });
               trHTML += "<tr>" + tdHTML + "</tr>";
           });


           //append records to table element
           $('#datatable').append(trHTML);
           //reset
           $('#messageRetrieve').text('');
           $('#messageRetrieve')[0].scrollIntoView(false);
       });

       // callback handler that will be called on failure
       request.fail(function(jqXHR, textStatus, errorThrown) {
           // log the error to the console
           console.error(
               "The following error occured: " +
               textStatus, errorThrown
           );
       });

       // callback handler that will be called regardless
       // if the request failed or succeeded
       request.always(function() {
           // reenable the inputs
           $inputs.prop("disabled", false);
       });

       // prevent default posting of form
       event.preventDefault();
   });

   $("#formUpdate").submit(function(event) {
       // abort any pending request
       if (request) {
           request.abort();
       }
       // setup some local variables
       var $form = $(this);
       // let's select and cache all the fields
       var $inputs = $form.find("input, select, button, textarea");
       // serialize the data in the form
       var serializedData = $form.serialize();

       // let's disable the inputs for the duration of the ajax request
       // Note: we disable elements AFTER the form data has been serialized.
       // Disabled form elements will not be serialized.
       $inputs.prop("disabled", true);
       resetForm();
       $('#messageUpdate').text('Updating data...');

       // fire off the request to /form.php
       request = $.ajax({
           url: "https://script.google.com/macros/s/AKfycbyRSm1mcIhcehIYg7YaUlLILgpx7bv0z_B8ry6ebDiTqeoPQ3E/exec",
           type: "post",
           data: serializedData
       });

       // callback handler that will be called on success
       request.done(function(response, textStatus, jqXHR) {
           // log a message to the console
           $('#result').html('<a href="https://docs.google.com/spreadsheets/d/1PIJYBTjol_tEBuJMk5YvTRiePzqkdLMlRaZsEpC_ljg?usp=sharing" target="_blank">Success - see Google Sheet</a>');
           console.log("Updated.");
           console.log(response);
           //console.log(textStatus);
           //console.log(jqXHR);
           resetForm();
           $('#messageUpdate').text('');
           $("#btnRetrieve").click();
       });

       // callback handler that will be called on failure
       request.fail(function(jqXHR, textStatus, errorThrown) {
           // log the error to the console
           console.error(
               "The following error occured: " +
               textStatus, errorThrown
           );
       });

       // callback handler that will be called regardless
       // if the request failed or succeeded
       request.always(function() {
           // reenable the inputs
           $inputs.prop("disabled", false);
       });

       // prevent default posting of form
       event.preventDefault();
   });


   $("#formDelete").submit(function(event) {
       // abort any pending request
       if (request) {
           request.abort();
       }
       // setup some local variables
       var $form = $(this);
       // let's select and cache all the fields
       var $inputs = $form.find("input, select, button, textarea");
       // serialize the data in the form
       var serializedData = $form.serialize();

       // let's disable the inputs for the duration of the ajax request
       // Note: we disable elements AFTER the form data has been serialized.
       // Disabled form elements will not be serialized.
       $inputs.prop("disabled", true);
       resetForm();
       $('#messageDelete').text('Deleting data...');

       // fire off the request to /form.php
       request = $.ajax({
           url: "https://script.google.com/macros/s/AKfycbyRSm1mcIhcehIYg7YaUlLILgpx7bv0z_B8ry6ebDiTqeoPQ3E/exec",
           type: "post",
           data: serializedData
       });

       // callback handler that will be called on success
       request.done(function(response, textStatus, jqXHR) {
           // log a message to the console
           $('#result').html('<a href="https://docs.google.com/spreadsheets/d/1PIJYBTjol_tEBuJMk5YvTRiePzqkdLMlRaZsEpC_ljg?usp=sharing" target="_blank">Success - see Google Sheet</a>');
           //console.log("Deleted.");
           //console.log(response);
           //console.log(textStatus);
           //console.log(jqXHR);
           //resetForm();
           $('#messageDelete').text('');
           $("#btnRetrieve").click();
       });

       // callback handler that will be called on failure
       request.fail(function(jqXHR, textStatus, errorThrown) {
           // log the error to the console
           console.error(
               "The following error occured: " +
               textStatus, errorThrown
           );
       });

       // callback handler that will be called regardless
       // if the request failed or succeeded
       request.always(function() {
           // reenable the inputs
           $inputs.prop("disabled", false);
       });

       // prevent default posting of form
       event.preventDefault();
   });


});

function recordEdit(param) {
   resetForm();
   var arrRecord = [];
   $(param).closest('tr').find('td').not(':first').each(function() {
       //var textval = $(this).text();
       arrRecord.push($(this).text());
   });
   $("#formUpdate #tid").val(arrRecord[1]);
   $("#formUpdate #name").val(arrRecord[3]);
   $("#formUpdate #comment").val(arrRecord[4]);
   //window.location.hash = '#formUpdate';
   // var formUpdate = document.getElementById ("formUpdate");
   //formUpdate.scrollIntoView(true);
   $('#formUpdate')[0].scrollIntoView();
   //alert(address.join('\n'));
};

function recordDelete(param) {
   resetForm();
   var arrRecord = [];
   $(param).closest('tr').find('td').not(':first').each(function() {
       //var textval = $(this).text();
       arrRecord.push($(this).text());
   });
   $("#formDelete #dtid").val(arrRecord[1]);
   $("#formDelete #dname").val(arrRecord[3]);
   //window.location.hash = '#formUpdate';
   // var formUpdate = document.getElementById ("formUpdate");
   //formUpdate.scrollIntoView(true);
   $('#formDelete')[0].scrollIntoView();
   //alert(address.join('\n'));
};

function resetForm() {
   $('#formCreate')[0].reset();
   $('#formUpdate')[0].reset();
   $("#formUpdate tid").prop("disabled", true);
   $('#formDelete')[0].reset();
   $("#formDelete dtid").prop("disabled", true);
   $("#formUpdate dname").prop("disabled", true);
}


GS

/**
*  See http://googlescripts.harryonline.net/objdb for full documentation
**/
var SHEET_NAME = "Sheet1";
var SCRIPT_PROP = PropertiesService.getScriptProperties(); // new property service

// If you don't want to expose either GET or POST methods you can comment out the appropriate function
function doGet(e) {
   return handleResponse(e);
}

function doPost(e) {
   return handleResponse(e);
}

function handleResponse(e) {
   // shortly after my original solution Google announced the LockService[1]
   // this prevents concurrent access overwritting data
   // [1] http://googleappsdeveloper.blogspot.co.uk/2011/10/concurrency-and-google-apps-script.html
   // we want a public lock, one that locks for all invocations
   var lock = LockService.getPublicLock();
   lock.waitLock(30000); // wait 30 seconds before conceding defeat.

   try {
       var action = e.parameter.action;

       if (action == 'create') {
           return addRecord(e);
       } else if (action == 'retrieve') {
           return getRecords(e);
       } else if (action == 'find') {
           return findRecords(e);
       } else if (action == 'update') {
           return setRecord(e);
       } else if (action == 'delete') {
           return delRecord(e);
       }
   } catch (e) {
       // if error return this
       return ContentService
           .createTextOutput(JSON.stringify({
               "result": "error",
               "error": e
           }))
           .setMimeType(ContentService.MimeType.JSON);
   } finally { //release lock
       lock.releaseLock();
   }
}

function addRecord(e) {
   var ss = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
   ssDB = objDB.open(ss.getId());
   var d = new Date();

   var rowCount = objDB.insertRow(ssDB, SHEET_NAME, {
       tid: d.getTime(),
       Timestamp: d,
       name: e.parameter.name,
       comment: e.parameter.comment
   });
   Logger.log(rowCount);
   return ContentService
       .createTextOutput(JSON.stringify({
           "result": "success",
           "values": rowCount
       }))
       .setMimeType(ContentService.MimeType.JSON);
}


function getRecords() {
   var ss = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
   ssDB = objDB.open(ss.getId());
   rows = objDB.getRows(ssDB, SHEET_NAME);
   Logger.log(rows);
   return ContentService
       .createTextOutput(JSON.stringify({
           "result": "success",
           "values": rows
       }))
       .setMimeType(ContentService.MimeType.JSON);
}

function findRecords(e) {
   var ss = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
   ssDB = objDB.open(ss.getId());

   var jsonObj = {}; //declare a json object
   jsonObj[e.parameter.field] = e.parameter.value; //set json object key:value
   rows = objDB.getRows(ssDB, SHEET_NAME, [], jsonObj);
   Logger.log(rows);
   return ContentService
       .createTextOutput(JSON.stringify({
           "result": "success",
           "values": rows
       }))
       .setMimeType(ContentService.MimeType.JSON);
}


function setRecord(e) {
   var ss = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
   ssDB = objDB.open(ss.getId());
   var d = new Date();

   var rowCount = objDB.updateRow(ssDB, SHEET_NAME, {
       tid: d.getTime(),
       Timestamp: d,
       name: e.parameter.name,
       comment: e.parameter.comment
   }, {
       tid: e.parameter.tid
   });
   Logger.log(rowCount);
   return ContentService
       .createTextOutput(JSON.stringify({
           "result": "success",
           "values": rowCount
       }))
       .setMimeType(ContentService.MimeType.JSON);
}


function delRecord(e) {
   var ss = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
   ssDB = objDB.open(ss.getId());
   var rowCount = objDB.deleteRow(ssDB, SHEET_NAME, {
       tid: e.parameter.tid
   });
   Logger.log(rowCount);
   return ContentService
       .createTextOutput(JSON.stringify({
           "result": "success",
           "values": rowCount
       }))
       .setMimeType(ContentService.MimeType.JSON);
}



function setup() {
   var doc = SpreadsheetApp.getActiveSpreadsheet();
   SCRIPT_PROP.setProperty("key", doc.getId());
}


CODEPEN

Demo:
.
.