Google-sheets – How to replace a formula with its result

google sheetsworksheet-function

I created a Google Spreadsheet that is made up of 20+ sheets (all formatted the same way). The company I work for loads containers. One of those sheets calculates a particular parameter on each of those 20+ sheets (countifs formula) and gives me a total value of how many containers were loaded on that day. For example, say on 11/20/14 we loaded 5 containers on sheet 1, sheet 2 = 6 containers, sheet 3 = 7 containers… etc. It is going to calculate how many total containers were loaded on 11/20/14 and spit out a number. Up to this part I am OK.

The problem is that these sheets need to be cleared and reused once we're finished loading on a sheet. What that means is that when a sheet is processed and cleared, any value attached to the parameter mentioned above is taken away. So if my total value is 20 containers loaded on 11/20/14, and I clear 5 containers on sheet 1, now my value will go down to 15 containers. But in reality, it was 20.

I've tried copying and pasting the end value at the beginning of the next day, before a sheet is processed and cleared, but sometimes, things get out of sync when a sheet is processed and cleared before the beginning of the next day.

Is there any way to have a value automatically set itself after "today" and no longer be associated with the formula?

Again, working with Google Spreadsheet. I'll need step by step help implementing a script if that is the answer.

Best Answer

In order to "freeze" the formula output on a summary sheet with a script, you can use copyTo method with the option contentsOnly:true. This can be used to copy the values to another place, or to exactly the same cells (thus, overwriting the formulas with their output). Here is a simple script that does this for cells A1:A10 of the sheet named "SummarySheet".

In order for such a process to run periodically, you have to decide which cells need to be frozen on each day; this is something specific to a particular worksheet. 

function freezeOutput(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("SummarySheet");
  var range = sheet.getRange("A1:A10");     
  range.copyTo(range, {contentsOnly:true});
}
Related Topic