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:
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.
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?