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
) :(