How find our zero values in Google Spreadsheet

In this tutorial we will see how to find out zero values in Google Spreadsheet using Conditional Formatting and Google Apps Script. We have already covered the change color based on rules for checking whether the values are empty or greater than, similarly we can use this feature for checking zero values.

Select the data in which you find the zero values. Then click the Format and select Conditional Formatting from the list of available menus.  In the Change color based on rules, select is equal to and set the value as 0, background color for highlighting the value. Click the Save rules button to confirm and save the changes.

This would highlight the zero values in the selected data.

Similarly you can also try the below Google Apps Script for highlighting the zero value.

function myFunction() {
  var aRange = SpreadsheetApp.getActiveRange();
  var totalRows = aRange.getNumRows();
  var totalColumns = aRange.getNumColumns();
  var aSheet = SpreadsheetApp.getActiveSheet();
  rowCount = 1;
  while (rowCount <= totalRows){
    columnCount = 1;
    while (columnCount <= totalColumns){
    var cValue = aSheet.getRange(rowCount, columnCount).getValue();
      if(parseFloat(cValue) == 0){
           aSheet.getRange(rowCount, columnCount).setBackground(“yellow”);
         }
    columnCount++;
    }
    rowCount ++;
  }
}

The script is comparatively slower than conditional formatting but it should do the job. The above script uses parseFloat() for checking zero value because values such as .01, .02 would be treated as zero by parseInt but not parseFloat.

In Category: Technical

Ravi Shankar

A Software developer and blogger who is always looking to provide technical help to the wider community.

Show 0 Comments
No comments yet. Be the first.

Leave a Comment

Get your free copies of the following tech guides by joining the Digital Answers mailing list.