CODEWITHSUNDEEP
google-sheets
sites
sites

Reputation: 21795

Handle blank values in Google Spreadsheet

I have in a cell:

=QUERY(A:B;"select A, sum(B) group by A")

Problem is that when there is a blank value in column B formula does not compute, how can I make that when a blank value is encountered in column B, it is taken as 0?

Previously I set those cells as 0, and it worked, but this time they are a lot of them.

Upvotes: 1

Views: 2710

Answers (2)

wchiquito
wchiquito

Reputation: 16551

A bit strange, with a very simple example, it seems that everything works as expected:

enter image description here

Upvotes: 2

sites
sites

Reputation: 21795

First, Click Tools > Script Editor:

function zeroIfBlank() {
  var range = SpreadsheetApp.getActiveSheet().getDataRange();
  var numRows = range.getNumRows();
  var numCols = range.getNumColumns();
  for (var i = 1; i <= numRows; i++) {
    for (var j = 1; j <= numCols; j++) {
      var currentValue = range.getCell(i,j).getValue();
      if(currentValue == '')
        range.getCell(i,j).setValue(0);
    }
  }
}

Second, Tools > Script Manager > Run zeroIfBlank.

Upvotes: 1

Related Questions