This page has 0 threads | Add post

Courseware

Introduction to Office Scripts course manual

Here are the chapters included in this course manual (we use exactly the same manuals for our classroom and online Office Scripts courses).

Click here to download a sample of the first two chapters of this Introduction to Office Scripts manual, or review how to license our courseware.

Chapter 1 - GETTING STARTED▼

Section	Title	Subsections	Page
1.1	Introduction to Office Scripts	What are Office Scripts? What do I need to use Office Scripts? Can’t I already Automate Excel? Why use Office Scripts and not VBA? What are the Limitations?	7
1.2	Preparing to Write Office Scripts	The Automate Ribbon Tab The Code Editor	9
1.3	Writing Your First Script	Creating a New Script The Basic Structure of a Script Adding a Comment Writing Basic Instructions Adding More Instructions Running the Script	11
1.4	Code Editor Settings	Changing Code Editor Settings A Note on Visual Studio Code	14

Chapter 2 - WORKING WITH SCRIPT FILES▼

Section	Title	Subsections	Page
2.1	Office Script Files	Viewing Existing Script Files The More Options Menu Creating a New Script File Opening an Existing Script Renaming an Open Script Deleting a Script	15
2.2	Saving Script Files	Where Office Scripts are Saved Saving an Open Script The Save As Option	17
2.3	Sharing Scripts	Sharing a Script Stopping Sharing a Script	18

Chapter 3 - WRITING OFFICE SCRIPTS▼

Section	Title	Subsections	Page
3.1	TypeScript Language Basics	TypeScript Language Elements	19
3.2	Tools for Writing Code	IntelliSense Showing More Information Showing Help Popups The Context Menu The Command Palette	20
3.3	Writing Neat Code	Writing Comments Commenting Out Code Writing Long Instructions White Space and Indenting Auto Formatting a Script Using Semicolons	22
3.4	Dealing with Errors	Spotting Compile-time Errors Viewing Error Information Cycling Through Errors Fixing Errors Automatically	25
3.5	Getting Help	Microsoft Documentation Other Documentation Recording Scripts Searching for Help	27

Chapter 4 - RUNNING SCRIPTS▼

Section	Title	Subsections	Page
4.1	Introduction	Running a Script Stopping a Script that is Running	29
4.2	When Things Go Wrong	Spotting Runtime Errors	30
4.3	Using Buttons	Creating a Button Using a Button to Run a Script Editing a Button Removing Buttons	31

Chapter 5 - RANGES▼

Section	Title	Subsections	Page
5.1	Referring to Range Objects	Referring to the Active Cell Referring to a Cell by Reference Referring to Multiple Cells Using Named Ranges Using Row and Column Numbers Multiple Cells using Indexes	34
5.2	The RangeAreas Object	Referring to Multiple Separate Ranges RangeAreas vs. Range Objects	37
5.3	Getting and Setting Cell Values	Changing the Value of a Cell Changing Values without Selecting Cells Reading the Value of a Cell Cell Data Types	38
5.4	Cell Formulas	Creating Formulas using Cell References Creating Formulas using R1C1 Notation	40
5.5	Formatting Cells	Fill Formatting Properties Font Formatting Properties Formatting Cell Borders Changing Number Formats Using Cell Styles	41
5.6	Moving Between Worksheets	Activating a Worksheet Modifying Cells without Switching Sheets	43
5.7	Relative Cell References	Finding the Last Cell in a List Selecting to the End of a Filled Range Referring to a Region of Cells Offsetting Rows and Columns Resizing a Range Entire Rows and Columns Getting the Used Range Finding Special Cells	44
5.8	Inserting, Deleting and Clearing Cells	Inserting Cells Deleting Cells Clearing Cells	48
5.9	Moving and Copying Cells	Moving Cells Copying Cells Options for Copying Cells	49
5.10	Finding Ranges	Finding a Single Cell Finding Multiple Cells	51

Chapter 6 - VARIABLES▼

Section	Title	Subsections	Page
6.1	What are Variables?	Declaring a Variable Reassigning Variables Declaring Constants Using the Var Statement	52
6.2	Variable Scope	Scope with the Let Statement Scope with the Var Statement	54
6.3	Variables and Types	Primitive and Object Types Implicitly Typed Variables Explicit Variable Types Casting Types Printing the Type of a Value The Unknown Type	55

Chapter 7 - CONDITIONAL STATEMENTS▼

Section	Title	Subsections	Page
7.1	The If Statement	Basic If Statements The Else Clause Else If Clauses	58
7.2	Writing Logical Tests	Comparison Operators Combining Conditions The Not Operator	60
7.3	Conditions and Objects	Testing if an Object Exists Conditional Actions	61
7.4	Conditions and Cell Values	Testing the Type of a Value Testing for Empty Cells	62
7.5	Other Conditional Statements	The Conditional Operator The Switch Statement	63

Chapter 8 - WORKING WITH DATA▼

Section	Title	Subsections	Page
8.1	Working with Numbers	Basic Operators for Numbers Order of Operations Number Assignment Operators Formatting Numbers Converting Strings to Numbers The Number Object The Math Object Rounding Numbers Generating Random Numbers	64
8.2	Working with Strings	Concatenating Text String Templates Escaping Characters New Lines Editing Strings Comparing Strings Searching Strings Regular Expressions	68
8.3	Working with Dates	Creating a Date Object Getting and Setting Date Parts Formatting Dates Converting Excel Dates to TypeScript Dates Testing for Date Values A Quirk of TypeScript Dates	72

Chapter 9 - FUNCTIONS AND PARAMETERS▼

Section	Title	Subsections	Page
9.1	Functions	Why Create Extra Functions? Declaring a Function Calling a Function	75
9.2	Parameters	Declaring Parameters Default Parameter Values Optional Parameters	77
9.3	Returning Values	The Return Statement Specifying the Return Type	79
9.4	Function Expressions	Creating a Function Expression Anonymous Function Expressions	80
9.5	Arrow Function Expressions	Creating an Arrow Function Expression Including Multiple Statements	81

Chapter 10 - CONDITIONAL LOOPS▼

Section	Title	Subsections	Page
10.1	Conditional Loops	While Loops Do While Loops Nesting Loops	82
10.2	Exiting a Loop	The Break Statement The Continue Statement Using Line Labels	84
10.3	Finding Text with a Loop		86

Chapter 11 - FOR LOOPS▼

Section	Title	Subsections	Page
11.1	Introduction to For Loops	For Loop Syntax A Basic Example	87
11.2	For Loops and Ranges	Getting a Cell by Row and Column Number Offsetting from a Range Looping Over Rows and Columns	88
11.3	More For Loop Features	Continue and Break Statements Optional Loop Statements Multiple Loop Statements	90

Chapter 12 - ARRAYS▼

Section	Title	Subsections	Page
12.1	Introduction to Arrays	Declaring and Populating an Array Viewing Array Contents Referring to Array Elements Declaring an Empty Array	91
12.2	Modifying Array Contents	Sorting Arrays Adding Items Removing Items Altering Array Values	93
12.3	Searching Arrays	Basic Array Search Methods Searching Arrays with Arrow Functions	95
12.4	Extracting Values from Arrays	Extracting Items to New Arrays Combining Arrays into a New Array Converting an Array to a String	96
12.5	Looping Over Arrays	While Loops For Loops For Of Loops The forEach Method	97
12.6	Multi-Dimensional Arrays	Creating a Multi-Dimensional Array Referring to Elements Looping Over Multi-Dimensional Arrays	99
12.7	Arrays and Ranges	Getting Cell Contents as an Array Writing an Array to a Range Looping Through Range Values RangeAreas and Arrays	101
12.8	Arrays and Function Parameters	The Argument Array Passing an Array to a Function The Rest Parameter Returning an Array	105

Chapter 13 - COLLECTIONS▼

Section	Title	Subsections	Page
13.1	Introduction to Collections	What are Collections?	107
13.2	Collections and Objects	Referencing Collections Collections as Arrays Referencing Objects in Collections Referencing Objects as Items in Arrays	108
13.3	Adding and Deleting Objects	Adding Items to Collections Deleting Items from a Collection	110
13.4	Looping Through Collections	Deleting Objects in a Loop	111
13.5	Worksheet Examples	Protecting and Unprotecting Worksheets Hiding all but One Worksheet Consolidating Worksheets Dividing a List into New Worksheets	112
13.6	Chart Examples	Changing Chart Types and Formats Creating a Chart on Every Worksheet Looping Over Chart Series Looping Through Data Points	115
13.7	Table Examples	Creating a Table on Each Worksheet Looping Through Tables Table Columns Adding Table Rows	117

Chapter 14 - ERROR HANDLING▼

Section	Title	Subsections	Page
14.1	What is Error Handling?	The Try Catch Statement The Finally Block The Error Object	119
14.2	Throwing Exceptions	The Throw Statement	121
14.3	Errors in Functions	Catching Errors in the Calling Function Throwing Errors in a Called Function Catching Errors in a Called Function	122

Chapter 15 - CRIB SHEET▼

Section	Title	Subsections	Page
15.1	Office Scripts Reference	Selecting and Activating Things Selecting a Range Relatively Using Variables Conditional Statements Looping Creating Functions	124

If you book a place on one of our courses you will automatically get access to the relevant courseware manual online, and also have the option to receive a paper copy of it.

This page has 0 threads

Add a new post

Choose software

Office Scripts course manuals