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

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://docs.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);

CodePudding user response：

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();
    });

CodePudding user response：

If you want to let Excel treat a double quote as literal text in a formula, you need to escape it. Try the following code to see if it can help:

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

    const abc = { id: 1, name: "KC" }

    // An additional double quote will escape a double quote.
    let serialzedFieldMappings = JSON.stringify(abc).replace(/"/g, `""`)

    try {
      range.formulas = [[`=("${serialzedFieldMappings}")`]];

    } catch (error) {
      console.log(error);
    }

    return context.sync();
  });

Thanks!

Rundong