Microsoft 365 Community Community Demos, 8 Sep 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Excel API v1.14 Preview: Worksheet Protection Changed event

Description

In this 7-minute Developer-focused demo, after a quick review of APIs releasing in Excel v1.14 Preview, Raymond Lu delves deeper into the WorksheetProtectionChangedEvent APIs. Previously protected worksheets threw an error when access was attempted, now add-in status can be controlled in event logic with a new event handler and the edit button will be grayed out when the worksheet is protected and fully visible and enabled when the worksheet is unprotected. This PnP Community demo is taken from the Office Add-ins community call – September 2021, recorded on September 8, 2021.

Demo Presenter: Raymond Lu, Senior Program Manager (Microsoft)

Stay connected:
• Learn more about the Microsoft 365 Patterns and Practices community at: http://aka.ms/m365pnp
• Visit the Microsoft 365 Patterns and Practices community YouTube channel: https://aka.ms/m365pnp/videos

A

Hello: everyone- this is raymond, so today I would like to introduce the preview api that we're going to release in 1.14, and then I will provide a live demo for the ship. uh Worksheet, protect change events and uh we'll share a scenario for that. So the first of all here is the 1.14 api list.

A

That is the candidate that we're gonna release in in this ignite, so which it include auto filter, which you enable you to clear the column criteria and also it allows you to access the you know: a worksheet tab id which is integer id and the wrench uh president api. uh We have released range to get direct president api, and this one will. uh You can not allow you to get the full president uh ap from this range, and uh we also gonna, really release chart uh data table api.

A

You can um you can make that a data table uh visible or not visible, and also some other control like a formatting uh of the of the data table, and we also provide some more like item or non object.

A

This can allow you to get non object instead of get an arrow, exceptional and last one, but not least, is the worksheet protection change events, and uh I'm gonna talk about this uh today, so yeah for how to preview this api. Actually, um for many of you already know that, but some of the new to js platform, you can change that to 1.0 to the beta and also add previews to uh to to have the intelligence on your api and uh so for product events.

A

uh The the design is quite simple: um uh in the worksheet collection we add a protection protection change hand, event handler and with with the event arcs the event arcs will include these four things and the type worksheet id and source is common for all the events and we have uh the uh production bullying value to telling whether our current status, the production status is a is protected or not, and uh so the scenario is I would like to tell uh share.

A

You is is quite uh simple: it's also very useful when you're doing this kind of interaction with the uh with your uh worksheets, for example, um if the planner he wants to do work on very important worksheets, and he will need you very careful for the anything they want to make and change on the worksheet. So usually he will do is to protect worksheets.

A

You avoid to some unexpected input, so uh sometimes he forget to unprotect it and then directly using the ui to do the interaction, but at that time it will see the error message does throw the exception and then to handle that uh by the id.

A

But with this event that adding can be the status can be controlled in the uh event logic, therefore, they can gray out the button. Instead of the you know, end user, uh uh making making the changes, but without knowing that this is actually not be able to do so.

A

Okay, so uh I will give live demo, but before that he's, this is the simple code that you can you use um right now you can use directly in your excel, so let me switch back to my uh excel. Okay. Here is the simple code, as I just write it and uh which will help me to demonstrate that scenario. So, for example, I'm um I'm doing this kind of uh analyze about the the model that our companies sell. So here uh I want to have uh doing some interaction.

A

For example, I I did as a planner, I just protect the sheets and and and then I forget, I protect the sheet and then I doing some no changes. For example, I add something and then of course we our api will will throw a exception which is access denied, which is uh annoying here. But you know this is a simple uh demo, but with your complicated demo, you a customer may need to do a lot of things and then click uh click the button and then that that will vary uh pretty much annoying.

A

Therefore, we we actually doing this to make it possible to that event to handle those um those situations. So I change it to new behavior. So our new is true. Therefore, it can control that. Let me let me refresh that.

A

Okay, so let me reduce the events and okay, so when I protect events uh protect the current sheet and then they will, uh the events will be fire, and in that events, you can gray out this button, and then user will know that. Okay, what I did I cannot actually you, okay, also create out this one as well, but uh it can let indicate the user know that they cannot access.

A

You know insert anything to their worksheet to avoid kind, this kind of arrow. So when you put unprotect when an user, I'm protect disease, for example, if I doing uh unprotect sheets.

A

Yeah, it can be, it will enable the button that can use it to change the interact with this worksheet, so that is, that is how you use typical use these events in your heading. Okay, that's all for my of my presentation. Thank you so much.

A

Thank you. Raymond okay, yeah, very cool to see those api updates coming out.