Find out in this quick experiment if a tab name can be alerted to the owner of the spreadsheet. Great for owners and operators who want to know if someone is changing the sheet.
Academy ↓
Hey there stranger!
Can I get alerted when a tab name is changed?
About this Tutorial
Sheet Resources
Video Transcript
0:00 So there's something that happens in BetterSheets and when members ask me questions, or non-members sometimes, ask me questions, ah, sometimes I don't know the answer.
0:10 For instance, someone might ask, if a tab name is changed, how do I get an alert for that? How can I create some kind of alert system if someone in my organization changes the name of the tab?
0:23 Can I do that? And the answer is I don't know. But what I'm going to show you here is me experimenting with it.
0:29 So at the moment I literally don't know if this is possible, but we're going to do an experiment with Apps Script because I do know that there is an on-edit simple trigger we can use.
0:40 But the edit means the user changes the value of a cell. There is, however, another type of trigger where we can install that trigger.
0:52 And when we install a trigger we can use the on-change. Now on-change means you change the structure of a sheet.
0:58 So you add a column, add a sheet. It doesn't, in the documentation, include any information about the changing name. So we're going to experiment with that today.
1:07 And I'm going to show you this experiment, literally, as I do it, because I have never done this before. And I wanted to show you that this kind of answering of these questions can be very playful and very much just playing around with what it is available, and understanding what is available and trying
1:23 new things. So we're going to create a function. We're going to say alert me on form, no, tab name change.
1:31 Okay. So this is the thing we want to do. So what we want it to do, let's do something pretty simple first.
1:38 spreadsheet app dot um we want toast, a spreadsheet toast. We'll just go here, and I always forget how this works.
1:48 Get active toast, that's all we need. Spreadsheet app, get active, and we're going to do toast. And the message we're going to say is, you've changed the tab name.
1:59 So we're You're not even- We're using any variables yet, we're not doing anything crazy, we're just going to check and experiment this.
2:07 We're going to save that, we can call this experiment tab name change. And let's see what. The next thing we need to do is actually create the trigger for it.
2:19 So we'll go over here to triggers. It will need a moment on the bottom right, click add trigger, choose which function to run.
2:26 There is only one function, we don't need to change that. The select event source is going to be from spreadsheet and it's this on change.
2:34 Again we're experimenting if the tab name is a change. You also have on edit here. But again, that is just editing a value in a cell and not the editing the sheet name, I think.
2:48 But let's see, on change should be it, or if it would include it, so save. Now we can also test this on change in another way.
3:00 So actually. we need to authorize these things. Let's authorize it. I think there's another way we can test this, ah, to show you that this will work.
3:10 If it doesn't work, I'll show you that it will work. But let's just save that. OK, now we are triggering any change.
3:18 So again, any change meaning, actually, let's be, this is the other way to test it. So we're going to duplicate that.
3:24 Let's see if we get a, and we have changed. But, OK, the message is just a hard-coded. We haven't actually change the tab name.
3:35 But let's go and change this to data2. That is a change of the structure. And it is, you've changed the tab name.
3:45 That is fantastic. So, it works. So now we know definitively that a change in the structure ah, is happening, but also changing the tab name is this onChange, ah, change.
4:01 Trigger. That is really cool. And so, let's say you are an organization and you are like hey, I want to know if one of my people has changed this sheet in some way, meaning they've added a tab, they've deleted columns, added columns, they're changing the name, That's all included here.
4:23 So this toast is going to show up in the, uh, in the app and in the spreadsheet. So you've changed something, we should probably say, because it's not so specific.
4:34 Also, what we can do here is add a mail app or gmail app. Uh, send email. And we're going to send an email to, uh, we can probably do the owner, but not the session.
4:49 So here in Apps Script we have a get owner, so we can use this app active spreadsheet, get owner, get email here.
4:57 so we will send an email to the owner. We can do variable owner equals get owner, get email. There we go.
5:10 We're going to send an email and we're going to say hey, uhm, hey something's changed. Uh, and in the body we'll say, uh, check.
5:20 out this spreadsheet. And we want to add a URL here. So we'll get variable URL equals spreadsheet app dot get active spreadsheet, get URL.
5:36 There we go. Uh, and we can also So, let's look at. at this body as it's sent. So variable body equals this.
5:46 And then we want to logger dot log body. So we can see it in here. And we'll send that email.
5:54 So now we don't have to update the trigger the trigger will still work we just have to save this and we will go and update something.
6:02 So let's duplicate this and we can go back into our executions look at the ah we see we failed. Let's see what we did here.
6:15 Ah if we refresh we should be able to get the error message. Script does not have permission. So, um what I think we need to do is go to the editor.
6:24 If I just hit run on here I should be able to access the authorization. There we go. We just have to authorize it again, or not again, one more time with all of these new things in it.
6:38 Ah we may get a failure here from the running it in here. But cool. Okay. Let's change something again. Let's change this to data three.
6:49 Go back to our ah trigger, actually execution, sorry. Let's see. It has completed. Refresh. And this is the body of the email.
7:05 And this is what the email looks like over here. It is just check out the spreadsheet. Something's changed. Ah, there we go.
7:12 So this ended up being a pretty interesting dive into emailing upon every change. But it started with the question. Of, can I get an alert every time a tab name is changed?
7:25 And the answer is yes. And we can also go much deeper into this and any kind of structural change to the spreadsheet can be alerted.
7:31 Uh, also we can send an email fairly quickly with these alerts. Hopefully this was pretty helpful and pretty. Very interesting to you because I didn't know the answer to this at the beginning of this and now we went through an experiment.
7:46 Eight minutes went by and we are, we know the definitive answer.
0:10 For instance, someone might ask, if a tab name is changed, how do I get an alert for that? How can I create some kind of alert system if someone in my organization changes the name of the tab?
0:23 Can I do that? And the answer is I don't know. But what I'm going to show you here is me experimenting with it.
0:29 So at the moment I literally don't know if this is possible, but we're going to do an experiment with Apps Script because I do know that there is an on-edit simple trigger we can use.
0:40 But the edit means the user changes the value of a cell. There is, however, another type of trigger where we can install that trigger.
0:52 And when we install a trigger we can use the on-change. Now on-change means you change the structure of a sheet.
0:58 So you add a column, add a sheet. It doesn't, in the documentation, include any information about the changing name. So we're going to experiment with that today.
1:07 And I'm going to show you this experiment, literally, as I do it, because I have never done this before. And I wanted to show you that this kind of answering of these questions can be very playful and very much just playing around with what it is available, and understanding what is available and trying
1:23 new things. So we're going to create a function. We're going to say alert me on form, no, tab name change.
1:31 Okay. So this is the thing we want to do. So what we want it to do, let's do something pretty simple first.
1:38 spreadsheet app dot um we want toast, a spreadsheet toast. We'll just go here, and I always forget how this works.
1:48 Get active toast, that's all we need. Spreadsheet app, get active, and we're going to do toast. And the message we're going to say is, you've changed the tab name.
1:59 So we're You're not even- We're using any variables yet, we're not doing anything crazy, we're just going to check and experiment this.
2:07 We're going to save that, we can call this experiment tab name change. And let's see what. The next thing we need to do is actually create the trigger for it.
2:19 So we'll go over here to triggers. It will need a moment on the bottom right, click add trigger, choose which function to run.
2:26 There is only one function, we don't need to change that. The select event source is going to be from spreadsheet and it's this on change.
2:34 Again we're experimenting if the tab name is a change. You also have on edit here. But again, that is just editing a value in a cell and not the editing the sheet name, I think.
2:48 But let's see, on change should be it, or if it would include it, so save. Now we can also test this on change in another way.
3:00 So actually. we need to authorize these things. Let's authorize it. I think there's another way we can test this, ah, to show you that this will work.
3:10 If it doesn't work, I'll show you that it will work. But let's just save that. OK, now we are triggering any change.
3:18 So again, any change meaning, actually, let's be, this is the other way to test it. So we're going to duplicate that.
3:24 Let's see if we get a, and we have changed. But, OK, the message is just a hard-coded. We haven't actually change the tab name.
3:35 But let's go and change this to data2. That is a change of the structure. And it is, you've changed the tab name.
3:45 That is fantastic. So, it works. So now we know definitively that a change in the structure ah, is happening, but also changing the tab name is this onChange, ah, change.
4:01 Trigger. That is really cool. And so, let's say you are an organization and you are like hey, I want to know if one of my people has changed this sheet in some way, meaning they've added a tab, they've deleted columns, added columns, they're changing the name, That's all included here.
4:23 So this toast is going to show up in the, uh, in the app and in the spreadsheet. So you've changed something, we should probably say, because it's not so specific.
4:34 Also, what we can do here is add a mail app or gmail app. Uh, send email. And we're going to send an email to, uh, we can probably do the owner, but not the session.
4:49 So here in Apps Script we have a get owner, so we can use this app active spreadsheet, get owner, get email here.
4:57 so we will send an email to the owner. We can do variable owner equals get owner, get email. There we go.
5:10 We're going to send an email and we're going to say hey, uhm, hey something's changed. Uh, and in the body we'll say, uh, check.
5:20 out this spreadsheet. And we want to add a URL here. So we'll get variable URL equals spreadsheet app dot get active spreadsheet, get URL.
5:36 There we go. Uh, and we can also So, let's look at. at this body as it's sent. So variable body equals this.
5:46 And then we want to logger dot log body. So we can see it in here. And we'll send that email.
5:54 So now we don't have to update the trigger the trigger will still work we just have to save this and we will go and update something.
6:02 So let's duplicate this and we can go back into our executions look at the ah we see we failed. Let's see what we did here.
6:15 Ah if we refresh we should be able to get the error message. Script does not have permission. So, um what I think we need to do is go to the editor.
6:24 If I just hit run on here I should be able to access the authorization. There we go. We just have to authorize it again, or not again, one more time with all of these new things in it.
6:38 Ah we may get a failure here from the running it in here. But cool. Okay. Let's change something again. Let's change this to data three.
6:49 Go back to our ah trigger, actually execution, sorry. Let's see. It has completed. Refresh. And this is the body of the email.
7:05 And this is what the email looks like over here. It is just check out the spreadsheet. Something's changed. Ah, there we go.
7:12 So this ended up being a pretty interesting dive into emailing upon every change. But it started with the question. Of, can I get an alert every time a tab name is changed?
7:25 And the answer is yes. And we can also go much deeper into this and any kind of structural change to the spreadsheet can be alerted.
7:31 Uh, also we can send an email fairly quickly with these alerts. Hopefully this was pretty helpful and pretty. Very interesting to you because I didn't know the answer to this at the beginning of this and now we went through an experiment.
7:46 Eight minutes went by and we are, we know the definitive answer.