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

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

first off, here 2 basic spreadsheets 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 using google script "update" spreadsheet titled "the re-create update". re-create automatically re-create of pages "the re-create pull updates from", delete current pages on "the re-create update", , rename copied sheets before. means changes create "the re-create pull updates from" automatically updated on other spreadsheet without having redistribute spreadsheet.

when button pressed on "update page" sheet pages transferred across. changes "updated" on update spreadsheet. however, page updated first in code has errors. cell formula acquires info sheets returns "#ref!" error "unresolved sheet name 'insert sheet name here'." means formula/cell can not see newly copied sheet script. can remedy situation clicking on cell afflicted , pressing come in - "refreshing" cell. doing on project 100+ cells , want have press 1 button able this.

should seek , solve issue using script, or alter method of updating spreadsheet pages different way? help in advance!

here script using:

<code> function update() { //sets id of page update var id = "1r86_0sratyijpahmw0ura-3gwoln5l9u7seuj2v0yca" //pulls right sheet using id var source = spreadsheetapp.openbyid(id); //identifies active spreadsheet in utilize update. (the spreadsheet pressed "update" on) var destination = spreadsheetapp.getactivespreadsheet(); //sheet pull , re-create current spreadsheet var sheet1 = source.getsheetbyname("1"); //copying sheet accross sheet1.copyto(destination); //identifies old re-create of sheet var sheet1 = destination.getsheetbyname('1'); //deletes old re-create of sheet destination.deletesheet(sheet1); //gets new re-create of sheet var sheet1 = destination.getsheetbyname('copy of 1'); //renames new re-create of sheet sheet1.setname("1") //repeating same code page 2 //sheet pull , re-create current spreadsheet var sheet1 = source.getsheetbyname("2"); //copying sheet accross sheet1.copyto(destination); //identifies old re-create of sheet var sheet1 = destination.getsheetbyname('2'); //deletes old re-create of sheet destination.deletesheet(sheet1); //gets new re-create of sheet var sheet1 = destination.getsheetbyname('copy of 2'); //renames new re-create of sheet sheet1.setname("2") //repeating same code } </code>

this related force formula refresh through apps script

i'm having exact same problem. of formulas =arrayformula formulas in first or sec row. solution iterates through cells in first 2 rows of given spreadsheet , reevalutes formulas.

this cleaned modify cell if formula (rather resetting text value non-formulas). modified run on entire spreadsheet changing assigned range.

you might run function on sheet after has been copied new location, or phone call myfunction while desired sheet 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 ==> formula (var r = 0; r < range.getheight(); r++) { (var c = 0; c < range.getwidth(); c++) { var cell = range.getcell(r+1,c+1); // clear contents, not notes or comments or formatting. cell.clearcontent(); if (formulas[r][c].length > 1) { // cell formula, insert formula in place cell.setvalue(formulas[r][c]); } else { // cell not formula, insert text content in place cell.setvalue(contents[r][c]); } } } } // run after selecting sheet reevalute formulas on function myfunction() { var curr_sheet = spreadsheetapp.getactivesheet(); updateformulastworows(curr_sheet); }

javascript google-apps-script spreadsheet