Forum Discussion
Working with Arrays of Ranges
Off topic. Not to forget OfficeScript tried to repeat that particular sample on it
function main(workbook: ExcelScript.Workbook) {
const tables = workbook.getTables()
const target = workbook.getActiveWorksheet().getRange("L5:M5")
const bar = 1000000
let i = 0
target.setValues([["Countries", "Cities > 1m"]])
for (let table of tables) {
target
.getOffsetRange(++i, 0)
.setValues( [[
table.getRange()
.getCell(-1, 0)
.getValue()
.toString(),
table.getRangeBetweenHeaderAndTotal()
.getLastColumn()
.getValues()
.filter(x => x[0] > bar).length
]] )
}
}
It would appear to do the job!
The Excel world is getting more confusing. There always were those for whom the Excel grid was merely a space from which values could be read into VBA for processing and returned for display. Then we were told that was to end, and everything would be OfficeScript. Yet I see less focus on OfficeScript than on Python or PowerQuery M. DAX also appears to be a somewhat niche artform.
I really should broaden my skillset but at the moment it is an interesting challenge to see whether the Excel formula language can do every calculation that can be performed in Turing machines. Python has NumPY and SciPY to support calculation but, for Excel formulas, one has to write one's own library of algorithms. Whether one regards that as service to enhance Excel, or merely a waste of time replicating code that already exists elsewhere, is a matter for debate 🤔