Google Sheet Data CRUD + App Script + jQuery
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/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); } |
GS
// 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
.
Please update this for the latest Jquery. I'm not clear on what commands have changed.
ReplyDeleteis there an update for this? Its the only one I've seen that is worth trying to update. Its simple, I'd really like a working copy...im stumped...I can insert but I can't retrieve .. thanks in advance
ReplyDelete"item" var is empty or causing issue with "length" loop
DeleteHi
ReplyDeletemy update and delete function works on second click, on first click it does not update or delete the entry
ReplyDeleteHere is the solution
Deletehttps://www.youtube.com/watch?v=V4JMjmwHPEg
Get: "The script completed but did not return anything."
ReplyDeleteAny help for noob?? thanks
This comment has been removed by the author.
ReplyDeleteCan I get source code from you? wl3102833@gmail.com
Deletewhere can I find the source code?
ReplyDeleteHii
ReplyDeleteNice Blog
Guys you can visit here to know about
seo company in south delhi