*** Proof of Product ***
Exploring the Essential Features of “Mark Proctor – Office Scripts – Automate Excel Everywhere”
Discover how to boost your productivity, automate Excel, and run tasks while you sleep!
Transform your Excel efficiency with the Office Scripts: Automate Excel Everywhere Course!
Unlock the power of automation in Excel 365 without any prior programming knowledge.
Say goodbye to the complexities of VBA Macros that have plagued users for decades. In Excel 365, Microsoft has introduced Office Scripts, a simpler and more intuitive language designed specifically for non-programmers.
Whether you’re using Windows, Mac, or Excel Online, Office Scripts empower you to streamline your workflow and reclaim countless hours spent on repetitive tasks.
What you will get
Office Scripts: Automate Excel Everywhere is the most comprehensive training on the marking for learning Office Scripts.
The course starts with the fundamentals and shows how all the pieces fit together. This means by the end of the course, you will be able to write your own Office Scripts without needing to copy and paste from others or rely on the script recorder.
Through this course, you will learn to become an Office Scripts expert:
- Write re-usable scripts to work with any spreadsheets
- Create simple applications for collecting and managing data through worksheet-based user interfaces
- Build self-running automation processes that operate in the background (you don’t even need to click a button – it runs while you sleep!)
With these skills, you can save days of time each month (meaning you can go home on time more often).
Taught by somebody who has been there
About 10 years ago, I set out on a mission…
My work life had become a common routine. Sometimes, I worked until 9:30 pm; other times, it was 11:30 pm. The exact time was irrelevant. What mattered was that I constantly missed seeing my children.
I had a young family, and wanted to be around and see them grow up. But due to work pressures, I hardly saw my children, I rarely saw my wife, and most days, I didn’t even see daylight.
In his book, The ONE Thing, Gary Keller states that answering and actioning the following question is the secret to efficiency: “What’s the ONE Thing I can do, such that by doing it, everything else will be easier or unnecessary?”
For me, the answer was obvious… automating Excel. That is where my journey began.
Through this journey, I discovered the best ways to combine Excel’s features to automate tasks. But, I didn’t save minutes; I saved months of time every year.
Having learned these skills, I decided to share them with others; this is when I set up Excel Off the Grid. Now, each year, we reach millions of people through our blog, YouTube channel, books, and courses.
Because of this commitment to helping others and uncovering new techniques, I was recognized as a Microsoft MVP. This award is saved for those who make the most significant impact on the community.
Detailed course curriculum
We’ve made it easy for you to learn Office Scripts with a clear structured approach.
Course includes:
Over 9 hours of training videos | Real-world projects to work through | Downloadable workbooks and scripts
Exercises to practice new skills | Quizzes to test your knowledge | English closed captions
Modules:
Introduction (18 mins)
Welcome
What are Office Scripts?
Office Scripts infrastructure
Office Scripts vs VBA: What’s the difference?
Do you have Office Scripts?
Golden rule for learning Office Scripts
Download the support files
Getting started with Office Scripts (49 mins)
Getting started with Office Scripts – Introduction
Writing your first script
Editing your first script
Save and delete Office Script files
Writing your second script
Exploring the Office Scripts user interface
Using the script recorder
The script recorder does not capture everything
Sharing scripts with others
Creating an Office Scripts button
Undo Office Script actions
Getting started with Office Scripts – Quiz
Understanding the Document Object Model (49 mins)
Understanding the Document Object Model – Introduction
What is the Document Object Model?
Basic examples of using the Document Object Model
Learning the DOM
Optimize code using variables with DOM
Active and relative reference with DOM
Objects and collections in the DOM
DOM – Exercise
DOM – Exercise answers
DOM – Exercise – Alternative approach
Document Object Model – Quiz
Programming fundamentals (82 mins)
Programming fundamentals – Introduction
The Main function
Adding and using comments
Colors used in the code editor
Whitespace, line breaks and semi-colons
Declaring & assigning variables
Understanding variable types
Working with strings
String properties and methods
Concatenating text strings
Working with numbers
Number and Math object methods and properties
Arithmetic operators
Working with booleans
Comparison operators
Type conversion
String converter – Exercise
String converter – Exercise Answers
Multiplication tester – Exercise
Multiplication tester – Exercise answers
Programming fundamentals – Quiz
Objects, arrays and functions (102 mins)
Objects, arrays and functions – Introduction
What are objects?
Working with objects
Object references
What are arrays?
Working with arrays
Ranges vs arrays
Working with ranges and arrays of unknown sizes
Concatenating array values
Split a string into an array
Adding and removing the first and last items
Other uses for pop() and shift()
Adding, removing and joining multiple items
Introduction to functions – Part 1
Introduction to functions – Part 2
Understanding the return statement
Filtering arrays
Sorting arrays
Applying a function to every item in array with map
Arrays – Exercise
Arrays – Exercise answers
Objects, arrays and functions – Quiz
Logic and loops (65 mins)
Logic and loops – Introduction
Logical operators (and, or, not)
If statements
Switch statements
Alternative if statement syntax
Catching and handing errors
For loop
For loop over collections
While loop
Alternative looping methods – map() and forEach()
Looping over cells in a range
Logic and loops – Exercise
Logic and loops – Exercise answers
Logic and loops – Quiz
Project: Table of Contents (45 mins)
Project: Table of Contents – Introduction
Overview
Create the worksheet
Format the worksheet
Listing the worksheets
Add the hyperlink
Working with hidden sheets
Final formatting
Bug fixing and tidy up
Project: Chart axis (23 mins)
Project: Chart axis – Introduction
Overview
Setting the axis value
Chart selection based on a list
Parameters based on named ranges
Testing and tidy-up
Project: User input (39 mins)
Project: User input – Introduction
Overview
Worksheet setup
Toggle between views
Improving the user experience
Save process
Enhancing the save process
Tidy up and enhancements
Using Power Automate with Office Scripts (70 mins)
Using Power Automate with Office Scripts – Introduction
What is Power Automate?
Getting started with Power Automate
Run an Office Script with Power Automate
Run an Office Script on all files in a folder
Passing parameters from Power Automate to Office Scripts
Using Table values as Office Script parameters
Passing values from Office Scripts back to Power Automate
Auto PivotTable Project – Overview
Auto PivotTable Project – Script #1
Auto PivotTable Project – Script #2
Auto PivotTable Project – Script #3
Auto PivotTable Project – Build the flow
Auto PivotTable Project – Testing
Asynchronous Office Scripts (27 mins)
Asynchronous code – Introduction
Understanding asynchronous code
Getting external data with an API
Getting external images into the workbook
Wrap-up (1 mins)
Wrap-up
Course Feedback
FREE Bonuses!
Bonus #1
Office Script Cheat Sheets
This bonus includes over 22 pages of code snippets and examples that you can copy and use in your own scripts.
Combine these code snippets together for infinite possibilities.
This bonus alone will save you hours of time and effort in learning to write your own scripts.
Bonus #2
Power Automate Ready Script Library
Ready to use scripts to plug into your Power Automate flows.
Includes:
- Moving data between workbooks
- Converting CSV to Excel
- Passing cell values as parameters
- Moving images between workbooks
- Sorting worksheets
Office Scripts saved 28 working days per year
“If it works, you pay me; if it doesn’t, you don’t”… I had no idea if it would work. But this was the agreement I made with the client.
My first Office Scripts experience was in 2021. The client was looking for a VBA solution to replace a daily 45-minute reporting task. None of it was difficult, but it was time-consuming.
Six times per day:
- An email would arrive containing new data.
- The data was downloaded and used in various reports
- The reports were emailed to a select set of individuals
VBA would have been a great option. However, with VBA, you still need to click the buttons. I had a gut feeling Power Automate and Office Scripts were the way to go.
After playing around for a while, I unveiled the solution. A zero-click, runs while you do other things, solution.
The client was blown away. The 45 minutes of daily work was now zero minutes. That is 28 days of work per year which they no longer needed to do. They didn’t even need to think about it… it just happened.
This course covers every technique used in that solution, along with every technique I’ve discovered since.
Please see the full list of alternative group-buy courses available here: https://lunacourse.com/shop/
