Google Sheets Apps Script - set all blank cells in a column to zero-CodePudding

I am a novice when it comes to macros or Google Sheets Apps Script. I'm trying to write a Google Sheets macro to reformat data periodically exported from a website. So far I've figured out most of it, but I'm getting stuck trying to find all blank cells in certain columns (ignoring the header row) and set them to zero. Here's what I have so far:

function _1() {
  var spreadsheet = SpreadsheetApp.getActive();
  var k = 2;
  var j = spreadsheet.getLastRow();
  var column = 10;
  for (var i = k; i = j; i  ){
    cell = spreadsheet.getRange(i,column);
    if (cell.isBlank()) { 
      cell.setValue(0);
    }
  }
}

This returns the following error:

Exception: The parameters (String,number) don't match the method signature for SpreadsheetApp.Spreadsheet.getRange.

I believe this means the getRange function is expecting an input of A1 style cell location, but I'm not certain. I also don't know how to convert to one. Finally I'm not sure that the script will work once I've fixed this. Any help would be appreciated.

CodePudding user response：

Modification points:

I think that the reason of your error of The parameters (String,number) don't match the method signature for SpreadsheetApp.Spreadsheet.getRange. is due to that you are using Class Spreadsheet with var spreadsheet = SpreadsheetApp.getActive();. Class Spreadsheet has no method of getRange(row, column).
In your for loop, (var i = k; i = j; i ) is required to be modified. When you want to modify, please modify to (var i = k; i <= j; i ).
But, in your script, setValue is used in the loop. In this case, the process cost will be high.

When these points are reflected to the sample script, it becomes as follows.

Sample script:

function _1() {
  var sheet = SpreadsheetApp.getActiveSheet();
  var ranges = sheet
    .getRange("J2:J"   sheet.getLastRow())
    .getDisplayValues()
    .flatMap(([v], i) => v == "" ? ["J"   (i   2)] : []);
  sheet.getRangeList(ranges).setValue(0);
}

In this script, at first, the values are retrieved from the column "J". And, check each cell and retrieve the a1Notations as an array. And then, the value 0 is put to the a1Notations.

If your script is directly modified, it becomes as follows.

  function _1() {
    var spreadsheet = SpreadsheetApp.getActiveSheet();
    var k = 2;
    var j = spreadsheet.getLastRow();
    var column = 10;
    for (var i = k; i <= j; i  ) {
      var cell = spreadsheet.getRange(i, column);
      if (cell.isBlank()) {
        cell.setValue(0);
      }
    }
  }

If you want to use var spreadsheet = SpreadsheetApp.getActive();, you can also use the following modification.

  function _1() {
    var spreadsheet = SpreadsheetApp.getActive();
    var k = 2;
    var j = spreadsheet.getLastRow();
    for (var i = k; i <= j; i  ) {
      cell = spreadsheet.getRange("J"   i);
      if (cell.isBlank()) {
        cell.setValue(0);
      }
    }
  }

References:

CodePudding user response：

function zero() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getActiveSheet();
  const vs = sh.getRange(2, 10, sh.getLastRow() - 1).getValues();
  vs.forEach(e => {
    if (e[0] == '') {
      e[0] = 0;
    }
  });
  sh.getRange(2, 10, vs.length, 1).setValues(vs).setNumberFormat('0.#####');
}