Hey there stranger!

Sign up to get access.

Show Sheet Tabs Based on Edit

About this Tutorial

Show a sheet or tab based on a code being entered in one sheet.

Video Transcript

0:00 Here's a relatively simple way to do Apps Script that's pretty interesting. You can hide sheets and unhide sheets right here.
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.

Courses

Sheet Stories / Video Notes + ADDED: Email Notifications

00:00:00

ChatGPT Clone in Google Sheets Part 2

Fast FAQS

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

00:29:08

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.

00:05:21

Bjarne Asks: Can I show the Last Time of the Last Edit in a sheet?

00:05:43

Email Yourself a Cell from a Google Sheet, Every Day

OpenSea Data Inside Sheets

Create an Email Campaign Stats Calculator

00:35:13

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

00:11:22

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

00:12:29

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.

00:00:00

Password Protecting Data In a Google Sheet Part 2 The Basics

Benoit Asks: How to Convert Case

00:07:35

Learn to Code in Google Sheets, For Programmers

Add a Checkbox to Turn on Dark Mode

00:05:10

Write Your First Script

00:08:31

Find Keywords in Any Column. Create quick search dropdown to find keywords

00:09:37

Basic CRM - Add a Powerful Script To Move Row Based on Status

How To Improve: 1,000 Business ideas: Business Idea Generator

00:11:20

Let's Make a Bookmarklet!

00:12:37

Troubleshooting Bitly in a Google Sheet Script

00:07:07

Unique Features - Design a Better Dashboard Part 2

00:04:13

How To Set Up Stripe Webhook to Google Sheets with Google Script

00:22:10

How to Edit a Macro

00:08:22

Sheet Stories / Video Notes + Clear 24 Hour Old Videos

00:35:10

Add A Timestamp to Task Lists (without Now Formula)

00:07:44

Make your Custom Functions Like Native Functions | Custom Function Autocomplete

00:17:58

Create a Changelog Between 2 Cells Custom Function | To learn Double For Loop

00:23:26

New Syntax for WhatChanged Formula in Google Script

00:07:14

How to Record Macros

00:06:20

2 Ways to Delete Lines Quickly (CAREFUL, it's a script!)

00:09:53

Deep Inside Dark Habits Google Script

00:18:54

How to Trigger Macros Daily

00:06:58

5 Ways to Create Coupon Codes | Create UUIDs

Create a Radio Button From Checkboxes Using Google Apps Script

00:15:01