CODEWITHSUNDEEP
javascriptgwtgoogle-sheets
Willy Ortiz
Willy Ortiz

Reputation: 1

Google Spreadsheet Script Editor: How to replace empty cells with a default value of 0

I'm having some trouble trying to figure out how to replace an empty cell with a default value of 0. Below is the code i've used, and it works for 1 cell, but it won't populate the rest of the column. I know that i'm missing a for loop - i've tested it with a for loop and a while, however, both did not work for me. Can someone please help me?

function onEdit(event) {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var rng = sheet.getRange(3,4,sheet.getLastRow());
  var data = rng.getValues();

  if(data === "") {
      rng.setValue(0);
  }
}

Upvotes: 0

Views: 3508

Answers (3)

Vishvas Chauhan
Vishvas Chauhan

Reputation: 250

function emptycellfilling() {
  var ss= SpreadsheetApp.getActiveSpreadsheet().getSheetByName('Sheet1');

  for(var i=2; i<ss.getLastRow(); i++){
    var val= ss.getRange(i,2).getValue();//select col num

    if(val==''){ss.getRange(i,2).setValue(0);}//select target col num and value you want to fill inside, here is 0
  }
  
}

leave a comment if you were looking something else

Upvotes: 3

Paul
Paul

Reputation: 897

I amended your code a little bit:

function siftBlanks() {
  var sheet = SpreadsheetApp.getActiveSpreadsheet().getActiveSheet();
  var colToZero = 2;

  var data = sheet.getRange(1,colToZero,sheet.getMaxRows()).getValues();

  var blank = data.map(testBlank); // this returns an array of true or false
  for (var i=0;i<data.length;i++){
    if(blank[i]){
      data[i] = ["0"];
    }
  }
  sheet.getRange(1,colToZero,sheet.getMaxRows()).setValues(data);
}

function testBlank(arg){
  return arg=="";
}

It loads in the whole column, determined by colToZero, returns an array of true or false values dependent upon whether the corresponding cells are empty or not, and then executes a for loop through the elements that zeroes anything that was blank. It then sets the values of the chosen column to the new, amended column.

I am sure there is nicer way to do this, without resorting to iterating through the array, but this is my first attempt at it. I hope it helps, or at least motivates someone else to improve upon it.

Upvotes: 1

JPV
JPV

Reputation: 27262

Try below script..

function fillWithZero() {
var ss = SpreadsheetApp.getActive()
    .getSheetByName('Sheet1');
var range = ss.getRange("A:B")
    .offset(0, 0, ss.getDataRange()
        .getNumRows());
range.setValues(range.getValues()
    .map(function (row) {
        return row.map(function (cell) {
            return !cell ? 0 : cell;
        });
    }));
}

Change sheet name and range to suit and see if that works for you ?

Upvotes: 0

Related Questions