3

I'm making use of the Excel/Office JS API in React. Data is being pushed to Excel Ranges. I'm trying to do the following logic:

  • Check if the range/cell formatting is General
  • If it IS General format, set the format to a specific datetime format
  • If it is NOT General format, don't change the formatting

Seems simple enough, but unfortunately I'm not able to get the existing format from Excel. It keeps on telling me it's General even though it is not. Check below for code. Any ideas?

Excel.run(async function (context) {
        let binding = context.workbook.bindings.getItemOrNullObject(queryId);

        await context.sync();

        if (!binding.isNullObject) {
            let bRange = binding.getRange();
            bRange.load('rowCount');
            bRange.load('columnCount');

            await context.sync();

            let dateRange = null;

            if (transpose) {
                dateRange = bRange.getAbsoluteResizedRange(1, bRange.columnCount);
            } else {
                dateRange = bRange.getAbsoluteResizedRange(bRange.rowCount, 1);
            }

            dateRange.load('numberFormat');
            await context.sync();

            //dateRange.numberFormat[0] is ALWAYS General no matter what the format is
            if (dateRange.numberFormat[0] == 'General') {
               dateRange.numberFormat = 'yyyy-MM-dd HH:mm:ss';
            }
        }

        return context.sync();
    });
ceds
  • 2,097
  • 5
  • 32
  • 50
  • I am not able to repro this issue with your code, (i cannot run your code directly with my script lab, so I modified it, after my changes it runs well here), could you please save your code with the script lab? by referencing this article? https://learn.microsoft.com/en-us/office/dev/add-ins/overview/explore-with-script-lab#save-and-share-snippets – Raymond Lu Aug 17 '20 at 10:35

0 Answers0