gpt4 book ai didi

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

转载 作者:行者123 更新时间:2023-12-02 01:10:27 27 4
gpt4 key购买 nike

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

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

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>

最佳答案

引用下面的代码来搜索和更新请求。我还修复了 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;
}

关于google-apps-script - 如何使用 google Sheet 作为数据库,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/45386321/

27 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com