#REF error when copying a Sheet to another Spreadsheet (Google)

Question

I'm currently working on a script that gets a sheet from another Spreadsheet and duplicates it to the active Spreadsheet. Here's is how it works:

1. I have a sheet where the user determines the name of the original sheet, the link to the original Spreadsheet and the name the duplicated sheet should get.

2. The sheet can be duplicated in two ways: a. Only the values (with getValues() ; setValues()) b. Clone it with CopyTo

The problem is with the second method:

If the original sheet has a formula referencing another sheet, i (expectedly) get a #ref error, because that referenced sheet doesn't exist in the current Spreadsheet.
Ideally, the local/internal reference should be transformed into an external reference. I guess a regex can be used to find these formulas and insert an importrange inside them ? I honestly have no idea how to treat this.
Any ideas?

Answer 1

Cooper meant writing a JS script in Apps Script by using copyTo(spreadsheet):

 var source = SpreadsheetApp.getActiveSpreadsheet();
 var sheet = source.getSheets()[0];

 var destination = SpreadsheetApp.openById('ID_GOES HERE');
 sheet.copyTo(destination);

Additonal sample scripts that may help - Copy Google Spreadsheet Data to another Sheet with Apps Script.

Answer 2

There is no regex for searching for a particular type of formula in a sheet from a macro perspective. Instead, if you want to retain specific formulas but not others, you will need to inspect each cell with a formula and determine if it is one you want to serialize vs one you want to keep. Then if it is one you want to serialize, read the values from the source sheet and write them to the copy:

function copyFormulasAndSomeValues() {
  var source = SpreadsheetApp.getActive(). getActiveSheet();
  var remote = SpreadsheetApp.openById("some ID");
  // Copy all static values and formulas (and charts, formatting, etc.)
  var dest = source.copyTo(remote);

  // To efficiently read and write values when we know nothing
  // about the structure of invalid formulas, we will use a RangeList.
  // If structure exists (e.g. if a1 is invalid we know `A2:A100` will be too), we can combine batch
  // get/set methods with the 4 parameter getRange() method.
  var toRead = [];

  // Read in all formulas on the created sheet (no formula appears as "" e.g. nullstring).
  var formulas = copy.getDataRange().getFormulas();
  formulas.forEach(function (row, r) {
    row.forEach(function (formula, c, rowData) {
      if (!formula) return;
      // Check if this is a formula we want to replace.
      if (/* your tests here */) {
        // Store R1C1 notation for later reading.
        toRead.push(String(r + 1) + String(c + 1));
      }
    }); // End column value checking
  }); // End sheet row checking

  // If all formulas checked out, quit.
  if (toRead.length === 0)
    return;

  // Read desired values into a RangeList.
  var rangeList = source.getRangeList(toRead);
  var toWrite = rangeList.getRanges().map(function (range) {
    return range.getValue();
  });

  // Write to the same regions in the destination.
  dest.getRangeList(toRead).getRanges().forEach(function (range, i) {
    range.setValue(toWrite[i]);
  });
}

If you know the structure/arrangement of what will be valid / invalid, you will be able to improve this method by batch-reading a batch-writing the static values.