We want to create formulas in a row when there's an edit, and in this case we're going to create a formula here that's equal to if this equals regular, We're just going to put the regular price. But if it's false, if it's on sale, for instance, we're going to take the regular price, and times 8 to get 80%. And we're going to fill in that formula , but as we come here, we have these zeros. Now we could hide this formula with, uh, if is blank, and just put A5 here. This is something I typically do when I just want to show nothing, not even a zero, until there's some item here. The problem when adding something here and having all of these formulas is that this page now, if you go to the last row, it's going to be the very last row with formulas. Creating Blank Cells For Good Reasons with IfError Formula and if(isBlank) Formula How to create a formula for every row in a column in a Google Spreadsheet? Don't Delete My Formulas Add Formula When Form Submitted

And I have to make sure that there's always a formula here, even if I end up deleting something, There's some quality control issues with having this formula right here. So basically I want to add the formula only when I actually add some item here. So let's do that with Apps Script . We're going to go Extensions, Apps Script . Our function is going to be called onEdit with a capital E. We're going to use an event here in this space. And basically we're going to go variable row equals event dot range dot get row. Now that makes sure we know what row we're on and we're going to get the sheet variable SS equals spreadsheet app dot get active sheet. We're going to do SS dot get range. We're going to go to the row that we're on comma. The column is going to be where we're going to write is four, which is D column. Spreadsheet Automation 101 Lesson 2: onEdit() Trigger Getting Started Coding in Apps Script Deep Inside Dark Habits Google Script How to Protect Spreadsheet From Yourself

I'm going to set value and it's going to be. So we can just copy this formula here. Now, if you see an issue, we need to change this row number . This is A5, C5, C5, every single time. So what we could do here is a couple of ways. We can put in a, the variable row, just using this plus sign here. But there's one other way we can do this. And that's using interpolation. We use dollar sign , and curly brackets. And we just say row. We are going to need to use backticks instead of quotes if we use double quotes. This won't work. But if we use backticks, this will work. So we're surrounding everything with backticks. And we know it works because this dollar sign curly brackets row is black. Move Cells to New Row with Formula Your First Formula in Google Sheets Visicalc 2023 Design Walkthrough How to Protect Spreadsheet From Yourself

So we're going to substitute everywhere that there's a number of the row with this interpolation of row. And now, every time we edit this sheet, it's going to add that to the row that we're editing We're going to need to know the column, variable column equals event dot range dot get column. And we're going to use column double is equal to one. Then we're going to do this addition. We also want to make sure that we're only adding this if there's, if we're not deleting something. So we need event dot value is equal to, is not equal to nothing, which is just two quotes. If we enter this, the price, New monitor, you can see it automatically enters that formula every time we're editing just this first column. Spreadsheet Automation 101 Lesson 2: onEdit() Trigger How To Create a Stop Watch in Google Sheets Visicalc 2023 Design Walkthrough How to Protect Spreadsheet From Yourself

If you're looking to get more out of your Google Sheets than you ever have before, consider subscribing here on YouTube to BetterSheets. Advanced Coding in Google Sheets for Programmers Absolute Basics of Google Sheets Better Sheets Videos Checklist Better Letters