Build array with not contiguous cells using Office Scripts

Question

The following helper function gets a single cell range as first parameter, and an integer as the second:

function identacaoGrupos(rng: object, numeroGrupos:number) {
  let arr =[rng];

  for(let i = 0; i<numeroGrupos; i++){
    arr.push(arr[i].getExtendedRange(ExcelScript.KeyboardDirection.down).getLastCell());
  };
}

I need to start on a given cell (eg: B3) and get an array with the cells below it which are not empty.

For example:

Calling this function with the B3 cell as the 1st argument, and 4 as the second, should return the array arr with the cells B3, B7, B11 and B20.

But it is returning the error: "Property getExtendedRange does not exist on type 'object'"

Any help?

Answer 1

• Change the parameter rng data type to Range
• identacaoGrupos return an array of Range object

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let startCell: ExcelScript.Range = selectedSheet.getRange("B2");
    let numeroGrupos: number = 4;
    let dataCell = identacaoGrupos(startCell, 4);
    dataCell.forEach(cell => {
        console.log(cell.getAddress());
    });
}

function identacaoGrupos(rng: ExcelScript.Range, numeroGrupos: number): ExcelScript.Range[] {
    let arrRange: ExcelScript.Range[] = [];
    // get next non-blank cell if rng is blank
    if (!rng.getText()) {
        rng = rng.getRangeEdge(ExcelScript.KeyboardDirection.down)
    }
    for (let i = 0; i < numeroGrupos; i++) {
        // collect non-blank cell
        if (rng.getText()) { arrRange.push(rng) };
        // locate next non-blank cell
        rng = rng.getRangeEdge(ExcelScript.KeyboardDirection.down);
    };
    return arrRange;
}