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

[ArunkumarManoha]

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

[ewilson]

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

[ArunkumarManoha]

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

[ewilson]

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

[ArunkumarManoha]

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

[ewilson]

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

[ArunkumarManoha]

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

[shashank.kumar280]

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

[ArunkumarManoha]

Hi Shashank,

Please find the Text collection screenshot below.

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

[shashank.kumar280]

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