Can't Use Entire Column Range with Excel JavaScript API

Question

I've been working with entire Col rng's and noticed some inconsistencies.

For instance, my Excel Spreadsheet has a Col Rng of A1:A1048576, but the following code fails.

var ws = context.workbook.worksheets.getActiveWorksheet();
var range = ws.getRange("A1:A1048576");
range.values = "A";

But, if I use the rng as "A1:A1048575" (minus one), it works. Am I doing something wrong? The RNG doesn't appear to be 0 indexed as A1 selects the correct cell. I suspect this might be a bug, but wanted to confirm.

Here is the error FYI:

InvalidOperation: This operation is not permitted for the current object.
   {
      [functions]: ,
      __proto__: {
         [functions]: ,
         __proto__: {
            [functions]: ,
            __proto__: {
               [functions]: ,
               __proto__: null
            },
            message: "",
            name: "Error",
            Symbol()_7.rurcc9qe7b1: undefined,
            Symbol(nodejs.util.inspect.custom)_j.rurcc9qe7hi: undefined
         },
         message: "",
         name: "Error",
         Symbol()_7.rurcc9qe7b1: undefined,
         Symbol(nodejs.util.inspect.custom)_j.rurcc9qe7hi: undefined
      },
      code: "InvalidOperation",
      data: undefined,
      debugInfo: {
         [functions]: ,
         __proto__: { },
         code: "InvalidOperation",
         errorLocation: "Range.values",
         fullStatements: [
            0: "Please enable config.extendedErrorLogging to see full statements.",
            length: 1
         ],
         message: "This operation is not permitted for the current object.",
         statement: "range.values = ...;",
         surroundingStatements: [
            0: "var workbook = context.workbook;",
            1: "var worksheets = workbook.worksheets;",
            2: "var activeWorksheet = worksheets.getActiveWorksheet();",
            3: "var range = activeWorksheet.getRange(...);",
            4: "// Instantiate {range}",
            5: "// >>>>>",
            6: "range.values = ...;",
            7: "// <<<<<",
            length: 8
         ],
         Symbol()_7.rurcc9qe7b1: undefined,
         Symbol(nodejs.util.inspect.custom)_j.rurcc9qe7hi: undefined
      },
      description: "This operation is not permitted for the current object.",
      httpStatusCode: 400,
      innerError: null,
      message: "This operation is not permitted for the current object.",
      name: "RichApi.Error",
      stack: "InvalidOperation: This operation is not permitted for the current object.
   at Anonymous function (https://appsforoffice.microsoft.com/lib/beta/hosted/excel-win32-16.01.js:26:305431)
   at Anonymous function (http://localhost:3000/yo/dist/polyfill.js:1:76119)
   at e (http://localhost:3000/yo/dist/polyfill.js:1:31843)",
      Symbol()_7.rurcc9qe7b1: undefined,
      Symbol(nodejs.util.inspect.custom)_j.rurcc9qe7hi: undefined,
      traceMessages: [
         length: 0
      ]
   }

score 1 · Answer 1 · answered Dec 23 '22 at 07:26

1

Let me give a detailed answer.

Normally, we use range.values should like this:

range.values=[["A"]] the value is a 2-d array. As shown in the api doc: https://learn.microsoft.com/en-us/javascript/api/excel/excel.range?view=excel-js-preview#excel-excel-range-values-member

I tested your way and it did runs. So I would suppose we also support this kind of input.

And for your issue, yes I can repro it. And I have a workaround now.

var ws = context.workbook.worksheets.getActiveWorksheet();
var range = ws.getRange("A1:A1048575");
range.values = "A";
var range2 = ws.getRange("A1048576");
range2.values = [["A"]];
await context.sync();

First it will set the value for A1:A1048575, and set value for A1048576 separately.

Internal bug #7377474 created to track it (which is true).

And our public repo: https://github.com/OfficeDev/office-js. You can put your future issue here also. And my github profile: https://github.com/yuc014 for the prove.

Thanks for your report. :)

answered Dec 23 '22 at 07:26

yuc014

36
3

Update info: After confirmed with engineers who own this feature, cannot use entire column in range is by design. We do not want a user to be able to initialize a full column or range for setting (and getting) of values. Yes developers can work around it by doing a full column -1 and then setting the last row separately if they tried to be smart, but we try to prevent it being outright easy was put in specifically to make it hard so developers think hard about WHY they would do this. It's can be a very costly time operation that we want to prevent. – yuc014 Mar 23 '23 at 02:56
What a joke. This is the most lame excuse I've heard ever for not fixing a problem. – FreeSoftwareServers Mar 30 '23 at 22:35
That is what DOCUMENTATION is for, suggestions, you don't code road blocks for devs. And.... Is this documented anywhere but here, like say, official documentation? – FreeSoftwareServers Mar 30 '23 at 22:44

Can't Use Entire Column Range with Excel JavaScript API

1 Answers1

Linked