Google-sheets – Move row to different sheet based on answer in specific column

google sheetsgoogle-apps-script

I have a Google Sheet the has 5 tabs (Sheet 1, Sheet 2, Sheet 3, Sheet 4, Sheet 5). I am wanting to be able to automatically (on edit) move a single row of data based on the answer chosen from a list in column P, selections being the name of the sheet it needs to be moved to.

The rows need to be able to be moved back and forth between sheets and when they get moved from say, Sheet 1 to sheet 2, the data from the row ill only appear on sheet 2 and have been deleted off sheet 1.

I have the following code that works for 1 scenario, ie. moving from sheet 1 to sheet 2 when sheet 2 is selected, but when I add more scenarios ie. sheet 1 to sheet 3, sheet 2 to sheet 4, sheet 3 to sheet 1, sheet 1 to sheet 5, sheet 5 to sheet 3 etc. it doesn't work anymore.

It also worked when I had two scenarios, but when I added all the others it stopped working.

Here's what I when there was 2 scenarios:

function onEdit(e) {

  var s = e.source.getActiveSheet();
  if (s.getName() !== "Sheet 1" || e.range.columnStart !== 16 || e.value !== "Sheet 2") return;
  e.source.getSheetByName("Sheet 2")
    .appendRow(s.getRange(e.range.rowStart, 1, 1, s.getLastColumn())
      .getValues()
      .reduce(function (a, b) {
        return a.concat(b)
      }))
  s.deleteRow(e.range.rowStart);
}


function onEdit(e) {

  var s = e.source.getActiveSheet();
  if (s.getName() !== "Sheet 1" || e.range.columnStart !== 16 || e.value !== "Sheet 3") return;
  e.source.getSheetByName("Sheet 3")
    .appendRow(s.getRange(e.range.rowStart, 1, 1, s.getLastColumn())
      .getValues()
      .reduce(function (a, b) {
          return a.concat(b)
      }))
  s.deleteRow(e.range.rowStart);
}

Where it says sheet 1 is the sheet its pulling FROM, where it says sheet 2/3 after that is the response it's looking for and the second sheet 2/3 is the sheet to move it to.

How to I change this so that all scenarios work, so a row can be moved between all sheets?

Best Answer

Since your script is using the same name for both scenarios, the second one overwrites the definition made by the first one. One alternative is to make an on edit function that calls both scenarios.

Rename your current onEdit functions by using unique names, let say scenario01 and scenario02, and add a "master" on edit function, i.e.:

function onEdit(e){
  scenario01(e);
  scenario02(e);
}

Another alternative is to rethink the logic of your script, like putting all the "if this then that" rules on one on edit function. One advantage of this approach is that your script could be optimized to reduce the calls to the Spreadsheet Service and make it faster.

Personally I prefer the use of switch instead of a lot of ifs. I think that the following could give you an idea of what I meant by rethink the logic of your script:

function onEdit(e){
  var s = e.source.getActiveSheet();
  if (s.getName() !== "Sheet 1" || e.range.columnStart !== 16) return;
  switch (e.value){
    case 'Sheet 2':
      // Do something on Sheet 2
      break;
    case 'Sheet 3':
      // Do something on Sheet 3
      break;
    default:
      // Do something else in any other case
  }
}
Related Topic