topic RE: Microsoft 365 - Excel in Product Forum

Microsoft 365 - Excel

ArunkumarManoha — Thu, 23 Feb 2023 14:41:00 GMT

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

RE: Microsoft 365 - Excel

ewilson — Thu, 23 Feb 2023 17:11:00 GMT

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

RE: Microsoft 365 - Excel

ArunkumarManoha — Thu, 30 Mar 2023 15:12:00 GMT

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

RE: Microsoft 365 - Excel

ewilson — Thu, 30 Mar 2023 17:31:00 GMT

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

RE: Microsoft 365 - Excel

ArunkumarManoha — Thu, 30 Mar 2023 19:43:00 GMT

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

RE: Microsoft 365 - Excel

ewilson — Fri, 31 Mar 2023 15:51:00 GMT

@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 tokens. That capability is needed by the MSAL.NET VBO in order to pass the include the auth token in subsequent requests.

Cheers,

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

RE: Microsoft 365 - Excel

ArunkumarManoha — Tue, 04 Apr 2023 16:15:00 GMT

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

RE: Microsoft 365 - Excel

ewilson — Tue, 04 Apr 2023 16:39:00 GMT

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

RE: Microsoft 365 - Excel

ArunkumarManoha — Tue, 11 Apr 2023 09:23:00 GMT

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

RE: Microsoft 365 - Excel

ewilson — Mon, 17 Apr 2023 13:31:00 GMT

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

RE: Microsoft 365 - Excel

ArunkumarManoha — Wed, 26 Apr 2023 13:13:00 GMT

Hi Eric,

Site ID is correct but not sure about the Workbook ID since we didn't get the ID from any of the VBO action metadata. As per suggestion, we have opened the workbook in browser and taken the ID. Below snapshot is for your reference.

Could you please provide the exact action to get Workbook ID so that will give a try and check for the results.

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

RE: Microsoft 365 - Excel

ewilson — Wed, 26 Apr 2023 15:12:00 GMT

@Arunkumar Manoharan

You'll probably need to use a combination of the Get Default Drive Details and Get Drive Items actions. Get Default Drive Details will return metadata describing the default OneDrive associated with the specific SharePoint site. You'll need to pull the Drive ID out of that information. Then you can use the Get Drive Items action, passing in the Drive ID, to get a list of metadata all of the items on that specific drive. Note, if the file is located in a subfolder you may need to call Get Drive Items multiple times to drill into the folders to find the specific file and get its metadata. Once you've found the file, it's File ID is contained in its metadata.

Cheers,

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

RE: Microsoft 365 - Excel

ArunkumarManoha — Wed, 26 Apr 2023 18:28:00 GMT

Hi Eric,

Is there any specific action to get the Folder ID apart from the Get Root Folder ID action because I need to pass the specific folder id in the Get Drive Items action to get the Drive ID. Also please let me know which action we need to use to get the Worksheet ID.

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

RE: Microsoft 365 - Excel

ewilson — Thu, 27 Apr 2023 13:46:00 GMT

@Arunkumar Manoharan

You can start with the root folder and then work your way through recursively. Call Get Drive Items on the root folder and the return data will include folder and files. Then you pick out each folder ID from that meta data and work through it.

Microsoft Graph does support the ability to use OData queries to essentially perform a search without all this recursion, but we haven't added support for that to this connector yet. If you feel up to it, you can add it yourself though. Just refer to the Microsoft Graph API documentation regarding OData queries.

Cheers,

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

RE: Microsoft 365 - Excel

ArunkumarManoha — Fri, 28 Apr 2023 15:11:00 GMT

Thanks Eric.

Also would like to know which action we need to use to get Worksheet ID because it requires in all the other actions.

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

RE: Microsoft 365 - Excel

ewilson — Fri, 28 Apr 2023 15:48:00 GMT

@Arunkumar Manoharan

The same idea applies. Geta list of the items on a drive or from within a folder. If the item is a worksheet, it's ID will be included in its metadata. This is why I mentioned giving Microsoft Graph Explorer and go as it gives you a very visual way of understanding how all this fits together.

Cheers,

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

RE: Microsoft 365 - Excel

ArunkumarManoha — Wed, 03 May 2023 15:12:00 GMT

Hi Eric,

I have taken the worksheet ID using "Get Worksheet Names" action and now got stuck in "Get Worksheet as Collection" action. I was able to extract the values from the sharepoint excel into collection(Text), but in "Get the Range" action getting the below error message.

Regards,
Arun

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

RE: Microsoft 365 - Excel

shashank.kumar280 — Thu, 04 May 2023 11:58:00 GMT

Hi Arunkumar,

The code stage "Get the Range" is expecting column named JSON_Unnamed_Value in the "Text" collection which doesn't seem to be there based on the error message. Can you post a screenshot of the Text collection with the column names visible.

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------

RE: Microsoft 365 - Excel

ArunkumarManoha — Thu, 04 May 2023 12:34:00 GMT

Hi Shashank,

Please find the Text collection screenshot below.

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

RE: Microsoft 365 - Excel

shashank.kumar280 — Thu, 04 May 2023 16:12:00 GMT

Hi Arunkumar,

A test run on my end was successful for this action. Can you please check if you have the latest Utility - JSON from DX.
If you still get error after upgrading Utility - JSON, please share a sample copy of the excel file that you are trying to read.

------------------------------
Shashank Kumar
DX Integrations Partner Consultant
Blue Prism
Singapore
+6581326707
------------------------------