Move rows to another sheet when row is added, then delete-CodePudding

Needing to move a row from a source sheet to destination sheet when a row is added to source sheet. Then delete the source sheet row.

This is what I have but I keep getting errors:

 function moveValuesOnly () {
  var ss = SpreadsheetApp.getActiveSpreadsheet ();
  var sourceSheet = ss.getSheetByName("Source");
  var source = sourceSheet.getLastRow();
  var destSheet = ss.getSheetByName("Destination");
  var destRange = destSheet.getRange(destSheet.getLastRow() 1,1);
  source.copyTo (destRange, {contentsOnly: true});
  source.clear ();
}

CodePudding user response：

You can try your function this way:

function moveValuesOnly() {
  var ss = SpreadsheetApp.getActive();
  var sh = ss.getSheetByName("Sheet0");
  let lr = sh.getLastRow();
  var rg = sh.getRange(lr,1,1,sh.getLastColumn());
  var dsh = ss.getSheetByName("Sheet1");
  var drg = dsh.getRange(dsh.getLastRow()   1, 1);
  rg.copyTo(drg, { contentsOnly: true });
  sh.deleteRow(lr);
}

But this function will not be called by an onChange event of type "INSERT_ROW" unless the insert row is a user edit.

So for example:

If you create an installable onMyChange trigger of type onChange then the function insert row will not initiate moveValuesOnly because the onChange event is only triggered when a user inserts a row not when a formula or a script inserts the row.

function onMyChange(e) {
  Logger.log(JSON.stringify(e));
  if(e.changeType == "INSERT_ROW") {
    moveValuesOnly();
  }
}

function insertRow() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName('Sheet0');
  sh.insertRowAfter(1);
}