How would I get the name of a blank sheet in Google Apps Script?

Question

I'm trying to make a Google Apps Script that applies a filter function to each sheet in my spreadsheet except for certain sheets. I'd like it to ignore particular named sheets, as well as a "blankSheet" variable. I'd like "blankSheet" to consist of any sheets that have no data in a particular range of cells (A2:H).

I tried the following, but the resulting filter function in my spreadsheet still includes an empty sheet. Thanks for the help!

function myFunction() {

const ss = SpreadsheetApp.getActiveSpreadsheet();
const shts = ss.getSheets().filter(sh => sh.getDataRange().isBlank()).map(s => s.getName()).join(',');

//set variables
const masterSheet = "master";
const masterSheetFormulaCell = "A2";
const ignoreSheets = ["notifications","main",shts];
const dataRange = "A2:I";
const checkRangeA = "A2:A";
const checkRangeB = "B2:B";
const checkRangeC = "C2:C";
const checkRangeD = "D2:D";
const checkRangeE = "E2:E";
const checkRangeF = "F2:F";
const checkRangeG = "G2:G";
const checkRangeH = "H2:H";
const checkRangeI = "I2:I";
//end set variables

ignoreSheets.push(masterSheet);

const allSheets = ss.getSheets();

const filteredListOfSheets = allSheets.filter(s => ignoreSheets.indexOf(s.getSheetName()) == -1);

let formulaArray = filteredListOfSheets.map(s => `FILTER({'${s.getSheetName()}'!${dataRange},"${s.getSheetName()}" &LEFT(ROW('${s.getSheetName()}'!${checkRangeA}),0),ROW('${s.getSheetName()}'!${checkRangeA})},'${s.getSheetName()}'!${checkRangeB}&'${s.getSheetName()}'!${checkRangeC}&'${s.getSheetName()}'!${checkRangeD}&'${s.getSheetName()}'!${checkRangeE}&'${s.getSheetName()}'!${checkRangeF}&'${s.getSheetName()}'!${checkRangeG}&'${s.getSheetName()}'!${checkRangeH}&'${s.getSheetName()}'!${checkRangeI}<>"")`);

let formulaText = "=SORT({" + formulaArray.join(";") + "},10,True,11,True)";


ss.getSheetByName(masterSheet).getRange(masterSheetFormulaCell).setFormula(formulaText);



}

Cooper Cooper · Accepted Answer · 2021-08-27T20:28:24

This type of operation might be useful for finding blank sheets:

function blankSheets() {
  const ss = SpreadsheetApp.getActive();
  let shts = ss.getSheets().filter(sh => sh.getDataRange().isBlank()).map(s => s.getName()).join(',');
  Logger.log(shts);
  return shts;//returns an array of blank sheet names
}

isBlank()

You said you wanted to assign the array of blanks sheet names to a single const so how about this:

const shts = ss.getSheets().filter(sh => sh.getDataRange().isBlank()).map(s => s.getName()).join(',');

The filter function returns only the blanks sheets and the map section returns only the blank sheet names and the join section return all of the names separated by commas.

How would I get the name of a blank sheet in Google Apps Script?

1 Answers