Google Sheets onEdit moving rows between sheets with different columns

2
votes

I'm trying to create a simple shipment processing flow and want to move rows between two sheets(tabs) using an onEdit checkbox trigger.

Here's a link to my project: https://docs.google.com/spreadsheets/d/1SPO21kAyNX5fEqwkXVjmPASkfHvZ5Tzbq4s7e22VdYg/edit#gid=2005756436

The basic concept is that shipments made up of multiple pieces are input on the first sheet and then copied to the WB_Log sheet. When planning what to load into a truck, a checkbox is used to move the pieces from the WB_Log sheet to the Load_Plan sheet where their loading location will be determined in a column to the right of the checkbox. If a piece or pieces of a shipment will not fit in a particular truck, I want to uncheck the box on Load_Plan and have the row moved back to the WB_Log Sheet.

Here is the onEdit function I found that almost works:

function onEdit(event) {
 

   // assumes source data in sheet named WB_Log

  // target sheet of move to named Load_Plan

  // getColumn with check-boxes is currently set to column 14 or N

  var ss = SpreadsheetApp.getActiveSpreadsheet();

  var s = event.source.getActiveSheet();

  var r = event.source.getActiveRange();
  

  if(s.getName() == "WB_Log" && r.getColumn() == 14 && r.getValue() == true) {

    var row = r.getRow();

    var numColumns = s.getLastColumn();

    var targetSheet = ss.getSheetByName("Load_Plan");

    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

    s.getRange(row, 1, 1, numColumns).moveTo(target);

    s.deleteRow(row);

  } else if(s.getName() == "Load_Plan" && r.getColumn() == 14 && r.getValue() == false) {

    var row = r.getRow();

    var numColumns = s.getLastColumn();

    var targetSheet = ss.getSheetByName("WB_Log");

    var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

    s.getRange(row, 1, 1, numColumns).moveTo(target);

    s.deleteRow(row);
  }
}

My issue is that Load_Plan has columns to the right of the checkbox that I don’t want moved back to WB_Log. How do I adjust the code so that only the information from columns A:N move from Load_Plan back to WB_Log when the checkbox is unchecked (False)?

Any advice or ideas anyone can lend would be very much appreciated.

1
google-apps-scriptgoogle-sheetstriggers
In your script, I can see assumes source data in sheet named WB_Log and target sheet of move to named Completed. But when I saw your shared Spreadsheet, I can see WB_Log sheet. But I cannot see Completed. And about When I uncheck the box in Load_Plan, I cannot find the checkbox in Load_Plan sheet. Can I ask you about the detail of them? – Tanaike
Welcome to Stack Overflow. This question currently includes multiple questions in one. It should focus on one problem only. Once you select one question, replace your code by a [mvce] and include a brief description of your search/research efforts as is suggested in How to Ask. – Rubén
Sorry, forgot to change the comments to the correct sheet names. I’m trying to move the information between WB_Log and Load_Plan sheets. The Completed reference was copied from a different project. The idea is that some shipments are made up of multiple pieces/boxes. To plan a load, I want to check boxes from WB_Log and have the information moved to Load_Plan. Sometimes not all the boxes will fit so I want to uncheck them from Load_Plan and have it moved back to WB_Log. – Shaun Christian
Thank you for replying. I would like to recommend to modify your question by including detail information for avoiding to confuse other users. – Tanaike
Thank you for the recommendation(s). I’ve edited the question to provide more details on the project and focus on one issue. – Shaun Christian

1 Answers

2
votes

Modification points:

  • When I saw your shared Spreadsheet, "WB_Log" sheet has the checkboxes in the column "N". And the checkboxes are put to the bottom of the sheet. By this, targetSheet.getLastRow() + 1 returns the next row of the buttom of sheet. I think that this is the reason of your issue.
    • I think that in your situation, when the last row of after row of "B4" is retrieved, the row number can be used.
  • And, in your script, the columns "A" to "O" in "Load_Plan" sheet are retrieved by var numColumns = s.getLastColumn(). But "WB_Log" sheet has the columns "A" to "N". I think that the retrieved columns are required to be reduced 1 column.

When above points are reflected to your script, it becomes as follows.

Modified script:

Please modify your script as follows.

} else if(s.getName() == "Load_Plan" && r.getColumn() == 14 && r.getValue() == false) {

  var row = r.getRow();

  var numColumns = s.getLastColumn();

  var targetSheet = ss.getSheetByName("WB_Log");

  var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

  s.getRange(row, 1, 1, numColumns).moveTo(target);

  s.deleteRow(row);
}

} else if(s.getName() == "Load_Plan" && r.getColumn() == 14 && r.getValue() == false) {
  var row = r.getRow();
  var numColumns = s.getLastColumn() - 1;  // Modified
  var targetSheet = ss.getSheetByName("WB_Log");
  var lastRow = targetSheet.getRange("B4").getNextDataCell(SpreadsheetApp.Direction.DOWN).getRow();  // Added
  var target = targetSheet.getRange(lastRow + 1, 1);  // Modified
  s.getRange(row, 1, 1, numColumns).moveTo(target);
  s.deleteRow(row);
}

Reference: