Microsoft 365 Community Community Demos, 14 Dec 2021

Previous Meeting Next Meeting

⏯

youtube image

►

From YouTube: Script buttons for Office Scripts

Description

What are Office Scripts, target user and usage scenario? In this 19-minute developer focused demo, Nancy Wang explains Office Scripts buttons and how you can get involved evolving Office Scripts. Create buttons in Excel using the Action Recorder (capture actions and record them into a script – great for seeing how the automations are built – includes comments!) or the Code Editor (JS/Type Script call Office Script APIs). Also, Power Automate integration. Automations sharable within tenant. This PnP Community demo is taken from the weekly Microsoft 365 Platform Community call recorded on December 14, 2021.

Demo Presenter: Nancy Wang (Microsoft) | @nancyatMSFT

Supporting materials:
• Portal – Office Scripts | https://aka.ms/office-scripts
• Documentation – Office Scripts documentation | https://aka.ms/officedevscripts
• Samples - Basic scripts for Office Scripts in Excel on the web | https://aka.ms/officesamplescripts
• Feedback - Office Scripts Makers | https://aka.ms/oscripts

Learn more:
• 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

Thanks again, everyone for joining today, um I'm nancy and I'm from the office skips team as a pm, and I'm really excited just to be talking about one of the new features we're releasing um and also just give people an overview of what office scripts even is.

A

Since we are a pretty new feature- and I imagine some people are like well, I haven't even heard about what office gifts is yet, so I will be doing both those things during this meeting today, um but first, I kind of wanted to get a sense of people's automation experiences. So far, so you know feel free in the chat or you know, with the teams reacts just to share a little bit about how you currently automate using office.

A

If you don't that's totally okay as well, maybe this call will inspire you, but would love to just get a way of how people automate very cool some people using office scripts already or I'm hoping so, but a lot of validation for me and cool using powershell. That's very interesting as well awesome. So I see some people have some experience with vba. Maybe a little bit with office skips as well. I will spend some time just giving an overview of office skips but feel free to drop questions in the chat. Oh and power.

A

Automate is another one I didn't include on here. I apologize power automate, but that is also a very viable automation solution but, as I said before, feel free to drop questions in the chat, I will be moderating that and also you know, interrupt or anything um trying to engage in conversation as much as possible. So this is what's on the agenda for today. I want to cover three main questions: um what is office scripts? What are skip buttons and then how can you get involved in using our feature, getting started and giving feedback?

A

So we're really going to start off with this first question, what is office skips, so this might be refreshing material. Since I know some people might have office scripts experienced on the call, but you know hopefully this information will be insightful. If you don't have this much experience. So what is office scripts?

A

I want to start off with the mission statement, which is to empower office users to easily automate tasks and processes to run their businesses efficiently, anytime and anywhere, and so the key places that I want to highlight here is that this is for office users running their business processes. We currently are available on excel on the web as an automation platform for commercial and educational licenses and then the second part of this right.

A

We want to be able to allow users to run these automations, anytime and anywhere, and so currently, what this means is that we're in the browser, regardless of what browser you're using as long as you have an internet connection, you're able to run these automations and we want it also be possible for you to you know, even if you're on vacation, to be able to schedule these automations to also happen. And so I think in the chat someone had asked about power automate.

A

We do have an integration with power automate that allows you to essentially automate those automations, so you can schedule them to run even if you're away- and I know we talked a little about like vba office add-ins- those are also automation technology.

A

So what makes office skips different right, and so this graph is really just showing how our team thinks about the different automation, solutions um from the axes of the different platforms that you can use desktop to web, as well as how easy it is to pick up the programming language from approachable to end users, to more of a pro developer audience you'll notice that office scripts is more geared towards those end users that might not have a huge amount of programming experience, but also are working in a more collaborative environment in their workplace and that's part of our goal to make automation as accessible as possible.

A

You know we don't believe that you need to have a computer science degree in order to automate your tasks within your spreadsheets, and so we think this is the differentiating factor of office. Skips and I'll. Show you a little demo to kind of show some features that we've done to be more intentional, this regard, and then I would say the second part of this I mentioned earlier, being collaborative in the work uh in the work and web environment.

A

um So we do want users people to easily share these scripts with other people. So even if you're feeling a little lazy- and you don't want to create an automation- you can at least benefit from the automations that other people have created or if you are the one, that's creating the automation. You're able to share that with other colleagues. Have them benefit from that as well, currently see a question about power automate being in the bottom right. um So actually this chart is really specific to office itself.

A

I would say: um power automate's not captured on here, but I'll get into that a little bit later and feel free to ask the question again if you feel like it's a little bit unclear what the relationship is between office, scripts and power automate, um but good question. Thank you jim all right. So I'm gonna hop into just like first showing the functionality and then going into a demo and then again feel free to continue uh bringing questions into the chat and I'll raise them here as well.

A

So this is what office tips currently looks like today. If you go to the automate tab in excel on the web, you'll find a couple different key features. One is the action recorder and for those of you that are more familiar with vba, then this might seem very familiar to you. It is a way to capture actions within the workbook and record that into a script. We really recommend this. If you don't know too much about the programming language, um it's a great place to start and just see like how the automations are built.

A

We also have a code editor, and this allows you to program your scripts. If you do have that javascript typescript knowledge, we use the same apis as officejs, but in a more synchronous manner. So we call them office, skips apis, and I will show you that later as well in the demo and then the last thing I want to call out here is that you can actually share your scripts with other people.

A

So if you see that people icon, I know it's a little small on the slide, but that just indicates that those are shared scripts, and so anyone that has access to the workbook is also able to go to the automate tab run those automations. But if you think that's cool, I'm really excited to share with you. This new feature that we have for sharing scripts more easily as well, so we recently released this new feature called script buttons to um production or it's still currently going through the rings.

A

So this is kind of like a early sneak peek. I guess if you haven't gotten the feature yet, but essentially what this feature allows you to do is run your skips by selecting a button within the workbook itself, and so the goal here is that the person doesn't have to go to the automate tab and identify the right script anymore.

A

They can just select the button where they see it and we hope this allows users to use your automation as long as they have access to the workbook essentially and because we are essentially, this is like a shape within the workbook. You also can customize it to meet whatever fonts color preferences that you might have I've seen.

A

Some people do some pretty cool stuff with this, so I'm really excited to see how you all try this out or learn how to use it and then, as you see on the screenshot on the right, the way that you can access and see- if you have this feature today- is if you go to this overflow menu and then select add button.

A

I'm going to see if there's any questions in the chat and then I will hop over to the demo. Let's see yes, scripts are stored in onedrive. That's a really exciting thing for us as well. um That means that it's stored in the cloud and that you can really use it anytime anywhere.

A

um Can we prompt the user for input, whether in a dialogue, tab or cell? So that's a great question. Thanks again jim, we don't currently offer that functionality. um I think that's something that we could be exploring a little bit more feel free to drop in the chat. If there's like a use case, that comes to mind for something like that, because you know actively, we are starting to think about what are the next steps for either this feature or future features to come?

A

Oh, yes, is obviously placing macros. I think we do get this question quite a bit and just answering that question office scripts and vba they live to coexist. um You know you'll notice in that chart that we have right here. We didn't say that bba is going away at all. um So I want to be really clear about this because I know with the introduction of office scripts. We do get these questions of like well, what's happening to bva. Now nothing is happening to vba.

A

We think that office scripts and vba can live in harmony because optoscopes is more. You know if you're working in a more collaborative environment where you want to share things, then we recognize that that can be a really good solution, because we're really focusing on that modern and collaborative work environment. But we also recognize that office scripts can't do all the things that vba does. For example, I think vba you can access different files within the spreadsheet, like just one spreadsheet itself, because of the way that office service was built.

A

We want this to be in a more secure environment and we're thinking a little bit more. As I said about collaboration, those are some scenarios that vba will do that office. Gifts doesn't currently have to offer, and so um there are a couple differentiators. I think what I'll do after I give this demo is I'll drop, a link in the chat with some documentation, so you can learn more about the differences between vba and office scripts.

A

Thanks so much for the question, thomas cool- and I see I just want to make sure that I get through all of this. So I might answer some more of these questions as after I run through the demo, but thanks again for the questions, this is awesome, so I'm going to start sharing another screen with the excel workbook on it, and I'm really hoping that this works out. Well, as I was testing out the demo before this call.

A

Okay, so here I am in excel on the web, and I just want to run through a quick demo of what our feature does and also maybe an example scenario that you might see in the workplace. So what we have here is a table of um just like different products. What what is what has been sold by different countries at what time, and so the goal of this project, let's say, is to create a pivot table of all the total products that are sold by each country.

A

This is what the input looks like, and then we expect the output to look something like this. We have a table and then you're also able to filter this table by. I guess the different kinds of products that are being sold, so maybe fruit, just see just the fruit on here or maybe just vegetables.

A

Okay, so one way that you could do this is pretty much manually and I can just walk you through that as well. I'm just gonna copy this workbook.

A

A

So I would say, probably a manual way of doing this is just go in here. You might insert the pivot table.

A

And based on the filters of product category amount and country.

A

And we can see, this is pretty much what we want. I think we have a couple blanks here, um but this is the pivot table and then you might say I want to create the pivot chart based on this.

A

There we go uh so this is how you might do it manually. um The really awesome thing about this is that if you're doing this, let's say on a repetitive basis, then you can actually automate this task using office scripts, and so here I am selecting the automate tab that we saw in the screenshot earlier you'll see that you can actually record all these actions using your action recorder, because my demo was being a little wonky this morning.

A

I'm only going to show you that very last step of creating the chart itself, but technically all these things can be done recorded into a script for you using office scripts, and so I'm just going to show you that last bit about creating a pie chart based on the data and you'll see here it is creating the pivot chart for me, and even when I move it around, it is getting the latest um coordinates of my excel workbook.

A

One really neat thing about the action recorder that I didn't mention earlier is that if you decide to you know, if you just made a mistake in your recording, then it will just update to the latest code, and so you won't have those mistakes in your recording. I think that's one differentiator between us and vba in some ways- and I do want to show you a little bit of what the code looks like after I stop recording. So I'm going to go to that really quick.

A

If I click on edit, this is the typescript javascript code that I was mentioning earlier. um Something that we do is. We will automatically comment out or comment parts of the feature so earlier. As you can see, we recorded the insertion of the pivot chart and also the moving and resizing of the chart in the workbook itself, so that we hope that this makes it a little bit easier for people to understand.

A

What's going on in the code and also if you're trying to learn a little bit more about you know what each part of the code is doing, then you can look at these comments over here and I think what I'll do next is. I will go to back to this input and show you a little bit about this already created a script from earlier. So if I go back to the generate pivot chart and table, I just want to show you how I got to this button in the first place.

A

So if I select this you'll see in the overflow menu, I have this add button option and it just creates a button for me wherever I have my active cell. So if I decide to put it here, the button would show up here as well and you'll see that it just automatically shares that script with everyone within the workbook. So if I decided to share this workbook with someone that has the same tenant as me, then they could also use this script cool.

A

So I think I got a question about the power automate and I want to show that as well. I'm going to first run this script, so you can see exactly what's happening as a reminder, this was the expected output and if I select this script, let's see here it's loading, it's going to run the script and then it actually creates this pivot table on another sheet. So in sheet 3 you can see that it's created the pivot table and the chart that we were expecting from earlier.

A

ah It looks a little different. These are the filters.

A

There we go okay and then so I'm just going to quickly show the power automate piece of this as well. Since I think I got a question around it.

A

If I go to this overflow menu again you'll notice, one of the options is actually for creating a flow, and so we do have a power automatic integration that allows you to schedule a script to run within excel, but we've also see some really great community scenarios where you can connect office scripts with the contents of a outlook- email- maybe like a team's message, so it is very much something they can use as a connector to other services as well.

A

This is just the main one. I guess, but you know I'm happy to show a couple links at the end at this time, with links for documentation on additional scenarios that you can try out here, really excited to see what people do with this one.

A

I can't see the chat anymore, so I'm gonna go back into the presentation and feel free to continue asking questions. I just want to make sure I get through the rest of this demo because I recognize I don't know how am I doing on time so far.

B

uh We'll need to wrap up here by the next couple minutes, but I think you're, okay, for just a few more.

A

Okay sounds great.

A

All right, yeah, just a few more slides and I'll answer the rest of the questions in the chat, then, probably, but again, thanks so much everyone. I think it's really great to just get this feedback from people, because this is pretty new for us and especially for skip buttons. We want to get the feedback early as it's still rolling out to all the people within the production ring all right.

A

So the last two things I want to cover one is just how to get started and the other one is how you can give feedback to us whether or not you use office scripts. So if this is your first time, learning about us here are three links that we on the team think that might be helpful just to get started. um One is the link to our portal page, which is kind of like the home page for office scripts.

A

Another one is the documentation and that's where you can kind of see how app scripts work, but also look into the api if you are a little bit more familiar with programming. The third piece is the sample scripts that I was talking to you about, of how you can integrate with power automate, but also how you can just use our feature for automating some tasks in your workflow cool, and the last thing I have here is just about how you can give us feedback.

A

So this is definitely a great forum for this, and I really appreciate the feedback that I see in the chat so far. But you know you don't have to just wait until our demos. You can also give us feedback throughout the year through our green room, and so this is an opportunity for sharing automation requirements. You might have informing the future priorities as well as viewing some early designs of our investigations.

A

This is a nda group that we have going on and we recognize that not everyone knows office scripts. So we want to make sure that anyone who has an interest in automation or an interest in automation in office is able to join feel free to sign up. I will send that link out in the chat as well for aka dot, ms or scripts, and with that that is the end of my presentation.

A

So again a huge thank you to brian andressa for giving me this chance to present today and then everyone for listening and on my conversations so far. I think this has been super helpful and it's really exciting to be able to talk to the audience as well. So.

B

Absolutely thank you so much for this nancy. um Two quick questions. I got asked a couple of times. If you would mind answering before you drop um first, one is: will this be available for desktop as well.

A

uh Yeah great question, so um I think going back earlier to the mission statement that we had set out. So something that we have mentioned in here is that we want users to be able to run automations, anytime and anywhere, and so, when we think about cross-platform. That is very much in that anytime anywhere piece. I would say we are starting to investigate what it might mean to be on different platforms besides excel on the web, since we recognize not everyone's going to be using their browser to do their work all the time.

A

So all I can say right now is that we are looking into it. um I can't really promise any dates or features beyond that, but.

B

A

For the other question, yeah.

B

The other question was around: can you run this unattended and I believe the power automate option would fulfill that. But if there's anything else that you can share, that'd be helpful.

A

Yes, so I think I just want to make sure I have the same understanding unattended as the person who asked this question. But, yes, you could run this unattended by selecting the create flow and then going into power automate to schedule that script to run for you and I just selected the slide because I know has that create blow option in the overflow menu.

A

But I'm happy to answer any more questions in the chat as well.

B

Yep we've got a list of other ones in there, but if you'd like to answer them in chat, that'd be helpful and then we can move on to our next presenter so nancy. Thank you. So much for coming on to presented with us today really great information and people are people are really loving this with all the reactions. So thank you again.

A

Yeah. Thank you again. Everyone.