Microsoft 365 Excel Office Scripts for Dummies.

Format:

Book

Author/Creator:

Alexander, Michael.

Language:

English

Physical Description:

1 online resource (210 pages)

Edition:

1st ed.

Place of Publication:

Newark : John Wiley & Sons, Incorporated, 2026.

Summary:

Unlock the full potential of automation in the online version of Excel online with this easy-to-follow guide Microsoft 365 Excel Office Scripts For Dummies, by experienced Microsoft MVP Michael Alexander, explains how to use Office Scripts to unlock VBA-like capabilities in the online version of Excel.

Contents:

Intro

Title Page

Copyright Page

Table of Contents

Introduction

About This Book

Foolish Assumptions

Icons Used in This Book

Beyond the Book

Where to Go from Here

Part 1 The Fundamentals of Excel Office Scripts

Chapter 1 Introducing Office Scripts for Excel

Seeing How VBA and Office Scripts Differ

Offline versus online

Security

Events

Language

Types of procedures

Code storage

Script triggers

Understanding the Requirements and Limitations of Office Scripts

Subscription requirement

Technical and organizational limitations

Data limits

Meager documentation

Benefits of Office Scripts

Chapter 2 Exploring the Office Scripts Environment

Diving into the Automate Tab

Writing Your First Script

Sharing Scripts

Emailing the .osts file

Moving or copying the .osts file to a shared location

Attaching scripts to your workbook and adding a button

Detaching scripts from your workbook

Creating Office Scripts with Action Recorder

Recording your actions

Renaming your script

Running your script

Editing a script

Deleting scripts

Limitations of Action Recorder

Using the Insert Action Command

Comparing Absolute Mode and Relative Mode Recording

Part 2 Basic Programming Concepts in Office Scripts

Chapter 3 Understanding Scripting Fundamentals

Understanding the Object Model

Understanding Methods

Enhancing Code with Variables

Using let versus const

Understanding data types

String data types

Number data types

Boolean data types

Array data types

Adding Clarity with Comments

Adding Flexibility with Functions

Defining a function

Understanding function return values

Using function parameters

Defining default parameter values

Enhancing your scripts with input parameters

Defining optional parameters.

Creating a drop-down selector for a parameter

Chapter 4 Handling Errors with Style

Exploring Office Scripts Debugging Tools

Using the console.log method

Understanding try . . . catch statements

Leveraging the finally block

Best practices for using try . . . catch statements

Examining Error-Handling Patterns

The try . . . catch . . . finally pattern

The validation-first pattern

The guard clause pattern

The fallback pattern

Contending with Common Error Types

Syntax errors

Reference errors

Object not found errors

Invalid range and out-of-bounds errors

Invalid arguments

Chapter 5 Controlling Program Flow and Conditions

Getting Logical with Conditions

Using the basic if statement

A word about equality operators

Planning for false conditions with if . . . else statements

Extending conditional checks with if . . . else if statements

Simplifying if statements with switch statements

Using Logical Operators in Conditional Analyses

The AND operator (&amp

&amp

)

The OR operator (||)

The NOT operator (!)

The nullish coalescing operator (??)

The ternary operator

Implementing Loops

Understanding for loops

Understanding prefixing versus postfixing

Leveraging for. . .of loops

Understanding while loops

Part 3 Scripting Common Excel Objects

Chapter 6 Starting with Worksheets

Working with Worksheets

Adding a new worksheet

Adding a new worksheet for each month

Deleting all but a specified sheet

Unhiding all worksheets in a workbook

Hiding all but a specified worksheet

Moving Worksheets Around

Moving a sheet to the first position

Moving a sheet to the last position

Moving a specific sheet before another sheet

Duplicating worksheets

Understanding Worksheet Protection

Checking if a worksheet is protected.

Protecting a worksheet

Unprotecting a worksheet

Protecting all worksheets in a workbook

Unprotecting all worksheets in a workbook

Extending the Worksheets Collection

Sorting worksheets alphabetically

Creating a table of contents for your workbook

Chapter 7 Working with Ranges and Tables

Navigating Ranges

Using methods that return metadata

Leveraging range shortcut methods

Understanding the getOffsetRange method

Dynamically adding a total column

Getting flexible with the getCell method

Creating custom range functions

Manipulating Data in Ranges

Copying data from one range to another

Simulating paste-special functionality

Copying data between worksheets

Converting all formulas in a range to values

Clearing cell contents

Deleting rows and columns

Working with Excel Tables

Referencing parts of a table

Creating a table from a range

Applying table filters

Filtering numeric values

Performing advance text filtering

Clearing filters

Chapter 8 Pivoting with Pivot Tables

Understanding Pivot Table Hierarchy

Starting a pivot table

Adding data items to the pivot table

Customizing data field items

The setSummarizeBy method

The setNumberFormat method

The setName method

Avoiding errors when adding data items

Putting it all together

Refreshing All Pivot Tables in a Workbook

Changing Pivot Report Layouts

Setting the report layout

Adjusting grand totals and subtotals

Working with Slicers

Creating a slicer

Filtering a slicer

Chapter 9 Scripting Visual Elements

Understanding Custom Number Formatting

Hiding and suppressing zeroes

Formatting numbers in thousands and millions

Formatting dates and times

Enhancing Data with Conditional Formatting

Capturing conditional formatting code with Action Recorder.

Conditional formatting that fails to record properly

Examining a starter script for data bars

Examining a starter script for color scales

Examining a starter script for icon sets

Automating Charts

Understanding the chart object model

Working the chart elements

Setting the chart position

Formatting the chart title

Setting the legend

Formatting the x-axis and y-axis

Setting series formats

Overcoming chart recording challenges

Converting Visualizations into Images

Creating Custom Visuals

Creating a bullet graph

Creating a waffle chart with conditional formatting

Part 4 Going Beyond Excel with Power Automate

Chapter 10 Introducing Power Automate

Going with the Flow

Exploring the benefits of flows

Understanding Power Automate requirements

Exploring the Power Automate Interface

The Create option

The Templates option

The My Flows option

Creating Your First Flow

Understanding triggers

Gathering your thoughts and files

Building the flow

Passing Parameters to a Script from Power Automate

Understanding the Limitations of Power Automate

Excel data and API limitations

Power Automate platform limits

Best practices to avoid Office Scripts failures in flows

Discovering the Classic Flow Designer

Chapter 11 Integrating Excel with Email

Integrating Excel and Outlook

Emailing an attachment

Sending emails to a dynamic list of recipients

Reviewing the script

Updating the flow

Embedding images in emails

Distributing Excel Data as PDF Files

Introducing OfficeScript.convertToPdf

Leveraging OfficeScript.sendMail

Chapter 12 Integrating Excel with SharePoint

Preparing the Files

Reviewing the Scripts

Building the Flow

Reading data arrays from the SharePoint file.

Writing data arrays to new workbooks

Part 5 The Part of Tens

Chapter 13 Ten Best Practices When Writing Scripts

Apply Descriptive Variable and Function Names

Use Established JavaScript Conventions

Apply Indentation Standards

Write Effective Comments

Leverage Comments to Partition Code

Centralize Object References

Break Long Scripts into Custom Functions

Build Modular Scripts

Organize a Script Library

Manage Script Versions

Chapter 14 Ten Tips for Improving Script Performance

Read and Write Data in Batches

Using the tiny boat

Using the cargo ship

Avoid Repeated Lookups

Sort and Filter in Arrays, Not Cells

Apply Formatting to Ranges, Not Cells

Use Table Objects Whenever Possible

Leverage the getUsedRange Method

Postpone Calculations Until the End

Minimize Formula Usage in Scripts

Minimize Actions that Change Display

Create Modular Scripts

Index

EULA.

Notes:

Description based on publisher supplied metadata and other sources.

ISBN:

9781394412716