How to handle date format in Custom function officejs excel add-in

Question

I have this custom function (shared runtime):

/*
 * @customfunction
 * @param {number} first
 * @param {number} second
 * @param {string} third
 * @returns {number[][]}
 */

  function GETDADOS(first, second, third) {
    var promise = new Promise(function(resolve){
      $.ajax({
        ...
        //HTTP requisition
        ...
        }
      });
    })
    return promise.then(function(result){
      var data = result
      var resp = JSON.parse(data)
      return resp
    })
  })
  };

That provides me this output:

[[44225,1.8541],[44232,1.874],[44239,1.94]]

The first column is a date in the general format of excel. How can I present this date like the culture of each user?

EX: US 35062 -> 12/29/2021, UK 35062 -> 29/12/2021

My try:

I tried to use the "number format" property of "Excel. range class" but the problem is: I can't get just the range of custom function data.

...
return promise.then(function(result){
      var data = result
      var resp = JSON.parse(data)
      return resp
    }).then(function(){
      format();
  })
  };

  async function format() {
    await Excel.run(async (context) => {
      const sheet = context.workbook.worksheets.getActiveWorksheet();
      const range = sheet.getUsedRange();
  
      range.load('rowCount') 
      range.load('columnCount')
  
      await context.sync().then(function (){
        
        var formatoPadrao = ["dd/mm/yy", null];
        var matrizConfig = [];
  
        for(var i = 0; i < range.rowCount; i++){
          matrizConfig.push(formatoPadrao);
        }
        range.numberFormat = matrizConfig;
        console.log(matrizConfig);
  
      });
    });
  }  

Obs: getUsedRange() not working because the sheet has a lot of tables and I need to format just the custom function data.

could you share some code samples or give some direction?

Dev environment: Windows 10, office 365 MSO (16.0.13929.20222) 64 bits, Edge Version 90.0.818.51 Up to date, Vs Code with Yeoman generator, Excel desktop, Shared Runtime.

References:

Finding dirty cells

Getting current region

numberFormat() question

Setting the number format question

The ways to get a range with excel javascript API

MsDate plugin document

Answer 1

Once set dd/mm/yy, then no matter which region or country, the format is defined, cannot change depends on the culture. One way to unblock this is in js judge the culture or country, and then set different format want to show in this region.

Answer 2

To get just the custom function data, you can register onCalculatedEvent, which can return calculated addresses when onCalculatedEvent is fired.

Code snippet looks like:

const sheet = context.workbook.worksheets.getActiveWorksheet();
eventResult = sheet.onCalculated.add(onCalculatedEvent);


async function onCalculatedEvent(eventArgs: Excel.WorksheetCalculatedEventArgs) {
    await Excel.run(async (context) => {
        const calculateType = eventArgs.type;
        const calculateAddress = eventArgs.address;
        console.log(`Calculation is at ${calculateAddress}, type is ${calculateType})`);
    });
 }