4

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.

  • Please explain what do you tried to adapt the solutions from the possible duplicate. – Rubén Nov 5 '16 at 17:02
  • I tried the scripts in the other solution shown, just adapted using my sheet name and range. Please understand that I lack an in-depth understanding of JavaScript. I get no result when applying those. – johnbonjowski Nov 5 '16 at 17:20
  • Regarding my comment, there is no problem with how much you know about JavaScript. IMHO the problem is that the question isn't clear enough, as it doesn't include enough details about your efforts to find the solution by yourself. It's worth so say that this is not a "write the code for me" service. Please take the tour and checkout How to Ask to learn about this site workings. – Rubén Nov 5 '16 at 17:24
  • I believe I have exhausted research on all relevant posts. I am trying to learn more. I'm not asking anyone to write the code for me. It would be nice if someone could point me in the right direction. – johnbonjowski Nov 5 '16 at 17:29
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;
}

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.