Office Script count empty cells in a column

Question

I have a script to count empty rows in a worksheet, but need a script to count empty cells in a column, the script I have is below and the column will always be column S, any ideas?

function main(workbook: ExcelScript.Workbook): number {

  // Get the worksheet named "Sheet1".

  const sheet = workbook.getWorksheet('Data');





  // Get the entire data range.

  const range = sheet.getUsedRange(true);





  // If the used range is empty, end the script.

  if (!range) {

    console.log(`No data on this sheet.`);

    return;

  }





  // Log the address of the used range.

  console.log(`Used range for the worksheet: ${range.getAddress()}`);





  // Look through the values in the range for blank rows.

  const values = range.getValues();

  let emptyRows = 0;

  for (let row of values) {

    let emptyRow = true;





    // Look at every cell in the row for one with a value.

    for (let cell of row) {

      if (cell.toString().length > 0) {

        emptyRow = false

      }

    }





    // If no cell had a value, the row is empty.

    if (emptyRow) {

      emptyRows++;

    }

  }





  // Log the number of empty rows.

  console.log(`Total empty rows: ${emptyRows}`);





  // Return the number of empty rows for use in a Power Automate flow.

  return emptyRows;

}

Answer 1

Give this a go ...

function main(workbook: ExcelScript.Workbook) {
  let activeWorksheet = workbook.getActiveWorksheet();

  let columnS = activeWorksheet.getUsedRange().getColumn(19); 

  let blankCells = columnS.getEntireColumn().getUsedRange().getSpecialCells(ExcelScript.SpecialCellType.blanks);

  blankCells.forEach((cell) => {
    console.log(cell.getAddress());
  });
}

... there are a few ways to skin this cat. Change out what you need for your own implementation. Catching erroneous runtime behavior will be required.

Answer 2

• The return value getSpecialCells might be a non-contiguous range object. You need loop through its areas first.

• There are several source data range (column x) for your reference in the code.

function main(workbook: ExcelScript.Workbook) {
    // Get the active cell and worksheet.
    let selectedCell = workbook.getActiveCell();
    let selectedSheet = workbook.getActiveWorksheet();
    // Col S on active sheet
    // let columnRng = selectedSheet.getRange("S:S");
    // the first col in table1
    // let columnRng = selectedSheet.getTable("table1").getColumn(1).getRange();
    // the first col in used range
    let columnRng = selectedSheet.getUsedRange().getColumn(1);
    let blankCellRng = columnRng.getSpecialCells(ExcelScript.SpecialCellType.blanks);
    let cellCount = 0;
    if(blankCellRng){
        for(let areaRng of blankCellRng.getAreas()){
            cellCount += areaRng.getCellCount();
        }
    }
    console.log(`There are ${cellCount} blank cells.`)    
}