Automate Your Most Annoying Excel Tasks With Simple Code

A coding environment is like your kitchen. If it's messy, you'll want to shy away from it. Let's discuss what we can do to clean it up!

In this video, you will follow precise instructions to get your coding environment up and running, transforming your computer into an automation powerhouse. All the tools you need are absolutely free, battle tested and completely risk free. Once installed, you'll have a powerful arsenal of productivity potential.

Before beginning to use VS Code, there are some really nice configurations that will make your coding experience as pleasant as possible, without having to think too much about the small, annoying things. This short video will guide you through all the components to install and configure to make you happy in the long run!

Well done! You should have a suped up computer right now! Any questions, please ask! There are always teething problems with installation things.

Let's jump into the simplest crash course in Javascript you'll find out there. Soon you'll be equipped with all the coding principles you need to make automation a reality!

It's beneficial to have a basic overview of the coding language we will be using and how JavaScript evolved from something that just made websites fancy, to something that powers many, many companies out there.

Can you believe coding can do what a calculator can do? Amazing! Let's discuss this data type in brief, and add another weapon to our arsenal

Do you want to continue this course? TRUE. Do you want to cut time off your Excel expeditions? TRUE. Or do you submit to a sub-par way of doing things? FALSE!

Sometimes it boils down to a Yes / No response. In coding, that "Sometimes" is "Almost Always". Let's learn Booleans here and get a solid foundation of conditional logic in code!

Let's look at how we work with basic text in our code. Think: renaming files, working with data in files, special instructions and the like. This is an important tool in our toolbox. Let's go!

Variables make our code cleaner and reusable. They are standard and best practice across every language and are critical to be understood as we make our way through the course! They are so simple, you'll get it right in no time!

We can, and will, do all cool things with strings. This video gives a brief introduction to some simple methods to make strings do whatever you want them to do. We will expand on this as we continue through the course. REMEMBER: the goal is NOT about learning specific words and actions off by heart, it is about KNOWING where to find what you think you need to do to achieve your outcome, and then experimenting until you get it right!

Now we stop working with single variables and work more intuitively - data structures that mimic more real life interpretation of information, such as lists, and relationships between one piece of data and another. Let's look at lists first.

Objects house different pieces of data that relate to one another. Client's name, age, address, date of birth, etc. It all relates to one entity. Let's see what we can do with them!

Finally, we can add some logic to our code. If certain conditions are met, we'll do one thing. If not, another thing. That's all there is to it! We will discuss basic "If" Statements that will help us tremendously down the line.

Loops help us do the same thing to each item in a list. Loops help us do the same thing to each item in a list.

Functions are like little Fit-For-Purpose machines. You feed something into it. It does something. And it hands something back over to you. It makes code readable and more effective. And something we'll be using A LOT!

Well done for making it this far! You have absolutely owned your skillset and your expertise. Let's continue to real Scriptworthy application!

Welcome to your computer, the way you've never experienced it before!

Now we're going to explore Nodejs. We will discover how to access all the functionality by requiring it using require(). Then on to fun stuff!

Let's introduce important concepts such as __dirname and the "fs" and "path" libraries to traverse your files and folders like an absolute pro!

Now that we know how to get to certain folders, we will explore actually creating folders programmatically, and using loops to make folders en masse!

Let's put pedal to the metal and harness all our coding skills so far to create a practical and useful file structure for a typical work case. Once scripted, watch how over 350 nested folders appear almost instantly!

This introduces the idea of renaming folders through code. It's a small tool but useful enough to justify a dedicated lesson... though we probably won't use it again going forward :(

WARNING: Always remember to save your work and make copies if necessary! Don't try this alone! Nodejs does come with a few fail-safes but when deleting, you should always act cautiously! Full file system access comes with great responsibility, Peter!

Now we are getting into the juicy bits of the power of these scripts. We can create files, build data, do stuff with data, and let's get going!

We will learn how to use the fs package to read files into our code and manipulate them on the spot.

Now let's work on a more real-world example. We will launch into a text file with inventory data and use cool clean code to pull out the total inventory. Super useful!

The inventory.txt file is downloadable in the resources section of this video.

Well done! You should now have a grasp of fundamental File System mechanics. Let's journey onward to Microsoft Excel!

Now for some fun! Let's dive into industry standard software and disrupt the norm with code!

Now we will evolve from basic text files and hone in on industry standards. Every software out there boldly tries to replace Excel. But most fail! Let's simply use our coding expertise to work ALONGSIDE Excel instead!

Callbacks are an important part of JavaScript and we will be using them extensively with the exceljs package as we go forward, so let's grab the bull by the horns and squelch any unpleasant surprises!

Now let's get into the nitty gritty of using code to extract some valuable information from an excel file!

Once more we digress to learn some important skills for both simplifying our code and making it cleaner, as well as more effectively working with objects, which we will need to summarize data in the following exercise!

We are going to consolidate everything we've learned and aggregate data from an Excel file onto a new sheet. Yay!

Here's a short instruction on doing some fancy things like colors and whatnot. But then I leave you with the exceljs documentation and the golden ticket to do as you wish! The world is your oyster!

Now we have a good command of Excel file manipulation. Next let's work on a real-world project with lots of complexity!

Let's work through 7M+ rows of data over 1 000+ sheets. Copy paste? Not on your life!

Let's set up the script with an outline of the basic logic of getting into each Excel file!

Now let's fetch all the rows in each and every sheet and build a big array of data!

We're SO CLOSE! Let's now create a new workbook in code and add the rows to it, with a little cherry-on-top date format!

Finally, let's dump these 7 million rows of... oh wait? What? OH NO! Find out what happens next time on DRAGONBALL Z!

We've encountered a big Memory error. Let's try and fix it. DON'T BE DESPONDENT! Even if the first attempt fails, we just grow stronger. And it will eventually be fixed! And we will be better for it!

Who needs Excel? CSV (Comma Separated Values) format doesn't have a 1-million-row-per-sheet limitation. CSV doesn't work with sheets! Let's see what principles we can learn while coding out this solution.

For the last stretch of the course, we are going to learn a fundamental skill: using modules in Node.js and making our own common code to reuse, much like the packages we have been requiring so far!

The index.js of the common folder is downloadable as a resource because we did some Chat GPT copy paste stuff, so for your convenience you can just grab it here!

Let's build up some cool objects and see how we can summarize over a thousand sheets' worth of data into a succinct one-pager!

Finally, after some more hurdles, we will produce a beautifully simple summary of our 7 million rows of data across thousands of sheets, while learning even more coding best practices and cleanup along the way!

Finally! The grand finale! let's put our skills to the test to deliver a single workbook with seven sheets of data!

WELL DONE! I hope you are now as excited as I am. You can take any monstrous task and turn it into a pancake!

Come and learn the SUPER EPIC FIVE STEP FORMULA for success when working with any NPM package and any business problem! This is the 1% clickbait of the course :-)

Let's jump into a PDF generation library for Node.js, and learn some essential tips when navigating the waters of NPM and trying to find the package to solve your problem!

Let's have some good ol' fun with Microsoft Word. Tears are welling up within me as we are so close to saying goodbye. I hope you've had fun! And I can't wait to hear how you've been smashing it with your new skills.

If i type I might get teary-eyed, so I'll just let the video speak for itself. Goodbye my friends :)