google-sheetsgoogle-apps-script

appscript to move checked rows to another sheet when clicked custom menu item in google sheets


enter image description here

As seen in the picture i have 2 sheets. I need the appscript code for the following.

  1. I need to create a custom menu as seen in the image.
  2. When Move to Done is clicked, the checked rows should get appended to the Done sheet.
  3. The checked rows should also be deleted from the pending sheet.

sheet link


Solution

  • Move data from one sheet to another

    I have crafted a solution that you may try to get your expected result.

    How this code works:

    1. The code will validate the values in column A that are "checked" or set to "TRUE"
    2. All of the values of these checkboxes will be moved to the "completed" sheet.
    3. The moved values will be deleted in the "pending" sheet.

    To meet your requirements of having a custom menu, please run the onOpen() function

    Completed code:

    function onOpen() {
      var ui = SpreadsheetApp.getUi();
      ui.createMenu('New Menu')
        .addItem('Move to Done', 'myFunction')
        .addToUi();
    }
    
    function myFunction() {
      var ss = SpreadsheetApp.getActiveSpreadsheet();
      var pending = ss.getSheetByName('pending');
      var completed = ss.getSheetByName('completed');
      
      var pendingData = pending.getDataRange().getValues();
      var values = [];
    
      for (var i = pendingData.length - 1; i >= 0; i--) {
        if (pendingData[i][0] === true) {
          values.push([pendingData[i][1], pendingData[i][2], pendingData[i][3]]);
          pending.deleteRow(i + 1);
        }
      }
      completed.getRange(completed.getLastRow() + 1, 2, values.length, 3).setValues(values.reverse());
    }
    

    Sample output:

    enter image description here

    Reference:

    Custom Menu