Converting Range to Array of Cell Addresses Office Script

Question

I'm trying to work out how I can convert a selected range in to a list of selected cells. So far, I have the following code:

  workbook.getWorksheet("Sheet1").activate();
  var range = workbook.getSelectedRanges().getAddress();
  console.log(range);

However, I'm unsure how to convert this in to a list of cell addresses. Ex [A1, A2, A3, B1, B2] I found this code, however it only works with Office.js, not OfficeScript. Thanks.

Your question is not clear at all -- you want the user to select a range of cells and then output the value of those cells? See [this answer](https://stackoverflow.com/a/47543778/4717755) as a possible solution. — PeterT, Jul 25 '23 at 00:16
My apologies, I have edited the post. I hope it's more clear now? — Simon Joubert, Jul 25 '23 at 00:23

taller_ExcelHome · Answer 1 · 2023-07-25T20:19:44.870

Option 1: Get cell reference

function main(workbook: ExcelScript.Workbook) {
    // workbook.getWorksheet("Sheet1").activate();
    let selectedSheet = workbook.getActiveWorksheet()
    var range = workbook.getSelectedRanges();
    var selectAreas = range.getAreas();
    let cellList: string[] = []
    selectAreas.forEach(areaRange => {
        let colCnt = areaRange.getColumnCount();
        let rowCnt = areaRange.getRowCount();
        for (let r = 0; r < rowCnt; r++) {
            let rowRange = areaRange.getRow(r);
            for (let c = 0; c < colCnt; c++) {
                let cellRange = rowRange.getCell(0, c);
                // get reference
                cellList.push(cellRange.getAddress().split("!")[1]);
                // get cell value
                // cellList.push(cellRange.getText());
            }
        }
    });
    console.log(cellList);
}

Option 2: Get cell Value

The order to retrieve it: selection (Range) > Area > 2D Array > Item(cell). Of course, you can get same result with Option 1.

function main(workbook: ExcelScript.Workbook) {
    // workbook.getWorksheet("Sheet1").activate();
    let selectedSheet = workbook.getActiveWorksheet()
    let selectAreas = workbook.getSelectedRanges().getAreas();
    let cellList:string [] = [];
    selectAreas.forEach(areaRange => {
        areaRange.getTexts().forEach(textRow => {
            textRow.forEach(textCell => {
                cellList.push(textCell);
            });
        });
    });
    console.log(cellList);
}

Converting Range to Array of Cell Addresses Office Script

1 Answers1