topic Re: Microsoft Graph - Excel in Digital Exchange

Microsoft Graph - Excel

JeremyRTDean — Wed, 28 Aug 2024 02:38:14 GMT

We are transitioning to use SharePoint as the file location for our bots to use. I've managed to navigate folder structures in SharePoint using a bot process and identify/download files OK using the Microsoft Graph - SharePoint VBO.

I'd like to perform some simple actions using the Microsoft Graph - Excel VBO from the DX. Initially just get a range of cells into a Collection. I have the file ItemID.

But I don't understand the Inputs and there are no descriptions. How do you drive this thing? For example, what should the inputs be for this?:

Re: Microsoft Graph - Excel

ewilson — Thu, 29 Aug 2024 14:00:08 GMT

@JeremyRTDean you're using the old Web API Service definition object. I don't recommend it. Instead, I'd switch to the Microsoft 365 - Excel VBO. It's newer and the one that will be maintained going forward. It also has better documentation and descriptions.

Cheers,
Eric

Re: Microsoft Graph - Excel

JeremyRTDean — Fri, 30 Aug 2024 01:33:14 GMT

Thanks Eric, I downloaded the Microsoft 365 - Excel VBO and it looks much better. I can Authenticate fine.

However, I still can't see how to open a workbook. In particular I don't know what 'Workbook ID' is. I have a file ID from Microsoft Graph - SharePoint, Get Drive Items but it doesn't seem to be the same as Workbook ID

Re: Microsoft Graph - Excel

JeremyRTDean — Fri, 30 Aug 2024 02:51:30 GMT

Scrub that, Workbook ID is just File ID. However I can't find Worksheet ID.

Get Worksheet Name doesn't return the name or the ID, just 'Position' and 'Sheet Exists'.

Also note that 'Worksheet Exists' doesn't work if the worksheet name has a space in it.

Re: Microsoft Graph - Excel

JeremyRTDean — Fri, 30 Aug 2024 04:21:10 GMT

OK, scrub that too. The ID or name of the specific worksheet. The worksheet name works fine, except where it has a space, as above.

We're good to go:

Re: Microsoft Graph - Excel

ewilson — Fri, 30 Aug 2024 16:06:30 GMT

@JeremyRTDean can you show an example of the sheet name not working for you when you have a space? I just tested that feature and it worked fine for me. In my case, I created a test workbook on a SharePoint drive and added a sheet names "Months Numbers". Only data in the sheet is a column of months and another column of their corresponding month numbers. When I run the process, the data is returned in a Collection as expected.

Cheers,
Eric

Re: Microsoft Graph - Excel

JeremyRTDean — Fri, 06 Sep 2024 05:00:44 GMT

Apologies for the delay, I've been on Jury service this week. I thought I tested it pretty carefully before I submitted that but will try again next week. Your examples look promising.

Re: Microsoft Graph - Excel

JeremyRTDean — Fri, 06 Sep 2024 05:04:26 GMT

Further to the Microsoft Graph - Excel topic. I am wondering why there in no 'Create New Workbook' option. And also why there is no 'Save' or 'Save As' or Close (Save - T/F) options?

Re: Microsoft Graph - Excel

ewilson — Fri, 06 Sep 2024 14:48:01 GMT

@JeremyRTDean as silly as it may seem, the Graph API doesn't actually include an endpoint for creating a new Workbook. As Microsoft state in their documentation, the Graph API (v1.0) only supports reading and modifying existing Workbooks stored on OneDrive and SharePoint locations.

Having said that, I do believe there is a way around this. You could use the OpenXML VBO for Excel, from the DX, to create a new empty Workbook, and then use the SharePoint Upload session VBO to upload that empty Workbook to a SharePoint drive. From there, you can use the Excel VBO to create a session against that Workbook.

Regarding saving, when you create the session against the Workbook there's an input parameter that controls whether changes to the Workbook are persisted or not.

Cheers,
Eric

Re: Microsoft Graph - Excel

JeremyRTDean — Fri, 13 Sep 2024 04:59:05 GMT

In the end I was missing a couple of dlls for that OpenXML so I used the usual MS Excel VBO. A lot of palaver to create a fresh Excel workbook in SharePoint but once it works, it works beautifully. Thanks!

Re: Microsoft Graph - Excel

JeremyRTDean — Sun, 15 Sep 2024 21:11:26 GMT

On the 'Check if Worksheet Exists' issue where the worksheet name has a space in it, I've tested this a few times now and it consistently fails to find the worksheet if the name has a space in it. Here is the Worksheet Exists action in the Microsoft 365 - Excel

© 2022 Blue Prism Limited
Licensed under the Blue Prism Asset License and Support Terms
https://digitalexchange.blueprism.com/fileMedia/download/a9111324-3192-43ff-9166-566620ca1182

Re: Microsoft Graph - Excel

ewilson — Sun, 15 Sep 2024 21:33:31 GMT

Hi @JeremyRTDean,

Yes, turns out there is a bug. It seems the action is URL encoding the value of the worksheet name (i.e. replacing the space with a %20 and writing it back to the local variable in the action. The problem is that it then passes that value into the Code stage that does a comparison of retrieved worksheet names, and as you can imagine the worksheet name returned from Graph includes the actual space instead of a URL encoded value.

We'll publish a fix for it tomorrow.

Cheers,
Eric

Re: Microsoft Graph - Excel

JeremyRTDean — Sun, 15 Sep 2024 21:44:20 GMT

That was impressively quick! Thanks.

Re: Microsoft Graph - Excel

ewilson — Mon, 16 Sep 2024 15:42:44 GMT

@JeremyRTDean version 1.5.1 of the VBO has been posted to the DX. You can access it here: https://digitalexchange.blueprism.com/dx/entry/3439/solution/microsoft-365---excel

Cheers,
Eric

Re: Microsoft Graph - Excel

Neel1 — Fri, 18 Apr 2025 15:51:43 GMT

hello @ewilson - i compared the V 1.5 with 1.5.1 - i noticed only one change that new Output has been created and this variable is not used further in code

Where exactly is the issue fixed in 1.5.1.