I am using a MoveTo function to move a row from one worksheet to another based on the value of a cell.

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  if (r.getColumn() == 2 && r.getRow() == 2 && r.getWidth() == 1 && r.getHeight() == 1) {
    var sheetName;
    switch (e.value) {
      case "Pending":
        sheetName = "Pending";
        break;
      case "In Hand":
        sheetName = "In Hand";
        break;
    }
    if (sheetName) {
      const dest = e.source.getSheetByName(sheetName);
      src.getRange(r.getRow(),1,1,18).moveTo(dest.getRange(dest.getLastRow() 1,1,1,18));
      src.deleteRow(r.getRow());
    }
  }
}

It's working fine, the problems I'm facing are with the formulas. The formulas within the range always reference the original worksheet - not the worksheet the range has moved to.

Here's an example:

I use the following formula on my Pending worksheet

=ARRAYFORMULA(P2:P-L2:L)

When the function moves this to the In Hand worksheet - the formula still references the old sheet and looks like this:

=ARRAYFORMULA(Pending!P2:P-Pending!L2:L)

This breaks the spreadsheet. I need it look like this after the move:

=ARRAYFORMULA('In Hand'!P2:P-'In Hand'!L2:L)

Is it possible to:

1. Edit the function so that in addition to moving the row - it changes any formula instance of Pending! to 'In Hand'! (and vice versa). OR
2. Reference the active sheet in a formula - something like =ARRAYFORMULA(***Active-Sheet*!**P2:P-*Active-Sheet*!L2:L)

If I can just move the data and formulas that would also work. I hope that's clear and I welcome any potential solutions.

CodePudding user response：

Instead of using moveTo

1. build an array of the values and formulas to be "moved"
2. then use Range.setValues(values-formulas)

Sample:

function onEdit(e) {
  const src = e.source.getActiveSheet();
  const r = e.range;
  if (r.getColumn() == 2 && r.getRow() == 2 && r.getWidth() == 1 && r.getHeight() == 1) {
    var sheetName;
    switch (e.value) {
      case "Pending":
        sheetName = "Pending";
        break;
      case "In Hand":
        sheetName = "In Hand";
        break;
    }
    if (sheetName) {
      const dest = e.source.getSheetByName(sheetName);
      const srcRange = src.getRange(r.getRow(),1,1,18);
      const formulas = srcRange.getFormulas()[0];
      const values =  srcRange.getValues();
      formulas.forEach((formula,i) => {
        if(formula !== '') values[0][i] = formula;
      });
      dest.getRange(dest.getLastRow() 1,1,1,18)).setValues(values);
      src.deleteRow(r.getRow();
    }
  }
}

CodePudding user response：

function onEdit(e) {
  const sh = e.range.getSheet();
  const shts = ["Pending","In Hand"]
  const idx = shts.indexOf(e.value);
  if (e.range.columnStart == 2 && e.range.rowStart == 2 && e.range.columnEnd == e.range.columnStart && e.range.rowStart == e.range.rowStart) {
    if (idx) {
      const dest = e.source.getSheetByName(shts[idx]);
      sh.getRange(e.range.rowStart, 1, 1, 18).copyTo(dest.getRange(dest.getLastRow()   1, 1));
      sh.deleteRow(e.range.rowStart);
    }
  }
}