Error "The argument is invalid or missing or has an incorrect format." in Worksheet.getRanges

[matthiasgemmel]

Provide required information needed to triage your issue

Workshhet.getRanges returns error "The argument is invalid or missing or has an incorrect format." for a range address with multiple areas. But the limit of getRanges is unclear. With some addresses more than 500 areas work, with some not.

Your Environment

• Platform [PC desktop, Mac, iOS, Office on the web]: PC desktop, Office on the web
• Host [Excel, Word, PowerPoint, etc.]: Excel
• Office version number: Microsoft® Excel® for Microsoft 365 MSO (Version 2402 Build 16.0.17328.20648) 64-bit
• Operating System: Windows 11 23H2
• Browser (if using Office on the web): Chrome Version 131.0.6778.86

Expected behavior

An explanation when to split areas into multiple ranges.

Current behavior

Worksheet.getRanges returns an error without knowing what caused the error. The range address string is valid. The sample yaml contains one range with 513 areas that fails and another one with 513 that works. Thus area count seems not to be the issue. The failing range has total address string length of 8207. But a third example has 649 areas with total address string length 9085 which works for me in Excel Desktop (just tested this one fails as well with the same error in Excel Online).

Steps to reproduce

1. Open attached yaml in script lab
2. Click button "Try coloring 513 areas"

Link to live example(s)

yaml attached to thus bug report
Error_in_Worksheet.getRanges.zip

[RuizhiSunMS]

Hi @matthiasgemmel, thx for reaching out here. I did a try and was able to repro. Mark it as #9628123 into our backlog. I will involve experts to investigate. Will reply here if any update.

[matthiasgemmel]

Thanks for letting me know you involved the experts. Happy to hear from them soon.

@RuizhiSunMS , I hope this was sufficient to not auto close this bug while waiting for the Microsoft experts to comment on this bug.

[RuizhiSunMS]

Thanks for letting me know you involved the experts. Happy to hear from them soon.

@RuizhiSunMS , I hope this was sufficient to not auto close this bug while waiting for the Microsoft experts to comment on this bug.

don't worry, no case would get closed automatically except it is tagged as 'need author feedback' for a long time.

[SiruiSun-MSFT]

Hi @matthiasgemmel,

Based on our investigation, there indeed exists a limitation on 8192 characters for the address string in this API you used。 And it shares the same limitation with formula limitation of Excel Application.
If your input has over the limitation, sometimes it will hit some cutoff issue and trigger a invalid input error.
Thanks for catching this issue! We will update the detailed info about this limitation to our public document later!

Thanks again!

[matthiasgemmel]

Hi @SiruiSun-MSFT,

Thanks for your answer and sharing the magic number 8192. Address strings with a length greater than this might work but it is not guaranteed. But address strings with a length lower than 8192 will always work. This is the important part for me. I will change the algorithm to cut strings into multiple ones if their address length exceeds 8192 characters. The const working_649Areas_stringLength9085 in my sample would then be split into two although it would work as one. But that is fine for me,

I tested the change of the algorithm with my test workbook I used to extract the sample shared here. All cases work as expected without an error. Thus I close this issue as completed.

Best regards
Matthias