Microsoft 365 Community Community Demos, 17 Mar 2023

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Create a spreadsheet from your website

Description

A 14-minute Developer-focused demo delivered by David Chesnut. Walk through the tutorial document, solution architecture, and code sample for a solution that automatically generates a new spreadsheet with the requested data (data in table on a web page), uploads it to the initiator’s OneDrive, and opens it in Excel on a new browser tab. The action is initiated with a single click. Additionally, the pattern embeds your own Office Add-in inside the spreadsheet.

This PnP Community demo is taken from the Office Add-ins community call – February 2023, recorded on February 8, 2023.

Demo Presenter
• David Chesnut, Senior Technical Writer (Microsoft) | @davidchesnut

Supporting documents
• Documentation - Create an Excel spreadsheet from your web page, populate it with data, and embed your Office Add-in | https://learn.microsoft.com/office/dev/add-ins/excel/pnp-open-in-excel
• Sample - Create a spreadsheet from your web site, populate it with data, and embed your Excel add-in | https://github.com/OfficeDev/Office-Add-in-samples/tree/main/Samples/excel-create-worksheet-from-web-site
• Identity samples - Vanilla JavaScript single-page application using MSAL.js to authenticate users to call Microsoft Graph | https://github.com/Azure-Samples/ms-identity-javascript-tutorial/blob/main/2-Authorization-I/1-call-graph/README.md
• Survey - Office Add-ins code sample survey | aka.ms/officesamplessurvey

Learn more
• Microsoft 365 Unified Sample gallery - https://aka.ms/community/samples
• Microsoft 365 Platform Community in YouTube - https://aka.ms/community/videos
• Microsoft 365 Platform Community - http://aka.ms/community/home

A

Foreign I'm going to talk about a scenario that we've had documented for a while a little over a year, I think, but we just recently released a code sample for it as well. So I just want to walk through that. So let me go ahead and share my desktop okay.

A

So in our documentation, under our samples, we've had this open in Excel scenario that we documented a while back and just got updated this morning along with a code sample for it, because it was a bit handwavy before it just kind of pointed you at some stuff and said: hey put this together and you can do this scenario, but now it has an end-to-end code sample for it which I'm going to walk you through.

A

So this is actually the first in a series of what we're hoping to create, which is more scenario based content, and the idea is to base this on successful patterns that we see our partners using. So if you see Partners presenting at conferences like Microsoft, ignite or Microsoft, build you'll see they're, often sharing their experiences with the office platform, but we've also realized. There are patterns that we can share with the broader audience to share that success, basically with other folks and so it'd, be great to get your feedback in the chat or later on.

A

I'll share a link to a survey. If you want to share more about what you think about this, if you think anything's missing or it could be clearer as we you know, we're going to continue to iterate on this and create more scenarios as we go forward. So it starts with talking about what the success is about like why you would want to use this, and so this first paragraph here was talking about the basic scenarios.

A

You have a website like this contoso product sales here, and it shows a user's data, they've signed in and they're seeing data specific to them, but they want to take that into Excel. Do some number crunching on it or some other analysis and maybe eventually sync it back up to the database on your website and usually that could work in a number of ways, and this is like a pain point that our partners were noticing is that end users will go in they'll, see their data.

A

They'll have to put it like in a CSV file open in Excel, do their thing and then re-upload it at some point.

A

So it's kind of a lot of steps to it and what this pattern does is it takes it more into a one-click kind of thing, so you have an Excel button on your website and when the user clicks it, it creates the spreadsheet for them and then opens in a new tab, so it just all happens at once and one of the bonuses to it also is you get your office ad in embedded, you can embed your add-in, which is nice, because you can keep the user connected to your services and we found our partners have found that this is increased customer satisfaction and also the engagement numbers on the add-in itself.

A

So you'll get more views to this more usage of your add-in so that we have some prerequisites: we're working with the code sample I'll just walk through the solution; architecture, real quick, because there's a number of moving parts to this. So one is going to be your web server, which serves up this web page with the button and that's going to handle citing in the user, and so at some point.

A

The user clicks this button saying all right: let's take the data into Excel, so the first thing it's going to do is call this Azure functions app to create the spreadsheet and it's going to inside the functions app. It's going to use openxml SDK so because the openxml SDK is.net, based and c-sharp, we've encapsulated that in Azure functions, app separate from the node server. That's running the web page.

A

So when the Azure functions app constructs it it Returns the spreadsheet in the form of a base64 encoded string. Then it takes that string and uploads it to one drive. So it's going to call the Microsoft graph, API say: here's! The string, Microsoft graph API turns that into an actual file on OneDrive and Returns the web URL of that spreadsheet. Now you can take the web URL open that in a new tab and the user gets their spreadsheet so at a high level, that's how it works.

A

The key things you want to note about the solution is one you want to use the fluent UI buttons, so these are the official ones that will help users understand where they're going when they click on the button- and you don't have to like literally write out the text open this up in Microsoft Excel. You could just have the button um that should be obvious enough and so in the sample code it shows how you can get these.

A

These images from the CDN you'll want to cite in the user, and so the sample code is built on this existing sample from the Microsoft identity team. So there's some information there and basically, if you want to know anything about the outside, I would just point to that sample. There's nothing office specific about the authentication here and if you're using other platforms, there's also additional samples out there you can use.

A

Then you of course want to create the spreadsheet using the openxml SDK. So we'll talk in a moment about the function create spreadsheet, as well as the spreadsheet Builder class, which is using the openxml SDK. You want to populate the spreadsheet with data um and we'll show how that works.

A

That's also using the openxml SDK and then you can embed your office ad and inside the spreadsheet, and so in this case, we'll show how to do that using the openxml, SDK and re-embed the script lab add-in, and this is the idea of the script lab add-in and the version the store and store type properties and there's some information down here about what those do there's. Also, if you're, not an app source and you're essentially deployed. There's some commented code in there that you can use that will actually set you up for doing central deployment.

A

It's not really possible to do like a side load scenario. It's just really. Squirrely I try to get that to work, but it turns out you have to be side loaded before you side load. It's it's uh really hard to get it working correctly. So for right now, it's not in the sample to show how to do that.

A

Then to upload the spreadsheet, that's kind of the last step. You do some additional things. This sample doesn't do, but you might want to consider doing it is you can embed custom properties when you embed the add-in, so, for example, this shows some code for how you could put in name value pairs like a username, and that can be useful so that when the spreadsheet opens and your add-in pops up, it can just go read that data out and be ready to go and then on the JavaScript side.

A

You can you know in your add-in, you could read the properties using this office.context.document.settos.get and then the name of the property. Big caution here, don't put anything sensitive in there like off tokens, because this information is not protected in any way inside Excel anyone can open up that Excel file and see what's in there. So if you do need to use like an auth token or something use single sign-on and there's a link here to how to set up single sign on and that way you don't have to store anything sensitive in there.

A

So, let's take a look at the demo here: I have the home page open. So we can imagine on my website. I need to go sign in to start seeing product sales data for me, so I'll click on the sign in button and I'm going to sign in my I have a test account. Microsoft 365 test account.

A

A

All right so once I'm signed in this just mock data, there's like an actual database up there anything but uh return. Some sales data and I can see sales by quarter and at this point, I'm like okay, so I want to take that you can see like I'm signed in as well. It's got my name there. So let's take that into Microsoft.

A

Excel click on the button see it opens up a new tab and then opens up the spreadsheet, and you can see this spreadsheet has the data and it should pop up script lab here in a second there's: script lab it's embedded, so that's cool! So it's ready to go so just yeah, one click! It's pretty simple! So let's take a look at the code over here. I have the Azure function project this is in Visual Studio, and so we have.

A

The function creates spreadsheet right here that gets called I'm going to post to it, Json that represents the rows and Columns of data that I want to insert into the spreadsheet, and so we'll pull that out into this table data variable by deserializing, the Json. Then it calls the spreadsheet Builder class to go, create that spreadsheet. Passing at the table data you can this just names the sheet, that's going to put it into when we're done. We convert that. It returns a byte array.

A

We convert that into a Basics before string and return it back to the client. So let's look at spreadsheet Builder. This has all the open, XML part of it, and so it doesn't work with files on the disk. That'd be very I'm, not sure you could do that in Azure function or even on the you know on a web page. So instead it's using a memory stream. It creates a document in memory and then adds the workbook part and that's the sheet I'm not going to like go line by line through this.

A

Basically, it's constructs the fundamental frame of the spreadsheet, and then it puts the data in it by calling insert data at Row one column, one that into this particular sheet with this table of data, and then it also embeds your add-ins. So we can take a look at these two. um These are interesting. So insert data is just going to go through each row, each column and insert each cell one by one using the sensor cell value function.

A

So it's all open, XML stuff, but let's go down here where we're going to embed our or where our add-in. So here's, where you create the web extension section and we're going to add into that information, it needs, and then here's where you indicate the idea of which web extension we want. This is the add-in script lab here's the commented code if you want to use Central deployment instead, um here's where we put in a property say: hey open this up when the spreadsheet opens, so you can have it actually Auto open.

A

Oh by the way, let me show I'm not sure why this isn't working online, but if I go and openness on desktop it works. So this is something I'm trying to track down. I'm, not sure why there's this little hiccup but like here, you can see it auto opens like script. Lab just opens up automatically, which is the be everywhere we want anyway, let's go back over here.

A

So here's where you can add additional properties, if you want and to find out just commented out and yeah, that's the Azure function, side of it for the web applications side of it all right, there's a bunch of files. You get now. These all come from the base identity sample that I base this on so I would say if you want to understand what auth config is doing and auth redirect and so on, go refer to that sample.

A

I'll have a link to it at the end, all that's changed in this sample to make this work is adding a button on the HTML page and then, when the buttons clicked it's going to call scroll down to it.

A

This guy here open in Excel all right. So what this does? It says all right: let's open up our new tab on the browser and then we're going to get our mock data which comes from this table. Data object that's over here in this table data.js. So this is another thing I added just so we have our mock data and this is the formatted. You can change this to whatever format you want.

A

If you're doing something different, um then this is the URL of our Azure function that we're going to call so we'll go ahead and call that hosting the body Json. And then, when it comes back, we get this blob of base64 data basically and then we're going to call upload file to upload that to OneDrive and that's down here so to do that.

A

We're going to call the graph API and since we're already signed in as an Spa, we can make that call from here tell it which folder to put it in the file name to put it in and then uh we'll get back a result and you'll see that later on, we'll use that to set our tab to that web URL location. This call graph is a helper that came with the original sample, so you just need to pass it again, which scope you're using and uh interaction types and stuff like that.

A

You could create additional graph calls by just reusing. This call graph call and that's pretty much how this code works. One more thing to show is: if you open up, oh I was going to show you. Where creates it in OneDrive? So if you go to your OneDrive, it creates this open in Excel folder and you get the spreadsheet file.

A

Then, if you pop it open, if you rename that file to like a zip, this is how you can just see like the raw parts inside there. This is I like doing this, to discover how things work you can go into this Excel folder under web extensions, and this is a web extension one XML, and if you open that up and then we'll do a little reformatting here, I just want to show you that these properties are pretty easy to get to like here's, a property right. So that's!

A

This is why I'm saying like don't put secrets in here like this, is how easy it is to go in there and see what it's doing all right. Let me see I think that's pretty much it. Let's go to the back to the presentation yeah on the link slide. I was just going to point out like it has a link to the article has a link to the sample code. It also has a link to the original sample that I modified just in case.

A

You want to reference that and then there's also a link to a code sample survey. So if you have feedback on the scenario approach or the code or anything, you know suggestions to make it better just go out there put in the name of the sample that you're referring to so we know and then just give us verbatim feedback about what you'd like to see, changed and I. Believe that's it thanks. Preethica thanks everyone great thanks, David. That was an awesome demo.