Google sheets/excel script - help

[audi321]

Hi all, can anyone help. I've been given a task at work to move a line in sheets if a cell in that line (Ln) is the word 'complete'. It is to move to another tab within the same sheet.

I've googled and got the following script, but I haven't the knowledge to finish it off, can anyone help? I assume it's the same in excel?

/**
* Moves row of data to another spreadsheet based on criteria in column 6 to sheet with same name as the value in column 4.

• /

function onEdit(e) {
// see Sheet event objects docs
// https://developers.google.com/apps-script/guides/t...
var ss = e.source;
var s = ss.getActiveSheet();
var r = e.range;

// to let you modify where the action and move columns are in the form responses sheet
var actionCol = 10;
var nameCol = 4;

// Get the row and column of the active cell.
var rowIndex = r.getRowIndex();
var colIndex = r.getColumnIndex();

// Get the number of columns in the active sheet.
// -1 to drop our action/status column
var colNumber = s.getLastColumn()-1;

// if our action/status col is changed to ok do stuff
if (e.value == "complete" && colIndex == actionCol) {
// get our target sheet name - in this example we are using the priority column
var targetSheet = s.getRange(rowIndex, nameCol).getValue();
// if the sheet exists do more stuff
if (ss.getSheetByName(targetSheet)) {
// set our target sheet and target range
var targetSheet = ss.getSheetByName(targetSheet);
var targetRange = targetSheet.getRange(targetSheet.getLastRow()+1, 1, 1, colNumber);
// get our source range/row
var sourceRange = s.getRange(rowIndex, 1, 1, colNumber);
// new sheets says: 'Cannot cut from form data. Use copy instead.'
sourceRange.copyTo(targetRange);
// ..but we can still delete the row after
s.deleteRow(rowIndex);
// or you might want to keep but note move e.g. r.setValue("moved");
}
}
}

[Davie_GLA]

Seems like a very complicated way to do something relatively simple.

If it were me and I'm no whizz, I'd have a filter on the column that has "complete" in it and set to not display (unchecked).

Then, if your work are looking for a lost of those that are complete then a new sheet can be created and either use a vlookup to return the completed rows or maybe a more pretty way would be a pivot table over the complete dataset.

Then it's just a case of a couple of clicks to refresh the filter and the pivot table and bobs yer auntie....

Failing that you can go full hog and build a database or buy a tool that will do it all for you. Some are even open source.