Moving row to different sheet in google sheets from dropdown app script-CodePudding

I have tried this script and it does move the row to the right destination sheet, but if you need to change it back it will delete a row in the original sheet. I do know I need to make the row count a variable, but my main issue has been getting this to work without deleting rows. I have another script but it is without a loop and only works for sheet1. Any help would be appreciated here, thank you. I've only set it up as 5 columns in the table example

function onEdit(e) {
  const sh = SpreadsheetApp.getActive();
  const ss = sh.getActiveSheet();

  var dest;
  if (ss.getName() == 'sheet1' && e.value == 'Done') {
    dest = sh.getSheetByName('sheet1 Archive');
  }else if (ss.getName() == 'sheet1 Archive' && e.value != 'Done') {
    dest = sh.getSheetByName('sheet1');
  } else if (ss.getName() == 'sheet2' && e.value == 'Done') {
    dest = sh.getSheetByName('sheet2 Archive');
  } else if (ss.getName() == 'sheet2 Archive' && e.value != 'Done') {
    dest = sh.getSheetByName('sheet2');
  } else if (ss.getName() == 'sheet3' && e.value == 'Done') {
    dest = sh.getSheetByName('sheet3 Archive');
  } else if (ss.getName() == 'sheet3 Archive' && e.value != 'Done') {
    dest = sh.getSheetByName('sheet3');
  } else return;

  var rData = dest.getRange(1, 1, dest.getLastRow()).getValues();
  var dr = 0;
  for (dr; dr < rData.length; dr  ) {
    if (rData[dr][0] == '') break;
  }
  dr  ;
  var r = e.range.rowStart;
  ss.getRange(r, 1, 1, 11).copyTo(dest.getRange(dr, 1, 1, 11));
  ss.deleteRow(e.range.rowStart);
}

COL1	COL2	COL3	COL4	Status (dropdowns)
4	6	7	8	Live
5	7	8	9	Live
6	8	9	10	Done
7	9	10	11	Done
8	10	11	12	Live

CodePudding user response：

As you are interested only on the values, use Sheet.appendRow instead of Sheet.copyTo / Sheet.getRange

function onEdit(e) {
  const sh = SpreadsheetApp.getActive();
  const ss = sh.getActiveSheet();

  var dest;
  if (ss.getName() == 'sheet1' && e.value == 'Done') {
    dest = sh.getSheetByName('sheet1 Archive');
  }else if (ss.getName() == 'sheet1 Archive' && e.value != 'Done') {
    dest = sh.getSheetByName('sheet1');
  } else if (ss.getName() == 'sheet2' && e.value == 'Done') {
    dest = sh.getSheetByName('sheet2 Archive');
  } else if (ss.getName() == 'sheet2 Archive' && e.value != 'Done') {
    dest = sh.getSheetByName('sheet2');
  } else if (ss.getName() == 'sheet3' && e.value == 'Done') {
    dest = sh.getSheetByName('sheet3 Archive');
  } else if (ss.getName() == 'sheet3 Archive' && e.value != 'Done') {
    dest = sh.getSheetByName('sheet3');
  } else return;

  dest.appendRow(ss.getRange(e.range.rowStart, 1, 1, 11).getValues()[0]);
  ss.deleteRow(e.range.rowStart);
}

CodePudding user response：

Try it this way:

function onEdit(e) {
  e.source.toast("Entry");
  Logger.log(JSON.stringify(e))
  const sh = e.range.getSheet();
  const a = ["Sheet0"];//You can added more sheets 
  const b = ["Sheet1"];//ddestinations and sources have to be in same column
  const idx0 = a.indexOf(sh.getName());
  const idx1 = b.indexOf(sh.getName());

  if (~idx0 && e.range.columnStart == 2 && e.range.rowStart > 1 && e.value == "Done") {
    e.source.toast("0")
    let sh = e.source.getSheetByName(a[idx0]);
    let dsh = e.source.getSheetByName(b[idx0]);
    sh.getRange(e.range.rowStart, 1, 1,sh.getLastColumn()).copyTo(dsh.getRange(dsh.getLastRow()   1, 1));
    sh.deleteRow(e.range.rowStart);
  }
  if (~idx1 && e.range.columnStart == 2 && e.range.rowStart > 1 && e.value != "Done") {
    e.source.toast("1");
    let dsh = e.source.getSheetByName(a[idx1]);
    let sh = e.source.getSheetByName(b[idx1]);
    sh.getRange(e.range.rowStart, 1, 1, sh.getLastColumn()).copyTo(dsh.getRange(dsh.getLastRow()   1, 1));
    sh.deleteRow(e.range.rowStart);
  }
}

Sheet0:

COL1	COL2	COL3	COL4	COL5
4		6	7	8
5		7	8	9
6		8	9	10
7		9	10	11
8		10	11	12
9		11	12	13
10		12	13	14
11		13	14	15
12		14	15	16
13		15	16	17
14		16	17	18
15		17	18	19
16		18	19	20
17		19	20	21
18		20	21	22
19		21	22	23
20		22	23	24
4	UnDone	6	7	8
1	Undone	3	4	5
2	Undone	4	5	6

Sheet 1:

COL1	COL2	COL3	COL4	COL5
5		7	8	9
6		8	9	10
7		9	10	11
8		10	11	12
9		11	12	13
10		12	13	14
11		13	14	15
12		14	15	16
13		15	16	17
14		16	17	18
15		17	18	19
16		18	19	20
17		19	20	21
18		20	21	22
19		21	22	23
20		22	23	24
3	Done	5	6	7
3	Done	5	6	7