如何使用谷歌表作为数据库 [英] How to use google Sheet as an database
本文介绍了如何使用谷歌表作为数据库的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
如何在HTML Web应用程序中生成Google表格数据，它应该允许用户从HTML Web应用程序更新评论...?
How to generate the google sheet data in HTML web app and it should allow users to update the comments from HTML web app...?
我在Google工作表中有一些数据(通过Google表单提交)我想使用搜索按钮+基于请求编号"从Web应用程序中搜索工作表数据，应该能够更新Web应用程序中的评论，并且应该得到反映在Google工作表中(在同一单元格中)
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 )
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>
推荐答案
请参考以下代码以搜索&更新请求.另外，我还修复了引导程序类.
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脚本:
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屋！
查看全文
