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;

}

Skin · Accepted Answer · 2024-02-07 09:41:13Z

0

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.

answered Feb 7, 2024 at 9:41

Skin

11.7k3 gold badges16 silver badges37 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

taller · Accepted Answer · 2024-02-07 19:44:32Z

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.`)    
}

Collectives™ on Stack Overflow

Office Script count empty cells in a column

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related