Learn Google Apps Script

Learn Google Apps Script || Learn CRUD in AppsScript

Create or Insert Data in Google sheet using Apps Script:

First create a function to index or display html page:

function doGet(){
  return HtmlService.createTemplateFromFile("page")
                    .evaluate()
                    .setTitle("MyProject")
}

Above code add in Code.gs, as follows has been shown:

whole code:

function doGet(){
  return HtmlService.createTemplateFromFile("page")
                    .evaluate()
                    .setTitle("MyProject")
}

var ws = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");

function sub(obj){
ws.appendRow([new Date(),obj.name,obj.mob,obj.add])
ws.getRange(ws.getLastRow(), 1).setNumberFormat('@STRING@');
return "Data added.";
}


function readDT(id){
var db = ws.getDataRange().getValues();
for(var i=0;i<db.length;i++){
if(db[i][2]==id){
var dt = db[i];
db.length =0
return dt;
}
}
db.length =0
return "Data not found!";
}



function updateDT(name,id,add){
var db = ws.getDataRange().getValues();
for(var i=0;i<db.length;i++){
if(db[i][2]==id){
ws.getRange(i+1,1).setValue(new Date())
ws.getRange(i+1,2).setValue(name);
ws.getRange(i+1,4).setValue(add)
ws.getRange(i+1, 1).setNumberFormat('@STRING@');
db.length =0
return "Update made";
}
}
db.length =0
return "Data not found!";
}


function delDT(id){
var db = ws.getDataRange().getValues();
for(var i=0;i<db.length;i++){
if(db[i][2]==id){
ws.deleteRow(i+1)
db.length =0
return "Delete made.";
}
}
db.length =0
return "Data not found!";
}

page.html

<!DOCTYPE html>
<html>
  <head>
    <base target="_top">
  </head>
  <body>
 <form id="form1" onsubmit="event.preventDefault();sub1(this)">
  <input name="name"id="name">
  <input name="mob"id="mob">
  <input name="add"id="add">
  <input type="submit" value="Create">
  <input type="reset"value="reset">
 </form>
<button onclick="getD()">Read</button>
<button onclick="updateD()">Update</button>
<button onclick="dltD()">Delete</button>
<div id="info"></div>

<script>
function sub1(obj){
  document.getElementById("info").innerHTML ="working ...";
  google.script.run.withFailureHandler(res)
                    .withSuccessHandler(res)
                    .sub(obj);
}

function getD(){
  var id = document.getElementById("mob").value;
  document.getElementById("info").innerHTML ="working ...";
  google.script.run.withFailureHandler(res)
                    .withSuccessHandler(res)
                    .readDT(id);
}
function updateD(){
  var name = document.getElementById("name").value;
  var id = document.getElementById("mob").value;
  var add = document.getElementById("add").value;
  document.getElementById("info").innerHTML ="working ...";
  google.script.run.withFailureHandler(res)
                    .withSuccessHandler(res)
                    .updateDT(name,id,add);
}
function dltD(){
  var id = document.getElementById("mob").value;
  document.getElementById("info").innerHTML ="Deleting ...";
  google.script.run.withFailureHandler(res)
                    .withSuccessHandler(res)
                    .delDT(id);}

function res(x){
  document.getElementById("info").innerHTML =x;
}

  </script>


  </body>
</html>