1

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

DeSanterra
  • 81
  • 1
  • 1
  • 8

2 Answers2

0

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.

Rita
  • 72
  • 3
0

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})`);
    });
 }
xiaochun
  • 114
  • 5
  • Xiaochun, thank you for your reply. I was able to get the string address but I can't manipulate this, like var range = sheet.getRange(calculateAddress); / range.select(); – DeSanterra May 10 '21 at 17:08
  • Do you add "await context.sync();" after range.select()? – xiaochun May 11 '21 at 05:27
  • Xiaochun, yes. I tried this: function onCalculatedEvent(event) { return Excel.run(function (context){ return context.sync() .then(function(){ console.log("Address of event: " + event.address); var stringAddress = JSON.stringify(event.address) var range = sheet.getRange(stringAddress); range.numberFormat = [["dd/mm/yy", null], ["dd/mm/yy", null], ["dd/mm/yy", null]]; }) }) } – DeSanterra May 13 '21 at 14:45
  • You may can try to revise codes like followings: 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})`); let range = context.workbook.worksheets.getActiveWorksheet().getRange(calculateAddress); var numberFormat = [["dd/mm/yy", null], ["dd/mm/yy", null], ["dd/mm/yy", null]]; range.numberFormat = numberFormat; await context.sync(); });} – xiaochun May 14 '21 at 01:45
  • DeSanterra, the reason your code does not work is due to the address is not correct. Please use event.address directly as the input parameter for sheet.getRange instead of the one processed by JSON.stringify. – xiaochun May 14 '21 at 06:49