How to pass object or json stringify to custom function in office js?

Question

I am working on an office js add-in now I have created a custom function that works as expected but when I pass JSON stringify string in the custom function it does not work and does not give any error. if I have to pass a simple comment then it working correctly. I follow this solution but had no luck https://learn.microsoft.com/en-us/office/dev/add-ins/excel/custom-functions-parameter-options?tabs=javascript. here is my code Please guide me on what I do wrong. Thank in advanced

await Excel.run(async (context) => {
          const wb = context.workbook;
          const range = wb.getSelectedRange();
    
          const abc={id:1,name:"KC"}

         serialzedFieldMappings=JSON.stringify(abc)
    
          try {
            range.formulas = [[`=ANALYST.LOG(${serialzedFieldMappings})`]];
           
          } catch (error) {
            console.log(error);
          }
       
          return context.sync();
        });   

function.json file

{
        "functions": [
      {
    "description": "Writes a message to console.log().",
                "id": "LOG",
                "name": "LOG",
                "parameters": [
                    {
                        "description": "String to write.",
                        "name": "message",
                        "type": "string"
                    }
                ],
                "result": {}
            }
        ]
    }

function.js

function logMessage(message) {
  console.log("message", message);
  return `Cmarix Example${message}`;
}
CustomFunctions.associate("LOG", logMessage);

Answer 1

Please follow below solution for your problem

 await Excel.run(async (context) => {
      console.log("data");
      const wb = context.workbook;
      const range = wb.getSelectedRange();

      const timeDimension: any = selectedTimeDimensions[0];
      const dimension: any = selectedDimensions[0];
      //Create jsonstringfy
      const serialzedFieldMappings = new CellDimensionMapSerializer().Serialize(
        selectedScenario,
        [timeDimension],
        [dimension]
      );
     
      const abc = { id: 1, name: "KC" }
      let datastring = JSON.stringify(abc).split('"').join("'");

      try {
        range.formulas = [[`=ANALYST.COMMENT("${datastring}")`]];
      } catch (error) {
        console.log(error);
      }
      return context.sync();
    });