CODEWITHSUNDEEP
google-apps-script
KiKu
KiKu

Reputation: 377

How to use google Sheet as an database

How to generate the google sheet data in HTML web app and it should allow users to update the comments from HTML web app...?

I have some data in google sheet (Submitted through google form) I want to search the sheet data from web app based on "Request Number" using search button + should able to update the comments from web apps and that should get reflected in google sheet (On the same cell )

Google Sheet-File

function doGet() {
  return HtmlService
      .createTemplateFromFile('index')
      .evaluate();
}
<!DOCTYPE html>
<html lang="en">
<head>
  <title>Bootstrap Example</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<nav class="navbar navbar-inverse">
  <div class="container-fluid">
    <div class="navbar-header">
      <img src="https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcS5i2zvZQw0qJxPbgHrCp3tu_L0RjzTvBZBr5xROg7AVE0E4kR9" alt="IIFL LOGO" style="width:100px;height:50px;">
    </div>
    <ul class="nav navbar-nav">
      <li class="active"><a href="#">Home</a></li>
    </ul>
    <form class="navbar-form navbar-left">
      <div class="form-group">
        <input type="text" class="form-control" placeholder="Search Requset Number">
      </div>
      <button type="submit" class="btn btn-default">Submit</button>
    </form>
  </div>
</nav>
<div class="row">
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Requset Number</label><input type="text" class="form-control" id="usr"></div>
     <div class="col-xs-3"><label for="usr"><p class="text-primary">Assgined To</label><select class="form-control" id="sel1">
     <option>User1</option>
    <option>User2</option>
    <option>User3</option>
    <option>User4</option>
    </select>
  </div>
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Request Status</label><select class="form-control" id="sel1">
    <option>New</option>
    <option>Open</option>
    <option>Hold</option>
    <option>Reject</option>
    <option>Quote - In Process</option>
    <option>Negotiation – In process</option>
    <option>Commercial Closed</option>
    <option>PRS in Process</option>
    <option>PO in Process</option>
    <option>PO Send to Vendor</option>
    <option>Negotiation – In process</option>
    <option>Delivered</option>
    <option>Paid</option>
    <option>Closed</option>
  </select>
  </div>
</div>
 <hr style="height:2px;border-width:0;color:gray;background-color:gray">
  <div class="row">
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Timestamp</label><input type="text" class="form-control" id="usr"></div>
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Business User</label><input type="text" class="form-control" id="usr"></div>
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Request Type</label><input type="text" class="form-control" id="usr"></div>
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Request For</label><input type="text" class="form-control" id="usr"></div>
</div>
 <hr style="height:2px;border-width:0;color:gray;background-color:gray">
  <div class="row">
   <div class="col-md-6 col-sm-6 col-xs-6"><label for="usr">Short Description</label><textarea class="form-control" rows="3" id="comment"></textarea></div>
    <div class="col-md-6 col-sm-6 col-xs-6"><label for="usr">Remark Note</label><textarea class="form-control" rows="3" id="comment"></textarea></div>
</div>
 <hr style="height:2px;border-width:0;color:gray;background-color:gray">
<div class="container">
  <button type="button" class="btn btn-success" class="center">Clik here to Update/Save the Comments </button>    
</div>
</body>
</html>

Upvotes: 3

Views: 544

Answers (1)

Ritesh Nair
Ritesh Nair

Reputation: 3355

Refer the below code to search & update a request. Also I have fixed the bootstrap classes.

HTML:

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Bootstrap Example</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</head>
<body>
<nav class="navbar navbar-inverse">
  <div class="container-fluid">
    <div class="navbar-header">
      <img src="https://encrypted-tbn0.gstatic.com/images?q=tbn:ANd9GcS5i2zvZQw0qJxPbgHrCp3tu_L0RjzTvBZBr5xROg7AVE0E4kR9" alt="IIFL LOGO" style="width:100px;height:50px;">
    </div>
    <ul class="nav navbar-nav">
      <li class="active"><a href="#">Home</a></li>
    </ul>
    <form class="navbar-form navbar-left" onsubmit="return searchRequest();">
      <div class="form-group">
        <input type="text" id="requestID" class="form-control" placeholder="Search Requset Number">
      </div>
      <button type="submit" class="btn btn-default">Submit</button>
    </form>
  </div>
</nav>
<div class="container-fluid">
<div class="row">
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Requset Number</label><input type="text" class="form-control" id="requestNumber"></div>
     <div class="col-xs-3"><label for="usr"><p class="text-primary">Assgined To</label><select class="form-control" id="assignedTo">
     <option>User1</option>
    <option>User2</option>
    <option>User3</option>
    <option>User4</option>
    </select>
  </div>
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Request Status</label><select class="form-control" id="status">
    <option>New</option>
    <option>Open</option>
    <option>Hold</option>
    <option>Reject</option>
    <option>Quote - In Process</option>
    <option>Negotiation – In process</option>
    <option>Commercial Closed</option>
    <option>PRS in Process</option>
    <option>PO in Process</option>
    <option>PO Send to Vendor</option>
    <option>Negotiation – In process</option>
    <option>Delivered</option>
    <option>Paid</option>
    <option>Closed</option>
  </select>
  </div>
</div>
 <hr style="height:2px;border-width:0;color:gray;background-color:gray">
  <div class="row">
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Timestamp</label><input type="text" class="form-control" id="timestamp"></div>
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Business User</label><input type="text" class="form-control" id="BU"></div>
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Request Type</label><input type="text" class="form-control" id="rTpe"></div>
    <div class="col-xs-3"><label for="usr"><p class="text-primary">Request For</label><input type="text" class="form-control" id="rFor"></div>
</div>
 <hr style="height:2px;border-width:0;color:gray;background-color:gray">
  <div class="row">
   <div class="col-md-6 col-sm-6 col-xs-6"><label for="usr">Short Description</label><textarea class="form-control" rows="3" id="shortDesc"></textarea></div>
    <div class="col-md-6 col-sm-6 col-xs-6"><label for="usr">Remark Note</label><textarea class="form-control" rows="3" id="remark"></textarea></div>
</div>
 <hr style="height:2px;border-width:0;color:gray;background-color:gray">
<div class="container">
  <button type="button" class="btn btn-success" onclick="updateRequest()" id="updateBtn" class="center">Clik here to Update/Save the Comments </button>    
</div>
</div>
</body>
</html>
<script>
function searchRequest(){
  if($("#requestID").val()!=""){
    $("form input,form button").attr("disabled",true);
    google.script.run.withSuccessHandler(displayData).fetchRequestDetails($("#requestID").val())
  };
  return false;
}

function displayData(result){
  $("form input,form button").attr("disabled",false);
  if(result!= -1){
    console.log(result)
    $("#updateBtn").attr("disabled",false);
    $("#requestNumber").val(result[0][0]);
    $("#assignedTo").val(result[0][6]);
    $("#status").val(result[0][7]);
    $("#timestamp").val(result[0][1]);
    $("#BU").val(result[0][2]);
    $("#rTpe").val(result[0][3]);
    $("#rFor").val(result[0][4]);
    $("#shortDesc").val(result[0][5]);
    $("#remark").val(result[0][8]);
  }else{
    alert("Not Found");
  }
}


function updateRequest(){

  if($("#requestNumber").val()!=""){
     $("#updateBtn").attr("disabled",true);
     var updateJson = {};
     updateJson["Short Description"]=$("#shortDesc").val();
     updateJson["Remark Note"]=$("#remark").val();
     updateJson["Assgined To"]=$("#remark").val();
     updateJson["Assgined To"]=$("#assignedTo").val();
     updateJson["Request Status"]=$("#status").val();
     google.script.run.withSuccessHandler(function (){
         alert("Updated");
         $("#updateBtn").attr("disabled",false);
         }
     ).updateRequestComments($("#requestNumber").val(),updateJson);
  }
}

</script>

Google script: 

function doGet() {
  return HtmlService
      .createTemplateFromFile('index')
      .evaluate();
}

function fetchRequestDetails(id){
    var sheetDatabase = SpreadsheetApp.openById("1jrq4fgQdk1ccHGRD3cEY4EQsx5FvXRJuZp-9smOEVsw").getSheetByName("Sheet1");
    var columnValues = sheetDatabase.getRange(2, 1, sheetDatabase.getLastRow()-1).getValues(); 
    var searchResult = columnValues.findIndex(id);

    if(searchResult != -1)
    {
      var aData=sheetDatabase.getRange(searchResult+2, 1, 1,sheetDatabase.getLastColumn()).getValues();
      aData[0][1]= Utilities.formatDate(aData[0][1], "GMT +1","yyyy-MM-dd hh:mm:ss a")
      return aData;
    }
    return -1;
}

function updateRequestComments(reqID, comments){
    var scriptLock = LockService.getScriptLock();
    scriptLock.waitLock(3000);
      var sheetDatabase = SpreadsheetApp.openById("1jrq4fgQdk1ccHGRD3cEY4EQsx5FvXRJuZp-9smOEVsw").getSheetByName("Sheet1");
      var columnValues = sheetDatabase.getRange(2, 1, sheetDatabase.getLastRow()-1).getValues(); 
      var searchResult = columnValues.findIndex(reqID);
      if(searchResult != -1)
      {
        sheetDatabase.getRange(searchResult+2, 6, 1,1).setValue(comments["Short Description"]);
        sheetDatabase.getRange(searchResult+2, 9, 1,1).setValue(comments["Remark Note"]);
        sheetDatabase.getRange(searchResult+2, 7, 1,1).setValue(comments["Assgined To"]);
        sheetDatabase.getRange(searchResult+2, 8, 1,1).setValue(comments["Request Status"]);
      }
    scriptLock.releaseLock();
}

Array.prototype.findIndex = function(search){
  if(search == "") return false;
  for (var i=0; i<this.length; i++)
    if (this[i].toString().indexOf(search) > -1 ) return i;
  return -1;
}

Upvotes: 2

Related Questions