Microsoft 365 - Excel

[ArunkumarManoha]

Hello,

We are trying to automate M365 excel and for that I have imported M365 Excel asset from DX. I have no idea on how to get the Workbook ID, Worksheet ID, File ItemID, FolderID to work with M365 Excel actions like Open Workbook, Get Worksheet as Collection, etc. So can someone please guide me on how to get those parameters using Microsoft Graph - Sharepoint? 

------------------------------
Arunkumar Manoharan
------------------------------

[ewilson]

@Arunkumar Manoharan

You can get the Workbook ID in various ways:

• You can use the SharePoint connector or OneDrive connector to get a list of the files in the specific OneDrive. Find the workbook you're interested in and the ID will be part of its metadata returned from Graph.
• Alternatively, you can open the workbook directly in your browser and see the workbooks ID in the URL of the browser.

Same general idea applies to File ID and Folder ID. Once you have to workbook ID and you open the workbook in Graph, there are actions on the Excel connector that let you get info (i.e. metadata) about the worksheets in the workbook.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[ArunkumarManoha]

Hi Eric,

As a first step for M365 excel automation, I have tried to authenticate using Get Auth Token-Client Secret under MSAL.Net VBO but am getting the below error. Can you please help me?

Error: Internal : Could not execute code stage because exception thrown by code stage: One or more errors occurred.

I don't see any errors in the code level and it says 0 errors.

Regards,

Arun

------------------------------
Arunkumar Manoharan
------------------------------

[ewilson]

@Arunkumar Manoharan

We've just pushed an update to the MSAL.NET VBO (version 2.2.0) up to the DX. This version includes the addition of an aggregate exception handler in each action. This should result in the multiple exceptions being combined into the Exception output string so you can see what the messages are.

Cheers, 

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[ArunkumarManoha]

Thanks Eric. I have imported the latest MSAL.Net VBO 2.2.0 and resolved the issue. But while trying to use open workbook, getting the below error.

Error: {"error":{"code":"InvalidAuthenticationToken","message":"Access token is empty.","innerError":{"date":"2023-03-30T19:36:48","request-id":"9c48a9b7-4e81-4e9b-9399-5a6fcc8efc7e","client-request-id":"9c48a9b7-4e81-4e9b-9399-5a6fcc8efc7e"}}}

Access Token is not empty and it didn't expire but still getting the above error in Create Session stage. Also we are not passing the Access Token in this create session stage.

Regards,

Arun

------------------------------
Arunkumar Manoharan
------------------------------

[ewilson]

@Arunkumar Manoharan,

This is usually a sign that you're using an older version of the Utility - HTTP VBO. We introduced an update to that VBO a little over a year ago where we added support for Bearer...

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[ArunkumarManoha]

Hi Eric,

I have imported the latest Utility - HTTP VBO and ran open workbook action but got below error message in create session stage.
Error: The remote server returned an error: (400) Bad Request.
I have passed site ID, sites and workbook ID as an input parameters for open workbook. 


Basically, My requirement is to extract the contents/values from the SharePoint Excel and do some excel manipulation and again update the values to the same SharePoint Excel. Below are the sequence of steps that have followed. Please correct me if I am doing incorrectly.
1. Authenticate
2. Open Workbook
3. Get Worksheet as Collection
4.Write Collection
File URL - https://shiftup.sharepoint.com/:x:/r/sites/RPA_NA_COE/_layouts/15/Doc.aspx?sourcedoc=%7B9F057DA2-0745-4376-B100-C8B0911D9349%7D&file=WDSS_Bot_Input(1).xlsx&action=default&mobileredirect=true&cid=1bce6de8-e216-4a0e-b7c9-5f816e2bfda0
As you said, I have taken the Workbook ID from the above URL and marked it as Bold for your reference. so want to know whether this is correct or not.
Also It would be helpful if you provide the exact action to get Workbook ID, Worksheet ID, File ItemID because its very difficult to find the right action to get the right metadata. 

------------------------------
Arunkumar Manoharan
------------------------------

[ewilson]

@Arunkumar Manoharan 

First thing I notice is that your Workbook ID is incorrect. The %7B and %7D portions are actually HTML encoded special characters of { and } respectively. They are not part of the actual ID. So, your Workbook ID value should actually be 9F057DA2-0745-4376-B100-C8B0911D9349 (surrounded by double quotes of course). Try changing that and see what happens.

Cheers,

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------

[ArunkumarManoha]

Hi Eric,

As per suggestion, I have changed the workbook ID and ran the create session stage but getting the below error message.
Error: The remote server returned an error: (404) Not Found.

------------------------------
Arunkumar Manoharan
------------------------------

[ewilson]

Hi @Arunkumar Manoharan 

Ok, so Graph doesn't like something about the ID's you've provided, either the site ID or the workbook ID. Are you sure both of those IDs are correct? Have you ever used Microsoft's Graph Explorer utility? If not, you might want to give it a look. It's an excellent utility for troubleshooting issues with Microsoft Graph API calls. You can find it here:

https://developer.microsoft.com/en-us/graph/graph-explorer

Cheers, 

------------------------------
Eric Wilson
Director, Integrations and Enablement
Blue Prism Digital Exchange
------------------------------