Programming Steps: Google Sheet Data CRUD + App Script + jQuery

Google Sheet Data CRUD + App Script + jQuery

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

HTML

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/AKfycbwODToMRUJLIXoZF7RUVFf8v8KXKjZVx51URbsoj-P-oSGbhMk/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("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/AKfycbwODToMRUJLIXoZF7RUVFf8v8KXKjZVx51URbsoj-P-oSGbhMk/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 ="";

$.each(data, function (i, item) {
var tdHTML="";
//prepare first two columns
if (i==0){
tdHTML= "<td>Delete</td><td>Edit</td>" + tdHTML;
tdHTML=tdHTML.replace(/td>/gi,"th>");
}else{
tdHTML+= "<td><button onclick='recordDelete(this)' >delete</button></td>";
tdHTML+= "<td><button onclick='recordEdit(this)' >edit</button></td>";
}
//prepare remaining columns
for (j = 0; j < item.length; j++) {
tdHTML += "<td>" + item[j] + "</td>";
}
trHTML += "<tr>" + tdHTML + "</tr>";
}); //end each records

//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/AKfycbwODToMRUJLIXoZF7RUVFf8v8KXKjZVx51URbsoj-P-oSGbhMk/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/AKfycbwODToMRUJLIXoZF7RUVFf8v8KXKjZVx51URbsoj-P-oSGbhMk/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 front-end example at: http://codepen.io/notarazi/pen/yMqyXX
// Usage
// 1. Enter sheet name where data is to be written below
var SHEET_NAME = "Sheet1";

// 2. Run > setup
//
// 3. Publish > Deploy as web app
// - enter Project Version name and click 'Save New Version'
// - set security level and enable service (most likely execute as 'me' and access 'anyone, even anonymously)
//
// 4. Copy the 'Current web app URL' and post this in your form/script action
//
// 5. Insert column names on your destination sheet matching the parameter names of the data you are passing in (exactly matching case)

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 create(e);
} else if (action == 'retrieve') {
return retrieve(e);
} else if (action == 'update') {
return update(e);
} else if (action == 'delete') {
return del(e);
} else if (action == 'findRowId'){
return findRowId(e.parameter.findstr);
}
} 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 getDataArr(headers, e){
var row = [];

// loop through the header columns
for (i in headers){
var d= new Date();
if (headers[i] == "tid"){ // special case if you include a unix Timestamp column
row.push(d.getTime());
}else if (headers[i] == "Timestamp"){ // special case if you include a 'Timestamp' column
row.push(new Date());
} else { // else use header name to get data
row.push(e.parameter[headers[i]]);
}
}

return row;
}

function create0(e){
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": 99}))
.setMimeType(ContentService.MimeType.JSON);

}
function create(e) {
// next set where we write the data - you could write to multiple/alternate destinations
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);

// we'll assume header is in row 1 but you can override with header_row in GET/POST data
var headRow = e.parameter.header_row || 1;
var numColumns = sheet.getLastColumn();
var headers = sheet.getRange(1, 1, 1, numColumns).getValues()[0];
var nextRow = sheet.getLastRow()+1; // get next row
var row = getDataArr(headers, e);
// more efficient to set values as [][] array than individually
sheet.getRange(nextRow, 1, 1, row.length).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "row": nextRow}))
.setMimeType(ContentService.MimeType.JSON);
}

function retrieve(e) {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var numRows = sheet.getLastRow();
var numColumns = sheet.getLastColumn();
var range = sheet.getRange(1, 1, numRows, numColumns);
var values = range.getValues();

return ContentService
.createTextOutput(JSON.stringify({"result":"success", "values": values}))
.setMimeType(ContentService.MimeType.JSON);
}

function findRowId(data) {

var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
//var column = sheet.getRange(column + ":" + column); // like A:A
var column = sheet.getRange(1,1,sheet.getLastRow(),1);
var values = column.getValues();

var data1=Number(data);
var row = 0;
var result = 0;
while ( values[row] && values[row][0] !== data ) {
row++;
}

if (values[row][0] === data)
result= row+1;
else
result = -1;

//result += data1;
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "values": result}))
.setMimeType(ContentService.MimeType.JSON);

}

function findInColumn(data) {

var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
//var column = sheet.getRange(column + ":" + column); // like A:A
var column = sheet.getRange(1,1,sheet.getLastRow(),1);
var values = column.getValues();

//data=1490461629269;
var testdata = Number(data);
var row = 0;

while ( values[row] && values[row][0] !== testdata ) {
row++;
}

if (values[row][0] === testdata)
return row+1;
else
return -1;

// return ContentService
// .createTextOutput(JSON.stringify({"result":"success", "values": row}))
// .setMimeType(ContentService.MimeType.JSON);

}

function findInRow(data) {

var sheet = SpreadsheetApp.getActiveSpreadsheet();
var rows = sheet.getDataRange.getValues();

for (var r=0; r<rows.length; r++) {
if ( rows[r].join("#").indexOf(data) !== -1 ) {
return r+1;
}
}

return -1;

}

function update(e) {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var numColumns = sheet.getLastColumn();

var rowId = findInColumn(e.parameter.tid);

var headers = sheet.getRange(1, 1, 1, numColumns).getValues()[0];
var row = getDataArr(headers, e);
//var rowId = e.parameter.rowId;
var tid= row[0];

// more efficient to set values as [][] array than individually
sheet.getRange(rowId, 1, 1, numColumns).setValues([row]);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "tid": tid}))
.setMimeType(ContentService.MimeType.JSON);
}

// Todo
function del(e) {
var doc = SpreadsheetApp.openById(SCRIPT_PROP.getProperty("key"));
var sheet = doc.getSheetByName(SHEET_NAME);
var rowId = findInColumn(e.parameter.tid);

sheet.deleteRow(rowId);
// return json success results
return ContentService
.createTextOutput(JSON.stringify({"result":"success", "rowId": rowId}))
.setMimeType(ContentService.MimeType.JSON);
}

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

CODEPEN DEMO