Excel Script: How to get row numbers for all empty cells in column `categories` in table `transactions`

Question

I am trying to make an Office Script to get the row numbers of all empty cells in a specific column. At this point I have this script:

function main(workbook: ExcelScript.Workbook) {

  let transactionsTable = workbook.getTable("table_transactions");

  let emptyCatCells = transactionsTable.getColumnByName("category").getRange().getSpecialCells(ExcelScript.SpecialCellType.blanks)

}

I understand I get a RangeAreas object consisting of one or more areas, but I don't know how to get to the individual rows for the empty cells.

I tried the getAreas() method and managed to get one level closer to the actual cells, but the I got stuck for lack of understanding how to loop through.

Ultimately I'm working on this:

I have a table_transactions with two columns: description and category. For every row that has an empty category I want to lookup from a second table with columns matchWord and matchCategory. I'm looking if the matchWord is found in the description column (in the first table) and then I want to populate the cell in the category (in first table) column with the matchedCategory (from the second table).

With VBA I managed to get this working with this script, but it seems the BlankCategories Range in VBA was iteratable as an array:

Private Sub CommandButton1_Click()
Dim LookupRange As Range
Dim BlankCategories As Range


Set LookupRange = Sheets("Lists").Range("AutoCatReference[DescriptionText]")
Set BlankCategories = ActiveSheet.Range("Bank_Transactions[Category]").SpecialCells(xlCellTypeBlanks)


    If Not BlankCategories Is Nothing Then
    On Error Resume Next

        For Each BlankCategory In BlankCategories
        
            LookupCell = "H" & BlankCategory.Row
            LookupValue = Range(LookupCell).Value

            For Each LookupItem In LookupRange
                
                If InStr(1, UCase(LookupValue), UCase(LookupItem)) <> 0 Then
                    Range("I" & BlankCategory.Row) = LookupItem.Offset(0, 1).Value
                End If
                
                
            Next LookupItem
            

        Next BlankCategory
    
    End If

End Sub

Thank you so much for your attention. It would be so nice to get this working and also in the prcess learn a lot from your knowledge/skills.

Cheers, Paul

Answer 1

Pls try.

function main(workbook: ExcelScript.Workbook) {
    let transactionsTable = workbook.getTable("table_transactions");
    let emptyCatCells = transactionsTable.getColumnByName("category").getRange().getSpecialCells(ExcelScript.SpecialCellType.blanks)
    if (emptyCatCells) {
        // Loop through areas
        for (let i = 0; i < emptyCatCells.getAreas().length; i++) {
            let areaRange = emptyCatCells.getAreas()[i];
            // Loop through cells
            for (let j = 0; j < areaRange.getCellCount(); j++) {
                let cellRange = areaRange.getCell(j, 0)
                // Get the row#
                console.log(cellRange.getRowIndex() + 1);
            }
        }
    } else {
        console.log("No blank cells")
    }
}