Sign up for a FREE TRIAL to access this course today.

Spreadsheet Automation 101

31 Tutorials

Dive into Google Apps Script so that you can automate your business processes. Learn how to automatically send emails to yourself and others with data from inside your sheets. Available for non-members on Udemy → Spreadsheet Automation 101

Spreadsheet Automation 101: A Beginner's Guide to Google Apps Script
Are you tired of manually updating your spreadsheets and sending emails?
Do you wish you could automate those tasks and save time for more important things?
Then you need to learn about Spreadsheet Automation with Google Apps Script!

Google Apps Script is a scripting language based on JavaScript that allows you to automate tasks in Google Sheets, Docs, and Forms. In this course, we'll be focusing on Spreadsheet Automation, specifically using Google Sheets.

If you're new to Google Apps Script, don't worry. We'll start with the basics and build up from there. By the end of this course, you'll have a better understanding of how to automate your business processes and save valuable time.

Spreadsheet Taxonomy
Before we dive into Google Apps Script, let's review some basic concepts about Google Sheets. Understanding the terminology will make it easier to follow along with the rest of the article. We'll also dive into the basics of Javascript, and programming. The foundations are important here as we build on them quickly.

SpreadsheetApp
SpreadsheetApp is the main object that represents a Google Spreadsheet. You'll use it to access and manipulate the data in your spreadsheet. This is where javascript and Google Apps Scripts diverge slightly. We can access sheets here sort of like we use the "DOM" in Javascript in a browser.  I say that lightly because it does act very differently in reality.

For Loop and Arrays
Once you get through the basics of Google Sheets, we will move on to the For Loop an dArrays A loop is a way to repeat a set of actions multiple times. The For Loop is a specific type of loop that allows you to loop over a range of values.
We will break down each part of the For Loop and write it again and again. There are some common errors you might encounter and I'll happily go through those with you to prevent you from getting frustrated later.

onEdit() Trigger
The onEdit() Trigger is a special type of function that runs automatically when a user edits a cell in the spreadsheet. You can use it to trigger other functions or scripts that will perform actions based on the edit.

Event Object
When the onEdit() Trigger runs, it passes an event object to the function. The event object contains information about the edit, such as the range of cells that were edited and the new value of the cell.

Logger
The Logger is a built-in class that allows you to log messages to the console. You can use it to debug your code and see what values are being passed to your functions.

MailApp
Learn about the needed variables for the Mail App to work. In this lesson, I’ll walk you through how you can email yourself or other people the items in a to-do list that hasn’t been done yet.

Email Other People For Loop
Send the same email to different people by using the same Four Loop to figure out which ones we’re sending. Do cool things with this Mail App and email system.

UrlFetchApp
Use any URL to scrape the web or access an API and get that info into your Google Sheet.

Get data via Apps Script and API's
Use getData and UrlFetchApp functions, as well as Logger to get OMDb API data in our Google Sheet. Learn how to access an API outside of Google Sheets.

In addition to learning the basics and foundations, I try to help you develop a better mindset around programming and coding. Because in the best case scenario you'll be constantly learning. There are a few videos that help you become better able to learn later.

Think Like a Programmer: Develop The Mindset of an Apps Script Coder
It's hard to code. It's even harder to learn to code. This is my thought process to help you become resilient enough to learn, and continue to learn to code.

And I'll also go into a few automations you can do in Sheets without Apps Script.

This Seems Like Automation
4 formulas that make your sheets feel automated. Start with user-started actions, and get automated data flowing in other parts of your sheet.

Spreadsheet Automation 101 is available for non-members on Udemy → Spreadsheet Automation 101 and is available here on Better Sheets for both Monthly members and Lifetime Members.

31Tutorials

Learn how to automate spreadsheets with Apps Script. The pre-course videos equip you with the fundamentals of coding before you start.
Get introduced to Apps Script functions and functions syntax. Learn how to write new functions, how to name functions and call functions from your sheet.
Learn how to think like a programmer and develop a coding mindset with these expert tips from a seasoned Google Sheets programmer. Don't fall for the common, ineffective advice, and instead follow this practical advice, including learning the process, figuring out how to search, and avoiding memorization. With these habits, you'll be on the path to successful coding. 

It's hard to code. It's even harder to learn to code. This is my thought process to help you become resilient enough to learn, and continue to learn to code.
Dive deeper into the world of variables. Learn what variables do. Learn how to assign information to variables.
Learn how Dot Notation allows you to go deeper into the functions.
Learn the importance of camel case. Camel Case is a simple technique that involves capitalizing the first letter of each word in a text string, except for the first word. This creates a consistent and easily readable format for text that may be used in formulas, labels, or other applications.
We’ll tackle the importance of parenthesis, as well as when we should not use them. Learn how to make a functional function with parentheses. I’ll also teach you how to fix an error you might get with using parentheses.
Learn to write codes in Apps Script. Understand what SpreadsheetApp is used for. Get to know getActiveSpreadsheet and use it correctly.
Learn about the strategy and concept of the SpreadsheetApp. Understand rows, columns, and cells better.
Make A1 not so limited. Learn more about A1 Notation and how it can be changed to row, column. Do more with A1 Notation.
Learn the difference between getActiveSpreadsheet() and get ActiveSheet(). Don’t get confused. Learn more with this lesson.
This lesson will get you started on your first trigger. It’s the first real spreadsheet automation you can do. Fun stuff!
4 formulas that make your sheets feel automated. Start with user-started actions, and get automated data flowing in other parts of your sheet.
Get Values is done in the Apps Script. In this lesson, you’ll learn what changing .getValue() to .getValues() do. You’ll also learn about Logger and For Loop, which I mention here briefly.
Learn the basics of Arrays – how to create one, how to reference rows and columns of data in a different way, and how to get an entire column or row in Apps Script.
Learn about the syntax of the For Loop. Find out how to do fantastic automations.
Learn how and when to use Bracket Notation in an array. No memorization is needed!. You’ll also be able to see the gap between what it is that’s missing.
Learn to log any variable you want and get notes inside your log. Debug your code to understand what’s going on and find out where you went wrong.
Let’s talk about the IF formula and the differences. Learn how to insert a checkbox and all the other things it can do for you.
One of the things to learn: We can only have one onEdit() in our entire spreadsheet file. I’ll also walk you through on how you can use Event and Logger to get cool information.
This lesson will teach you how to email yourself by sending some texts. Learn how to get some data from your sheet and send them into your inboxes or other people’s inboxes.
Learn about the needed variables for the Mail App to work. In this lesson, I’ll walk you through how you can email yourself or other people the items in a to-do list that hasn’t been done yet.
I’ll walk you through how you can receive an email about tasks that haven’t been done.
Learn about how to add and delete Triggers very quickly. I’ll teach you how you can send out an email regularly using Triggers.
Send the same email to different people by using the same Four Loop to figure out which ones we’re sending. Do cool things with this Mail App and email system.
We’re getting data on movies and getting it into Google Sheets. Let’s create some magic inside Google Sheets!
Use any URL to scrape the web or access an API and get that info into your Google Sheet.
Capture data from OMDb in our Google Sheets with a free API Key. It’s easier than it sounds. Let’s do this!
Use getData and UrlFetchApp functions, as well as Logger to get OMDb API data in our Google Sheet. Learn how to access an API outside of Google Sheets.
Learn how we get information out of a JSON response. I’ll even show you how you can bring in some cool images in Google Sheets!
Get more things than just the poster and movie title by changing our script a bit. By creating different functions, we can get other data such as movie posters or directors.

Start a FREE TRIAL today to access this course.