Microsoft 365 Community Community Demos, 9 Jun 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Insert template from external Excel file

Description

In this 8-minute developer-focused demo, David Chesnut steps through the insertWorksheetFromBase64 capability. See how to insert an external Excel file into the open Excel worksheet and populate it with JSON data. Click through the VS Code. The sheet is activated upon insertion. Live discussion during call to assess whether the newly inserted sheet should be activated upon insertion. Capability applies to Excel on Windows, Mac, and web. This feature is currently in preview. This PnP Community demo is taken from the Office Add-ins community call – June 2021, recorded on June 9, 2021

Demo Presenter - David Chesnut (Microsoft) | @davidchesnut

Supporting documents:
• Documentation - Insert an external Excel file and populate it with JSON data | https://docs.microsoft.com/en-us/samples/officedev/pnp-officeaddins/insert-an-external-excel-file-and-populate-it-with-json-data/

Stay connected:
• Twitter https://twitter.com/microsoft365dev
• YouTube https://aka.ms/M365DevYouTube
• Blogs https://aka.ms/M365DevBlog

A

Hello, my name is david chester and I'm a senior dev writer on the office platform team and I'm going to show a pnp sample that we've released. That shows how to insert an external file into an existing worksheet and then populate it with json data.

A

So I'm going to go over to excel and let me just show what this does to start with, I'm going to go to my insert menu and select my add-ins and what I've done is I've already downloaded from the pnp site. The manifest for this pnp insert excel file, so you'll see the icon there for it.

A

So I'll go ahead and add that to my spreadsheet and go to the home tab and we'll see it here, so I'm going to go ahead and open the task pane and there's a little bit of uh help text here for trying it out.

A

Basically, all you need to do is go choose the file that you want to insert so I'll click on that, and it opens up file explorer here and I'm going to go grab the sales template, which is also in the pnp repo and open that and then you'll, see that it inserts a table.

A

It creates this template worksheet. You can see at the bottom and inserted this and then uh I have a table and then that table was populated with some json data. So this is pretty simple. It's just a scenario where, if you have a situation where you want to bring in some data or some kind of template from another excel worksheet and put it into the current one with your added, this is how you can do it. So, let's take a look at the code switch over to visual studio code, and this is the project here.

A

It's basically just an index html file, that's the task pane page and then the index.js.

A

So in html you can see we create this input element, which is where the user is going to go, select a file and then, in our index js file in our office on ready. We basically hook up that element to a change.

A

We add a change event for it and it's going to call our insert sheets function and down here in insert sheets we're going to get the file name and put that into this. My file variable, then we're going to create a file reader and we're going to have the file reader open up that file, that spreadsheet that the user selected and we're going to have an onload event so that when the reader reads it in it's going to call this function in which we're going to call excel.run because we're going to interact with excel apis.

A

We will go into the contents of that file and search for the literal string base64, because that's where the base64 encoding will be located uh once we've located that we pull it out into this workbook contents variable we then go get the current workbook, which is in the office context object. So we go context.workbook and grab that and the the magic is going to happen here. This is the api, so we're going to call workbook.insert worksheets from base64 and we pass it to basics for content along with these options.

A

Now, in this case, the options you can see up here, we've set them to we have you can specify which sheets do we want to insert in this case we're selecting the template sheet. That's in there. We can indicate where we want. To put it so we say relative to sheet one. We want to put it after that one. So that's what position type is doing, let's place it after so once this is called. We then wait for the context.sync, so that's going to wait until it actually gets inserted.

A

Then we need to insert the json data. So we go get the new template worksheet that we just inserted from the current context. Current workbook, we go fetch the data.json. This is just a json file, that's out on github, so we fetch it from there. uh You know you could get the json from what you know. If you had a service or something, however, you want to set that up doesn't really matter once we have the json.

A

We then map it into the structure that we want to use for our table, which is product and four quarters.

A

We then go and basically set up a range in that table where we're going to insert all that data we get that range and we assign the json data to the values of that range, and then we activate the sheet, so it's visible and we return context.sync.

A

So that's how the json data gets inserted.

A

Now something could go wrong, so you want to make sure you have error handling a case where this can happen is if you're, inserting a worksheet in excel on the web and excel on the web. Doesn't support every feature: that's in a spreadsheet, so, for example, if you had a chart that worksheet will fail and you'll see this error and the arrow will indicate that there's an unsupported type in there that can't be inserted.

A

So in that case, here you'd want to add some additional code to basically notify the user. Hey there's something in that worksheet, that's not going to work, so you could go to a desktop and it should work, but you wouldn't be able to do it online.

A

So just don't forget about that little piece and that's pretty much how it works. One more thing to point out is that this sample is also in script lab. If you want to try it there, I've already opened it up here but like if you just go into samples and you search for whoops, not my snippets. I want samples and you search for rsa insert. You can see this insert external worksheets.

A

So if you want to try it out right away, you can go here.

A

It's slightly different in that it just passes an empty array when it, when you give it the options of sheet names to insert which basically just inserts all of them so definitely go. Take a look at the sample and try it out. You'll notice, there's no yo office project. Here, it's just hosted on github, so it's really simple to set up and use. You just have to sideload the manifest.

A

So let me switch back see if there's any questions.

B

Yeah hi david, I actually raised my hand. I just want to come, learn the feedback from our community, so as david's demos, when inserted when qualities api insert worksheets, the worksheet will be activated.

B

So this is our current behavior, so we receive some of the feedback say that what they don't want to activate the the sheets, that's newly added. So what's your? What's your opinion, whether that is, we should activate as current behavior or we should not activate it.

A

Does anyone have any feedback on that and he has has a hand up there.

C

I would prefer not to activate the sheet, we do use hidden sheets and we would rather not activate the sheet once we create it and have that be optional.

C

My personal opinion.

B

Got it great, thank you yeah, it makes sense. Okay, thank you. Thank you. Andy.

A

I agree with the previous speaker: okay, all right, another, a vote for for for not activating.

B

B

Okay, okay, I see uh yeah yeah because because uh we we internally have a very hot debate, so we saw this if you actually preview for two years is activated, but we receive some feedback say, do not activate it, and I want to verify uh whether we should not activate it. So it seems like today we got answer.

B

Thank you. Thank you very much.

A

That I think we should activate I'll just ask it that way, and so, if I see a hand up I'll assume, maybe you think it should be activated after this api call um vit, I'm just curious. If I remember correctly, there is a select sheet right where you can activate a sheet.

B

Yeah correct correct.

D

Yes and yeah, so that's why it's not activated exactly. That was my point that I can activate it using different api so so for not activating definitely.

B

Got it got it? Thank you. Thank you very much that makes.

D