Office Scripts Tutorial: Scripting for Beginners

Key Takeaways

hi everyone i am ayoshi gurg and i am an engineering manager on office scripts team last time we introduced you to the office scripts code editor which is one of the tools that can help you automate your workflow in excel on the web when you create a script using code editor you will find a bunch of code you may be unfamiliar with this video is catered towards folks that might have little to zero coding background we will show you how to access the code editor explain the core elements of script code walk you through a small code snippet using our action recorder now before we dive in let's run through some facts about office scripts a script contains a set of instructions to automate tasks in excel you can think about it as the recipe for your computer to follow and excel as the kitchen where you use the recipe for the computer to understand the recipe we need to write instructions in a language it understands scripts are written using typescript which is an open source language that builds upon javascript in this video though we will assume minimal no background in either javascript or typescript now let's see where code editor lives so first we would select the automate tab in excel then we can click on the all scripts button here so notice there are no scripts that i have right now so i would click on a new script button which automatically creates this new script for me now when you create a script in code editor you will always find this chunk of code otherwise known as the main function earlier we said the script was like a recipe to add on to this analogy each function is like a mini recipe you can have as many or as few as you want but their purpose is to split up the instructions into smaller chunks what differentiates the main function is that it's where the script begins execution for the purpose of this video we'll focus on just the main function however the structure of functions is generally very similar the first thing you'll notice about any script is the words function main workbook excel script dot workbook in most cases all the main function needs to know is which excel workbook to run the script on and this precisely does that it states that we require an excel workbook to run the script and that our current workbook referred to as workbook meets that requirement we always run the script on the current workbook so we don't need to worry about changing the header now right after the function header are the curly braces within the curly braces is where we'll include the steps the mini recipe needs to complete to get our desired result we call this function body and that's where the fun comes in to give you an idea of how the function body works let's say i want the script to type hello world in the first cell now you do not need to worry about understanding how to write that code as we have a recorder to the rescue so i'll start off by clicking on the record button you notice that this has started recording right now i can just type in hello world into the first cell now the recorder has recorded this action and shows here that we have edited cell a1 which has been recorded and that's all we want to do for now so i will stop recording so before i show you the code that it generated let's quickly try running the script that we just recorded so i'll create a new sheet so that we have a blank canvas let's click run so it has run the script and you notice hello world shows up here now let's go behind the code and look at that so i click on edit to access the generated script you'll notice that any script you record the function body will almost always start with the following line of code that is let selected sheet is equal to workbook dot get active worksheet earlier we described anything between the curly braces as a step in the function so what is this step doing we know that workbook refers to the current workbook the script is being run on now if you hover over get active worksheet we now see the full story where it tells us it gets the currently active worksheet in the workbook now let's move on to the left hand side of the equal sign let selected sheet is saying that we are setting the active worksheet to a variable named selected sheet in other words we are going to name the worksheet selected sheet from here on out so we don't have to keep fetching it again and again now you can see this variable selected sheet to do practically anything you want on your worksheet you can do you can add shape change text create graphs you name it and we do that using methods in this example we are actually doing two steps in one line of code by a method called method chaining sounds super fancy but basically means we're first getting the specific cell a1 and then setting that cell's value to hello world we could have also written this as two separate lines of code so selected sheet dot get range and let's end that here and store this in another variable and then selected cell dot set value hello world right now this would do the exact same thing that we were doing earlier where we chained the methods selected sheet.getrange dot set value all we have done is split those lines up we also generate comments here to tell you what is the next piece of code doing so that should also help you understand the code that is auto generated now let's save the script rename it so okay and save so we have successfully created our first script ran it and edited it now what if i want to modify the script to also highlight the hello world text how do i go about writing further code in here when i do not understand the language yet and your recorder is at the rescue again so let's start recording again okay so i click on record so it has started recording again now i've already have this typed in so i don't need to type that again i just select it and choose to fill color so let's fill it with some color and notice that it has recorded set background color to the color on a1 so stop now let's jump right into the code this time so notice that here we have generated a similar function and a body although the body does different steps than last time this time what it is doing is on the selected sheet it is first getting the cell a1 then getting the format of that cell the fill on that cell so this gives its the current values and then it is setting those values to the color that we selected again the method chaining has been used you could do these as separate lines as well now what i'm going to do is i will just copy this piece of code and go back to my previous script so this is where my existing script was remember i created set hello world and i will paste my code here so now this script is doing two things first it is setting the range a1 to be hello world and then it is filling the color to be your color okay let's save this and try it out so i'll create a new blank sheet now what i expect the script to do is do two things as i mentioned to also type in hello world and fill the background with yellow so let's run it watch a1 in action right now it's empty and when i run there you go we get hello world with the color filled in and you've just learned how to modify your script without learning the language we recommend recording workbook actions using the office scripts action recorder and reusing the auto-generated code in the code editor to put together your own script if you're unfamiliar with the language itself so in summary a script is like a recipe for your computer to follow and excel is the kitchen where you use this recipe excel script is made up of functions which you can think of as many recipes the main function is the entry point for any script it kicks off with the function header that tells the function the ingredients it needs to run successfully so when you see the words function main workbook it's really saying to run the script you need a workbook and which workbook you want to run the script on current workbook then the curly braces are where you write the steps for your function using the action recorder can be a great way to start learning to code especially because of the automatic comments selected sheet will be your best friend it grants you the ability to change anything in your current excel worksheet so what's next give us feedback we'd love to hear your thoughts questions and suggestions for demos you'd like to see next and if you'd like to join us in shaping the future of office automation please join our focus group try out our feature and get started at aka.mstryscripts and follow our journey by liking this video and subscribing to our channel for latest updates