This answer addresses the OP's goal of selecting the "next sheet" option from a drop down list in a dialogue. It should be considered as an example of one method, and possibly requires further fine-tuning, error checking and/or validation.
The OP has an onOpen menu. Currently this lists several function, each of which is a permutation of the source and target sheet. In this answer, the onOpen menu is a single single function to launch the Dialog .addItem('Select Next', 'jqueryOpenMyDialog')
- The dialog contains a dropdown which creates the value of the "next" (or target) sheet.
- The result is returned to 'jqueryshowselect'. This performs a simple validation to ensure that the source and the target sheet are different.
- if they are NOT different, then an alert is shown, and the Dialog is opened again
- if they are different, then the code called a new function
assignnext(value);
to move the data.
assignnext(value);
is identical to the existing assignBryan()
except that:
nextSheet = ss.getSheetByName("Bryan"),
is replaced by
nextSheet = ss.getSheetByName(value),
.
jquerygs.gs
function jqueryOpenMyDialog() {
//Open a dialog
var htmlDlg = HtmlService.createHtmlOutputFromFile('jqueryhtml')
.setWidth(400)
.setHeight(300);
SpreadsheetApp.getUi()
.showModalDialog(htmlDlg, 'JQ Select target sheet');
};
function jqueryshowselect(value){
// Logger.log("DEBUG: Showselect - value = "+value);
var ss = SpreadsheetApp.getActiveSpreadsheet();
var sheetName = ss.getActiveSheet().getName();
// Logger.log("DEBUG: this sheet = "+sheetName+", and next sheet = "+value);
// Logger.log("DEBUG: this sheet len = "+sheetName.length+", and next sheet len = "+value.length);
if(sheetName != value){
Logger.log("this sheet is not equal to value. Proceed to move the data");
assignnext(value);
}
else{
Logger.log("this sheet IS equal to value. Select a different sheet");
showAlert(value);
}
}
function assignnext(value) {
var ss = SpreadsheetApp.getActiveSpreadsheet()
sheet = ss.getActiveSheet(),
sheetName = sheet.getName(),
data = sheet.getDataRange().getValues();
// Logger.log("DEBUG: next sheet = "+value)
if (sheetName !== 'Master') {
var range = sheet.getActiveRange(),
startRow = range.getRowIndex(),
numRows = range.getNumRows(),
numCols = range.getNumColumns()
if (numCols == 7) {
if (data.length > 1) {
var values = range.getValues(),
nextSheet = ss.getSheetByName(value),
lastRow = nextSheet.getLastRow();
nextSheet.getRange(lastRow+1,1,numRows,7).setValues(values);
sheet.deleteRows(startRow,numRows);
}
}
}
}
function showAlert(value) {
var ui = SpreadsheetApp.getUi();
var result = ui.alert(
'ERROR: CURRENT and NEXT Sheet are identical ',
'Choose another NEXT sheet',
ui.ButtonSet.OK);
// Process the user's response.
if (result == ui.Button.OK) {
// User clicked "Yes".
jqueryOpenMyDialog();
} else {
// User clicked X in the title bar.
}
}
jqueryhtml.html
<!DOCTYPE html>
<html>
<head>
<script src="//code.jquery.com/jquery-1.12.0.min.js"></script>
<script type="text/javascript">
$(document).ready(function(){
$("#jqueryselect").change(function(){
var selectedoption = $('#jqueryselect').val();
google.script.run
.withSuccessHandler(
// Dates delivered, close dialog
function() {
google.script.host.close();
})
// Display failure messages
.withFailureHandler(
function() {
var div = $('<div id="error" class="error">' + msg + '</div>');
$(element).after($("#errors"));
})
.jqueryshowselect(selectedoption);
});
});
</script>
<style>#jqueryselect{font-size:22px;}</style>
</head>
<body>
<div class="jquerysel">
<select id="jqueryselect">
<option value="Gerry">Gerry</option>
<option value="Lisa">Lisa</option>
<option value="Jamie">Jamie</option>
<option value="Bryan">Bryan</option>
</select>
</div>
<div id="errors"></div>
</body>
</html>