I Thought He Came With You (ITHCWY) is Robert Ellison's blog.

Download a Sharepoint File with GraphServiceClient (Microsoft Graph API)

There is a stunningly simple way to get a file out of sharepoint and I'll get to that soon (or just skip to the very end of the post).

I have been automating the shit out of a lot of routine work in Microsoft Teams recently. Teams is the result of Skype and Sharepoint having too much to drink at the Microsoft holiday party. It often shows. One annoyance is that channel threads are ordered by the time that someone last responded. Useful for quickly seeing the latest gossip but a pain when you need to keep an eye on each individual thread. After listlessly scrolling around trying to keep up with the flow I came up with a dumb solution - I sync the channel to Obsidian (my choice of note app, could be anything) and then I can just check there for new threads. It's a small convenience but has meaningully improved my life.

Unfortunately I got greedy. These messages usually have a PowerPoint presentation attached to them and so why not have an LLM summarize this while updating my notes?

It doesn't look like Copilot has a useful API yet. You can build plug-ins, but I don't want to talk to Copilot about presentations, I just want it to do the heavy lifting while I sleep so I can read the summary in the morning. Hopefully in the future there will be a simple way to say hey, Copilot, summarize this PPTX. Not yet.

So the outline of a solution here is download the presentation, send it ChatGPT, generate a summary and stick that in Obsidian. This felt like a half hour type of project. And it should have been - getting GPT4 Turbo to summarize a PPTX file took about ten minutes. Downloading the file has taken days and sent my self esteem back to primary school.

You would think that downloading a file would be the Graph API's bread and butter. Especially as I have a ChatMessage from the channel that includes attachments and links. The link is for a logged in human, but it must be easy to translate from this to an API call, right?

It turns out that all you need is the site ID, the drive ID and the item ID.

These IDs are not in the attachment URL or the ChatMessageAttachment. It would be pretty RESTful to include the obvious next resource I'm going to need in that return type. No dice though.

I tried ChatGPT which helpfully suggested API calls that looked really plausible and helpful but that did not in fact exist. So I then read probably hundreds of blogs and forum posts from equally confused and desperate developers. Here is a typical example:

"Now how can I upload and download files to this library with the help of Graph API (GraphServiceClient)."

To which Microsoft, terrifyingly, reply:

"We are currently looking into this issue and will give you an update as soon as possible."

Before eventually suggesting:

"await graphClient.Drives["{drive-id}"].Items["{driveItem-id}"].Content.GetAsync();"

Ignoring the sharepoint part and glossing over where that drive ID is coming from. Other documentation suggests that you can lookup your site by the URL, and then download a list of drives to go looking for the right one. Well, the first page in paginated drive collection anyway implying that just finding the ID might get you a call from the quota police.

I know Microsoft is looking after a lot of files for a lot of organizations, but how can it be this hard?

It isn't. It's just hidden. I eventually found this post from Alex Terentiev that points out that you just need to base64 encode the sharing url, swap some characters around and then call:

"GET https://graph.microsoft.com/v1.0/shares/{sharing-url}/driveItem"

If Google was doing its job right this would be the top result. I should be grateful they're still serving results at all and not just telling me that my pastimes are all harmful.

The documentation is here and Microsoft should link to it on every page that discusses drives and DriveItems. For GraphServiceClient the call to get to an actual stream is:

"graphClient.Shares[encodedUrl].DriveItem.Content.GetAsync()"

Add your comment...

(All Code Posts)

(Published to the Fediverse as: Download a Sharepoint File with GraphServiceClient (Microsoft Graph API) #code #ml #graph #sharepoint #c# Everyone developing applications with the Graph API should know about the shares endpoint that allows you to download files easily. )

code, ml, graph, sharepoint, c#

Reading and Writing Office 365 Excel from a Console app using the Microsoft.Graph C# Client API

Updated on Sunday, September 18, 2022

I needed a console app that reads some inputs from an online Excel workbook, does some processing and then writes back the results to a different worksheet. Because I enjoy pain I decided to use the thinly documented new Microsoft.Graph client library. The sample code below assumes that you have a work or education Office 365 subscription.

using System; using System.Collections.Generic; using System.Globalization; using System.IO; using System.Linq; using System.Net.Http.Headers; using System.Threading.Tasks; using Microsoft.Graph; using Microsoft.IdentityModel.Clients.ActiveDirectory; using Newtonsoft.Json.Linq; namespace Excel365Test { /// <summary> /// 1) Install Microsoft.Graph NuGet Package /// 2) Install Microsoft.IdentityModel.Clients.ActiveDirectory NuGet Package /// 3) Register app at https://portal.azure.com/ - need app ID and redirct URL below /// </summary> class Program { static void Main(string[] args) { TokenCache tokenCache = new TokenCache(); // load tokens from file string tokenPath = Path.Combine(Environment.GetFolderPath(Environment.SpecialFolder.LocalApplicationData), "Excel365Test"); if (!Directory.Exists(tokenPath)) { Directory.CreateDirectory(tokenPath); } tokenPath = Path.Combine(tokenPath, "tokens.dat"); if (System.IO.File.Exists(tokenPath)) { tokenCache.Deserialize(System.IO.File.ReadAllBytes(tokenPath)); } // this is the OAUTH 2.0 TOKEN ENDPOINT from https://portal.azure.com/ -> Azure Active Directory -> App Registratuons -> End Points var authenticationContext = new AuthenticationContext("https://login.windows.net/your-url-here/", tokenCache); // only prompt when needed, you'll get a UI the first time you run var platformParametes = new PlatformParameters(PromptBehavior.Auto); var authenticationResult = authenticationContext.AcquireTokenAsync("https://graph.microsoft.com/", "your-app-id", // Application ID from https://portal.azure.com/ new Uri("http://some.redirect.thing/"), // Made up redirect URL, also from https://portal.azure.com/ platformParametes).Result; string token = authenticationResult.AccessToken; // save token so we don't need to re-authorize System.IO.File.WriteAllBytes(tokenPath, tokenCache.Serialize()); // use the token with Microsoft.Graph calls GraphServiceClient client = new GraphServiceClient(new DelegateAuthenticationProvider( (requestMessage) => { requestMessage.Headers.Authorization = new AuthenticationHeaderValue("bearer", token); return Task.FromResult(0); })); // test reading from a sheet - in this case I have a test worksheet with a two column table for name/value pairs var readSheet = client.Me.Drive.Items["your-workbook-id"].Workbook.Worksheets["test"]; var readTables = readSheet.Tables.Request().GetAsync().Result; string readTableId = readTables[0].Name; var table = readSheet.Tables[readTableId].Rows.Request().GetAsync().Result; // convert page to a dictionary... this doesn't handle pagination Dictionary<string, decimal> tableValues = table.CurrentPage.ToDictionary(r => r.Values.First.First.ToString(), r => Convert.ToDecimal(r.Values.First.Last, CultureInfo.InvariantCulture)); // test adding a row to a table with four columns // sadly it seems you need this exact format, a regular JArray or JObject fails WorkbookTableRow newRow = new WorkbookTableRow { Values = JArray.Parse("[[\"1\",\"2\",\"3\",\"4\"]]") }; var outputSheet = client.Me.Drive.Items["your-workbook-id"].Workbook.Worksheets["data"]; var outputTables = outputSheet.Tables.Request().GetAsync().Result; string outputTableId = outputTables[0].Name; var outputResult = outputSheet.Tables[outputTableId].Rows.Request().AddAsync(newRow).Result; // the excel unit tests seem to be the most useful documentation right now: // https://github.com/microsoftgraph/msgraph-sdk-dotnet/blob/dev/tests/Microsoft.Graph.Test/Requests/Functional/ExcelTests.cs } } }

Paste the code into a new console project and then follow the instructions at the top to add the necessary NuGet packages. You'll also need to register an application at https://portal.azure.com/. You want a Native application and you'll need the Application ID and the redirect URL (just make up some non-routable URL for this). Under Required Permissions for the app you should add read and write files delegated permissions for the Microsoft Graph API.

Hope this saves you a few hours. Comment below if you need a more detailed explanation for any of the above.

Add your comment...

(All Code Posts)

(Published to the Fediverse as: Reading and Writing Office 365 Excel from a Console app using the Microsoft.Graph C# Client API #code #microsoft #excel #graph #office365 How to read and write data to an Office 365 Excel workbook using the Microsoft.Graph client library from a C# console application. )

code, microsoft, excel, graph, office 365

Download a Sharepoint File with GraphServiceClient (Microsoft Graph API)

Related Posts

You Might Also Like

Reading and Writing Office 365 Excel from a Console app using the Microsoft.Graph C# Client API

Related Posts

You Might Also Like

Newsletter

Popular Posts

Recent Posts

Recent Tags