Move row script not working

[Eric Morandeau]

Hi!

I have this script that I'm trying to merge several moving scripts into one single function.

I got the tip to use the switch function which I think is a good idea but I haven't use that function before in script and I can't get it to work.

So I need help to see what I'm doing wrong here.

Here is how it works:

The script will move rows to different sheets within the spreadsheet depending on which Col is edited.

In the example spreadsheet I have these Col's:

Col 7 (Återkomster) - In this one I put in a date and the row will be moved to the sheet "Återkomster"

Col 8 (Bokad) - In this one I put in a date and the row will be moved to the sheet "Bokad"

Col 9 (Nej) - In this one i put in 1 and the row will be moved to the sheet Nej

Col 10 (Avtal) - In this one I put in a date and the row will be moved to the sheet "Avtal"

Here is a link to a dummy sheet: https://docs.google.com/spreadsheets/d/1HzLRtSFULijIVbtW8xcIPDOgQKCYdQ4TLN2FgEtSqIg/edit?usp=sharing

Here is the script: 

function allInOne(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var r = e.range;

var sheet = range.sheet;

var sheetName = sheet.getName();

if (e.value != "") {

if (sheetName == "Pipe") {

switch (r.getColumn()) {

case 7:

// if (r.getColumn() == 7 && r.getValue() != "") { // you don't need this because the "if (e.value != "")" above and the "case" takes care of it

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Återkomster");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent();

//

// do column 7 tasks

break;

case 8:

// if (r.getColumn() == 8 && r.getValue() != "")

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Bokad");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent();

//

// do column 8 tasks

break;

case 9:

// if (r.getColumn() == 8 && r.getValue() != "")

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Bokad");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent();

//

// do column 8 tasks

break;

case 13:

// if (r.getColumn() == 10 && r.getValue() != "")

var row = r.getRow();

var numColumns = s.getLastColumn();

var targetSheet = ss.getSheetByName("Avtal");

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

s.getRange(row, 2, 1, numColumns).moveTo(target).range.clearContent();

//

// do column 13 tasks

break;

default:

// do nothing because the edit is happening on a column we don't want to watch

return;

}

}

}

}

[Chuck Grieshaber]

There are several errors: 

1. var sheet = range.sheet  should be var sheet = r.sheet which will return the spreadsheet you are working on.  
2. You can get the sheet name via the range: var sheetName = r.getSheet().getName();
3. range variable is not defined, s/b r.
4. s variable is used but is not defined.
5. line to move the row I'm not sure what you are trying to do with range.clearContent().  Clearing the row in the destination sheet?  Removing that will allow the row to be copied.

Here i the modified code, you still have some work to clean it up but it should be a good start:

function allInOne(e) {

var ss = SpreadsheetApp.getActiveSpreadsheet();

var r = e.range;

var sheet = r.getSheet();

var sheetName = r.getSheet().getName();

if (e.value != "") {

if (sheetName == "Pipe") {

switch (r.getColumn()) {

case 7:

// if (r.getColumn() == 7 && r.getValue() != "") { // you don't need this because the "if (e.value != "")" above and the "case" takes care of it

var row = r.getRow();

var numColumns = r.getLastColumn();

var targetSheet = ss.getSheetByName("Återkomster");

console.log(` ${row instanceof String} "${row}" "${numColumns}"`);

var target = targetSheet.getRange(targetSheet.getLastRow() + 1, 1);

var tSht = sheet.getRange(row, 2, 1, numColumns)

.moveTo(target);

//.range.clearContent();

//