0

I am trying to set up an assignment sheet for my work team. I have a script to move a selected range from one sheet to another, but is there a way to modify it to have the person doing the assigning to fill in the tab name to which the selection should be moved?

This is my current move script:

​function onOpen(e) {
   SpreadsheetApp.getUi()
       .createMenu('Move')
       .addItem('Bryan', 'assignBryan')
       .addToUi();
 }

function assignBryan() {
 var ss = SpreadsheetApp.getActiveSpreadsheet()
     sheet = ss.getActiveSheet(),
     sheetName = sheet.getName(),
     data = sheet.getDataRange().getValues();
 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("Bryan"),
        lastRow = nextSheet.getLastRow();
    nextSheet.getRange(lastRow+1,1,numRows,7).setValues(values);
    sheet.deleteRows(startRow,numRows);
   } 
  } 
 } 
}

Is there a way to identify the destination sheet with manual input from the user in a dialog box?

Here's a link to the test sheet I'm working on: https://docs.google.com/spreadsheets/d/1JvfUQbzroo7NjZcXXzCtwobeFK8I2iZ_7uUmjrYMKpU

0

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>

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.