Don't Delete My Formulas

0:00 Don't delete my formulas . Are people deleting your formulas and you don't want them to? Something like this where we get a nice little waterfall of formulas and people are deleting them and we're like, please don't delete them. And maybe you put a little message here. You say, don't, don't delete. Please don't delete. You maybe bold it and maybe you put it in red and you're like please don't delete. Maybe you've also figured out that you can protect your sheet. People will get around this as well. You can even protect a range . You can just say the whole B column, nobody but me can edit this. And I will not allow anyone to edit this. Or you can even just show a warning before they try to edit it. But you're like, hey, I don't want anyone to edit it. So you protect your sheet. And still to this day, people come in here, they delete the protection . And then they delete your formulas. Don't Delete My Formulas Protect Your Formulas from Accidental Deletion in Google Sheets Don't Delete My Formulas How to Protect Spreadsheet From Yourself

0:46 And you're like, no, why are you doing that? Here's another little trick you can do if people don't want to. Not delete it if they're like hey, I actually don't want formulas here I want maybe they're cutting and pasting or something and you don't want them to we can hide this a little bit more so instead of doing just here a two plus seven, we're going to do a Array formula equals array formula I'm going to change A2 to A2 colon A to get the entire array, and then we're going to add 7, just like before. And here we have a nice column, oh wait, we have all these 7s. How do we fix that? At the beginning of this, we can put an if, and then inside the if is blank, and then put the A2 here. And we can That doesn't really fix it. Ah, so the situation is we need to put this, if is blank inside of the array formula . Don't Delete My Formulas How to create a formula for every row in a column in a Google Spreadsheet? Don't Delete My Formulas Remove Non Numbers

1:32 So if is blank a two colon a, and now everything is wait, it's a number . Oh, we need to format this as well. So go up to format custom date and time. And here we can choose any thing we want apply. And now we have everything nice and tidy. Even if somebody tries to, if I'm deleting this, like I'm hitting the delete key I'm. cutting and pasting. Oh, that cut didn't work. So but copy paste, I'm trying to mess this up and I'm not. But if somebody wants to mess up this array formula and it's in B2 as well, I can actually get rid of that as well. Let's cut this. And up here in next week, don't delete. I'm going to delete that. Don't delete. But I'm going to make a formula . I'm going to put in curly brackets in text next week. And then I'm going to use a semicolon. Don't Delete My Formulas How To Calculate ROAS in Google Sheets Don't Delete My Formulas Remove Non Numbers

2:17 And I'm going to use the array formula that we cut from before. We only want to include A2 here. We don't want A1. We don't want to change it to A1 because what's going to happen is this next week is going to be the first item in our vertical array. And then the second item will be this array formula . And there we go. We are now hiding our formula inside of B1. But let's say people are continuously harassing us and coming in and deleting this and then going off their merry way. I'm going to copy this formula that I've written right here, and I want to programmatically or automatically enter this into B1 every single day. So I'm going to go up to extensions Apps Script . This is not as hard as you think, we're just going to need a little bit of code, but I'll type it out here for you in this video. So we're going to write a function called replace my formula . And inside of this function , we're going to do basically one thing. Don't Delete My Formulas How to create a formula for every row in a column in a Google Spreadsheet? Don't Delete My Formulas Automatically Add Formula When Editing Row

3:05 We're just going to write. The formula here in B1. So we need to know what B1 is. Where B1 is it's in the spreadsheet app, which is our entire spreadsheet file. We're going to get the active spreadsheet , which is the spreadsheet file we're in right now. We're going to get sheet by name, which in our case is sheet 1. Sheet 1. We can actually also put this on the next rows. We're going to get range , and our range is B1. And then, we have actually two options. We can set value or set formula. I'm going to set formula and in our formula is going to be, actually, we need one bracket, one quote. That's our formula because we use double quotes here. We need to use a single quote to around it. So we can fix that in B1 as well. Just use single quotes here and single quotes there. Don't Delete My Formulas Your First Formula in Google Sheets How do I reference a different spreadsheet in Apps Script? What is a spreadsheet formula?

3:52 Okay. So now if I delete this, I'm going to copy it, but I'm going to delete it has nothing here. I'm going to check out. Can I run this? Thanks. Yes, I can go click here, replace my formula , click run, may ask me for authorization , and now it's there. Again, I will delete this and just check, does this work? So we're going to get this execution log, but I'm going to run it again just to make sure it works. Yes, it is continuously coming back. But now the second thing I want to do here is instead of just doing it once, I want to actually do it every day or every hour. I want to just keep putting this in just in case somebody deletes it. I want my formula to be there even if somebody comes and deletes my formula . So over on the left side, we're going to click triggers. It's the, Icon with a little clock bottom, right? We're going to click add trigger , choose which function we're going to choose the function . Don't Delete My Formulas Protect Your Formulas from Accidental Deletion in Google Sheets Don't Delete My Formulas How to Prevent Delete Tab

4:37 We have just written, which is replaced my formula and instead of a spreadsheet as the event source, we're going to choose time driven. Time driven has a lot of options here. We're going to actually use Let's say minutes, and we can do it every minute, every five minutes, every 10 minutes, every, let's say every 30 minutes. So our formula doesn't leave our sheet unless you delete it, and then within 30 minutes, it's going to come back right away. And we hit save, and now we have a trigger , it's time based, it's going to happen every 30 minutes, it's just going to write right here in B1 this formula , which is really cool. If for any reason there's an error , it'll show you an error rate. You can always go over to executions and see any executions that may have failed. One thing I would show you also is go back to triggers and on the right side, when you hover over a trigger on the, there's three dots, you can delete a trigger this way. Don't Delete My Formulas What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users Different Kinds of Automation Automate Two Sheets

5:24 So if you have for any reason whatsoever, you need to actually delete the formula , delete the app script that adds a formula there. You can delete it right away. And if you're a Better Sheets member and you're watching this on Better Sheets, you can get this formula and this sheet down below. If you're not a Better Sheets member yet, come one today. Don't Delete My Formulas Protect Your Formulas from Accidental Deletion in Google Sheets Don't Delete My Formulas How to Stop an Apps Script in Google Sheets