How to Automate Spreadsheets
In this video, Andrew from Better Sheets explains various methods to automate spreadsheets, covering simple formulas, conditional formatting, and Apps Script. He demystifies triggers and shares practical examples to enhance your spreadsheet automation skills.
Hi, it's Andrew from BetterSheets. I'm here to demystify the automation process of spreadsheets. I'm going to share with you a bunch of ways to automate spreadsheets from simple formulas in sheets to conditional formatting to Apps Script . We're going to cover it all, and I'm going to try to demystify this for you because a lot of people ask me how to automate spreadsheets. And when I ask them, what's the trigger you want? to automate with, they're really clueless as to what to do.
So some simple automation we can think of is a checklist that we may want to do some conditional formatting with, and we can always go up to formatting, conditional formatting, and we can say based on some rule, like custom formula, we'll say equals A2 equals true.
What if we want to show some data here? Like, we want done to be here. And we only want it to be shown if A2 is checked. Well, if it's just some text, we can hide it in a formula . We can use the if formula.
And so now if it's B2 is blank, there's nothing here. So nothing needs to be done, right? We have this actually opposite done and nothing. And so we need to say, hey. Let's take out laundry. Needs to be done.
But what if we want to do some more dramatic automation? Let's say we want to add a new line. Every time we click a checkbox that a task is done, we want to add a new line here. Well, we're going to have to start using Apps Script .
So this is a really big bummer. If we have to go into Apps Script and run a Apps Script from here. Let me show you a few ways we can do this.
So I'm just going to refresh it and we'll see what happens right next to help. There will be another menu shows up here. And so right now I do not have to go into Apps Script at all to use this automation.
And now every day, between that time, it'll happen once. The first time it happens, it'll be a random time within that hour, and then the next time it happens, it'll be 24 hours after that.
create a function called on edit. This is built in just like on open where we have an event that happens.
So if that is true, we also want to get the value. So value. I want to make sure that we're clicking from false to true.
Sometimes this happens. It says the parameters don't match the method signature for spreadsheet app.
If you are looking to go deeper, way more experience and you want way more experience in spreadsheet automation, check out master spreadsheet automation on Udemy or become a member of better sheets.