CRUD Using Google Apps Script

README.md

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

After all settle then just open the html code on your browser. Have Fun !!

Code.gs

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);
}

crud.html

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