there!

The functions below basically copies rows into another sheet. This is a tweaked sample of the code. The issue I'm finding is: When this copies the row(s) into the new sheet, that sheet becomes active. I'll delete it later, but the user gets dragged into this temporary sheet and I'd lie to know if there'd be a way to do it using copyTo() but without shifting tabs in front of the user.

/*
*It moves the order row into Production Sheet in WIP, as the user sets status Confirmed;
*/
function installedOnEdit(e) {
  const dstSpreadsheetId = "XXXXXxxxxxxxxXXXXXXXXXX" // Please set the destination Spreadsheet ID.
  const destSheetName = 'Página23'

  //Maps the values to serve as criteria to decide if this should continue running
  const ss = SpreadsheetApp.getActiveSpreadsheet();
  const orderSheet = ss.getSheetByName('Orders');
  const activeSheetName = ss.getActiveSheet().getName();
  const thisRow = e.range.getRow();
  const thisCol = e.range.getColumn();
  const cellValue = e.range.getValue();
  if (activeSheetName == orderSheet.getName() && thisRow > 5 && thisCol < 13 && cellValue == 'Confirmed') {
      moveOrder(ss, orderSheet, thisRow, dstSpreadsheetId, destSheetName)
      orderSheet.getRange(thisRow, thisCol).setValue('')
    }
  }
}

function moveOrder(ss, orderSheet, thisRow, dstSpreadsheetId, destSheetName) {
  const orderHeaders = orderSheet.getRange(5, 1, 1, 13);//Gets the table headers.    
  const rowValues = orderSheet.getRange(thisRow, 1, 1, 13);//Gets the changed row

  const newSheet = ss.insertSheet('MoveOrderToWIP');//Creates a temporary sheet
  orderHeaders.copyTo(newSheet.getRange(newSheet.getLastRow()   1, 1), { contentsOnly: true });//Moves the headers into the temporary sheet
  rowValues.copyTo(newSheet.getRange(newSheet.getLastRow()   1, 1), { contentsOnly: true });//Moves the row into the temporary sheet

}

Thank you!

CodePudding user response：

When I saw your updated script, it seems that dstSpreadsheetId and destSheetName are not used in moveOrder function. Although I cannot understand whether you wanted to use only one same Spreadsheet, for example, when you want to keep the edited range as the active range even when the new sheet is inserted by insertSheet, how about the following modification?

Issue and solution:

• In your script, ss is const ss = SpreadsheetApp.getActiveSpreadsheet();. In this case, when insertSheet is used, the activated ranges are changed. In this case, even when ss is used as e.source, the same result occurs. It seems that this is the current specification.

• When you want to keep the active range even when insertSheet is used, please use SpreadsheetApp.openById() instead of SpreadsheetApp.getActiveSpreadsheet(). By this, the active range is not changed even when insertSheet is used.

When this is reflected in your script, it becomes as follows. In this modification, your showing script is modified. Please be careful about this.

Modified script:

From:

const newSheet = ss.insertSheet('MoveOrderToWIP');

To:

const newSheet = SpreadsheetApp.openById(ss.getId()).insertSheet('MoveOrderToWIP');

• By calling the Spreadsheet from outside, even when insertSheet is used, the active range can be kept. In this case, even when Sheets API is used, the same result occurs.

Note:

• In your script, after "MoveOrderToWIP" sheet was inserted, the script is run again, an error occurs. Because the same sheet name is existing in the Spredsheet. So, please be careful this.

Reference:

• openById(id)

CodePudding user response：

All you need is from range and to range there is no need to actually activate the either of the sheets

fromRange.copyTo(toRange);

Probably the insertSheet() is making it the active sheet. So make some other the sheet the active sheet after doing the insert