.
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.
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:.
See the Pen Google Sheet Data CRUD + App Script + jQuery + objDB by notarazi (@notarazi) on CodePen.
.
