Learn the powerful enterprise adaptable database:

Getting Started With ADABAS & Natural

Monday, October 10, 2016

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

.

10 comments:

  1. Please update this for the latest Jquery. I'm not clear on what commands have changed.

    ReplyDelete
  2. is 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
    Replies
    1. "item" var is empty or causing issue with "length" loop

      Delete
  3. my update and delete function works on second click, on first click it does not update or delete the entry

    ReplyDelete
    Replies
    1. Here is the solution
      https://www.youtube.com/watch?v=V4JMjmwHPEg

      Delete
  4. Get: "The script completed but did not return anything."
    Any help for noob?? thanks

    ReplyDelete
  5. This comment has been removed by the author.

    ReplyDelete
    Replies
    1. Can I get source code from you? wl3102833@gmail.com

      Delete
  6. where can I find the source code?

    ReplyDelete