I want to change zeros to blanks in sheets using apps script-CodePudding

I'm new to using apps script and I'm just trying this out. I want to convert zeros to blanks.

This is the code I've managed to create, but it doesn't work and I don't know how to fix it.

    function zero() {
      var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet()
      var selectedRange = ss.getActiveRange()
      var selectedValues = selectedRange.getValues()
      var selectedColumns = selectedRange.getWidth()
      var selectedRows = selectedRange.getHeight()
    
        for(i = 0; i<selectedRows; i  ) {
        for(j = 0; j<selectedColumns; j  ) {
          if (selectedValues[i][j] = 0) {
            selectedRange.getCell(i   1, j   1).setValue(" ")
          }
        }
      }
    }

function onOpen() {
  SpreadsheetApp.getUi()
      .createMenu('Blank')
      .addItem('Change zero to blank', 'zero')
      .addToUi();
}

CodePudding user response：

When I saw your script, I think that your if statement of if (selectedValues[i][j] = 0) {} is required to be modified. If the cell value is the number of 0, please use === instead of ==. So, when your script is modified, please modify as follows.

Modified script:

function zero() {
  var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var selectedRange = ss.getActiveRange();
  var selectedValues = selectedRange.getValues();
  var selectedColumns = selectedRange.getWidth();
  var selectedRows = selectedRange.getHeight();
  for (i = 0; i < selectedRows; i  ) {
    for (j = 0; j < selectedColumns; j  ) {
      if (selectedValues[i][j] === 0) {
        selectedRange.getCell(i   1, j   1).setValue(" ");
      }
    }
  }
}

If you want to change the number of 0 to blank, you can also use selectedRange.getCell(i 1, j 1).setValue(null); instead of selectedRange.getCell(i 1, j 1).setValue(" ");.

I think that when setValue is used in a loop, the process cost will be high. In your situation, the following script can be used.

  function zero() {
    var ss = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
    var selectedRange = ss.getActiveRange();
    selectedRange.createTextFinder("0").matchEntireCell(true).replaceAllWith(" "); // or .replaceAllWith("");
  }

Modified script:

References: