I'm looking to edit this script so that before the rows are deleted, they are appended to the tab "Done". I tried creating a target sheet, but can't seem to get it working:

function remove(){
var SS = SpreadsheetApp.getActive();
var SHEET = SS.getSheetByName("Todo");
var TARGETSHEET = SS.getSheetByName("Done");
var RANGE = SHEET.getDataRange();
var DELETE_VAL = "Remove";
var COL_TO_SEARCH = 0;
var rangeVals = RANGE.getValues();

for(var i = rangeVals.length-1; i >= 0; i--){
if(rangeVals[i][COL_TO_SEARCH] === DELETE_VAL){
// TARGETSHEET.appendRow(i 1);
// TARGETSHEET.moveRows(rangeVals);
SHEET.deleteRow(i 1);
};
};
};

I essentially am trying to get it to operate like the script below, but for ANY value that has the word "Remove" in a specific column; not one by one. I want to run it from a button:

function onEdit(e){
    var sourceSheet = e.range.getSheet();
    if(sourceSheet.getSheetName() === 'Todo'){
        var row = e.range.getRow();
        var rowRange = sourceSheet.getRange(row, 1, 1, sourceSheet.getLastColumn());
        var rowValues = rowRange.getValues()[0];
        if(rowValues[0] === "Remove"){
            var targetSheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Done");  
            targetSheet.appendRow(rowValues);  
            sourceSheet.deleteRow(row);
        }
    } 
}

Any thoughts are greatly appreciated

CodePudding user response：

I believe your goal is as follows.

• You want to check column "A" of "Todo" sheet, and when the value of column "A" is "Remove", you want to move the row to the destination sheet "Done".
• You want to run the script by a button on the Spreadsheet.

In this case, how about the following sample script?

Sample script:

function myFunction() {
  const srcSheetName = "Todo";
  const dstSheetName = "Done";
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const srcSheet = ss.getSheetByName(srcSheetName);
  const dstSheet = ss.getSheetByName(dstSheetName);
  const range = srcSheet.getDataRange();
  let srcValues = range.getValues();
  const dstValues = srcValues.filter(([a]) => a == "Remove");
  srcValues = srcValues.filter(([a]) => a != "Remove");
  dstSheet.getRange(dstSheet.getLastRow()   1, 1, dstValues.length, dstValues[0].length).setValues(dstValues);
  range.clearContent().offset(0, 0, srcValues.length, srcValues[0].length).setValues(srcValues);
}

• When this script is run, the above goal is obtained. In this sample, the sheet of "Todo" is updated by new values filtered by "Remove".
• When you want to run this script by a button, please assign myFunction to the button.

Reference:

• filter()