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]

Thanks Shashank.

After upgrading Utility - JSON, it was working fine but only when the file is in documents folder of SharePoint(Refer Screenshot1).
But actually we need to go to the sub folders of the main drive and read the specific sharepoint excel file (Refer Screenshot2) but here I am getting the below error while running Get Worksheet Names action. I have used the same site id whatever used for documents folder which was working fine so only workbook ID got changed. (Refer Screenshot3)

Error: The remote server returned an error: (400) Bad Request.

Screenshot1

Screenshot2

Screenshot3

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

[ArunkumarManoha]

Hi @shashank.kumar280 @ewilson ,

Looking for your assistance on the above issue.

Regards,
Arun

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

[shashank.kumar280]

Hi Arun,

A different folder in the site will have a different Drive ID. If Drive ID is not specified the API points to the Documents Folder of the site by default. Note that current version of the VBO doesn't expose the Drive ID as a parameter, so it is pointing to Documents Folder.

This article is handy https://morgantechspace.com/2022/09/how-to-find-site-id-drive-id-and-item-id-using-microsoft-graph-api.html to get the site id, drive id and item id quickly using Graph Explorer.

You will have to modify the API URL in the VBO action to accommodate Drive ID. For example, for the Open Workbook action the current URL is-

[Base URL] & "/" & [Type] & "/" & [ID] & "/drive/items/" & [Workbook ID] & "/workbook/createSession"

This needs to be modified to -

[Base URL] & "/" & [Type] & "/" & [ID] & "/drives/" & [DriveID] & "/items/" & [Workbook ID] & "/workbook/createSession"

You can then expose the Data Item so that value can be passed from the process. I am assuming here that you are setting the Type as sites.

Similarly the other actions can be modified to cater for Drive ID.

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

[ArunkumarManoha]

Thanks @shashank.kumar280 

I have modified the API URL with Drive ID and it is working fine.

One more query - Is there any specific action to auto fill the values for the other cells for M365 excel because we are having an requirement to update a vlookup formula in one cell and that needs to be auto filled for the remaining cells as well. 

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

[shashank.kumar280]

@Arunkumar Manoharan I didn't find anything in the API doc related to auto fill.

https://learn.microsoft.com/en-us/graph/api/overview?view=graph-rest-1.0 

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

[shashank.kumar280]

FYI - Drive ID has now been included as a parameter in all the actions in the latest version of Excel 365 VBO.

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

[ArunkumarManoha]

@shashank.kumar280 Thanks for the update, will download the latest version from DX.

Reg Auto fill on M365 excel, how can we achieve this? or Can you suggest how can we update the vlookup formula to n number of cells?

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

[ThomasHutchins]

Hi,

To simulate AutoFill, I added a new action in our Excel VBO named Set Range Value. 
It has the input parameters: Handle (number), Start Cell Reference (text), End Cell Reference (text) and Formula (text).
The workbook and sheet to be updated must be the active workbook/worksheet.

In the code module, the inputs are assigned as:
Handle >> handle
Start Cell Reference >> cellref1
End Cell Reference >> cellref2
Formula >> Formula 

The code is very simple:
Try
GetWorkbook(handle,Nothing).ActiveSheet.Range(cellref1,cellref2).Formula = Formula
Success = True
Catch e As Exception
 Success = False
 Message = e.Message
Finally
End Try

The outputs are Success (flag) and Message (text).

You can use this to enter values or formulas to one cell or a range of cells in a single action.

Hope this helps,

Hutch

------------------------------
Thomas Hutchins
Lead Developer
Discover
America/Chicago
------------------------------