Office Scripts (Sum Cells By Color)

Question

I'm trying convert one code that it make a sum of cells by color in VBA, but i need to use the same code or action from code in Office Scripts, i dont know how is the structure in this plataform, maybe, can you help me to do it?

the code in VBA is this: code

Function SumByColor(Cellcolor As Range, RangeSum As Range) As Double

Dim cell As Range

For Each cell In RangeSum

If celda.Interior.ColorIndex = Celdacolor.Cells(1, 1).Interior.ColorIndex Then SumByColor = SumByColor+ cell

Next cell

Set cell = Nothing

End Function

So i need to use this code in office scripts

I have no experience with office scripts, but here's how to get a cell color: [LINK](https://learn.microsoft.com/en-us/office/dev/scripts/resources/samples/excel-samples) — Cameron Critchlow, Jan 26 '23 at 20:23
Office scripts does not currently support functions. So you can't write a function that does this. — Brian Gonzalez, Jan 26 '23 at 23:39

score 0 · Accepted Answer · edited Feb 03 '23 at 00:35

0

Here is one way to write your function in OfficeScript and how to call it -

function main(workbook: ExcelScript.Workbook) {
    let sheet = workbook.getActiveWorksheet();
    console.log (sumByColor(sheet.getRange("E41"), workbook.getSelectedRange()))
}

function sumByColor(cellColor:ExcelScript.Range, rangeSum:ExcelScript.Range):number {
    let value = 0;
    let rowCount = rangeSum.getRowCount();
    let columnCount = rangeSum.getColumnCount();
    let colorToCheck = cellColor.getFormat().getFill().getColor();

    // loop through each cell in rangeSum
    for (let row=0; row<rowCount; row++)
        for (let column = 0; column < columnCount; column++)
        {
            if (rangeSum.getCell(row,column).getFormat().getFill().getColor() == colorToCheck)
                value += rangeSum.getCell(row, column).getValue() as number
        }
    return value;
}

edited Feb 03 '23 at 00:35

Brian Gonzalez

1,178
1
3
15

answered Jan 27 '23 at 18:19

Jay Rathi - Microsoft

241
1
6

Hi bro, thanks a lot, but if i want to get the sum of the cell values with a specific color? I mean, Just the cells C5 and C6 have values ($12000 and $20000) respectively, and need that return this value (32000). How i can do it? to get the cell value and sum this value – santiago carvajal Jan 27 '23 at 19:48
Brian's suggestion would make the function work as asked in the question, so I have accepted it. For better performance, values should be read outside the for-loops. – Jay Rathi - Microsoft Feb 03 '23 at 00:38

score 0 · Answer 2 · answered Feb 02 '23 at 16:25

Thanks for your help. My final code is it :

function main(workbook: ExcelScript.Workbook) {
     let sheet = workbook.getActiveWorksheet();
     var cont = 2;
     const celdas = ['B10', 'C10', 'D10', 'E10', 'F10', 'G10', 'H10', 'I10',   'J10', 'K10', 'L10', 'M10'];
    celdas.forEach(celda => {
    let valCel = celda;
    let startingCell = sheet.getRange(valCel);
    var ranguito = "O" + cont.toString();
    let rangeDataValue = sheet.getRange(ranguito).getValue() as string;
    console.log(sumByColor(sheet.getRange("Q3"),      sheet.getRange(rangeDataValue), startingCell))
    cont = cont + 1;

});
}

 function sumByColor(cellColor: ExcelScript.Range, rangeSum:   ExcelScript.Range, writeCell: ExcelScript.Range) {
  let value = 0;
  let rowCount = rangeSum.getRowCount();
  let columnCount = rangeSum.getColumnCount();
let colorToCheck = cellColor.getFormat().getFill().getColor();
// loop through each cell in rangeSum
for (let row = 0; row < rowCount; row++)
    for (let column = 0; column < columnCount; column++) {
        if (rangeSum.getCell(row, column).getFormat().getFill().getColor() == colorToCheck) {
            var total = rangeSum.getCell(row, column).getValue() as string;
            value = value + parseFloat(total);

        }
   }
writeCell.setValue(value);
console.log(value)
}

Office Scripts (Sum Cells By Color)

2 Answers2