Apps Script: How To Load Dialog Boxes In A Loop At The Click Of Two Buttons?

August 18, 2022 Post a Comment

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 i

Solution 1:

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.

EDIT: 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.

Sample code below:

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

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>

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);
}

References:

Baca Juga

showModalDialog()

Templated HTML

Communication between HTML and Apps Script