Office Scripts Tutorial: Introduction to the Code Editor

Key Takeaways

hi in this video i'm going to walk through some of the basics of office scripts which is a new feature in excel for web that allows end users to automate their tasks by recording their actions into scripts and playing it back or editing the scripts to customize your automation needs so let's go into a little bit more details about the code editor itself so when i uh launch the code editor it shows me all the scripts that i own or i have created or recorded and edited and each of these scripts carry a name by default you get like script some number but you can always make uh change like you can go in here and make uh give it a new name and the script themselves are not part of the excel file so that is a key thing to remember the files themselves are stored in a folder in your onedrive for business as a user so it's under my files documents office scripts folder and this one-to-one relation between the file that you see here that has a custom extension called osts and the scripts that you see in the editor as an end user someone who's interacting with office scripts using office skips you don't really need to worry about where the scripts are stored and how they're managed the code editor does everything for you you only need to know that the files are not stored in excel file so that's sort of a key difference compared to how vba macros are which which is actually stored part of the workbook itself and the files themselves are really type skip files on your onedrive they carry a special extension because it contains some additional metadata that office scripts requires but you don't need to deal with this file directly on the on the drive you can just directly edit and save and manage all through the the code editor all right um so next thing is so when you go into edit one of these scripts you will see that the code editor offers sort of full editing capabilities what i mean by that is you can edit you can navigate around you can search and replace and because each script is like one full file there isn't anything else that you need to think about in terms of hey is this calling another script or another file so all is contained in a single file and the editor makes it easy for you to navigate around one cool thing feature because it's this typescript language it offers something called intellisense it's really kind of fancy name for chord completion if i have a variable and that's defined as a type for instance here i have a workbook which is an argument into the main function it's defined as a workbook object which is really the top level object within the object model i can just type that variable and then press dot here and it comes up with all the other methods that that function that object offers so let's say i want to add a table i pick that and then i can open parenthesis and see hey to add a table you need an address variable which is of type string or you can also use range and it takes a second parameter called hash headers which is of type boolean so all of this is driven through the intellisense and you can discover your way around by simply just learning how to read this intellisense you'll also find some helpful description here another way to learn more about like a method so as you type along the intellisense keeps the i see as you can see the it's underlined the current argument that i'm typing so let's say you know a1 through c4 and then when i press the comma for the next argument it highlights hey your next argument is of type i said as i type boolean so let's say i press the like something incorrect argument it highlights saying hey this is an invalid argument it expects an argument of type boolean but you're giving me something that's different so um so this is sort of what i mentioned earlier about typing that typescript offers and it helps with development time work so i i can correct myself and i don't really need to wait till i run to discover that oh i pass the wrong argument it actually helps as you're developing the you know that i've given links to two files here one is for mac os another one for windows it includes some of the shortcut keys uh it hit it has like a compilation of all the shortcut keys that the editor offers and it looks like this you can use this to kind of familiarize yourself with the basic interaction with the editor the things like basic editing navigation search and replace the multi-cursor so i think these four sections will help you navigate yourself around i'm going to show a couple that i use the most beginning with search do control f and i can find a variable and then i can use these uh to go back and forth in the file you can also replace your string and then just replace directly within here the other thing i use is the commenting feature so on a single line or a multi line like this i can do uh control in windows control forward slash you know you can toggle back and forth so that's that's quite helpful another one i use is uh to select uh all all the names and make a change directly so let's say target table name is a variable name as long as the cursor is on or i've selected it i can do control uppercase l and it will select all the instance of that throughout the script and i can make change directly here let's say instead of target table name i just want to call it target table and you'll see that it's making changes in in both places and i can just hit escape and come out of that multi-cursor selection mode i find that useful the right click also offers some core features that you can also use and uh yeah so i think the more you interact you'll become familiar and then you'll start referencing this shortcut keys and you'll have your own sort of favorite set that you will end up using quite often um and then through that you can cut down your time as you edit and navigate around all right so i think i've covered most of the basic aspects that will help you discover and get started with i'll do another video around just the anatomy of the script which is when you see a script what do you see what are the code components so look forward to that video following this one so hopefully this will help you kind of get started and and if you have any other questions or want to see some other demonstration please do leave a comment in the video and that'll help me understand where sort of the next opportunity areas for me to work on all right thanks for watching and hope you try it out thank you