javascript excel office-js office-addins excel-web-addins

Access each cell's specific properties in a RangeAreas object in OfficeJS

I am converting my VBA Excel add-in to OfficeJS, and one function takes each cell in a range and: if the cell is hardcoded, turns the font blue if the cell has a formulas, turns the font black if the cell links to another sheet, turns the font green if the cell links to another file, turns the font red if the cell links to an external datasource, turns the font dark red.

In VBA, I can iterate over the cells in my selection and identify the cell's formula/value, then in the same iteration change the font color accordingly (VBA code below). As far as I understand, in OfficeJS I can load the cell's value/formula into an an array, or an array structured as RangeArea[Range[row[column]]], but I cannot load both the formula and the font into an array.

How can I access the formula in a cell, then change the cell font accordingly in OfficeJS?

Sub AutoColorCells()
  Dim cell As Range
  For Each cell In Selection
    If cell.HasFormula Then
      Dim letters As String
      Dim formula As String
      Dim cellRef As Boolean
      Dim i As Integer
      cellRef = False
      letters = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"
      formula = cell.formula
      For i = 1 To Len(formula)
        If InStr(letters, Mid(formula, i, 1)) > 0 Then
          cellRef = True
        End If
      Next i
      If InStr(formula, "!") Then
        If InStr(formula, "[") Then
          cell.Font.Color = RGB(192, 0, 0)  ' link to file
        Else
          cell.Font.Color = RGB(0, 128, 0) ' link to sheet
        End If
      ElseIf cellRef = True Then
        cell.Font.Color = RGB(0, 0, 0)  ' cell reference formula
      Else
        cell.Font.Color = RGB(0, 0, 255)  ' math formula
      End If
    Else
      cell.Font.Color = RGB(0, 0, 255)  ' Something hardcoded- no "="
    End If
  Next cell
End Sub

I have tried using the .map() method, but that only works on the cell formula, not other properties. I have also tried loading arrays into two variables, one for formula and one empty, then using a for loop to get the RangeAreas.flormulas[range][row][column] address and create a parallel array with the appropriate formulas. The problem with this method is that I cannot change cell fonts with an array- only the entire RangeAreas object.

It might be possible to iterate over each cell in the RangeAreas object, call context.sync() within the iteration, and change the cells like that, but it would be slow. Is there even a way to iterate over individual cells in the RangeAreas object?

Solution

This was actually quite an interesting question!

https://learn.microsoft.com/en-us/office/dev/add-ins/excel/excel-add-ins-ranges-set-get-values#get-formulas-from-a-range-of-cells

A new notes:

You can't load values and formulas as they are somewhat the same

loads its formulas property, and writes it to the console. The formulas property of a range specifies the formulas for cells in the range that contain formulas and the raw values for cells in the range that do not contain formulas.

You can't check if it's a formula via API, you need to use logic, below I check for =, but you may need to improve logic.
I also moved from VBA, note that JS is a whole different beast, mostly learning WebDev is much steeper learning curve. One trick I learned is in VBA, I'd often set a rng and then loop through it. In JS w/ the Excel API, you need to load properties and therefor sync to load for each rng. As context.sync is to be avoided due to causing lag, I've got in the habit of just loading the entire UsedRange and its props and working with that/drilling down to the ranges I need inside of that range via the loop.

Below is an example, I write some basic numbers, sum them, then highlight each cell according to "Is Formula = Light Red" and "Is NOT Formula and Is NOT Blank = Yellow".

This should give you an idea of where to start.

var ws = context.workbook.worksheets.getActiveWorksheet();
ws.getRange("A1").values = 1
ws.getRange("A2").values = 2
ws.getRange("A3").values = "=SUM(A1:A2)"
await context.sync()

var Used_Rng_And_Props = ws.getUsedRange(true)
var Opt_LoadItemsArr = ["rowCount", "columnCount", "columnIndex", "rowIndex", "address", "rowHidden", "columnHidden", "hidden", "formulas"] //"values" Don't use Values as it can't be used w/ Formulas
Used_Rng_And_Props.load(Opt_LoadItemsArr);
await context.sync()

for (var ri = 0; ri < Used_Rng_And_Props.rowCount; ++ri) {
    var val = Used_Rng_And_Props.formulas[ri][0]
    console.log('val:')
    console.log(val)
    if (val != "") {
        var cell = Used_Rng_And_Props.getCell(ri, 0)
        if (!isNaN(val)) { //Cell is #
            console.log("Is#")
            cell.format.fill.color = "#ffff00" //Yellow
        } else if (val.includes("=")) {
            console.log('IsFormula')
            cell.format.fill.color = "#ffc4c4" //Light Red
        } else {
            console.log('Is NOT Formula')
            cell.format.fill.color = "#ffff00" //Yellow //Cell is STR But not Formula
        }
    }
}
await context.sync()