Replace formula with result, but exclude formulas that return blanks?

Question

Think I'm on the right track with this and the links below helped a lot.

Have modified one answer from another post to suit my sheet. So I have this...

function freezeOutput(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inbound Loads");
  var range = sheet.getRange("D2615:D5000");     
  range.copyTo(range, {contentsOnly:true});
}

Which works really well except that it removes the calculated formula that I use to return a blank cell when there is no matching result.

I have this in Column D, copied down... E2618, E2619 etc.

=IF(ISNA(index(MRP!A:A,match(E2617,MRP!B:B,0))), "", index(MRP!A:A,match(E2617,MRP!B:B,0)))

Which is returning the desired result (Supplier Name) from the MRP sheet, based on matching Purchase Order number in column E.

However, I really need the script to ignore cells with formulas that have no text (Supplier Name) - leaving the formula intact for when the sheet is updated throughout the day.

I've read the answers on replacing a spreadsheet formula with its result when the result satisfies a condition but can't seem to adapt or make this apply to what I want to do. Any thoughts please?

How do you replace a formula with its result?

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

edit: where trying to use an implementation of the script where the result satisfies a condition, I'm not certain but I think the sheet "views" the formula input as giving a result, albeit blank? So not a value of "0"

function freezeSup(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inbound Loads");
  var range = sheet.getRange("D2615:D5000");
  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});
}

&

function FreezeValueEntry(){
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getSheetByName("Inbound Loads");
  var range = sheet.getRange("D2615:D5000");
  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});
    }
  }
}

Doesn't return anything.

Answer 1

Short answer

From the question:

edit: where trying to use an implementation of the script where the result satisfies a condition, I'm not certain but I think the sheet "views" the formula input as giving a result, albeit blank? So not a value of "0"

Instead of something like

values[i][0] > 0

you should use

values[i][0] != ''

Explanation

Part 1: Misconception about blank cells and empty strings.

Your formula doesn't return a blank, it returns a empty string "". A blank cell is coerced to 0 always but a empty string doesn't.

Example:
If A1 has the following formula ="", =ISNUMBER(A1) returns FALSE but =A1+1 returns 1.

By the other hand, on Google Apps Script a blank cell value is handled as an empty string, not as 0 (zero).

Google Apps Script is based on JavaScript no on the "spreadsheet thinking" so there are several things like the default value of a blank cell that are handle in a different way than it's on Google Sheets.

Part 2: One practice for Google Apps Script troubleshooting

In case of doubts, a general practice is to create simple test functions that return the result to the log but in the case of Google Sheets you could use a custom function if you feel more comfortable with the Google Sheets UI than with the Script Editor.

To check the value that Google Apps Script got from an empty string you could use something like the following custom function:

/**
 * Returns a 2D array 
 *
 * @param {"A1"} input Cell reference string
 * @customfunction
 */
function valueDataType(input){
  var ss = SpreadsheetApp.getActiveSpreadsheet();
  var range = ss.getRange(input);
  var value = range.getValue();
  var output = [  
     ['Value: ' + value],
     ['Data type: ' + typeof value]
  ];
  return output;
}