Spreadsheet formulas are not updating after the pages are copied using Google script. How do I fix this issue

Question

First off, here are two basic spreadsheets for what I am talking about:

Page one: https://docs.google.com/spreadsheets/d/1GZs_I9_beIO9xYBXatoxStRMmk9Aj3-UxPQpB9-iA-I/edit?usp=sharing

Page two: https://docs.google.com/spreadsheets/d/1R86_0SrAtyIJPAhMW0Ura-3gwOLn5l9u7Seuj2V0YCA/edit?usp=sharing

Essentially I am using a Google script to "update" the spreadsheet titled "The copy to update". This copy will automatically copy all of the pages from "The copy to pull updates from", delete the current pages on "The copy to update", and then rename the copied sheets back to what they were before. This means that any changes I make to "The copy to pull updates from" will automatically be updated on the other spreadsheet without having to redistribute the spreadsheet.

When the button is pressed on the "Update page" sheet the pages are successfully transferred across. The changes are successfully "updated" on the to update spreadsheet. However, the page updated first in the code has errors. Any cell with a formula that acquires data from another sheets returns "#REF!" with the error "Unresolved sheet name 'Insert sheet name here'." This means that the formula/cell can not see the newly copied sheet from the script. I can remedy this situation by clicking on any cell afflicted by this and pressing enter - Essentially "refreshing" the cell. However I am doing this on a project with 100+ cells like this and I only want to have to press one button to be able to do this.

Should I try and solve this issue using script, or change my method of updating the Spreadsheet pages a different way? Thanks to all help in advance!

Here is the script that I am using:

<code>
function Update() {
//Sets the ID of the page to update from
var ID = "1R86_0SrAtyIJPAhMW0Ura-3gwOLn5l9u7Seuj2V0YCA"
//Pulls the correct sheet using the ID
var source = SpreadsheetApp.openById(ID);
//Identifies the active spreadsheet in use to update. (The spreadsheet you pressed "Update" on)
var destination = SpreadsheetApp.getActiveSpreadsheet();
//Sheet to pull and copy to the current spreadsheet
var sheet1 = source.getSheetByName("1");
//Copying the sheet accross
sheet1.copyTo(destination);
//Identifies the old copy of the sheet
var sheet1 = destination.getSheetByName('1');
//Deletes the old copy of the sheet
destination.deleteSheet(sheet1);
//Gets the new copy of the sheet
var sheet1 = destination.getSheetByName('Copy of 1');
//Renames the new copy of the sheet
sheet1.setName("1")
//Repeating the same code with page 2
//Sheet to pull and copy to the current spreadsheet
var sheet1 = source.getSheetByName("2");
//Copying the sheet accross
sheet1.copyTo(destination);
//Identifies the old copy of the sheet
var sheet1 = destination.getSheetByName('2');
//Deletes the old copy of the sheet
destination.deleteSheet(sheet1);
//Gets the new copy of the sheet
var sheet1 = destination.getSheetByName('Copy of 2');
//Renames the new copy of the sheet
sheet1.setName("2")
//Repeating the same code
}
</code>

Answer 1

This is related to Force formula refresh through Apps Script

I'm having the exact same problem. Most of my formulas are =ArrayFormula formulas in the first or second row. My solution iterates through all cells in the first two rows of a given Spreadsheet and reevalutes any formulas.

This could probably be cleaned up to only modify the cell if it is a formula (rather than resetting the text value for non-formulas). It could also be modified to run over an entire Spreadsheet by changing the assigned range.

You might run this function on the sheet after it has been copied to the new location, or you could call myFunction while the desired sheet is active.

function updateFormulasTwoRows(given_sheet) {
  var last_col = given_sheet.getDataRange().getLastColumn();
  var range = given_sheet.getRange(1,1,2,last_col);
  // contains empty strings or cell formulas
  var formulas = range.getFormulas();
  // contains text, formula errors, formula output
  var contents = range.getValues(); 

  // formulas[0][0].length > 1 ==> is a formula
  for (var r = 0; r < range.getHeight(); r++) {
    for (var c = 0; c < range.getWidth(); c++) {
      var cell = range.getCell(r+1,c+1);
      // clear only the contents, not notes or comments or formatting.
      cell.clearContent();
      if (formulas[r][c].length > 1) {
        // cell was a formula, so insert the formula back in place
        cell.setValue(formulas[r][c]);
      } else {
        // cell was not a formula, so insert the text content back in place
        cell.setValue(contents[r][c]);
      }
    }
  }
}

// run this after selecting a sheet to reevalute formulas on
function myFunction() {
  var curr_sheet = SpreadsheetApp.getActiveSheet();
  updateFormulasTwoRows(curr_sheet);
}

Answer 2

Building on @bryan's answer above, I found a more efficient version of the code is the following, which performs the clearContent() and setValue() calls at the range level, rather than on each individual cell, thus saving a lot of calls to the back-end.

var updateFormulasAllRows = function updateFormulasAllRows(sheet) {
  var range = sheet.getDataRange();
  var formulas = range.getFormulas();
  var contents = range.getValues();
  var rangeWidth = range.getWidth(), rangeHeight = range.getHeight();
  for (var r = 0; r < rangeHeight; r++) {
    for (var c = 0; c < rangeWidth; c++) {
      if (formulas[r][c].length > 1) {
        contents[r][c] = formulas[r][c];
      }
    }
  }
  range.clearContent().setValues(contents);
};

This also changes the code to run over all the rows of the spreadsheet as suggested as an improvement.