Moving Row Contents to Next Open Row in Google Sheets

Question

I have two Sheets that I will be moving Data between frequently. On Sheet1, I have all my data and checkboxes are used to send that same data to Sheet2. When the data is sent over, empty rows are left behind so I know what positions are available when I rebuild the sheet at the end of the day.

The data on Sheet2 will be modified and at the end of the day I'd like to move everything back to Sheet1 taking the first available empty row. I have the first part working fine, but I cannot seem to get the data to move back to Sheet1 into the first available (empty Cell).

Currently the issue is when I uncheck the box to give the row a value of false, the data is removed from Sheet2 but not moved into Sheet1.

Here is what I have so far:

function onEdit(event) {
var ss = SpreadsheetApp.getActiveSpreadsheet();
var s = event.source.getActiveSheet();
var r = event.source.getActiveRange();

if(s.getName() == "Foss Test 2" && r.getColumn() == 4 && r.getValue() == true) {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Foss Test");
var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);
s.getRange(row, 1, 1, numColumns).moveTo(target);
} else if(s.getName() == "Foss Test" && r.getColumn() == 4 && r.getValue() == false) {
var row = r.getRow();
var numColumns = s.getLastColumn();
var targetSheet = ss.getSheetByName("Foss Test 2");
var target = targetSheet.getRange(targetSheet.getDataRange().getHeight() + 1, 1)
targetSheet.appendRow(target.getValues()[0]);
s.deleteRow(row);
}
}

I feel like my error is in the else block as currently no data is moved back to Sheet1.

Cooper Cooper · Accepted Answer · 2019-12-13T19:20:44

Although you didn't define the term "source" I assume this is what you want.

function onEdit(e) {
  e.source.toast('flag0');
  var sh=e.range.getSheet();
  if(sh.getName()=="Sheet1" && e.range.columnStart==4 && e.value=="TRUE") {
    var targetSheet=e.source.getSheetByName("Sheet2");
    var target=targetSheet.getRange(targetSheet.getLastRow() + 1,1);
    sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).moveTo(target);
  } else if(sh.getName()=="Sheet2" && e.range.columnStart==4 && e.value=="FALSE") {
    var targetSheet=e.source.getSheetByName("Sheet1");
    var vA=targetSheet.getDataRange().getValues();
    for(var i=0;i<vA.length;i++) {
      if(vA[i].join('').length==0) {
        var target=targetSheet.getRange(i+1,1);
        sh.getRange(e.range.rowStart,1,1,sh.getLastColumn()).moveTo(target);
        break;
      }
    }   
  }
}

If that's not what you want please let me know.

Moving Row Contents to Next Open Row in Google Sheets

1 Answers