Amend script to move Google Sheet tabs into the correct order

I have 30 near-identical tabs in a Google sheet. It also contains many other tabs.

Now and then I need to make changes to one of the 30, named SUR. When I am satisfied with the change I need to delete 29 tabs, then recreate them by duplicating the SUR tab and storing the name of each new tab in cell A1.

I got ChatGPT to write the script because I have no training in this field and it did a pretty good job. It works.

But I want the new tabs to appear before the SUR tab, in the right order. The SUR tab is somewhere in the middle and its position might change, depending on other requirements later.

No matter how I try to explain this to ChatGPT, it seems unable to accomplish it. In various attempts, the code has put the all the new tabs at the beginning, or at the end, or in reverse sequence of the array.

As I mentioned there are other tabs e.g. "Data", "Summary", which should not be affected in any way. The 29 tabs mentioned in the array should appear, in order, directly before the SUR tab, when they have been created.

In fact, if it's easier, they could go directly after the SUR tab instead. Provided the stay in the right order and appear either just before or just after the SUR tab, I'd be happy.

Can anyone help me fix it?

function duplicateAndRenameTabs() {
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var sheet = ss.getSheetByName('SUR');
  var tabNames = ["AGE", "ANA", "CAN", "CAR", "CHI", "CRI", "DEM", "DER", "DIA", "ENT", "GAS", "GEN", "HAE", "HSR", "HEP", "INF", "MH", "MET", "MUS", "NEU", "OPH", "ORA", "PC", "PUB", "REN", "REP", "RES", "STR", "TEC"]; // Add the names you want for new tabs here

  // Find the index of the 'SUR' sheet
  var surTabIndex = ss.getSheets().indexOf(sheet);

  // Delete existing tabs with names from the array
  var existingTabs = ss.getSheets();
  for (var i = existingTabs.length - 1; i >= 0; i--) {
    var sheetName = existingTabs[i].getName();
    if (tabNames.indexOf(sheetName) !== -1) {
      ss.deleteSheet(existingTabs[i]);
    }
  }

  // Duplicate the SUR tab and rename it with names from the array in the correct order
  for (var i = 0; i < tabNames.length; i++) {
    // Duplicate the 'SUR' sheet
    var newSheet = sheet.copyTo(ss);
    
    // Rename the new sheet
    newSheet.setName(tabNames[i]);

    // Populate cell A1 with the name of the tab only if it's in the array
    if (tabNames.indexOf(tabNames[i]) !== -1) {
      newSheet.getRange('A1').setValue(tabNames[i]);
    }

    // Move the new sheet immediately before the 'SUR' tab
    ss.setActiveSheet(newSheet);
    ss.moveActiveSheet(surTabIndex);
  }
}

Solution

You didn't mention anything about having charts in your SUR sheet. But this should work.

function duplicateTabs() {
  let tabs = ["AGE","ANA","CAN"];
  let spread = SpreadsheetApp.getActiveSpreadsheet();
  let sur = spread.getSheetByName("SUR");
  tabs.forEach( tab => {
      let sheet = spread.getSheetByName(tab);
      if( sheet ) spread.deleteSheet(sheet);
      let index = sur.getIndex();
      sheet = sur.copyTo(spread);
      sheet.setName(tab);
      sheet.getRange("A1").setValue(tab);
      sheet.activate();
      spread.moveActiveSheet(index);
    }
  )
  sur.activate();
}

Reference