Apps 脚本:如何通过单击两个按钮循环加载对话框?

基本上,我正在比较两张表中的数据,因此我想显示一个对话框,其中包含两张表中两个单元格的数据和两个按钮,供用户选择哪个数据单元格是正确的。然后我想循环遍历一张纸与另一张纸不同的所有数据。


如何显示包含数据的对话框,使脚本等待按下按钮,然后转到列表中的下一项?


这是我到目前为止的脚本:


<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 对话框:


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


呼啦一阵风
浏览 95回答 1
1回答

千巷猫影

要在值不相等时显示对话框,您可以调用 HTML 服务在 Apps 脚本中创建 HTML,然后使用getUi().showModalDialog.编辑:for循环不是最好的解决方案,因为它们将在对话框打开时继续执行。在这种情况下最好使用递归。示例代码如下: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() {&nbsp; &nbsp; var row = 0, col = 0;&nbsp; &nbsp; compare(row, col);}function compare(row, col) {&nbsp; &nbsp; Logger.log(row, col);&nbsp; &nbsp; if (range1[row][col] != range2[row][col]) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Logger.log("Different values!");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var html = HtmlService.createTemplateFromFile("page");&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; html.row = row;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; html.col = col;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; html.cell1 = range1[row][col];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; html.cell2 = range2[row][col];&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var htmlOutput = html.evaluate();&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; SpreadsheetApp.getUi().showModalDialog(htmlOutput, 'Choice');&nbsp; &nbsp; }&nbsp; &nbsp; else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; compareNext(row, col);&nbsp; &nbsp; }}function compareNext(row, col) {&nbsp; &nbsp; Logger.log("Compare next", row, col);&nbsp; &nbsp; if (col < range1[row].length) {&nbsp; &nbsp; &nbsp; if (row < range1[col].length-1) {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; compare(++row, col);&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; &nbsp; else {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; row = 0;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; compare(row, ++col);&nbsp; &nbsp; &nbsp; }&nbsp; &nbsp; }&nbsp; &nbsp; return;}HTML 已更改为接受来自 Apps 脚本的值,示例代码如下:<!DOCTYPE html><html>&nbsp; <head>&nbsp; &nbsp; <base target="_top">&nbsp; </head>&nbsp; <body>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp;<table id="qc-table" class="qc-table">&nbsp; &nbsp; &nbsp; &nbsp; <tr>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <td>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <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>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <td class="profile-data"><p id="sheet-1-profile">Data from Sheet 1: <?=cell1?>&nbsp; &nbsp;</p></td>&nbsp; &nbsp; &nbsp; &nbsp; </tr>&nbsp; &nbsp; &nbsp; &nbsp; <tr>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <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>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; <td class="profile-data"><p id="sheet-2-profile">Data from Sheet 2: <?=cell2?>&nbsp; &nbsp;</p></td>&nbsp; &nbsp; &nbsp; &nbsp; </tr>&nbsp; &nbsp; &nbsp; </table>&nbsp; </body></html>请注意,脚本现在在单击工作表 1 或工作表 2 时运行函数来更新值:function setSheet1(row, col, value) {&nbsp; &nbsp; sheet2.getRange(++row,++col).setValue(value);&nbsp; &nbsp; compareNext(--row, --col);}function setSheet2(row, col, value) {&nbsp; &nbsp; sheet1.getRange(++row,++col).setValue(value);&nbsp; &nbsp; compareNext(--row, --col);}
打开App,查看更多内容
随时随地看视频慕课网APP

相关分类

JavaScript