CODEWITHSUNDEEP
google-apps-scriptgoogle-sheets
stackers
stackers

Reputation: 3270

How can I count the number of cells that are both bolded AND empty?

I have found this function which can count the number of bolded cells:

function countIfBold(inputRng) {
  const rng  = SpreadsheetApp.getActiveSheet().getRange(inputRng).getFontWeights();  
  const flattenedRng = [].concat.apply([], rng)
  return flattenedRng.filter(e => e == 'bold').length;
}

but it unfortunately includes empty cells.

I modified it to count how many cells are not empty:

function countIfNotEmpty(inputRng) {
  const rng  = SpreadsheetApp.getActiveSheet().getRange(inputRng).getValues();  
  const flattenedRng = [].concat.apply([], rng)
  return flattenedRng.filter(e => e.length>0).length;
}

but I'm not sure how to combine them so that it counts only the cells that are bold AND not empty.

any ideas?

Upvotes: 0

Views: 231

Answers (1)

Cooper
Cooper

Reputation: 64040

Bold & Null

Identifies all of the cells in all of the sheets of a spreadsheet that are both bold and empty.

function countBoldAndEmpty() {
  const ss = SpreadsheetApp.getActive();
  const shts = ss.getSheets();
  let beO = { pA: [] };
  shts.forEach((sh, i) => {
    let name = sh.getName();
    let rg = sh.getRange(1, 1, sh.getMaxRows(), sh.getMaxColumns());
    let vs = rg.getValues();
    let bs = rg.getFontWeights();
    vs.forEach((r, i) => {
      r.forEach((c, j) => {
        if (vs[i][j] == '' && bs[i][j] == 'bold') {
          if (!beO.hasOwnProperty(name)) {
            beO[name] = [];
            beO.pA.push(name);
            beO[name].push(sh.getRange(i + 1, j + 1).getA1Notation());
          } else {
            beO[name].push(sh.getRange(i + 1, j + 1).getA1Notation());
          }
        }
      });
    });
  });
  Logger.log(JSON.stringify(beO));
}

Upvotes: 1

Related Questions