UnitedWithCode · October 20, 2022 12:15 · Apr 18, 2020 · Apr 18, 2020 · Apr 18, 2020
diff --git a/README.md b/README.md
@@ -1,4 +1,4 @@
-# Sample Code CRUD Using Google Apps Script
+# CRUD Using Google Apps Script
 I want to learn some google apps script, found some tutorial here: https://www.crazycodersclub.com/appscript/crud-operation-on-google-spread-sheet-using-google-app-script-html-jquery
 but the code is not work well, so here my modified code based on newest guide  from google docs: https://developers.google.com/apps-script/api/reference/rest
 
@@ -10,3 +10,5 @@ YOUR_SPREADSHEET_LINK --> https://docs.google.com/spreadsheets/d/xxxxxx/edit?usp
 
 YOUR_APPS_SCRIPT_URL --> https://script.google.com/macros/s/xxxxxx/exec
 ```
+
+After all settle then just open the html code on your browser. Have Fun !!
diff --git a/Code.gs b/Code.gs
@@ -0,0 +1,142 @@
+function doGet(e) {
+    Logger.log(e);
+    var op = e.parameter.action;
+    var ss = SpreadsheetApp.open(DriveApp.getFileById("YOUR_SPREADSHEET_ID"));
+    var sn = "YOUR_SHEET_NAME";
+    var sheet = ss.getSheetByName(sn);
+
+
+    if (op == "insert")
+        return insert_value(e, sheet);
+
+    //Make sure you are sending proper parameters
+    if (op == "read")
+        return read_value(e, ss, sn);
+
+    if (op == "update")
+        return update_value(e, sheet);
+
+    if (op == "delete")
+        return delete_value(e, sheet);
+
+}
+
+//Receive parameter and pass it to function to handle
+function insert_value(request, sheet) {
+    var id = request.parameter.id;
+    var name = request.parameter.name;
+
+    //add new row with received parameter from client
+    var d = new Date();
+    var currentTime = d.toLocaleString();
+    var rowData = sheet.appendRow([currentTime, id, name]);
+    var result = "Insert successful";
+
+    result = JSON.stringify({
+        "result": result
+    });
+
+    return ContentService
+        .createTextOutput(request.parameter.callback + "(" + result + ")")
+        .setMimeType(ContentService.MimeType.JAVASCRIPT);
+}
+
+function read_value(request, ss, sheetName) {
+    var output = ContentService.createTextOutput(), data = {};
+    data.records = readData_(ss, sheetName);
+
+    var callback = request.parameters.callback;
+    if (callback === undefined) {
+        output.setContent(JSON.stringify(data));
+    } else {
+        output.setContent(callback + "(" + JSON.stringify(data) + ")");
+    }
+    output.setMimeType(ContentService.MimeType.JAVASCRIPT);
+
+    return output;
+}
+
+
+function readData_(ss, sheetName, properties) {
+    if (typeof properties == "undefined") {
+        properties = getHeaderRow_(ss, sheetName);
+        properties = properties.map(function (p) { return p.replace(/\s+/g, '_'); });
+    }
+
+    var rows = getDataRows_(ss, sheetName),
+        data = [];
+
+    for (var r = 0, l = rows.length; r < l; r++) {
+        var row = rows[r],
+            record = {};
+
+        for (var p in properties) {
+            record[properties[p]] = row[p];
+        }
+
+        data.push(record);
+
+    }
+    return data;
+}
+
+function getDataRows_(ss, sheetName) {
+    var sh = ss.getSheetByName(sheetName);
+    return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
+}
+
+
+function getHeaderRow_(ss, sheetName) {
+    var sh = ss.getSheetByName(sheetName);
+    return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
+}
+
+//update function
+function update_value(request, sheet) {
+    var id = request.parameter.id;
+    var flag = 0;
+    var country = request.parameter.name;
+    var lr = sheet.getLastRow();
+    for (var i = 1; i <= lr; i++) {
+        var rid = sheet.getRange(i, 2).getValue();
+        if (rid == id) {
+            sheet.getRange(i, 3).setValue(country);
+            var result = "value updated successfully";
+            flag = 1;
+        }
+    }
+    if (flag == 0)
+        var result = "id not found";
+
+    result = JSON.stringify({
+        "result": result
+    });
+
+    return ContentService
+        .createTextOutput(request.parameter.callback + "(" + result + ")")
+        .setMimeType(ContentService.MimeType.JAVASCRIPT);
+}
+
+function delete_value(request, sheet) {
+    var id = request.parameter.id;
+    var flag = 0;
+    var lr = sheet.getLastRow();
+    for (var i = 1; i <= lr; i++) {
+        var rid = sheet.getRange(i, 2).getValue();
+        if (rid == id) {
+            sheet.deleteRow(i);
+            var result = "value deleted successfully";
+            flag = 1;
+        }
+    }
+    if (flag == 0)
+        var result = "id not found";
+
+    result = JSON.stringify({
+        "result": result
+    });
+
+    return ContentService
+        .createTextOutput(request.parameter.callback + "(" + result + ")")
+        .setMimeType(ContentService.MimeType.JAVASCRIPT);
+}
diff --git a/README.md b/README.md
@@ -0,0 +1,12 @@
+# Sample Code CRUD Using Google Apps Script
+I want to learn some google apps script, found some tutorial here: https://www.crazycodersclub.com/appscript/crud-operation-on-google-spread-sheet-using-google-app-script-html-jquery
+but the code is not work well, so here my modified code based on newest guide  from google docs: https://developers.google.com/apps-script/api/reference/rest
+
+Don't forget to create your own Google SpreadSheet and Apps Script URL then change the code
+```
+YOUR_SPREADSHEET_ID --> https://docs.google.com/spreadsheets/d/YOUR_SPREADSHEET_ID/edit#gid=0
+YOUR_SHEET_NAME --> usually "Sheet1"
+YOUR_SPREADSHEET_LINK --> https://docs.google.com/spreadsheets/d/xxxxxx/edit?usp=sharing
+
+YOUR_APPS_SCRIPT_URL --> https://script.google.com/macros/s/xxxxxx/exec
+```
diff --git a/crud.html b/crud.html
@@ -0,0 +1,179 @@
+<html>
+
+<head>
+  <style>
+    table,
+    th,
+    td {
+      margin: 10px 0;
+      border: solid 1px #333;
+      padding: 2px 4px;
+      font: 15px Verdana;
+    }
+
+    th {
+      font-weight: bold;
+    }
+
+    #loader {
+      border: 16px solid #f3f3f3;
+      border-radius: 50%;
+      border-top: 16px solid blue;
+      border-bottom: 16px solid blue;
+      width: 60px;
+      height: 60px;
+      -webkit-animation: spin 2s linear infinite;
+      animation: spin 2s linear infinite;
+      visibility: hidden;
+    }
+
+    @-webkit-keyframes spin {
+      0% {
+        -webkit-transform: rotate(0deg);
+      }
+
+      100% {
+        -webkit-transform: rotate(360deg);
+      }
+    }
+
+    @keyframes spin {
+      0% {
+        transform: rotate(0deg);
+      }
+
+      100% {
+        transform: rotate(360deg);
+      }
+    }
+  </style>
+
+  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.0/jquery.min.js"></script>
+  <script>
+    var script_url = "YOUR_APPS_SCRIPT_URL";
+
+    // Make an AJAX call to Google Script
+    function insert_value() {
+      $("#re").css("visibility", "hidden");
+      document.getElementById("loader").style.visibility = "visible";
+      $('#mySpinner').addClass('spinner');
+
+      var id1 = $("#id").val();
+      var name = $("#name").val();
+
+
+      var url = script_url + "?callback=ctrlq&name=" + name + "&id=" + id1 + "&action=insert";
+
+
+      var request = jQuery.ajax({
+        crossDomain: true,
+        url: url,
+        method: "GET",
+        dataType: "jsonp"
+      });
+
+    }
+
+    function update_value() {
+      $("#re").css("visibility", "hidden");
+      document.getElementById("loader").style.visibility = "visible";
+      var id1 = $("#id").val();
+      var name = $("#name").val();
+      var url = script_url + "?callback=ctrlq&name=" + name + "&id=" + id1 + "&action=update";
+      var request = jQuery.ajax({
+        crossDomain: true,
+        url: url,
+        method: "GET",
+        dataType: "jsonp"
+      });
+    }
+
+    function delete_value() {
+      $("#re").css("visibility", "hidden");
+      document.getElementById("loader").style.visibility = "visible";
+      $('#mySpinner').addClass('spinner');
+      var id1 = $("#id").val();
+      var name = $("#name").val();
+      var url = script_url + "?callback=ctrlq&name=" + name + "&id=" + id1 + "&action=delete";
+      var request = jQuery.ajax({
+        crossDomain: true,
+        url: url,
+        method: "GET",
+        dataType: "jsonp"
+      });
+    }
+
+    // print the returned data
+    function ctrlq(e) {
+      $("#re").html(e.result);
+      $("#re").css("visibility", "visible");
+      read_value();
+    }
+
+    function read_value() {
+      $("#re").css("visibility", "hidden");
+      document.getElementById("loader").style.visibility = "visible";
+      var url = script_url + "?action=read";
+
+      $.getJSON(url, function (json) {
+        // Set the variables from the results array
+
+        // CREATE DYNAMIC TABLE.
+        var table = document.createElement("table");
+
+        var header = table.createTHead();
+        var row = header.insertRow(0);
+        var cell1 = row.insertCell(0);
+        var cell2 = row.insertCell(1);
+
+        cell1.innerHTML = "<b>ID</b>";
+        cell2.innerHTML = "<b>Name</b>";
+
+        // ADD JSON DATA TO THE TABLE AS ROWS.
+        for (var i = 0; i < json.records.length; i++) {
+
+          tr = table.insertRow(-1);
+          var tabCell = tr.insertCell(-1);
+          tabCell.innerHTML = json.records[i].ID;
+          tabCell = tr.insertCell(-1);
+          tabCell.innerHTML = json.records[i].NAME;
+        }
+
+        // FINALLY ADD THE NEWLY CREATED TABLE WITH JSON DATA TO A CONTAINER.
+        var divContainer = document.getElementById("showData");
+        divContainer.innerHTML = "";
+        divContainer.appendChild(table);
+        document.getElementById("loader").style.visibility = "hidden";
+        $("#re").css("visibility", "visible");
+      });
+    }
+  </script>
+</head>
+
+<body>
+  <div align="center">
+    <h1>CRUD OPERATION ON GOOGLE SPREAD SHEET, WEB APPLICATION USING GOOGLE APP SCRIPT .</h1>
+    <p>This is simple application, You can develop your own logic based on your requiremnets.<p>
+        <p>Be careful with the parameters you are sending, it should match the parameters that are recived in google app script.</p>
+
+        <form>
+          ID
+          <input type="text" name="id" id="id">
+          Name
+          <input type="text" name="name" id="name">
+        </form>
+
+        <div id="loader"></div>
+
+        <p id="re"></p>
+
+        <input type="button" id="b1" onClick="insert_value()" value="Insert"></input>
+        <input type="button" onclick="read_value()" value="Read" />
+        <input type="button" onclick="update_value()" value="Update" />
+        <input type="button" onclick="delete_value()" value="Delete" />
+        <a href="YOUR_SPREADSHEET_LINK"
+          target="_blank">Click here to open Spread Sheet </a>
+        <div id="showData"></div>
+  </div>
+</body>
+<html>