This tutorial is available for all users. Start with a 7 Day Free Trial today and get access.
Hey there stranger!
Let's not be strangers any more. Start a FREE TRIAL today to view this tutorial, right now.
Show Sheet Tabs Based on Edit
About this Tutorial
0:09 Like these are tabs, right? Or unhide or hide tabs or show or hide. So what we'll do is we'll go up to Apps Script.
0:16 And if you have access to this video, you also have access to this apps. Apps Script, just access the sheet, copy the sheet and then open extensions, Apps Script, and you'll get it.
0:27 So what we're going to do is we're going to create these functions here. Just copy them here. So we have a function show sheet and we're going to use the sheet name.
0:36 We have spreadsheet app dot get active, which is the whole file. Then we're going to say get sheet by name and the sheet name is a variable same here.
0:45 So in this function we can enter a sheet name and then we'll have dot show sheet at the end. That's pretty simple, right?
0:57 Let's see it in action. How can we access this function? So we're going to say get sheet by name and then open the sheet name.
1:00 So with the sheet name. Well, one thing we can do is we can check sort of on edit function on edit for an event and we can say e dot source dot.
1:16 We're going to get active sheet here. And what's going to happen is we can name this variable sheet equals. Now we know anytime this is editing what sheet it's on and we can say if sheet is equal to this double equal sign and we can say sheet one.
1:36 So we, we want to make sure that only if you're editing on sheet one. Do you get to do this action, this unedit action?
1:45 We're going to put some curly brackets here. And then we're going to say if that is the case, if sheet equals sheet one, and obviously if sheet equals sheet two, then this won't happen.
1:55 We're going to show sheet and now we're going to It's exact same name as the function here. And in here.
2:01 We're going to put in a sheet two, that's the second sheet. So we're going to hit save. Now we first need to hide this sheet.
2:11 Let's go to hide sheet and let's test this right. Meaning if we edit anything, we can add some more. Criteria here.
2:22 We can do this by getting what is actually being, what is actually being edited and what is the value that that edit is.
2:32 So what text is someone putting in. So what it is is going to be e.value. Now what this means is this is actually.
2:41 What is being edited. So we're going to say, let's say it's the code. We wouldn't even do a variable here.
2:48 Access code. Let's say, and we can even say variable access code equals one, one, one, four ones. Well, we want to double check that this e.value is actually the.
3:02 Editing value, the value that whenever we edit, so we'll go logger.log. Logger.log. We'll do e.value. We'll see if that's the case.
3:15 So let's go back here. We'll put any, some numbers here. We'll go to our execution. We'll see, this is the last execution here.
3:27 Oh, nope. This was the last execution. Just showed up. Alright, we're going to take a moment. This does take a moment to see.
3:35 Here it is, our value. So we have logged this thing that we've entered. And now we can say. And obviously we've already put this here.
3:44 We say double ampersand, which means that if the sheet that we're editing on is actually called sheet one. And the value that we're editing it is the access code here, 111.
3:57 Then we're going to show sheet. So let's see if this occurs. We'll do four ones here. And nothing's happening. So we'll go back and double check that there's some execution.
4:10 We'll see if there's anything new here happens. Does say 111. So our She said the script is wrong in some case.
4:23 So one way to figure out if this is working or not is to go to this function show sheet. I actually deleted the variable here.
4:31 Just put sheet two in here and I selected the show sheet. It looks like we have to because it's spreadsheet app.
4:37 We need to authorize it. So that might be one of the reasons why it's not working. And we're gonna authorize.
4:43 Is it? Now we can. Let's see if we hit run again. Should be fine. It's done it. So we're gonna hide this sheet two again.
4:57 Hide sheet. We want to delete this code. And go back to this is going to be sheet name. We're going to replace it with the sheet two so we can do this with any sheet we want.
5:13 We also want to make sure we activate it. So we're going to have activate sheet two as well. We're gonna save all of that and let's go back to our sheet here and.
5:23 And go one one one four ones. See if it works now. Still not working. One thing that might happen is this on edit is a simple trigger so it might not be able to activate this a particular type of function.
5:39 So one way around that is to do this. We're gonna take this whole thing. Hmm. But before that we do that let's double check logger dot log.
5:54 I think we might need to get this name. Probably this name doesn't show up. That might be it. So if we just go do this.
6:03 Just again just do any edits. Look at our log. Now we'll see if this is true. And there it is.
6:12 It's true. So we just have an error. We don't need to do anything with the unedit. We just need this sheet dot get name.
6:23 You might think double check that. There we go. Now we got sheet one. So we actually needed the name of the sheet in our source, our code.
6:32 So we got sheet dot get name here. We can, I was just editing that. We can testing that. So we need sheet dot get name.
6:44 We get the name of the sheet. This will actually work. So if we type in one, one, one, let's see it up here.
6:50 There we go. Now we have a new sheet and we have set that sheet to active. So if you don't want to sheet set the sheet to active, just comment out this active sheet, activate sheet.
7:02 And now when let's go back, set this back up, hide it here. We can delete all this stuff and it will work anywhere on the sheet.
7:12 One, two, three, four. And it should just, and it does not activate it. Just shows up. Doesn't activate it. So if we unhide this or uncomment it, we now have activate sheet and sheet.
7:24 So now you can have this code. You can grab this code from this sheet show and hide show, show sheets based on a code.
7:35 There you go.
Sheet Stories / Video Notes + ADDED: Email Notifications
ChatGPT Clone in Google Sheets Part 2
Why Different Cell References in AI Integration in Sheets?
Show Sheet Tabs Based on Edit
Add Title Case to Google Sheets
How to Power Testimonials with Google Forms and Sheets
Getting Started Coding in Apps Script
Seek Errors When Coding Apps Script
Think Like a Programmer: Develop The Mindset of an Apps Script Coder
ChatGPT Clone in Google Sheets Part 1
Embed a Number in a Website from a Google Sheet
Create Navigation Like A Book or Presentation
Add Click Tracking To Your Google Sheets | Bitly in a Google Sheet
Hold a Giveaway Raffle in a Google Sheet
Embed a Headline in a Website from Google Sheets
Capture Emails from Website Form to a Google Sheet (Without Zapier)
Quickstart Tutorial OpenAI API in Google Sheets
Create a new Spreadsheet from just a Name in a Sheet.
Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?
Email Yourself a Cell from a Google Sheet, Every Day
OpenSea Data Inside Sheets
Create an Email Campaign Stats Calculator
Twitter App Clone in a Google Sheet
Dylan Asks: How to Automatically Delete Rows If Cell Contains Value
Highlight Row as You Move Your Cell Selection
Create a Timer with Apps Script
LinkTree in a Google Sheet
Password Protecting Data In a Google Sheet
Automatic Weekly Backup of Google Sheets
Create a CPM Custom Function (Create Better Calculators!)
Move Entire Row when a Cell is changed to "Yes" - The $75,000 Google Script
What Can You Automate in Google Sheets? Every single trigger available to Google Sheet users
Sync Two Tabs Without ImportRange()
Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets.
Password Protecting Data In a Google Sheet Part 2 The Basics
Benoit Asks: How to Convert Case
Learn to Code in Google Sheets, For Programmers
Add a Checkbox to Turn on Dark Mode
Write Your First Script
Find Keywords in Any Column. Create quick search dropdown to find keywords
Basic CRM - Add a Powerful Script To Move Row Based on Status
How To Improve: 1,000 Business ideas: Business Idea Generator
Let's Make a Bookmarklet!
Troubleshooting Bitly in a Google Sheet Script
Unique Features - Design a Better Dashboard Part 2
How To Set Up Stripe Webhook to Google Sheets with Google Script
How to Edit a Macro
Sheet Stories / Video Notes + Clear 24 Hour Old Videos
Add A Timestamp to Task Lists (without Now Formula)
Make your Custom Functions Like Native Functions | Custom Function Autocomplete
Create a Changelog Between 2 Cells Custom Function | To learn Double For Loop
New Syntax for WhatChanged Formula in Google Script
How to Record Macros
2 Ways to Delete Lines Quickly (CAREFUL, it's a script!)
Deep Inside Dark Habits Google Script
How to Trigger Macros Daily
5 Ways to Create Coupon Codes | Create UUIDs
Create a Radio Button From Checkboxes Using Google Apps Script