Microsoft 365 Community Community Demos, 21 Jul 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Microsoft Excel v1.13 API updates – June 2021

Description

In this 11-minute developer-focused demo, Raymond Lu’s presentation focuses on 2 JS APIs in Excel v1.13 to be released in mid-July. 1) insertWorksheetFromBase64 (top requested API - feedback on API behavior requested) and 2) onFormulaChanged event (new event using custom function to write back to database). Overview, demos and Q&A. APIs will be available on desktop (PC/MAC), online and web. This PnP Community demo is taken from the Office Add-ins community call – June 2021, recorded on June 9, 2021

Demo Presenter: Raymond Lu (Microsoft)

Stay connected:
• Twitter https://twitter.com/microsoft365dev
• YouTube https://aka.ms/M365DevYouTube
• Blog https://aka.ms/m365pnp/community/blog

A

Hello: everyone, my name, is raymond, I'm program manager for excel api. uh Today, I would like to share some updates about the excel js api for 1.13.

A

is the api list that we are going to release in the requirements at 1.13, and uh the good news is that almost all these um code work uh almost done and its own many of them on the way are rolling out. um You will be soon to check the the release version of this api and uh but also some of them. We still have some feedback today. I would like to confirm with you about one one behavior of the one other the ap api. That uh was the top.

A

That was the top ask which is insert from base64 api. uh This api. uh We received some feedback from the github, and uh uh people are asking for whether what's the default behavior when insert worksheet for uh x, uh worksheet from the external workbook, uh whether we should activate it the newly added worksheets or we should we should keep the original uh sheets uh that don't activate the newly added one. So uh I would like to learn your feedback uh which, which you do you prefer?

A

If you have uh some feedback, please let me know which is a better uh way for you to for this api. Okay today I would like to spend yeah one. Second, I would like to I'm not sure whether you can see my chat window. um I will uh these two behavior to you. uh One is the insert worksheet that activate the first sheet, that newly added, which is the current preview behavior, and we receive some feedback that they want to. You know, don't activate a new led newly added window.

A

So if you are have some comments, we you you want to have a first behavior type, one, the second behavior type, two okay, okay and uh yeah. So this this one and next uh today I would like to introduce about a newly added event, which is on formula change events.

A

So these events is to we built this for the uh for the scenario which we called uh right back know that many of the our you know, customer uh engaged many of you and we learned that the writing bags very most important scenario that you you would like to enable in in your adding.

A

So today I would like to introduce one write: the write back solution, which is using a custom function. So here is the example that you, you might try it out in your, I don't add in so so the idea is to adding load the data to the worksheet through the custom function. Here, for example, I have I have my projection for my sales information for the coming year.

A

So I I want to know, which is uh the data is actually coming from the data external database, so the custom function will retrieve the data from the external database and put the render here in these workbook worksheets, which is, I show you here in the picture, and the planner wants to do some change the the budget changing for example. I I would like to change the model three in quarter two, I see the sales might be higher than that, so I can.

A

I can type it I type directly in this in this particular cell, then the number that I tied will need to write back to the database.

A

So uh so I think askley is uh listening to the events, which is the events I will introduce you, which is the on formula change. Events in these events that you adding can do is to uh to get the uh the formula previous formula to analyze the pure formulas. We will know that, which is the right place in the database right right, place right table to write the the newly projected data for model, 3 q2, that information to the database and and after you write those information to your database. Then you can get these return.

A

The original formula, which is the uh the the formula back to that and that's uh and and that one will give- will retrieve the data from the database yeah. So uh here I would like to introduce about the design for the for these events, so the design is like uh when you, when you listen to these events. uh The events are we provide. Those kind of information.

A

First is the formula details. The formal details includes two information, which is a cell address, so indicate what which is this right cell that that you change that and also we present you the. What is the formula or previous formula that you overwrite the end user overwrite and with that you can do uh you can restore the information and also you can you will know what is the right place in the database you're going?

A

We are going to write it back and then the worksheet ide and and the source events, or whether this is triggered by no calls retriggered by the remote and last and also the event type we'll get what type of events. So uh this is pretty much the the sample code. uh It's very easily. Just just tell you what is the um the even arc information yeah. So I would like to give you a a a simple demo of what I what I have introduced to you, but I, as as I don't have the external database.

A

I just want to uh let you imagine that I'm using the custom function, but now I'm using actually just a normal formula. So imagine this is a custom function that can that you're retrieving the data from from the database. So if you, if I am an end user, I can I want to change this formula. I want to say this is a two less.

A

I want to do uh this much of sales for for this quarter, because I I invest a lot of advertisement on that on that uh appearance on that, so I type that so and this information will write back to directly to the base. So if we don't have these events, then then your your custom function is actually lost, you lost it and then uh you cannot connect to the database. However, if I undo it and if I regist the events, if I reduce events then if then I've tried to type it, it's the big number.

A

I just demoed these. uh This idea to you. I just smoked up these because I don't have the external database connection thing, so I want to give you an idea. What is what is uh purpose of this api? So if I project I, I want to change my model 3's projection to 20k and then, if I type enter, then this number. Actually you receive the events.

A

uh The events can tell you that what is the previous formula, and then you actually can get the previous formula to do to do, uh to restore this to the the formula and those formula can retrieve the data from the database, and meanwhile you uh be previous to that you, you can write the write this new number to your database accordingly in these events, so that will solve the problem of writing back okay uh yeah. So that is um uh what I want to uh introduce you today.

A

I hope that these events can benefit for you if you are using a custom function and- uh and uh uh these events will help you on that. Okay, I see.

B

What if multiple cells are changed at the same time like say a paste operation? Will the yeah we get all.

A

Yes, uh it's great question uh josh, so uh if a multiple multiple, if you control a c paste uh the multiple formula- and we will this actually is the ac array, so that would represent the information that we can enable uh the multiple uh ranges.

A

So if you copy paste a big range- and we all can support, but we have a limitation so but, as you know that the payload size is five megabyte, uh actually it's less than 505 megabyte we uh there. It can't be too much uh too too big of the the size but yeah, but we support multiple range, a multiple formula change. Thank you. Thank you for a good question.

C

Okay, it looks like a.

A

C

From john w.

B

This is um for uh excel, desktop, correct.

A

This actually, these events is available not only for desktop, but also all the platform excel online and mac. It will be released to all this platform.

A

You said all you mean desktop two correct desktop and mac and excel web online. Do you know the release date? Oh the release date. uh Actually, I cannot uh uh because the code work is really done. uh That engineer has already checked in the code and it's on the way to uh out maybe uh next month. I cannot tell you the exact exactly date of that event is available, but it will be uh next month in the middle of next month. Yeah. Thank you.

A

Thank you. Thank you for your question, john.

C

Okay and let's we'll just do one more question: there's admin at analysis cloud. So wherever that admin is.

B

Hi, sorry, I'm using my business card. Sorry about that. So it looks like the track changes uh functionality that already exists. That was my question.

A

Yeah, I think I think yeah uh thanks for for your question. Yes track changes. We have events called on change events, so that will track the changes you made in in a normal range. So so, if you change from value to value, then that will be triggered, but this one is more like the uh monitor the changes from the formula, so that will be targeting to uh to the scenario for the custom function right back. So when, when the formula is going is being changed, this event will be triggered.

C

Okay, great okay,.