Deleted string from plain text cell in Google Spreadsheet yields TRUE in ISBLANK function

Question

I´m using the ISBLANK function to determine whether a cell is empty or not. But the ISBLANK function responses a bit odd when the following is happening:

When a cell is formatted to have plain text (Menu Format, Number, Plain Text), and the content of that cell is deleted, the ISBLANK function considers the cell to be "filled" aka returning TRUE. The LEN function returns zero. Adding IFERROR(1/0) will yield in FALSE again.

Is it possible to obtain the state of that cell in either Google Spreadsheet or Google Apps Script, so that I can build a function (in GAS) that covers this phenomenon as well.

See example file I've prepared: ISBLANK returning TRUE for empty cell

I found some simular behaviour thats why I check if the value is not empty. Take a look at this post to check out the function I use. — Jacobvdb

GTM GTM · Accepted Answer · 2013-02-11T22:44:33

You can have a isBlank function that performs similarly.

function isBlank(sheet, cellRange) {
  var cell = sheet.getRange(cellRange);
  var value = cell.getValue();
  return String(value).replace(/ /g, '') == '';
}

If you happen to have a formula or predefined formatting in a cell, the value returned on an empty cell would be ' '(A single whitespace character). So I am stripping the whitespace before comparing the value against an empty string.

Deleted string from plain text cell in Google Spreadsheet yields TRUE in ISBLANK function

1 Answers