"Copy to" confusion

Question

In my spread sheet, there are 3 tabs, let's call them A,B and C.

• Tab A has user input data
• Tab B picks up that data (get.range and set.formula) and time stamps it (daily interval) with rows appended (script) . This data is stored in columns B to F.
• Tab C has charts based on this data (columns B to F), so that real time trend info is available

To ensure that users don't go back to previous data and change it (in Tab A) ,the script uses this for Tab B:

var ulock = sh.getRange("B:F"); ulock.copyTo(ulock,{contentsOnly:true});

This works fine but with a drawback- if a user makes a mistake in data entry and corrects it, this correction is not updated in Tab B and Tab C because of the above copy command.

How do I ensure that the last row of data in Tab B stays current (changes in Tab A are reflected) and only on trigger firing the last row loses it formulae and retains only values? Something like range B:F to exclude the last row? or any other way?

Edit: I have last row variable defined In the script, so maybe looping copy command from Row1 to last row - 1 is an option? Not sure how to do that

To clarify my expectation further, I should be able to only retain values from A2 to F5 if there are six rows of data (Row 1 being header). The sixth row should also have the formulae so that corrections in Tab A are captured. Once the trigger is fired and new row created A2:F6 should have values only with 7th row having formulae and so on

This is important for me so spent a lot of time searching for a solution. This answer looked promising and I tweaked it to

=INDEX( FILTER( F:F , NOT( ISBLANK( F:F ) ) ) , ROWS( FILTER( F:F , NOT( ISBLANK( F:F ) ) ) )-1 ) for the last but one F populated column and it works fine on the spreadsheet but when I try

var freeze = sh.getRange("A2:=INDEX( FILTER( F:F , NOT( ISBLANK( F:F ) ) ) , ROWS( FILTER( F:F , NOT( ISBLANK( F:F ) ) ) )-1 )");

it returns a range error, whether I put the = sign or not before the second argument(Exception: Range not found) :(

Answer 1

I couldn't find a solution but here's a workaround:

Instead of appending rows after last row, I prepended rows by sh.insertRowAfter(1) and declared the statements to populate columns , as before.

At the end :

var freeze = sh.getRange("A3:F1000");freeze.copyTo(freeze,{contentsOnly:true})

This ensured that top most row 2 (row 1 is header) has formulae and values and any changes made in Tab A are reflected , while all other rows below are frozen (have values only). This arrangement has dates in descending order but that is fine for now. As this worksheet evolves, this date arrangement may cause a problem but I guess not insurmountable