Correct Range Definition for Google Sheets Script - replace formulas

Question

while trying to make an script to make a copy of a sheet inside a Google Sheet file I am facing what I think it is a bad range definition problem.

The scripts runs fine except from one "small" detail. When the script is executed, there are two (or more) sheets. One sheet is "Export DB" which is used to have an export structured table to copy and paste in an external DB. The other sheet would be the object of the script (active sheet where the button executes) which is going to be copied and renamed as version_n.

Problem is that the active sheet from where "DB Export" is pulling information, changes its name and thus the formulas in the export should be renamed.

Everything works well except for the small detail that the original headers in "DB Export" disappear.

HERE THE CODE:

function duplicateSheet() {
  var spreadsheet = SpreadsheetApp.getActiveSpreadsheet();
  var activeSheet = spreadsheet.getActiveSheet();

  // Get the original sheet name
  var originalSheetName = activeSheet.getName();

  // Create a copy of the active sheet
  var newSheet = activeSheet.copyTo(spreadsheet);

    // Rename the old sheet with a version number
  var sheets = spreadsheet.getSheets();
  for (var i = 0; i < sheets.length; i++) {
    if (sheets[i].getName() === originalSheetName) {
      var versionNumber = sheets.length - 1; // Version number is the number of existing sheets
      
      // Check if the original sheet name already contains a version number
      if (/_v\d+$/.test(originalSheetName)) {
        sheets[i].setName(originalSheetName.replace(/_v(\d+)$/, '_v' + versionNumber));
      } else {
        sheets[i].setName(originalSheetName + '_v' + versionNumber);
      }
      break;
    }
  }

  // Rename the new sheet to the original sheet name
  newSheet.setName(originalSheetName);

  // Update the link in the "DB Export" sheet to the new sheet
  var dbExportSheet = spreadsheet.getSheetByName('DB Export');
  if (dbExportSheet) {
    var formulas = dbExportSheet.getDataRange().getFormulas();
    for (var i = 0; i < formulas.length; i++) {
      for (var j = 0; j < formulas[i].length; j++) {
        formulas[i][j] = formulas[i][j].replace(originalSheetName, newSheet.getName());
      }
    }
    dbExportSheet.getRange(1, 1, formulas.length, formulas[0].length).setFormulas(formulas);
  }

On the last part of the code, we define dbExportSheet DataRange(). I think it is here where the problem locates. The DataRange is taking all the row/colums of the sheet while I want it to only modify all the sheet except for Row nº1.

Should be easy studd but I am missing something.

Answer 1

In your showing script, how about the following modification?

From:

// Update the link in the "DB Export" sheet to the new sheet
var dbExportSheet = spreadsheet.getSheetByName('DB Export');
if (dbExportSheet) {
  var formulas = dbExportSheet.getDataRange().getFormulas();
  for (var i = 0; i < formulas.length; i++) {
    for (var j = 0; j < formulas[i].length; j++) {
      formulas[i][j] = formulas[i][j].replace(originalSheetName, newSheet.getName());
    }
  }
  dbExportSheet.getRange(1, 1, formulas.length, formulas[0].length).setFormulas(formulas);
}

To:

// Update the link in the "DB Export" sheet to the new sheet
var dbExportSheet = spreadsheet.getSheetByName('DB Export');
if (dbExportSheet) {
  var [, ...formulas] = dbExportSheet.getDataRange().getFormulas(); // Modified
  for (var i = 0; i < formulas.length; i++) {
    for (var j = 0; j < formulas[i].length; j++) {
      formulas[i][j] = formulas[i][j].replace(originalSheetName, newSheet.getName());
    }
  }
  dbExportSheet.getRange(2, 1, formulas.length, formulas[0].length).setFormulas(formulas); // Modified
}

or, when TextFinder is used, I thought that the following modification might be able to be also used.

// Update the link in the "DB Export" sheet to the new sheet
var dbExportSheet = spreadsheet.getSheetByName('DB Export');
if (dbExportSheet) {
  dbExportSheet.getDataRange().createTextFinder(originalSheetName).matchFormulaText(true).replaceAllWith(newSheet.getName());
}

• By this modification, the sheet DB Export is updated by skipping the 1st row.