Is there a way to script for "refreshing" a given tab?-CodePudding

So essentially, I create a copy of a spreadsheet for each new client, and I have a workout program template tab ("Week Template") that I duplicate and re-name based upon the week (Week 1, Week 2, Week 3, etc.). I have another tab called "Data" which pulls data from the week tabs, and sorts it according to the tab/sheet name.

I'm using this function for tracking data: =if('Week 1'!$A4="SQ",PRODUCT('Week 1'!$E4,'Week 1'!$K4,'Week 1'!$L4),"") In this instance, I'm tracking data when the SQ identifier is present.

My issue is that whenever I re-create, say "Week 1", the data won't auto-populate over on "Data" and shows #REF!. However, whenever I double click on the desired cell, it's almost as if the cell refreshes, and then provides the correct data. I believe this because the function is losing its original data source, and can't "re-connect".

As far as I can tell, there isn't a way for this to work natively on Google Sheets, so I'm wondering if there's a way to write a script to work around this issue.

Sheet: https://docs.google.com/spreadsheets/d/1KqSGcIAn_X4v0YtGnGZVXaY4Je6B5SS5tz70Mw6U9uk/edit?usp=sharing

Screenshots = https://imgur.com/a/ZCLaM1V

Here's a script that works as intended, however, it only works when triggered from the Editor menu, I would like it to update automatically:

function updateDataTab() {
  
  let ss = SpreadsheetApp.getActive();
  let sheet = ss.getSheetByName("Data");
  let range = sheet.getRange(3, 1, 60, 77);
  let formulas = range.getFormulas();
  range.clearContent();
  SpreadsheetApp.flush();
  range.setFormulas(formulas);

}

Any and all help is greatly appreciated as I'm quite new to coding.

Edit: added code Edit 2: Added clarification

CodePudding user response：

Looking at your spreadsheet you have set the formulas for several weeks in advance, in other words, the formulas include invalid references.

IMHO instead of refreshing the spreadsheet to make these formulas work, you should add the formulas after the new sheet is added. There are several ways to do this, i.e. you could use a custom menu to create the copy of the week template, renamed it add the formulas having references to this new sheet, or you could use an change installable trigger to add the formulas when a new sheet is inserted that meet certain criteria.