I am trying to set up a custom format using Office scripts, so that when the number 10 is entered in a cell, it displays the string ten instead. Of course, the method will work for any number/string pair.
This can be done manually and with VBA, but I need Typescript for this one. I got the syntax from the action recorder, but when running the snippet below an error is thrown: The argument is invalid, missing or has an incorrect format.
I would accept a Script Lab solution as well.
function main(workbook: ExcelScript.Workbook) {
let s = workbook.getActiveWorksheet().getRange("g9")
s.setValue("7")
s.setNumberFormatLocal("[=7]\"\"seven\"\";General"); }
Similar to VBA macro recording, the action recorder is not always entirely right. Duplicated double quotation marks (""
) is used in VBA, but it doesn't work with OfficeScripts.
Try
s.selectedSheet.getRange("G9").setNumberFormatLocal('[=7]"seven";General');