43

I want to input a variable in a cell only if the cell is empty. The if statement, however, does not work. Any advice?

var ss=SpreadsheetApp.getActiveSpreadsheet();
var r=ss.getRange("'odpovědi'!A2:J");

var rws=r.getNumRows();

ax=r.getCell(rws-1, 10).getValue();

if (ax == "") {
  ax = "foo";
  r.getCell(rws-1, 9).setValue(ax);
}
Alan Wells
  • 30,746
  • 15
  • 104
  • 152
orobinec
  • 445
  • 1
  • 4
  • 5
  • 4
    You have column 10 on one line, and 9 on the other, so those are two different cells. A typo. Otherwise it works as intended. –  Jan 15 '17 at 19:34

3 Answers3

51

No need to extact the value to determine if the cell is empty. Google Spreadsheet API already has a method for this: Range - isBlank method

var cell = r.getCell(rws-1, 10);

if (cell.isBlank()) {
    cell.setValue("foo");
}
A. Masson
  • 2,287
  • 3
  • 30
  • 36
  • 2
    does this method `isBlank()` has a negation? Line `cell.isNotBlank()`. – Snail-Horn Oct 01 '20 at 19:13
  • No, so only option available is to use the _negation symbol_ like: `if ( !cell.isBlank() )` – A. Masson Oct 03 '20 at 20:10
  • suppose, from my row range, first five row is not blank but t last three rows are blank, then what to do ? sh.getRange(2, 1, 8, 26).getValues(); ? how to check For blank ? – Noor Hossain Dec 07 '20 at 07:06
11

Try:

function myFunction() {
  var ss=SpreadsheetApp.getActiveSpreadsheet();
  var s=ss.getSheetByName('odpovědi')
  var lr=s.getLastRow()  
  var ax=s.getRange(lr, 10).getValue();
     if(ax == ""){
        ax = "foo";
        s.getRange(lr, 10).setValue(ax);
  }
  }
Ed Nelson
  • 10,015
  • 2
  • 27
  • 29
9

If you already have cell value then use === or !== operators if you want avoid type conversion (where 0 == "" ).

Code snippet for decreasing not empty cell values

var sheet = ss.getSheets()[0];     
var range = sheet.getRange("E2:H5");
var currentValues = range.getValues();  

var newValues = [];
for (var row in currentValues) {
  var newRow = []; 
  for (var col in currentValues[row]) {
    if (currentValues[row][col] !==  "") { // if not empty cell
      newRow[col] = currentValues[row][col] - 1;          
    }
    else {
      newRow[col] = currentValues[row][col];
    }
  }
  newValues[row] = newRow;
}    
range.setValues(newValues);
koxt
  • 683
  • 7
  • 16
  • Upvoting this because it's super-useful to know that empty cells are represented as an empty string `""` in the array returned from `getValues()` – Seth Battis Aug 17 '23 at 14:09