Microsoft Graph - Excel

[JeremyRTDean]

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?:

[ewilson · Accepted solution]

@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

[JeremyRTDean]

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

[JeremyRTDean]

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.

[JeremyRTDean]

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:

[ewilson]

@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

[JeremyRTDean]

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.

[JeremyRTDean]

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?

[ewilson · Accepted solution]

@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

[JeremyRTDean]

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!