topic Re: Microsoft 365 - Excel::Write Collection fail in Digital Exchange

Microsoft 365 - Excel::Write Collection fail

JeremyRTDean — Tue, 17 Sep 2024 05:57:12 GMT

I'm getting mixed results when I try to write the content of a Collection to Excel using the Microsoft 365 - Excel::Write Collection action. Sometimes it works, in particular with simple Collections with limited content.

My current collection has around 20 columns and 200 rows and I get this:

Status Code: 400 Result: {"error":{"code":"BadRequest","message":"Unable to read JSON request payload. Please ensure Content-Type header is set and payload is of valid JSON format.","innerError":{"date":"2024-09-17T05:49:46","request-id":"69f0f961-1aad-4a17-8a80-22fc5dd6xxxx","client-request-id":"69f0f961-1aad-4a17-8a80-22fc5dd6xxxx"}}}

The data isn't complex and doesn't seem to have any special characters. Any clues on what could be causing this?

Re: Microsoft 365 - Excel::Write Collection fail

JeremyRTDean — Sun, 22 Sep 2024 23:28:23 GMT

Does anyone have experience using this object? If I step through the object it is failing at the 'Set Range Value' step with the Status Code 400:

Our 'Utility - JSON' object is the latest v10.0.1.

Re: Microsoft 365 - Excel::Write Collection fail

JeremyRTDean — Mon, 23 Sep 2024 00:48:17 GMT

Seems there is some sort of limit. 16 rows in my Collection and it works fine. 17 rows and it fails. (Its not the data content of row 17 either - I removed a random row to take it back down to 16).

Re: Microsoft 365 - Excel::Write Collection fail

JeremyRTDean — Mon, 23 Sep 2024 03:10:50 GMT

Can I solve my own Question? Problem was double quotes in the data. Initially I didn't think they were a problem because a line near the top of the collection had what I thought were double quotes without error. Turns out they were two single quotes ('').

Conclusion: The Microsoft 365 - Excel::Write Collection action cannot handle double quotes in the Collection data.