👩‍💻 How to use Office Scripts in Excel & Power Automate

[Music] hey everyone kevin here today i want to show you how you can use office scripts in excel on the web so what are office scripts and how can they help you well if you find yourself doing the same steps again and again in microsoft excel office scripts can save you a considerable amount of time let me give a quick example so let's say at the kevin cookie company every week i get the latest cookie sales data and once i receive it i have to go through and i have to make some formatting changes to it i also have to add a formula that calculates the profit and every single week i have to run through these steps again and again it's a pretty massive pain instead i can use office scripts to record all of these manual steps and next week when i get the latest cookie sales data i can simply play back all of these steps and it can automate all of those different edits and formatting changes that i made so my job is now a lot easier now if you've ever used macros before in microsoft excel or the desktop app this probably sounds really familiar to you what's the difference between macros and office scripts well with office scripts it's all stored in the cloud and the benefit is it makes it really easy to share with others so at the kevin cookie company if we get a new employee i could just hand over the script to them and now they can run it making my job even easier also best of all you can use power automate to kick off your office script so what i mean by this is let's say my manager sends me an email saying hey kevin the latest cookie sales data is now available i can use that email as a trigger to run my script so it'll now format all of the sales data now i'm not really doing anything hopefully they keep me on at the company because i'm not really doing much work today we're going to run through first off how you can record an office script then we'll also go in and we'll edit it we'll make some tweaks and then i'll show you how you can use power automate to kick off one of your scripts alright let's jump on the pc and let's get started here i am now on my pc and to be able to follow along today there are a few different requirements first off you need a work or school microsoft 365 account unfortunately if you have a free personal account this won't work for you also you need access to onedrive and also excel on the web if you have all of those you'll be able to follow along today as a first step head to excel on the web here i am on the excel start page and i've already created a workbook called cookies sold if you want to follow along today i've included a link to this workbook in the description so you can pull all the data from there and then you can follow me as i walk through this let's click on this this now drops me in the cookies sold workbook and i get a sheet like this every single week you get a view into my life here at the kevin cookie company and for all of you who thought that the kevin cookie company was just a cover for tutorial videos look we actually sell cookies at this cookie company see we have the data right here to get started with office scripts right up here on the top toolbar there's a tab called automate let's click on this one this opens up the automate ribbon and we have a few different options here let's take a moment to orient ourselves to what we have right over on the left hand side i can click on record actions and this is very similar to recording a macro here i could go through i could click i can make changes i can make formatting changes and this will record all of those different steps that i take in a moment we'll come back and we'll create our first office script using the recorder right to the right of that over here there's a gallery with a whole bunch of sample scripts i could click into them to see how they work so here i'll click into this one and i can click in and actually see the code of how this script works so this is a good way to familiarize myself with what's possible i'm going to close out of this right back at the top all the way over on the far right hand side there's an option called all scripts when i click on this this opens up a pane again over on the right hand side right here i can see all the scripts that i've created now i haven't created any yet so right now it's just blank to the right of that i have an option called this workbook as soon as someone shares a script with me i'll see all of their scripts appear in this area also all the way over on the far right hand side i see even more samples so here are some of the samples we saw before in this gallery and then i see the addition of another one online there's a massive collection of different samples that i can look at and this is one of the best ways to learn how to code using office scripts you could just look at different samples to see how they did it right up here near the top i also have the option to simply kick off a new script not using the recorder so i could just enter in my code but once again i want to start with the simplest possible method of creating a script so i'm going to go up here and let's click on record actions this once again opens up the pane over on the right hand side called record actions and right now you see that it's recording and this is going to record every single step that i take so any change that i make here will be captured over here in this pane so this really works best to record steps that you're pretty familiar with if you're clicking around and you're clicking on different buttons remember it's going to keep track of all of that also another good technique to pulling together a script is you could record in chunks maybe you want to make a formatting change here and maybe you want to change the formatting of some numbers here you can record that stop and you can take that recording and integrate it into another script that you have lastly as i go through this let's say that maybe you make a change that you didn't intend to make you can stop and you can restart again or as an alternative you can just leave it in and then we can clean it up in the code later in a moment i'll show you exactly how you can do this okay enough talk i have some work i need to get done i need to format this report with our cookies sold data and keep in mind i do this every single week i know this is terrible to have to do this in a job but hopefully we can automate this now right here i want to bold all my column headers so i'll highlight them and press ctrl b to bold them and i'll also bold this column right here with all the different cookie types that looks good so far now over here i want to calculate the profit so for this i'll enter in the formula i'll take the revenue minus the cost so here i'll do five minus two i'll close the parentheses and next i'll multiply it by the number of cookies sold so there's my profit formula now as i'm making all of these changes so here i bolted this row i bolted this column here i put in a formula you'll notice here that it's recording all of these different actions here i'll take this formula and i'll apply it all the way down i also want to calculate the total profit this is a pretty profitable company here i'll enter the sum formula and here let me enter this up and let's see how much profit we had wow 2.3 million for a week's worth of work that's pretty good next i also want to make sure that my revenue and cost column are formatted as a currency so i'll highlight all of these cells and then i'll go up to home and over here i can select currency formatting i'll just set it to the usd and lastly i want to make one more change let me just select this cell and let me paint it yellow i didn't really mean to do this but it looks like it's been recorded now so i'll show you how we can go into the code later on and remove this step for now i'm all done recording my different steps so i'll go right up here and click on stop and my script has now been recorded and right now it's referred to as simply script one now that won't help me find it again in the future so let me click up here and i'll change the title i entered in cookie sold formatting and so my script is now all ready to go let me zoom out just a little bit here and let me remove some of the changes that i made so here i'll remove the profit column let me get rid of the bolding here and let me get rid of this color in this cell so i'll set no fill so right now the data is pretty close to what it was when i first started now let's say i got more data like this i can simply come over to my scripts and once again if i wanted to access this without this pane open i could go to automate i could click on all scripts and here i see the script right here next i can go down and click on run once i click on run once again you'll see all the changes that i went through and i made are now automatically applied to this data so my life is already getting a lot easier right up here near the top you'll see that it saved this script under my files this is in your onedrive let's take a quick look to see where this file goes to access onedrive go up to the waffle in the top left hand corner and here i'm going to click on onedrive this opens up onedrive for business here i see all of my different files here i'll click into the folder called documents and within documents there's another folder called office scripts when i click on that here i see a new script file called cookies sold formatting so this is where all of my scripts are saved back in microsoft excel here i have another sheet with all of the cookie sales data from april 10th and when i click into this this once again is just like it was on the other sheet before i started with all my edits here i could simply click on run and look at this all of my editing has now been applied here so once again my job is pretty easy and i could use this script on any one of my worksheets so far we've just been recording all of our steps and we ran them against two different sheets so congratulations we're already starting to automate things but what if you want to see how things work under the hood here i have the code editor open and here i see the script that we just created here i can click on edit or i can click over here and this will drop me into the code that we generated and look at this to make all of those changes excel executes all of the following code now you might be thinking wow this looks really scary but actually it's pretty easy to understand and we'll walk our way through this to help you understand at a very high level you might be wondering well what is this what programming language is this the action recorder that we used earlier it generates code in typescript it's basically a programming language and typescript is a superset of javascript now you could come in here just as well and you don't have to use the recorder and you can write code directly in javascript or in typescript either one whichever one you're more comfortable with also if you just want to use the recorder that's okay too you don't actually have to write any code but having just a very high level knowledge of how this works will allow you to tweak it so it works to meet your needs before we go through and look at the code to understand it at a high level i do want to make one quick change right up here in the top right hand corner there's an ellipsis let's click on this within here towards the bottom there's an option for editor settings let's click on that within editor settings you can make several different changes the one that i made is the font size i wanted to make it a little bit easier to see so i adjusted this to 18. you can adjust this to make it easier to read the code choose whichever value you like the best now that i made my selection i'll click on the x also before we jump in and we run through this code if you have no programming background at all but maybe you're curious and you want to learn about just the fundamentals of programming i pulled together a video recently on how to get started using python python is one of the most popular programming languages the good thing though is when you look at python you'll also understand any other programming language as well the fundamental concepts are all the same you can watch that video and i've included a link in the description of this video okay with that out of the way let's take a look at this code to see how this works right up here at the very top there's a function we're basically calling a function what this does is when we click on the run button office script says okay i need to run this function now and it passes in a parameter which is the current open workbook so this way excel knows what workbook it should run this script against next within the code here we need to define what worksheet we want to work with so this up here this is the overall workbook called cookie sold and then right down here across the bottom of the workbook i have two different worksheets each one with a separate date so here i'm defining what worksheet i want to use and this is pretty simple i'm simply saying i want to use the current active worksheet so here i'm currently on this worksheet and so if i run this script it'll run it across this page that we see right here here if i shift to 4 3 well now this is the active worksheet so if i now run this script it'll run it against this instead of saying use the currently active worksheet i could also simply define a specific worksheet and in a little bit we'll come back and we'll do that okay so so far we have our function main we're also defining what sheet we want to work with let's now look at what happens next and what's really nice is this is the comment you'll see that office scripts automatically comments against the code so if you remember early on i selected all of these different cells and i made them bold so that's what this step here is doing it's making those cells bold and here what's really nice is the comment tells us exactly what it's doing so first off it selects these cells a3 through a9 or it gets that range so right here it'll select that range first it has to get the formatting it has to get the font and once it has all of that it can set it to bold and here it sets bold to true now i could go in and if i modify this to false and let's say i run it again here now you'll see that it sets these to not being bold so just a quick example of how i could change some of the properties i'm going to change this back to true if you remember when i went through and i recorded my steps i set this one cell to yellow now i didn't intend to do that i want to show you how we could remove a step by going through our code finding it and then removing it so if you remember i did that at the very end so here i could scroll down through my code and here at the very end i see that it says set fill color to this value for cell c9 well if i click in here this is cell c9 so that's another easy way to identify that step it also so happens to be that this code right here this represents yellow on the sheet now right here i can see all the code that made that change and if i didn't want it i could simply highlight this and then i could delete that and that code is now gone let me go back and here i'm going to remove the fill color i'll set it back to white and here i can now run my script and we'll see that it no longer applies this formatting so my script now ran it made all of the changes again and this cell is no longer yellow so we just made a very simple edit to our code now this is just a very simple example of how you can look at the code to understand how it works now of course because this is using typescript or you could use javascript you can write office scripts that are much much more complicated than this you can set up classes additional functions you can set up loops you can even add conditional statements so you can make this far more complex and you can run some pretty interesting applications using this next i want to show you how you can share this script with others before i share it i'm going to go up to the top and i'll click on save script this is now saved the script on my onedrive so it's already in the cloud over here i'll click on back this brings me back to the view now where i can see all of my different scripts i'll click into this one cookies sold formatting with this card view open i can click on this ellipsis down here and here i have the option to share my script let's click on this this opens up a prompt and basically anyone who has access to this workbook i can also make it so they have access to this script that sounds good so i'll click on share and just like that my script has now been shared so if anyone else comes in and opens up this workbook they can click into this workbook and they can see all of the different shared scripts and here you see cookies sold formatting is one of those shared scripts now that i've shared my workbook i want to show you how we can use power automate to kick off your script and before we do that i simply want to undo some of the changes that this script made so here i'll delete the profit column and let me just get rid of the bolding here i'm going to leave the currency formatting as is if you've never heard of power automate before it's kind of like the service if this then that basically you can set up automated workflows between different applications and the really cool thing is you can run your scripts without even having to be in your workbook so so far to run my script i'd have to click on automate all scripts and then i could hover over and here i could run my script but i could have it start for all sorts of different scenarios for example maybe i receive an email i could have the email serve as a trigger for my script or maybe i get a message in microsoft teams that can be a trigger for my script i could even set up a schedule maybe i could set it up so every single week on monday afternoon it simply runs this script so i could decide when it should run to get to power automate let's go to the top left hand corner and once again let's click on the app launcher or the waffle within this list you should see an option for power automate if you don't see it in this initial list you can also go down and click on all apps i see it here so i'll click on this this opens up power automate and once again if you've never used it before you can see a whole bunch of different examples of when you can use it over on the left hand side i'll click on create to create a new flow within create a flow you'll see a few different examples of the types of different flows i can create and once again i mentioned that you can have all sorts of different triggers for your flow you can have it be automated you can schedule it you can even set up desktop flows now for now i just want to show a very basic example so i'll click on instant cloudflow right here basically i'm going to initiate this flow this opens up a screen where i can start building my flow here for the flow name i'll simply call this format cookie sales data once i finish typing in my name i'll select manually trigger a flow once again i can have all sorts of different triggers for my flow right down here i'll click on create here now i can start building out my flow and right here i see that the first step is just manually triggering this flow i'll click on new step and here now i can choose what the next step is now i have this script in excel online and this is a work or school account so it's excel online business i'll select this option as my next step for excel online there are all sorts of different actions i can take now i want to run my script so right here in this list i see the option to run the script i'll select this option right here next i need to define where my file is right here first off i need to select the location and for my location this file is stored in onedrive for business so i'll pick this next i need to choose a document library i'll click on this and it's in onedrive so i'll select that value next i need to navigate to where my spreadsheet is saved i'll click on this file picker right here and this opens up a view of all of my folders in one drive and at the very bottom i see my different spreadsheets now my spreadsheet is called cookies sold so i'll select this one now at the very bottom i need to select the script that i want to run when i click on this drop down here i see that there's only one script and it's called cookies sold formatting so i'll select this one right here now that i'm all done i'll click on save to test out my new flow over on the left hand side i can click on my flows and right here i see format cookie sales data i can click on this run or this play icon to run my script let's click on this this opens up a pane over on the right hand side i'll click on run flow and here it said your flow run successfully started that looks good next i'll click on done back now on the excel start page i'll click into my cookies sold sheet and let's see if the formatting was applied and look at this here i see all of my different column headers have been bolded and here all of the cookie types i also see that it added the profit calculation and it also summed it up that's pretty awesome power automate went through and it updated all this automatically now you might be wondering well i never defined what worksheet it should update now it simply went in and it updated the first sheet but what if i wanted to update the second sheet instead well we can update that in the script to let it know what specific sheet we should update once again let's go up to the top toolbar here and let's click on automate and then over here let's click on all scripts this once again opens up the pane on the right hand side let's click into the script that we created called cookies sold formatting here i'll click in so we can see the code within the code right near the top here you see where it's selecting the worksheet and here it's simply selecting the active worksheet now i want to change this to call out a specific worksheet so right here i'll leave most of this in place but instead of having it say get active worksheet i'm simply going to type in get worksheet and here i'll type in 410 2021 so that references this specific worksheet right here this all looks good so once again i'll save my script before i run this script again through power automate i'll click into my second worksheet the one 410 2021 and here let me just remove some of the changes so i'll get rid of the bolding and here i'll get rid of the highlighting okay this all looks good so i'm going to go back to power automate and let's see if it can make these changes back within power automate i'll select my new flow and let me click on the run icon right down here i'll run the flow and it looks like it successfully ran it let's now jump back into excel and look at that right now i'm back in excel and it looks like it automatically applied all of my changes to this specific worksheet so far we've just looked at a very simple example of how you can use power automate to kick off your office script but you can also use much more advanced scenarios you can also take data from power automate and you can feed it into your script let's say for example you receive an email you can take the sender the subject and you can feed all those values back into your script alternatively you can also take values from your script and from your spreadsheet and you can feed them back into power automate let's say for example maybe you have an on call list and you want to send an email to whoever's on point maybe your descript will determine who's on point and then it can send that data to power automate and that can connect with outlook that then sends out an email all of that is possible with office scripts together with power automate hopefully by now you're starting to get a sense for how much power office scripts have and when you combine that together with power automate you get well a lot of power if you're curious about how you take this to the next level and all the different things that are possible with it i've included a link to a url in the description of this video that'll go through a lot of additional detail here you see additional getting started information you'll see some of the scripting fundamentals and one of my favorites you can also look at lots of different sample scripts so here you see lots of different examples of things that you can do using scripting this is in my opinion one of the best ways to learn what all is possible all right well that was a quick look at how you can get started using office scripts if this video helped you please give it a thumbs up to see more videos like this please consider subscribing also if you want to see me cover any other topics on this channel leave a note down below alright well that's all i had for you today i hope you enjoyed and as always i hope to see you next time bye [Music] you

Channel: Kevin Stratvert

Views: 223,026

Keywords: kevin stratvert, office scripts, script, excel, excel script, excel code, vba, macro, web, power automate, automate, power, microsoft, stratvert, microsoft office, scripts in excel, typescript, type script, javascript, java script, code, code editor, record actions, record, automations, microsoft 365, trigger, share, onedrive, business, workbook, worksheet, work sheet, work book, how to, create, excel macros, excel macro, macro excel, microsoft excel, beginners, macros, in excel, macros in excel

Id: il7YS7Dx228

Length: 25min 23sec (1523 seconds)

Published: Thu Apr 08 2021