Replacing a spreadsheet formula with its result when the result satisfies a condition

Question

In Google Sheets I'm setting up a sheet with a column of formulas referencing a cell in which there is an hourly rate. That hourly rate can occasionally change, but I don't want previous calculations to be affected, only future calculations (ie, cells further down the column).

A great script solution was posted here: How do you replace a formula with its result?

With this script from red red wine:

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

But because formulas that have not yet been "triggered" show as "$0.00" in my column, those cells are converted from formulas to 0's.

Is there any way to make this script work on a cell only if the formula result is > 0?

Answer 1

In order to freeze only those cells in a column (say, A) where the value is positive, one can do the following:

function freezePositiveOutput(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet");
  var range = sheet.getRange("A1:A");
  var values = range.getValues();
  for (var i = 0; i < values.length; i++) {
    if (values[i][0] > 0) {
      var cell = range.offset(i, 0, 1, 1);   
      cell.copyTo(cell, {contentsOnly:true});
    }
  }
}

Having to freeze each cell individually, as this script does, can degrade performance if there are many cells to deal with. An alternative is to make the assumption that we'll have some number of nonzero values followed by zero values. In this case we can simply look for the last nonzero value, and freeze the range up to there, eliminating the inefficient for loop with cell-by-cell operations.

function freezePositiveOutput(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Sheet9");
  var range = sheet.getRange("A1:A");
  var values = range.getValues();
  var lastNonzero = Math.max.apply(null, values.map(function(a, i) {
    return a[0] > 0 ? i : 0;
  }));
  var truncatedRange = range.offset(0, 0, lastNonzero + 1, 1);
  truncatedRange.copyTo(truncatedRange, {contentsOnly:true});
}