google-apps-script之如何使用 google Sheet 作为数据库

lhb25 2024年02月23日编程语言 14 0

如何在 HTML 网络应用程序中生成 google 表格数据，它应该允许用户更新来自 HTML 网络应用程序的评论...？

我在谷歌表格中有一些数据(通过谷歌表格提交)我想使用搜索按钮基于“请求编号”从网络应用程序搜索表格数据+应该能够更新来自网络应用程序的评论并且应该得到反射(reflect)在谷歌表格中(在同一个单元格上)

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>

请您参考如下方法:

引用下面的代码来搜索和更新请求。我还修复了 Bootstrap 类。

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>

谷歌脚本:

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

IT序号网数据库

微信公众号号：IT虾米（左侧二维码扫一扫）欢迎添加！

Q : 823053937

热贴推荐

google-apps-script之如何使用 google Sheet 作为数据库

Q : 823053937

热贴推荐

google-apps-script之如何使用 google Sheet 作为数据库

sql之'#' 附近的语法不正确。 VB.net 与 SQL 数据库

sql-server之选择元素在接下来的 2 个月内出现。数据库服务器

aerospike之使用 Aerospike CE 为同一数据中心在 3 VPS 上复制数据库，无需 XDR

laravel之如何从laravel中具有相同ID的数据库中获取最新记录

openerp之我如何从数据库中获取 Odoo 的版本

sql之删除数据库但不删除 *.mdf/*.ldf

yii2之为 Yii 2 设置数据库

sql之Float 在数据库 Sql Server 中存储为 Real

sql-server之服务器级别的排序规则导致数据库过程中区分大小写的问题

api之为每个数据库表拥有一个 RESTful 资源是一种好习惯吗

sql之更新数据库架构 Doctrine 时执行 sql 脚本

sql之Azure SQL数据库缺少属性等

sql之删除数据库但不删除 .mdf/.ldf