yihyang · September 27, 2020 13:54 · Jul 17, 2020 · Aug 4, 2019 · Aug 7, 2017 · Aug 7, 2017
diff --git a/README.md b/README.md
@@ -1,7 +1,7 @@
 # Google Script CRUD 
 By Ryan Aunur Rassyid
 
-Simplily create RESTful API with Google Script and store it to Google SpreadSheet like a Pro. 
+Simply create RESTful API with Google Script and store it to Google SpreadSheet like a Pro. 
 
 
 Learn From http://www.androidlabs.info/appscript/crud-operation-on-google-spread-sheet-using-google-app-script-html-jquery/
diff --git a/google-script-crud.gs b/google-script-crud.gs
@@ -101,7 +101,7 @@ function doUpdate(req, sheet)
 
    var lr = sheet.getLastRow();
 
-   var headers = getHeaderRow_(sheet);
+   var headers = _getHeaderRow(sheet);
    var updatesHeader = Object.keys(updates);
 
    // Looping for row

diff --git a/google-script-crud.gs b/google-script-crud.gs
@@ -6,16 +6,27 @@ function doGet(req) {
 
    var db = SpreadsheetApp.openById("YOUR SPREADSHEET ID");
 
+   // Don't forget to change your Sheet Name by default is 'Sheet1'
    var sheetUsers = db.getSheetByName("Users");
-
-   // Get Route
-   if (action == "read") {
-      return doRead(req, sheetUsers);
-   }
 
-   // Update Route
-   if (action == "update") {
-      return doUpdate(req, sheetUsers);
+   switch(action) {
+       case "read":
+           return doRead(req, sheetUsers);
+           break;
+       case "insert":
+           return doInsert(req, sheetUsers);
+           break;
+       case "update":
+           return doUpdate(req, sheetUsers);
+           break;
+       case "delete":
+           return doDelete(req, sheetUsers);
+           break;
+       default:
+           return response().json({
+              status: false,
+              message: 'silent!'
+           });
    }
 }
 
@@ -25,19 +36,57 @@ function doGet(req) {
  * @request-parameter | action<string>
  * @example-request | ?action=read
  */
-function read_value(request, sheetObject) 
+function doRead(request, sheetObject) 
 {
-   var output = ContentService.createTextOutput(),
    var data = {};
 
    data.records = _readData(sheetObject);
 
-   output.setContent(JSON.stringify(data))
-         .setMimeType(ContentService.MimeType.JSON);
+   return response().json(data);
 
-   return output;
 }
 
+/* Insert
+ *
+ */
+function doInsert(req, sheet) {
+   var id = req.parameter.id;
+   var username = req.parameter.username;
+   var email = req.parameter.email;
+   // all data your needed
+
+   var flag = 1;
+   var Row = sheet.getLastRow();
+   for (var i = 1; i <= Row; i++) {
+      /* getRange(i, 2) 
+       * i | is a row index
+       * 1 | is a id column index ('id')
+       */
+      var idTemp = sheet.getRange(i, 1).getValue();
+      if (idTemp == id) {
+         flag = 0;
+         var result = "Sorry bratha, id already exist";
+      }
+   }
+
+   // add new row with recieved parameter from client
+   if (flag == 1) {
+      var timestamp = Date.now();
+      var currentTime = new Date().toLocaleString(); // Full Datetime
+      var rowData = sheet.appendRow([
+         id,
+         username,
+         email,
+         timestamp,
+         currentTime
+      ]);
+      var result = "Insertion successful";
+   }
+
+   return response().json({
+      result: result
+   });
+}
 
 /* Update
  * request for Update
@@ -75,16 +124,45 @@ function doUpdate(req, sheet)
       }
    }
 
-   result = JSON.stringify({
+
+   // Output
+   return response().json({
       status: true,
       message: "Update successfully"
    });
-
-   // Output
-   var output = ContentService.createTextOutput();
-   return ContentService
-      .createTextOutput(result)
-      .setMimeType(ContentService.MimeType.JSON);
+}
+
+
+/* Delete
+ *
+ */
+function doDelete(req, sheet) {
+   var id = req.parameter.id;
+   var flag = 0;
+
+   var Row = sheet.getLastRow();
+   for (var i = 1; i <= Row; i++) {
+      var idTemp = sheet.getRange(i, 1).getValue();
+      if (idTemp == id) {
+         sheet.deleteRow(i);
+
+         var result = "deleted successfully";
+         flag = 1;
+      }
+
+   }
+
+   if (flag == 0) {
+      return response().json({
+         status: false,
+         message: "ID not found"
+      });
+   }
+
+   return response().json({
+      status: true,
+      message: result
+   });
 }
 
 
@@ -124,4 +202,13 @@ function _getHeaderRow(sheetObject) {
    var sh = sheetObject;
 
    return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
+}
+function response() {
+   return {
+      json: function(data) {
+         return ContentService
+            .createTextOutput(JSON.stringify(data))
+            .setMimeType(ContentService.MimeType.JSON);
+      }
+   }
 }
diff --git a/README.md b/README.md
@@ -1,2 +1,7 @@
 # Google Script CRUD 
-By Ryan Aunur Rassyid<[email protected]>
+By Ryan Aunur Rassyid
+
+Simplily create RESTful API with Google Script and store it to Google SpreadSheet like a Pro. 
+
+
+Learn From http://www.androidlabs.info/appscript/crud-operation-on-google-spread-sheet-using-google-app-script-html-jquery/
diff --git a/README.md b/README.md
@@ -0,0 +1,2 @@
+# Google Script CRUD 
+By Ryan Aunur Rassyid<[email protected]>
diff --git a/google-script-crud.gs b/google-script-crud.gs
@@ -8,34 +8,66 @@ function doGet(req) {
 
    var sheetUsers = db.getSheetByName("Users");
 
+   // Get Route
+   if (action == "read") {
+      return doRead(req, sheetUsers);
+   }
+
    // Update Route
    if (action == "update") {
-      return update_value(req, sheetUsers);
+      return doUpdate(req, sheetUsers);
    }
 }
 
+/* Read
+ * request for all Data
+ *
+ * @request-parameter | action<string>
+ * @example-request | ?action=read
+ */
+function read_value(request, sheetObject) 
+{
+   var output = ContentService.createTextOutput(),
+   var data = {};
+
+   data.records = _readData(sheetObject);
+
+   output.setContent(JSON.stringify(data))
+         .setMimeType(ContentService.MimeType.JSON);
 
-// Update
-function update_value(req, sheet) {
-   var output = ContentService.createTextOutput();
+   return output;
+}
+
+
+/* Update
+ * request for Update
+ *
+ * @request-parameter | id<string>, data<JSON>, action<string>
+ * @example-request | ?action=update&data={"email":"ryandevstudio@gmail.com", "username":"nyancodeid"}
+ */
+function doUpdate(req, sheet) 
+{
    var id = req.parameter.id;
    var updates = JSON.parse(req.parameter.data);
 
    var lr = sheet.getLastRow();
 
    var headers = getHeaderRow_(sheet);
    var updatesHeader = Object.keys(updates);
-
+
+   // Looping for row
    for (var row = 1; row <= lr; row++) {
+      // Looping for available header / column
       for (var i = 0; i <= (headers.length - 1); i++) {
-
          var header = headers[i];
-
+         // Looping for column need to updated
          for (var update in updatesHeader) {
             if (updatesHeader[update] == header) {
+               // Get ID for every row
                var rid = sheet.getRange(row, 1).getValue();
 
                if (rid == id) {
+                  // Lets Update
                   sheet.getRange(row, i + 1).setValue(updates[updatesHeader[update]]);
                }
             }
@@ -45,11 +77,51 @@ function update_value(req, sheet) {
 
    result = JSON.stringify({
       status: true,
-      message: "Update successfully",
-     log: logH
+      message: "Update successfully"
    });
-
+
+   // Output
+   var output = ContentService.createTextOutput();
    return ContentService
       .createTextOutput(result)
       .setMimeType(ContentService.MimeType.JSON);
+}
+
+
+/* Service
+ */
+function _readData(sheetObject, properties) {
+
+   if (typeof properties == "undefined") {
+      properties = _getHeaderRow(sheetObject);
+      properties = properties.map(function (p) {
+         return p.replace(/\s+/g, '_');
+      });
+   }
+
+   var rows = _getDataRows(sheetObject),
+      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(sheetObject) {
+   var sh = sheetObject;
+
+   return sh.getRange(2, 1, sh.getLastRow() - 1, sh.getLastColumn()).getValues();
+}
+function _getHeaderRow(sheetObject) {
+   var sh = sheetObject;
+
+   return sh.getRange(1, 1, 1, sh.getLastColumn()).getValues()[0];
 }
diff --git a/google-script-crud.gs b/google-script-crud.gs
@@ -0,0 +1,55 @@
+/* Route
+ * All Request with Method Get will be proces here
+ */
+function doGet(req) {
+   var action = req.parameter.action;
+
+   var db = SpreadsheetApp.openById("YOUR SPREADSHEET ID");
+
+   var sheetUsers = db.getSheetByName("Users");
+
+   // Update Route
+   if (action == "update") {
+      return update_value(req, sheetUsers);
+   }
+}
+
+
+// Update
+function update_value(req, sheet) {
+   var output = ContentService.createTextOutput();
+   var id = req.parameter.id;
+   var updates = JSON.parse(req.parameter.data);
+
+   var lr = sheet.getLastRow();
+
+   var headers = getHeaderRow_(sheet);
+   var updatesHeader = Object.keys(updates);
+
+   for (var row = 1; row <= lr; row++) {
+      for (var i = 0; i <= (headers.length - 1); i++) {
+
+         var header = headers[i];
+
+         for (var update in updatesHeader) {
+            if (updatesHeader[update] == header) {
+               var rid = sheet.getRange(row, 1).getValue();
+
+               if (rid == id) {
+                  sheet.getRange(row, i + 1).setValue(updates[updatesHeader[update]]);
+               }
+            }
+         }
+      }
+   }
+
+   result = JSON.stringify({
+      status: true,
+      message: "Update successfully",
+     log: logH
+   });
+
+   return ContentService
+      .createTextOutput(result)
+      .setMimeType(ContentService.MimeType.JSON);
+}
Original file line number	Diff line number	Diff line change
		@@ -0,0 +1,2 @@
		# Google Script CRUD
		By Ryan Aunur Rassyid<[email protected]>