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