如何将值从Google脚本传递到HTML页面 [英] How to pass values to an html page from google script
问题描述
我有一个Google工作表，其中维护着一个项目列表，并且后面运行着一些脚本.我已经能够添加功能来单击添加项目"按钮，该按钮会打开一个HTML窗口以输入信息，并在提交时向工作表添加新记录.
I have a google sheet, maintaining a list of projects, with some scripting running behind it. I have been able to add functionality to click an Add Project button which opens an HTML window for entering the information, and on submit, add a new record to the sheet.
现在，如果状态更改为已取消"，我正在研究删除记录的过程.我想做的是显示一个html窗口，其中列出了项目的某些详细信息，并为用户提供了一个机会，可以不取消项目就返回，或者输入一些有关为什么要取消项目然后继续进行操作的注释.
Now I am working on a process to remove a record if the status is changed to Cancelled. What I would like to do is show an html window listing certain details of the project, and give the user a chance to either go back without cancelling the project, or enter some notes as to why it's being cancelled and then continue.
我被困的地方是用项目的详细信息填充html窗口.我想出了一种方法来做，但是我知道这不是最好的方法.
Where I am stuck is populating the html window with the details of the project. I have figured out one way to do it, but I know that this isn't the best way.
Google脚本:
function onEdit(e) {
if(e.range.getColumn() == 9 && e.value == "Cancelled" && e.source.getActiveSheet().getName() == "Summary") {
var cancelSheet = ss.getSheetByName(e.source.getActiveSheet().getName());
var cancelRange = cancelSheet.getRange(e.range.getRow(), 1, 1, cancelSheet.getLastColumn());
var cancelRow = cancelRange.getValues();
openCancelDialog(cancelRow);
}
}
function openCancelDialog(x) {
var html = HtmlService
//.createHtmlOutputFromFile('Cancel')
.createHtmlOutput(
'<table><tr><td colspan = \"2\"><b>You are cancelling the following project:</b></td></tr>' +
'<tr><td>Project Name: </td><td>' + x[0][4] + '</td></tr>' +
'<tr><td>Project Number: </td><td>' + x[0][0] + '</td></tr>' +
'<tr><td>Project Category: </td><td>' + x[0][1] + '</td></tr>' +
'<tr><td>Business Owner: </td><td>' + x[0][17] + '</td></tr>' +
'<tr><td>Project Manager: </td><td>' + x[0][18] + '</td></tr>' +
'</table>'
)
.setSandboxMode(HtmlService.SandboxMode.IFRAME);
SpreadsheetApp.getUi()
.showModalDialog(html, 'Cancel a Project');
}
这种方式是直接在gs中编写html.我想做的是创建一个单独的HTML页面.可以使用此方法完成操作(这也是我在gs中其他位置创建添加项目"对话框的方式):
This way is writing the html directly in the gs. What I'd like to do is have a separate html page that gets created. That can be done with this method (and is how I'm creating the Add Project dialog elsewhere in the gs):
function openCancelDialog(x) {
var html = HtmlService.createHtmlOutputFromFile('Cancel').setSandboxMode(HtmlService.SandboxMode.IFRAME);
SpreadsheetApp.getUi()
.showModalDialog(html, 'Cancel a Project');
}
这将是Cancel.html
This would be Cancel.html
<!DOCTYPE html>
<html>
<head>
<base target="_top">
<link rel="stylesheet" href="https://ssl.gstatic.com/docs/script/css/add-ons1.css">
<script src="//ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js">
</script>
<script>
<!-- Scripting to get my values? -->
</script>
</head>
<body>
<!-- Layout the window
Add a Comments section
Add a button to go back without cancel
Add a button to submit the cancel and update -->
</body>
</html>
但是我还没有弄清楚如何将数组从openCancelDialog函数传递给html，因此它可以显示在页面上.
But what I haven't figured out is how to pass the array from the openCancelDialog function to the html, so it cab be shown on the page..
我怀疑我需要将脚本添加到Cancel.html文件中以获得这些值.但是，有没有一种方法可以在创建数组时将该数组发送到html?
I suspect that I need to add scripting to the Cancel.html file to get those values. But is there a way to send that array to the html while it's being created?
推荐答案
Kos的答案为我提供了一些解决方案的想法.以及其他一些阅读材料，尤其是 https://www.w3schools.com/js/js_json_intro.asp 和后续部分，帮助我弄清楚了这一点.
Kos's answer gave me some ideas on how I could work it out. That, as well as some additional reading, especially https://www.w3schools.com/js/js_json_intro.asp and the follow up sections, helped me figure this one out.
新的js代码:
function onEdit(e) {
if(e.range.getColumn() == 9 && e.value == "Cancelled" && e.source.getActiveSheet().getName() == "Summary") {
var cancelSheet = ss.getSheetByName(e.source.getActiveSheet().getName());
var cancelRange = cancelSheet.getRange(e.range.getRow(), 1, 1, cancelSheet.getLastColumn());
var cancelRow = cancelRange.getValues();
//openCancelDialog(cancelRow);
var aSheet = e.source.getActiveSheet().getName();
var column = e.range.getColumn();
var row = e.range.getRow();
Logger.log("Col: " + column + " Row: " + row + " Sheet: " + aSheet);
Logger.log(cancelRow);
}
Logger.log(e);
}
function openCancelDialog(row) {
var ui = SpreadsheetApp.getUi();
// get template
var template = HtmlService.createTemplateFromFile('Cancel');
var myJSON = JSON.stringify(row);
// pass data to template
template.data = myJSON;
// get output html
var html = template.evaluate();
// show modal window
ui.showModalDialog(html, 'Cancel a Project');
}
新HTML:
<!DOCTYPE html>
<html>
<body>
<table>
<tr><td>Number: </td><td id="number"></td></tr>
<tr><td>Name: </td><td id="name"></td></tr>
<tr><td>Category: </td><td id="category"></td></tr>
<tr><td>Business Owner: </td><td id="owner"></td></tr>
<tr><td>Project : </td><td id="manager"></td></tr>
</table>
<script>
var objII = JSON.parse(<?=data?>);
document.getElementById("number").innerHTML = objII[0][0];
document.getElementById("name").innerHTML = objII[0][4];
document.getElementById("category").innerHTML = objII[0][1];
document.getElementById("owner").innerHTML = objII[0][17];
document.getElementById("manager").innerHTML = objII[0][18];
</script>
</body>
</html>
我怀疑可能会有更优雅的方法来做到这一点，甚至可能还有更多正确"的方法.但这似乎可以满足我的需要，所以我想我会把它贴出来，以防其他人在看.
I suspect there may be more elegant ways to do this, and probably even more "correct" ways. But this seems to be working for what I needed it to do, so I figured I'd post it in case someone else was looking.
谢谢
这篇关于如何将值从Google脚本传递到HTML页面的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
