Microsoft 365 Excel Write Excel

[EasyBluePrism]

I have implemented a process where a collection hast to be written to an Excel. As a test I created an Excel with the name TestExcel.xlsx and the table “Tabelle1”.

The Excel can be found, because I can copy it or read data out of it. But writting from a collection to the Excel does not work.

There is no error message. Everything looks fine. But, the collection is not written to Excel. 

I use the actions Authenticate, Open Workbook, Write Collection and Close Workbook.

Could it be a permission problem? Attached you can find the BP API permissions.

Thanks for your help.

[EasyBluePrism]

@ewilson: I had a brief meeting with our Microsoft and Sharepoint experts. They don’t think these permissions are a problem, for the following reasons:

• The permissions I chose (File.ReadWrite.All and Site.ReadWrite.All) are the most extensive permissions available. I can use it to copy, delete files, etc.
• There is no error message in BluePrism. It seems like everything’s going normally. In case of a permission problem, the error message 403 should appear.
• There is no documentation of BluePrism anywhere describing other permissions for this action (writing Excel files). If there were such, the manufacturer (BluePrism) would have to mention them explicitly.

For these reasons, there are only two options:

• This action has a bug and has never worked or has never been used (this also indicates that no one in the community has answered)
• This is another permission problem, but it should describe BluePrism.

Unfortunately, that’s why our project is on Hold right now.

[ewilson]

@EasyBluePrism I believe you mentioned that you're using an Application Access token. If so, that is probably your issue. This is one of the annoying things about Microsoft's Graph API. Different endpoints support different token types. Some support both Application Access and Delegated Access while others support only one or the other. In the case of updating or writing to a worksheet, the Graph API only supports Delegated Access tokens.

The reason you're not seeing an error response is because Microsoft doesn't return an error when using an Application Access token to write data to a worksheet. They simply ignore the request. Blaming Blue Prism, as you have, is entirely misplaced.

As I mentioned before, you need to take some time to get familiar with the Graph API. If you do, you'll be much better prepared to work with it. I'd also recommend checking out Microsoft's Graph Explorer utility which can be used to replicate the exact API calls the Blue Prism connector is making as a way of validation/verification. 

How did I know that a Delegated Access token is needed for writing data to a worksheet via Graph? I checked the Update Worksheet page in the Microsoft Graph API reference. If you open that page and scroll to the Permissions section, you'll see that only Delegated Access tokens are supported.

FWIW, here's a shot of my test process which, using a Delegated Access token, runs as expected and updates the desired worksheet.

Cheers,

Eric

[EasyBluePrism]

Thank you Eric. I have now studied the Graph API and am quite sure that the permissions are set correctly:

Now most actions work, except writing an excel. I created the process just like you did:

There is no error message, but the Excel (Sheet2 (Tabelle2)) remains empty.

What strikes me is that the range remains empty:

The IDs should be correct, because reading from the same Excel (Tabelle1) works.

What could be the reason for this? Unfortunately, I have been at the problem for many hours, but I cannot find the solution.

[ewilson]

The Range remains empty because Microsoft is ignoring the request. I'll play around with mine today and see if I can come up with a reason it's not working for you.

Cheers,

Eric

[EasyBluePrism]

Hi Eric

The range always remains empty, since it has no reference at all, as I noticed:

But with the result (Result, ResultHeaderJsonand ResultCode) everything seems fine:

Result:

{«@odata.context»:»https://graph.microsoft.com/v1.0/$metadata#microsoft.graph.workbookRange","@odata.id":"/sites('%20f39a4340-c40a-43b7-b8c7-9e37c5e3e833')/drive/items('01U2FO52UT2YGX6P7JVNFIUHLEUMIOEVEG')/workbook/worksheets(%27%7B0D45407F-F01A-4FBA-A789-CED7A70818CC%7D%27)/range(address=%27A1:B3%27)","address":"Tabelle2!A1:B3","addressLocal":"Tabelle2!A1:B3","columnCount":2,"cellCount":6,"columnHidden":false,"rowHidden":false,"numberFormat":[["General»,»General»],[«General»,»General»],[«General»,»General»]],»columnIndex»:0,»text»:[[«1»,»ABC»],[«2»,»FGH»],[«3»,»QRT»]],»formulas»:[[1,»ABC»],[2,»FGH»],[3,»QRT»]],»formulasLocal»:[[1,»ABC»],[2,»FGH»],[3,»QRT»]],»formulasR1C1»:[[[1,»ABC»ABC»], 2,»FGH»],[3,»QRT»]],»hidden»:false,»rowCount»:3,»rowIndex»:0,»valueTypes»:[[«Double»,»String»],[«Double»,»String»],[«Double»,»String»],[«Double»,»String»]],»values»:[[1,»ABC»],[2,»FGH»],[3,»QRT»]}

ResultHeaderJson
{«Transfer-Encoding»:»chunked»,»Strict-Transport-Security»:»max-age=31536000»,»request-id»:»61020b81–641a-497e-9a15-dcf30977e750»,»client-request-id»:»61020b81–641a-497e-9a15-dcf30977e750»,»x-ms-ags-diagnostic»:»{\»ServerInfo\»:{\»DataCenter\»Deutschland West Central\»,\»Slice\»:\»E\»,\»Ring\»:\»4\»,\»ScaleUnit\»:\»002\»,\»RoleInstance\»:\»FR3PEPF000005EC\»}»,»OData-Version»:»4,0»,»Cache-Control»:»no-cache»,»Content-Type»:»application / json;odata.metadata=minimal;odata.streaming=true;IEEE754Compatible=false;charset=utf-8», Date»:»Mo, 17 Jun 2024 07:07:58 GMT»}

ResultCode
200

[ewilson]

@EasyBluePrism did you download this VBO from the Digital Exchange? If so, do you recall when? You're missing the stage that sets the Range output.

I'd suggest grabbing the current release off the DX.

Cheers,

Eric

[EasyBluePrism]

Hello Eric
You’re right, I had an old version. I have updated all relevant VBOs. Now the range is written, but the Excel remains empty.

Patrick

[ewilson]

@EasyBluePrism are you viewing the Excel via the browser? If so, have you tried refreshing the page?

Cheers,

Eric

[EasyBluePrism]

No, with the app. But, I also tried it with the browser and refreshing the page.

Patrick

[ewilson]

@EasyBluePrism ok, if you're getting a 200 OK and valid response and header data then everything from the perspective of Blue Prism is correct. If you're not seeing the spreadsheet you have open reflecting the update, I would go back and verify the ID you're using because it sounds like you're either updating the wrong worksheet or you're viewing the wrong worksheet in the app.

How did you get the ID for the specific spreadsheet? Did you query it through the Graph API, or did you get it some other way?

Cheers,

Eric