topic Re: Microsoft 365 Excel Write Excel in Digital Exchange

Microsoft 365 Excel Write Excel

EasyBluePrism — Tue, 14 May 2024 05:49:14 GMT

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.

Re: Microsoft 365 Excel Write Excel

faheemsd — Tue, 14 May 2024 09:52:32 GMT

Dear @EasyBluePrism

Could you please elaborate the steps that what you're doing with the excel VBO's for writing the data into excel?

have you passed the exact inputs for the Write collection action? Or else use write collection as fast.

Re: Microsoft 365 Excel Write Excel

EasyBluePrism — Tue, 14 May 2024 10:50:58 GMT

Dear @faheemsd

The actions looks like this:

Important: I am talking about Microsoft 365 Excel not about the standard Excel VBO!

My page looks as follows:

Re: Microsoft 365 Excel Write Excel

ewilson — Tue, 14 May 2024 14:36:37 GMT

You'll need more permissions than those I believe. Is this Excel being created within your own OneDrive or on a SharePoint site? Are you using an Application Access token or Delegated Access token?

Re: Microsoft 365 Excel Write Excel

EasyBluePrism — Tue, 14 May 2024 14:43:57 GMT

That’s what I suspect too. The question is, what kind of permissions?
It is a Sharepoint Site.
We use Application Access Token.

Re: Microsoft 365 Excel Write Excel

ewilson — Tue, 14 May 2024 16:16:55 GMT

@EasyBluePrism you'll probably want to look at the Sites.* and Files.* permissions.

Cheers,

Eric

Re: Microsoft 365 Excel Write Excel

EasyBluePrism — Wed, 15 May 2024 06:20:07 GMT

@ewilson Thanks, but I don’t understand that. Where do I have to do what? Does this concern the BP Web API Permissions or where exactly do I have to change which Permissions?

Re: Microsoft 365 Excel Write Excel

ewilson — Wed, 15 May 2024 17:25:44 GMT

@EasyBluePrism the necessary permissions are applied via the Azure Portal, now known as the Microsoft Entra Admin Center I believe.

Cheers,

Eric

Re: Microsoft 365 Excel Write Excel

EasyBluePrism — Thu, 16 May 2024 09:48:10 GMT

@ewilson It still does not work yet. I have defined the following permissions:

It is not clear to me which of your depicted permits the robot needs and which not. The problem is, I have to submit an application to IT for every permission. That’s why I should know exactly what permissions I need. For Example openid I suppose will not solve the problem, right?

Re: Microsoft 365 Excel Write Excel

ewilson — Thu, 16 May 2024 14:55:06 GMT

@EasyBluePrism the screenshots I posted are simply an example of where you set the permissions. You'll need to review Microsoft's available permissions to determine the specific set required for your use case. That's why I suggested you review the Files.* and Sites.* permissions sets. If you're going to work with the Graph API, it's important to understand the available permissions which you can find listed here:

https://learn.microsoft.com/en-us/graph/permissions-reference

Cheers,

Eric

Re: Microsoft 365 Excel Write Excel

EasyBluePrism — Fri, 17 May 2024 06:44:42 GMT

@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.

Re: Microsoft 365 Excel Write Excel

ewilson — Fri, 17 May 2024 17:42:08 GMT

@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

Re: Microsoft 365 Excel Write Excel

EasyBluePrism — Mon, 17 Jun 2024 08:38:25 GMT

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.

Re: Microsoft 365 Excel Write Excel

ewilson — Fri, 14 Jun 2024 12:22:18 GMT

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

Re: Microsoft 365 Excel Write Excel

EasyBluePrism — Mon, 17 Jun 2024 07:30:10 GMT

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

Re: Microsoft 365 Excel Write Excel

ewilson — Mon, 17 Jun 2024 13:14:02 GMT

@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

Re: Microsoft 365 Excel Write Excel

EasyBluePrism — Tue, 18 Jun 2024 09:20:06 GMT

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

Re: Microsoft 365 Excel Write Excel

ewilson — Tue, 18 Jun 2024 12:47:11 GMT

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

Cheers,

Eric

Re: Microsoft 365 Excel Write Excel

EasyBluePrism — Tue, 18 Jun 2024 16:12:08 GMT

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

Patrick

Re: Microsoft 365 Excel Write Excel

ewilson — Tue, 18 Jun 2024 20:24:02 GMT

@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