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 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();
functionqcComparison() {
var row = 0, col = 0;
compare(row, col);
}
functioncompare(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);
}
}
functioncompareNext(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><basetarget="_top"></head><body><tableid="qc-table"class="qc-table"><tr><td><buttonid="sheet-1"class="btn btn-primary btn-sm"onclick="google.script.run.setSheet1(<?=row?>,<?=col?>,<?=cell1?>);google.script.host.close();">Sheet 1</button></td><tdclass="profile-data"><pid="sheet-1-profile">Data from Sheet 1: <?=cell1?></p></td></tr><tr><td><buttonid="sheet-2"class="btn btn-secondary btn-sm"onclick="google.script.run.setSheet2(<?=row?>,<?=col?>,<?=cell2?>);google.script.host.close();">Sheet 2</button></td><tdclass="profile-data"><pid="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:
Post a Comment for "Apps Script: How To Load Dialog Boxes In A Loop At The Click Of Two Buttons?"