Home > Software design >  See if getValues() is all no value
See if getValues() is all no value

Time:03-26

I have a huge range of cells I need to see if they have no value. I know that I could use isBlank, but the cells have formulas in them, so isBlank returns false. Is there a way to check an array of cells for no value quickly?

function mp4fix(){
var sheet = SpreadsheetApp.getActive();
//hide rows automatically
//var c will be a for loop eventually
var c = 80;
var accomm = sheet.getSheetByName("MP4").getRange(c,1,20,1).isBlank();
var academ = sheet.getSheetByName("MP4").getRange(c 22,1,2,1).isBlank();
var behavi = sheet.getSheetByName("MP4").getRange(c 25,1,2,1).isBlank();
if(accomm=="true" && academ=="true" && behavi=="true"){
    sheet.getSheetByName("MP4").hideRows(c-5,31);
  }
}

These cells have reference formulas in them (i.e. ='MP3'!A80), so the isBlank is coming back false, even though the formula is returning no value.

CodePudding user response:

Try it this way:

function mp4fix() {
  const ss = SpreadsheetApp.getActive();
  const sh = ss.getSheetByName("MP4");
  var c = 80;
  var accomm = sh.getRange(c, 1, 20, 1).isBlank();
  var academ = sh.getRange(c   22, 1, 2, 1).isBlank();
  var behavi = sh.getRange(c   25, 1, 2, 1).isBlank();
  if (accomm && academ && behavi) {
    sh.hideRows(c - 5, 31);
  }
}

"true" and "false" are strings true and false are booleans

  • Related