CODEWITHSUNDEEP
google-apps-scriptgoogle-sheetstriggers
Shaun Christian
Shaun Christian

Reputation: 35

Google Sheets onEdit moving rows between sheets with different columns

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.

Upvotes: 2

Views: 273

Answers (1)

Tanaike
Tanaike

Reputation: 201748

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.

From: 
} 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);
}
To: 
} 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:

Upvotes: 2

Related Questions