typescriptoffice-scripts

Build array with not contiguous cells using Office Scripts


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:

enter image description here

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?


Solution

  • 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;
    }