Apps脚本:如何通过单击两个按钮来循环加载对话框? [英] Apps Script: How to load dialog boxes in a loop at the click of two buttons?
问题描述
基本上，我要比较两张纸上的数据，因此我想显示一个对话框，其中包含两张纸上的两个单元格的数据和两个按钮，供用户选择正确的数据单元格.然后，我想遍历一张纸与另一张纸之间不同的所有数据.
Basically, I am comparing the data in two sheets, so I want to show a dialog box with the data of two cells from two sheets and two buttons for the user to select which data cell is correct. Then I would like to loop through all the data that differed from one sheet to the other.
如何显示带有数据的对话框，让脚本等待按钮被按下，然后转到列表中的下一项?
How can I show a dialog with the data, make the script wait for the button to be pressed and then go to the next item on the list?
这是我到目前为止拥有的脚本:
This is the script that I have so far:
<script>
function myfunction() {
google.script.run.withSuccessHandler(qcComparison).qcGetData();
}
function qcComparison(sheetsData) {
var sheet1 = sheetsData["sheet1"];
var sheet2 = sheetsData["sheet2"];
var lastRow = sheet1.length;
var lastCol = sheet1[0].length
var headers = sheet1[0];
for (var row=1; row<=lastRow; row++) {
for (var col=0; col<lastCol; col++) {
// Do the comparison one cell at a time
var value1 = sheet1[row][col];
var value2 = sheet1[row][col];
if (value1 != value2) {
// Do something
}
}
}
}
document.addEventListener("DOMContentLoaded", myfunction());
</script>
这是我想要用数据更新的HTML对话框:
And this is the HTML dialog that I wan to update with the data:
<table id="qc-table" class="qc-table">
<tr>
<td><button id="sheet-1" class="btn btn-primary btn-sm">Sheet 1</button></td>
<td class="profile-data"><p id="sheet-1-profile">Data from cell 1</p></td>
</tr>
<tr>
<td><button id="sheet-2" class="btn btn-secondary btn-sm">Sheet 2</button></td>
<td class="profile-data"><p id="sheet-2-profile">Data form cell 2</p></td>
</tr>
</table>
推荐答案
要在值不相等时显示对话框，可以调用HTML服务以在Apps脚本中创建HTML，然后使用 getUi().showModalDialog .
To display a dialog box when the values are not equal you can call an HTML service to create the HTML within Apps Script and then use getUi().showModalDialog.
for 循环不是最佳解决方案，因为在打开对话框时，它们将继续执行.在这种情况下，最好使用递归.
for loops aren't the best solution since they will continue to execute while the dialog box is open. It is better to use recursion in this case.
下面的示例代码:
var sheet1 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet1");
var sheet2 = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet2");
var range1 = sheet1.getRange(1,1,sheet1.getLastRow(),sheet1.getLastColumn()).getValues();
var range2 = sheet2.getRange(1,1,sheet2.getLastRow(),sheet2.getLastColumn()).getValues();
function qcComparison() {
var row = 0, col = 0;
compare(row, col);
}
function compare(row, col) {
Logger.log(row, col);
if (range1[row][col] != range2[row][col]) {
Logger.log("Different values!");
var html = HtmlService.createTemplateFromFile("page");
html.row = row;
html.col = col;
html.cell1 = range1[row][col];
html.cell2 = range2[row][col];
var htmlOutput = html.evaluate();
SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Choice');
}
else {
compareNext(row, col);
}
}
function compareNext(row, col) {
Logger.log("Compare next", row, col);
if (col < range1[row].length) {
if (row < range1[col].length-1) {
compare(++row, col);
}
else {
row = 0;
compare(row, ++col);
}
}
return;
}
HTML更改为接受来自Apps脚本的值，下面的示例代码:
The HTML is changed to accept values from Apps Script, sample code below:
<!DOCTYPE html>
<html>
<head>
<base target="_top">
</head>
<body>
<table id="qc-table" class="qc-table">
<tr>
<td>
<button id="sheet-1" class="btn btn-primary btn-sm" onclick="google.script.run.setSheet1(<?=row?>,<?=col?>,<?=cell1?>);google.script.host.close();">Sheet 1</button></td>
<td class="profile-data"><p id="sheet-1-profile">Data from Sheet 1: <?=cell1?> </p></td>
</tr>
<tr>
<td><button id="sheet-2" class="btn btn-secondary btn-sm" onclick="google.script.run.setSheet2(<?=row?>,<?=col?>,<?=cell2?>);google.script.host.close();">Sheet 2</button></td>
<td class="profile-data"><p id="sheet-2-profile">Data from Sheet 2: <?=cell2?> </p></td>
</tr>
</table>
</body>
</html>
请注意，脚本现在可以在单击工作表1或工作表2以更新值时运行功能:
Note that the script now runs functions upon click of Sheet 1 or Sheet 2 to update the values:
function setSheet1(row, col, value) {
sheet2.getRange(++row,++col).setValue(value);
compareNext(--row, --col);
}
function setSheet2(row, col, value) {
sheet1.getRange(++row,++col).setValue(value);
compareNext(--row, --col);
}
参考文献:
这篇关于Apps脚本:如何通过单击两个按钮来循环加载对话框?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
