CODEWITHSUNDEEP
exceloffice-scriptsexcel-onlinems-office-script
Griatsala
Griatsala

Reputation: 3

Output all cell colors from a range in Excel using Office Scripts

Is there a way of extracting the fill colours from a range of cells into an array quickly and without looping through cells? Something like the code shown below. Am I missing something obvious?

let sheet = workbook.getWorksheet('Sheet1')

//For cell values we can use
let values = sheet.getRange('A5:F35')?.getValues();

//but this doesn't work
let colors = sheet.getRange('A5:F35')?.getformat().getFill().getColor();

Upvotes: 0

Views: 525

Answers (2)

Starfield Screensaver
Starfield Screensaver

Reputation: 49

modification of @taller's excellent answer to tally columns instead of rows:

function get_colors(dataRange: ExcelScript.Range) {
    let arrayResult: Object[] = []
    let colCnt = dataRange.getColumnCount()
    for (let colIdx = 0; colIdx < colCnt; colIdx++) {
        let arrayRow: String[] = []
        let col = dataRange.getColumn(colIdx)
        let rowCnt = col.getRowCount()
        for (let rowIdx = 0; rowIdx < rowCnt; rowIdx++) {
            // console.log(row.getColumn(colIdx).getFormat().getFill().getColor())
            arrayRow.push(col.getRow(rowIdx).getFormat().getFill().getColor())
        }
        arrayResult.push(arrayRow)
    }
    return arrayResult
}

Upvotes: 0

taller
taller

Reputation: 18778

No native method from Microsoft gets colors for a range (multi-cells). A custom function makes it convenient.

function main(workbook: ExcelScript.Workbook) {
    let selectedSheet = workbook.getActiveWorksheet();
    let dataRange: ExcelScript.Range = selectedSheet.getRange("A4:B5")
    console.log(get_colors(dataRange))
}

function get_colors(dataRange: ExcelScript.Range) {
    let arrayResult: Object[] = []
    let rowCnt = dataRange.getRowCount()
    for(let rowIdx=0; rowIdx < rowCnt; rowIdx++){
        let arrayRow: String[] = []
        let row = dataRange.getRow(rowIdx)
        let colCnt = row.getColumnCount()
        for(let colIdx=0; colIdx < colCnt; colIdx++){
            // console.log(row.getColumn(colIdx).getFormat().getFill().getColor())
            arrayRow.push(row.getColumn(colIdx).getFormat().getFill().getColor())
        }
        arrayResult.push(arrayRow)
    }
    return arrayResult
}

Upvotes: 1

Related Questions