Duplicate Sheet in Google Sheets leaving Custom Script behind

Question

I recently added a script within the Script Editor of Google Sheets to auto-sort my page based on values in Column D. It was set up to apply to the active sheet – Let's call it 'Sheet A'

Sheet A is used so people can view the data sort in descending order, which is great. Every time a change/edit is made to Sheet A, the data automatically resorts itself.

I did plenty of cell merging, styling, conditional formatting, etc in Sheet A that I don't want to have to recreate it. So I duplicated Sheet A – let's call it 'Sheet B' and applied Data > Sort Range alphabetical (Column E is names) so that people can have to option to view this way and find themselves much quicker than they would in Sheet A.

THE ISSUE: The custom script I added in Sheet A is being carried over into Sheet B – so anytime I make an edit, Sheet B auto-sorts by descending order from values in Column D.

Is it possible to duplicate my Sheet A but not keep the script in the copy (Sheet B)? Or is there a better way to migrate Sheet A to Sheet B that ignores the script coming with it all together?

Are sheet A and be different spreadsheets or different sheets? Copy paste your active backend code so that we can tell you how to modify it. (it will probably involve checking the tab name in the onEdit) — J. G.

Rubén Rubén · Accepted Answer · 2021-02-05T23:40:02

There is no simple way to do copy a spreadsheet without the linked files, either a form or a bounded script.

In your case that the spreadsheet includes simple triggers perhaps the simplest solution is to include a condition to check if the id of the active spreadsheet is equal to the id of the "original" spreadsheet:

function onEdit(e){
  if( e.source.getId() !== 'put_here_the_id_of_the_original_spreadsheet') return;
  // put here the things to do
}

NOTE: Bounded scripts are bounded to the container (in this case the spreadsheet) not to an specific sheet (page in Google Docs or slide in Google Slides), so leaving a script behind when making a a copy of one sheet doesn't make sense.

If you want that a function only make changes to certain sheet then you should add a condition to compare the name of the active sheet with the name of the "original" sheet

function onEdit(e){
  // Get the active sheet
  var sheet = e.range.getSheet();
  var name = sheet.getName();
  if(name !== 'SheetA') return;
  // put here the things to do
}

Duplicate Sheet in Google Sheets leaving Custom Script behind

1 Answers