topic RE: Graph Connectors (Outlook and Excel) Questions in Digital Exchange

Graph Connectors (Outlook and Excel) Questions

john.hammond — Tue, 23 Aug 2022 12:48:00 GMT

Afternoon all.

We're starting to experiment with Graph API and starting to get somewhere, particularly on the SharePoint side of things. Although I'm still having a few difficulties with some of the other connectors from the DX, and having searched for these issues I can't see them mentioned anywhere on these forums. Could just be me being extremely daft, but I was wondering if anyone could lend me a hand in understanding them a bit more:

- In MS Graph - Outlook, I'm able to do most things, however the 'List Messages in Folder' action seems to only return a maximum of 10 items. I've seen similar responses in SharePoint, but that had a 'GetNextToken' output parameter which I could use, but I cannot see this here. Alternatively, there does appear to be the object 'List Messages' which does have a NextLinkToken output, however the format of the emails pulled using this object is nowhere near as pleasant as the other object - as in, the entire message with all headers and css/html tags are contained within a single string, making it difficult and inefficient to search, for example, a subject line.

- With Excel, a bit of a more fundamental issue this one - how do you open a file?! I'm able to download a file, which I could then feasibly use the Excel VBO to do any manipulation that I require before uploading it again and deleting the old version, but this seems like a very haphazard way of going about things. I assume that I need to rub the object 'Create Session'? If so, could I have a bit more of a clearer explanation as to the Input Parameters needed to identify the relevant file? I assume that 'ID' is the Site ID with Workbook ID the ID of the Excel file itself (found via Get Drive Items in SharePoint)?

Many Thanks!

------------------------------
John Hammond
------------------------------

RE: Graph Connectors (Outlook and Excel) Questions

Vit — Tue, 23 Aug 2022 12:58:00 GMT

Hello John,

First get the download link by one of request bellow then use action Utility File management - Download File by URL and Destination Path.

[GraphApi - Path] & "/sites/" & [Site ID] & "/lists/{" & [List ID] & "}" & "/drive/items/" & [File ID] & "?select=id,@microsoft.graph.downloadUrl"

[GraphApi - Path] & "/sites/" & [Site ID] & "/drives/" & [Drive ID] & "/items/" & [File ID] & "?select=id,@microsoft.graph.downloadUrl"

V.

------------------------------
Vit Blazek
BluePrism Aplication Manager at CSOB,a.s.
Ceskoslovenska obchodni banka, a. s.
Europe/Prague
------------------------------

RE: Graph Connectors (Outlook and Excel) Questions

john.hammond — Tue, 23 Aug 2022 13:07:00 GMT

Thank you for your prompt response V. Can I just ask though, is there not a pre-determined action within the Connector to do this? And does this not just download the file, which could be done using 'Microsoft Graph - Download Files' from the DX and then the action 'Download from SharePoint'?

------------------------------
John Hammond
------------------------------

RE: Graph Connectors (Outlook and Excel) Questions

ewilson — Tue, 23 Aug 2022 13:24:00 GMT

Hi @john.hammond,

Regarding the List Messages in Folder action and GetNextToken, that's an oversight on our part. Luckily enough we've undertaken an exercise over the next month or so to see what we can do to redesign the Excel and Outlook connectors for Graph to make them more user friendly. It will take a little time, but in the meantime we'll get an update posted in the next day or so to address the paging issue in that action.

With Excel, yes you'll use Create Session to get your work started with a specific workbook. Check the embedded user guide on the connector for descriptions of the input fields.

Cheers,

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

RE: Graph Connectors (Outlook and Excel) Questions

john.hammond — Tue, 23 Aug 2022 13:35:00 GMT

Thanks for this Eric, and good news about the Outlook connector. I'll keep my eyes peeled for that one.

As for the Excel documentation, I had looked at that, but I think it's just the last couple of parameters there that have confused me - For Workbook ID, I've navigated to the relevant folder using the SharePoint connector and have grabbed the 'id' value from that - so essentially, I'm treating 'Workbook ID' as 'File ID' - is that correct? Also, for the 'Type' parameter, I'm unsure what those values pertain to - whenever I try any of those suggested values (and assuming Workbook ID = File ID), stepping over the action returns this error message:

Internal : Unexpected error Cannot execute action "Create Session" because an error occurred while compiling custom code. Errors were found in the following locations:
Shared code

EDIT: Sorry to 'cross the streams', as it were, but I'm currently trying to configure 'Microsoft Graph - Sharepoint Upload Session' from the DX, but am getting a Compiler error (screenshot below). I've had a similar issue before, potentially down to the version of .NET framework we're running on. Am I OK to strip the dollar sign from this code? Only that when I do remove the character, I'm consistently presented with the following error message in the Response Body of the action 'Create Upload Session':
{"error":{"code":"BadRequest","message":"Resource not found for the segment '{folderPath}'.","innerError":{"date":"2022-08-23T14:19:44","request-id":"2e52c20c-e415-4092-a4fb-c0e7b769057d","client-request-id":"2e52c20c-e415-4092-a4fb-c0e7b769057d"}}}

------------------------------
John Hammond
------------------------------

RE: Graph Connectors (Outlook and Excel) Questions

ewilson — Tue, 23 Aug 2022 14:58:00 GMT

Hi @john.hammond,

That's an interesting error. The $ is a feature that was introduced in C# v6 back in 2015, I think, for string interpolation. I assume you're on .NET Framework 4.7 or later, correct? C# v6 was introduced as part of .NET Framework 4.6.

You can remove it, but you'll have to change the whole string such that you'll be concatenating the parameters together with the string. Either that, or you can replace the $ with String.Format(). Here are some examples:

// Example 1: Concatenating parameters.
var url = "https://graph.microsoft.com/v1.0/sites/" + siteId + "/drives/" + driveId + "/" + folderPath + "/" + fileName + ":/createUploadSession";


// Example 2: String.Format()
var url = "https://graph.microsoft.com/v1.0" + String.Format("/sites/{0}/drives/{1}/{2}/{3}:/createUploadSession", siteId, driveId, folderPath, fileName);

Regarding the Type parameter in the Excel connector, this refers to whether you're trying to work with a spreadsheet that stored in a user drive, group drive, or SharePoint drive. If you take a look at the Microsoft Graph API you'll see they expose multiple endpoints for just about every action depending on the data you're trying to work with. So, as an example, if you wanted to worked with an Excel spreadsheet that was stored on a SharePoint site you would set Type to site. What this ends up dong behind the scenes is changing the underlying URL path that's used in the REST request.

Cheers,

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

RE: Graph Connectors (Outlook and Excel) Questions

ewilson — Tue, 23 Aug 2022 23:59:00 GMT

Hi @john.hammond,

Version 2.3.1 of the Outlook connector for Graph was posted to the DX tonight. It includes support for the NextLinkToken output parameter on the List Messages in Folder action.

Cheers,

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

RE: Graph Connectors (Outlook and Excel) Questions

john.hammond — Fri, 26 Aug 2022 07:10:00 GMT

Hi @Eric Wilson, thank you for your swift response.

I do indeed see that the 'List Messages in Folder' action now has a NextLinkToken value returned, although it looks to me as if all response content is now compacted into a single field, much like how the 'List Messages' function did previously. I assume this has been done for reliability issues, but is there a 'legacy' version of what this action used to perform with the NextLinkToken parameter present? Or alternatively, is there perhaps a 'nice' way of unpacking this data?

------------------------------
John Hammond
------------------------------

RE: Graph Connectors (Outlook and Excel) Questions

john.hammond — Fri, 26 Aug 2022 07:12:00 GMT

And thank you for this response also! I'll have a look at both of these at some point today.

EDIT: Sorry @Eric Wilson, sent another reply to you regarding a potential missing dll which I now cannot find (in relation to the Excel connector). Found the dll and put it in the BP folder, but was still getting the issue. Investigated the Common Code for the Excel Graph API call and was presented with this message, if you have any ideas?!

Also, just spoken to someone more in the know than myself, and apparently we might be using .NET framework 3.5 (!!!), which might be limiting my functionality somewhat.

------------------------------
John Hammond
------------------------------

RE: Graph Connectors (Outlook and Excel) Questions

john.hammond — Fri, 26 Aug 2022 07:45:00 GMT

Hi again @ewilson.

That issue I was having RE: Shared Code when opening a file using the Excel API appears to be linked to a missing dll from the object on the DX - should Newtonsoft.Json.dll be mentioned as a prerequisite/download?

------------------------------
John Hammond
------------------------------

RE: Graph Connectors (Outlook and Excel) Questions

ewilson — Fri, 26 Aug 2022 11:55:00 GMT

Hi @john.hammond,

The Newtonsoft JSON DLL should already be included in the Blue Prism Automate folder. Was there not one in yours?

Cheers,

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

RE: Graph Connectors (Outlook and Excel) Questions

ewilson — Fri, 26 Aug 2022 12:02:00 GMT

@john.hammond,

I think I may have misunderstood your question. Are you saying you needed to add Newtonsoft to the Global Code section? But you only had to do it when this one action was added, and it continued to work find with the other actions that used it? 🤔

Cheers,

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

RE: Graph Connectors (Outlook and Excel) Questions

john.hammond — Fri, 26 Aug 2022 12:29:00 GMT

Sorry @Eric Wilson, I'm not being particularly clear with my plethora of multiple questions. This issue relates to the Excel Connector for Graph. As of right now, I've not been able to get any functions working from this - whilst stepping through/over any stage within the Studio, I get the error message 'Internal : Unexpected error Cannot execute action "Create Session" because an error occurred while compiling custom code. Errors were found in the following locations: Shared code'. When looking at the Common Code on the API definition section in System, and checking this code, I get the following error message:

------------------------------
John Hammond
------------------------------

RE: Graph Connectors (Outlook and Excel) Questions

ewilson — Fri, 26 Aug 2022 15:02:00 GMT

@john.hammond,

That's interesting. I don't receive that error when I use the Graph connector for Excel. What version of Blue Prism are you on? It must be at least v6.4 otherwise you wouldn't be able to use WebAPIs. Regarding the .NET Framework version, you should be on at least some variant of v4 (ex v4.7) as that has been the base requirement of the BP platform for a while now. I don't think the software will even function if you only have .NET 3.5 installed.

Have you tried adding a reference to System.Core.dll to the Global Code options?

Cheers,

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

RE: Graph Connectors (Outlook and Excel) Questions

john.hammond — Wed, 31 Aug 2022 08:36:00 GMT

Hi @Eric Wilson, thanks again for your response to this. Not sure if you saw this message regarding the output of the new and improved 'List Messages in Folder' action for Outlook API on the DX. Is there a way for the data to unpacked in a more readable manner/collection as it was previously?

Also, one other point - I noticed today that attachments have to be under a certain size to be sent using the Send Mail action (I tried sending a file which was around 6mb and received a HTTP error 419). I know that there is the chunking approach to uploading files larger than 4mb in SharePoint - is there something similar for email attachments?

------------------------------
John Hammond
------------------------------

RE: Graph Connectors (Outlook and Excel) Questions

ewilson — Thu, 01 Sep 2022 00:20:00 GMT

Hi @john.hammond,

There was a reason we moved away from returning the nicer Collection that, as I recall, had to do with Microsoft including an element in their response information that wasn't always set. If it wasn't set when the Collection (aka DataTable) was initially created, a data type exception could be raised if a subsequent record actually included a value. So we switched to just returning the raw JSON of the individual messages. You can then use the Utility - JSON VBO to turn it into something pretty.

We can go back and take another look at it to see if there's something else we might do with it.

Regarding attachments, Microsoft's Graph documentation states that attachments are limited to no more than 3MB when performing direct attach as part of the Send Message POST. That's what we currently support. There is an option to upload larger files (up to 150MB) by using upload sessions for attachments. We don't support that at the moment, but we'll get it in the backlog. For anything larger than 150MB, you'd probably have to create an upload session for OneDrive or SharePoint and then include the download URI of the file as the attachment in your email.

Cheers,

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

RE: Graph Connectors (Outlook and Excel) Questions

john.hammond — Mon, 12 Sep 2022 11:08:00 GMT

Hi again @Eric Wilson. Apologies for the delay, but have finally got around to testing this again - the compiler error has disappeared, however FYI, in the latest release of the Graph Upload Sessions object on the DX, the code stage contains reference to the following on Line 4:

long fileSize = new System.IO.FileInfo(@"C:\temp\3GB_TestFile.bin").Length;

I assume this is something that was used in testing?

------------------------------
John Hammond
------------------------------

RE: Graph Connectors (Outlook and Excel) Questions

ewilson — Mon, 12 Sep 2022 22:39:00 GMT

@john.hammond,

Oops! 😳 It's fixed.

Cheers,

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

RE: Graph Connectors (Outlook and Excel) Questions

john.hammond — Wed, 14 Sep 2022 07:51:00 GMT

Hi again @Eric Wilson. I'd just like to say thank you for your continued help and patience with trying to help me understand this! Added to my own lack of understanding, our Azure AD person is having some difficulty getting his head around this specific issue as well. When trying to create an Upload Session (or even use a regular upload using the SharePoint connector), I'm getting the following error message:

{"error":{"code":"accessDenied","message":"Access denied","innerError":{"date":"2022-09-14T07:40:35","request-id":"31699107-cc04-4a22-84d4-c219fd950ec4","client-request-id":"31699107-cc04-4a22-84d4-c219fd950ec4"}}}

'Access Denied' is making me think that this is an AD-side issue, although apparently we have full Write access at the site level. Is there a specific permission that these actions require? The only other potential thing I can think that may be causing this is that the 'Auth Token' parameter requires a text data type, so just before the Create Upload Session action in my workflow I have a calculation that pulls the AccessToken password from the authenticator and writes it to a text data item. This then gets passed in. This is the correct way of doing this, right?

------------------------------
John Hammond
------------------------------

RE: Graph Connectors (Outlook and Excel) Questions

ewilson — Wed, 14 Sep 2022 12:04:00 GMT

Hi @john.hammond,

The Password data type in Blue Prism is nothing more than a Text item that only displays dots for characters. Under the hood, it's still regular text and that's the way the connector sees it. So there's no need to convert it.

See this page from Microsoft regarding upload sessions and Graph. It lists the required permissions based on the type of token you're using (Application vs Delegated).

https://docs.microsoft.com/en-us/graph/api/driveitem-createuploadsession?view=graph-rest-1.0

Cheers,

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