How to prevent someone from deleting your formulas. Beyond protecting your range or sheet.
Academy ↓
Hey there stranger!
Don't Delete My Formulas
About this Tutorial
Sheet Resources
Video Transcript
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 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
0:26 delete you maybe bold it, and maybe you put it in Red and you're like please please please don't delete maybe you've also figured out that you can protect your sheet But people will get around this as well.
0:40 You can even protect a range you can say the whole B column Nobody, but me can edit this 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
0:58 here where they delete the person. Protection and then they delete your formulas, and you're like no why are you doing that?
1:06 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
1:24 going to do a Array formula equals array formula I'm going to change that. A2 to a2 colon a to get the entire array then we're going to add seven just like before and here We have a nice column.
1:39 Oh wait. We have all these sevens. How do we fix that well at the beginning of this? we can put an if and then inside the if is blank then put the a2 here We can Wait that doesn't really fix it ah so the situation is we need to put this if is blank inside of the array Formula, so if is blank a2 colon
2:03 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.
2:24 I'm I'm Cutting and pasting, oh, that cut didn't work. 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.
2:38 I can actually get rid of that as well. Let's cut this 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, and I'm going to use the array formula that we
2:58 cut from 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
3:21 But let's say people are continuously Continuously harassing us and coming in and deleting this and then going off their merry way.
3:29 I'm going to actually 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 app script.
3:40 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 my formula Inside of this function.
3:55 We're going to do well basically one thing. We're just going to write the formula here in b1 So we need to know what where b1 is.
4:06 Well, 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.
4:13 We're in right now We're going to get sheet by name which in our case is sheet one sheet one You can actually also put this on the next rows We're going to get range and our range is b1 And then, We have a actually two options.
4:33 We can set value or set formula I'm going to set formula and in our our formula is going to be Actually, we need one bracket one quote there That's our formula because we use double quotes here.
4:53 We need to use a single quote to around it So what we can fix that in b1 as well just use Single quotes here And single quotes there.
5:05 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.
5:11 Can I run this? Yes, I can go click here replace my formula click run may ask me for authorization And now it's there.
5:20 So 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 continuing.
5:31 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.
5:39 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.
5:48 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.
5:57 we have just written, which has replaced my formula. And instead of a spreadsheet, as the event source, we're going to choose time-driven.
6:03 Time-driven has a lot of options here. We're going to actually use say minutes and we can do it every minute, every five minutes, every ten minutes, every let's say every 30 minutes.
6:14 So our formula doesn't leave our sheet unless you delete it. And then if within 30 minutes, it's going to come back right away.
6:22 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 and be this one, this formula, which is really cool.
6:34 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.
6:41 Um, one thing I would, 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.
6:55 So if you have for any reason whatsoever, you need to actually delete the formula, Delete the app script that adds them the formula, there, you can delete it right away, right there.
7:05 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.
7:11 If you're not a Better Sheets member yet, come on today.
0:26 delete you maybe bold it, and maybe you put it in Red and you're like please please please don't delete maybe you've also figured out that you can protect your sheet But people will get around this as well.
0:40 You can even protect a range you can say the whole B column Nobody, but me can edit this 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
0:58 here where they delete the person. Protection and then they delete your formulas, and you're like no why are you doing that?
1:06 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
1:24 going to do a Array formula equals array formula I'm going to change that. A2 to a2 colon a to get the entire array then we're going to add seven just like before and here We have a nice column.
1:39 Oh wait. We have all these sevens. How do we fix that well at the beginning of this? we can put an if and then inside the if is blank then put the a2 here We can Wait that doesn't really fix it ah so the situation is we need to put this if is blank inside of the array Formula, so if is blank a2 colon
2:03 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.
2:24 I'm I'm Cutting and pasting, oh, that cut didn't work. 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.
2:38 I can actually get rid of that as well. Let's cut this 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, and I'm going to use the array formula that we
2:58 cut from 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
3:21 But let's say people are continuously Continuously harassing us and coming in and deleting this and then going off their merry way.
3:29 I'm going to actually 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 app script.
3:40 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 my formula Inside of this function.
3:55 We're going to do well basically one thing. We're just going to write the formula here in b1 So we need to know what where b1 is.
4:06 Well, 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.
4:13 We're in right now We're going to get sheet by name which in our case is sheet one sheet one You can actually also put this on the next rows We're going to get range and our range is b1 And then, We have a actually two options.
4:33 We can set value or set formula I'm going to set formula and in our our formula is going to be Actually, we need one bracket one quote there That's our formula because we use double quotes here.
4:53 We need to use a single quote to around it So what we can fix that in b1 as well just use Single quotes here And single quotes there.
5:05 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.
5:11 Can I run this? Yes, I can go click here replace my formula click run may ask me for authorization And now it's there.
5:20 So 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 continuing.
5:31 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.
5:39 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.
5:48 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.
5:57 we have just written, which has replaced my formula. And instead of a spreadsheet, as the event source, we're going to choose time-driven.
6:03 Time-driven has a lot of options here. We're going to actually use say minutes and we can do it every minute, every five minutes, every ten minutes, every let's say every 30 minutes.
6:14 So our formula doesn't leave our sheet unless you delete it. And then if within 30 minutes, it's going to come back right away.
6:22 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 and be this one, this formula, which is really cool.
6:34 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.
6:41 Um, one thing I would, 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.
6:55 So if you have for any reason whatsoever, you need to actually delete the formula, Delete the app script that adds them the formula, there, you can delete it right away, right there.
7:05 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.
7:11 If you're not a Better Sheets member yet, come on today.