Google-sheets – Copy Rows from Master Sheet to other sheets based on user input

google sheetsgoogle-apps-script

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

Best Answer

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