Google Sheet Data CRUD + App Script + jQuery + objDB

This tutorial is modified from http://basic-steps.blogspot.my/2017/02/google-sheet-data-crud-app-script-jquery.html.

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

<h3>Create Record</h3>

<p><label for="comment">Comment:</label><br/>

</form>

<hr/>

<h3>Retrieve Records</h3>

</form>

<br/>

</table>

<hr/>

<h3>Update Record</h3>

<p><label for="comment">Comment:</label><br/>

</form>

<hr/>

<h3>Delete Record</h3>

</form>

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

}

/**

* 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

Link: http://codepen.io/notarazi/pen/dvQaRQ

Demo:
.

See the Pen Google Sheet Data CRUD + App Script + jQuery + objDB by notarazi (@notarazi) on CodePen.

Programming Steps

Learn the powerful enterprise adaptable database:

Getting Started With ADABAS & Natural

Monday, December 12, 2016

Mac: Remove (non-empty) folders

Mac: start TextEditor from the command line

Mac: Display a tree-like structural view of any directory

Saturday, November 12, 2016

How To: Drag and Drop Files/Folders to Git Hub Web

Monday, October 10, 2016

Google Sheet Data CRUD + App Script + jQuery + objDB

CODEPEN