How to use google Sheet as an database

KiKu Published at Dev

KiKu

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>

Ritz

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2020-10-26

Comments

0 comments

How to use sheet ID in Google Sheets API?

How to use restricted data validation in Google sheet

How to use google Sheet as an database

How to use google Sheet as an database

pump.io port in URL

How to import an asset in swift using Bundle.main.path() in a react-native native module

Failed to listen on localhost:8000 (reason: Cannot assign requested address)

Inner Loop design for webscrapping

Can't pre-populate phone number and message body in SMS link on iPhones when SMS app is not running in the background

mysql.connector.errors.InterfaceError: 2003: Can't connect to MySQL server on '127.0.0.1:3306' (111 Connection refused)

Removed zsh, but forgot to change shell back to bash, and now Ubuntu crashes (wsl)

ggplotly no applicable method for 'plotly_build' applied to an object of class "NULL" if statements

How to run blender on webserver?

Resetting Value of <input type="time"> in Firefox

Converting a class method to a property with a backing field

Ambiguous use of 'init' with CFStringTransform and Swift 3

Execute ./script.sh with a crontab

How to set tab order for array of cluster,where cluster elements have different data types in LabVIEW?

How to pass data to the ng2-bs3-modal?

Retrieve Element Tag Value XML Using Bash

Spring Boot JPA PostgreSQL Web App - Internal Authentication Error

SQL Server : need add a dot before two last character

Making Array From Page Elements in jQuery

Laravel's ORM sync with timestamps doesn't update timestamps

Do animations stop css changes after animation completion?