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>

You don't get CORS/CORB errors ?

hi bro, you get any solution please reply

how do solve CORS/CORB problem. do you know any solution. please reply.

how do solve CORS/CORB problem. do you know any solution. please reply.

if you use GET method it's already handled CORS problem

if you use POST method you need to change the mimeType. example:

In Google Apps Script

function doPost(e) {
  return ContentService.createTextOutput(JSON.stringify({status: "success", "data": "my-data"})).setMimeType(ContentService.MimeType.JSON);
}

In JavaScript

fetch(URL, {
      redirect: "follow",
      method: "POST",
      body: JSON.stringify(DATA),
      headers: {
        "Content-Type": "text/plain;charset=utf-8",
      },
    })

*Note the attribute redirect: "follow" that is very important;