Can I Automatically Rename a Sheet based on a Date?

Intermediate

About this Tutorial

Change the title of a spreadsheet from within a cell in the spreadsheet.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Featured Formulas

DAY() MONTH() TODAY() YEAR()

Video Transcript

<div>00:00 We got this wonderful question, which is can we rename the sheet or the spreadsheet title, like the entire file name. 00:07 Can we rename that either every day or based on some actions, and yes we can.

Go over to extensions, Apps Script , we're gonna do that there, but the title we're gonna get first is gonna be this B1. 00:17 I'm gonna show you some really cool things you can do based on triggers and other things, but for right now, the first thing we're gonna do is we're gonna rename the sheet to whatever is in B1, and I'll show you that function . 00:27 So, come over to extensions, Apps Script , we'll write, phone number . Function rename sheet .

I'm gonna add a variable name, or actually title, it'll be, we'll get spreadsheet app dot get active spreadsheet . 00:44 We're gonna get the name, we need to get it from the value of the cell. So, we're gonna get sheet by name. 00:52 The name of the sheet back here it's called title so in quotes do title dot get range think it was let's not guess it is B1, B1 and get value. 01:05 So, now based on that we have the new name we wanna get ah now we wanna rename it. So, we're gonna do spreadsheet app dot get active spreadsheet dot rename. 01:15 It's gonna be pretty simple rename.

What are we gonna rename it? The title. So, anytime we run this, so we're gonna hit save, anytime we run this, click save, click run and we will rename the spreadsheet to whatever. 01:31 That title is. So, once we run it, you can see that it says name me this. So, rename me something else. 01:40 Let's do something very ah long. Let's run it again. Execution started and it's rename me something else. So, anything we put into this, ah we can even put some emojis. 01:54 Let's do done. Let's go hit run.

Now, I think we're going to asking the next question is, okay, we can rename the sheet, but we don't want to keep hitting run. 02:06 We don't want to keep come back to extensions app script . How do we do this? Well, we can set a trigger . 02:11 We can trigger this maybe every day or every hour. Click over triggers. Click down below here. Add trigger . Which function are we going to run? 02:19 We're going to say rename sheet . And instead of from spreadsheet as the event source, we're going to use time driven. 02:26 So, here we can use an hour, maybe every five minutes.

Let's go everybody. Every day, at the end of the day or at the beginning of the day, maybe two to three a.m., we will run this. 02:37 So, whatever that day is in this cell. So, maybe we do equals today. And so, we have a date. And we say maybe we have an ampersand here. 02:48 We go. I went in front of today we use quotes and we say hey project I'm gonna put a pipe and then add a ampersand. 03:04 And so, now, we have this number , right? We can format this as well.

You can wrap this with something like year and we can format this. 03:15 And we can say another ampersand, some quotes, another ampersand, and say month and wrap that around today. And there we go. 03:27 It's gonna always update. Maybe we add more stuff here live. Ampersand, another quote, or another slash, ampersand, and day. Today. 03:41 And there we go. We get the 8th of August, 2024. Cool. So, we can create that kind of date.

And now, every time we run this, or every time it runs by this trigger , it'll just go in here for us and click run, and it will update the name here. 03:59 We can click run, and you'll see that it's updated. Done, it'll say the date. There we go. We've renamed the project. 04:05 But now, what if, so that's all great, right? We take a single cell, and we say, hey, go and change the date. 04:15 Uh, name, change the name.

But let's say we have a list of names we want to call it, and we have the date. 04:23 We want to say, hey, today, or not today, but on the 8th of August, change the title of this to the first day. 04:30 On the 9th, call it sleep. Second day, right? So every day, we want a different one. So we have this trigger that's running every day, and every day right now, it's going to do this. 04:40 It's going to just change the title of whatever that cell is. But now, we have an array. So this gets a little bit more complicated, but it's doable. 04:47 Let's call rename sheet this title.

So we're going to create a whole new function . And instead of getting the title as just this one specific thing, we're going to basically get the range of B here, pick out which day it is, find out what day it is, and then get the C in C. 05:10 So we have two things here. We need variable titles is going to be spreadsheet app dot get active spreadsheet , get sheet by name, and here we have a new thing called daily. 05:26 That's the daily. We're going to get a range of the C, column, and we want to get values .

We want, so this is going to be an array of all of those values . 05:37 We want to put this in quotes. Now instead of, not instead of titles, but actually we want all the dates as well. 05:44 So variable dates is going to be the B column. Now we have to go through a, what's called a for loop. 05:53 We say for i equals zero, i is is less than dates dot length. The length means the amount of items in that array. 06:08 I plus plus, we're going to iterate through each one, and we're going to say if dates i is equal to today. 06:19 So what is today?

We have to get a variable that looks and acts, acts just like this variable here today, or the date. 06:28 So we're going to do variable today equals, new date. We do need to format this a little bit because this is a timestamp and it's always going to look different. So we're going to say utilities, utilities dot format date, and how do we want to format this? 06:46 Oh, we want to get the time zone , sure, GMT, whatever, plus eight, minus six, whatever you want to do. The format is going to be, um, year, actually what is that? 06:57 No, not year. It is is dayday slash monthmonth.

Slash yearyearyear. Okay, so we hopefully get the correct date and we can also look at them so we can log logger dot log, uh, today. 07:17 Let's actually just log that variable and see what it looks like.

And if these two things are the same, we want to do this rename to, instead of title, we are going to use use uhm titles, and we're gonna get the same row basically in that array. 07:41 So we'll do that, we'll also log this just in case there's an error , logger.log titles i, cause we might have to do zero here, let's look at it though. 07:55 Okay so let's just run this and see what we get, we may get some errors, we'll work through them. Which we didn't get. 08:01 We didn't get an error , but we did log something, and only one thing, we only logged today.

So we can find, we know that these dates and this today are not the same format , they're not finding the same exact ah format here. 08:15 So let's look at that and see, maybe we have to do some formatting here, format this text as a number actually, custom date and time, so let's just pick out the one that's probably Oh I see it's, I think it's, we need zeros, and we need it, oh we have month month, okay, month day. 08:43 So we need to change our format here, month month So we just need to make sure that these two variables are similar format , so that if it does uhm match , it's gonna rename, if this works, it's gonna rename our project as first day. 08:59 Let's go.

I just saw one. One more thing, as I ran that, I realized one more thing is we have just two numbers for year here, so let's actually keep this all four numbers, and change this format , format number , custom date and time, and use full digit, okay, perfect. 09:23 Now let's see if this works. So again, these two numbers need to be aligned. Let's see if it works. Nope. 09:32 Let's do one more thing to check and log actually uhm just all of this.

We will log it even if it's not, just to see what it looks like. 09:48 We can also just, uh, we're gonna delete all of these rows, so we only go through a few rows. Let's look at those. 09:58 Ah, I think we're looking at title, oh. Of course, we need to change this, oh no, dates. Let's go. Actually, let's log dates instead. 10:09 That was the mistake. Save it and run it. Ah, so we do have an entire timestamp here. Um, so let's make sure that these dates are the same. 10:28 So variable date to date. Check is equal to utilities. format and we're going to format this date.

GMT on the exact same format as up here. 10:56 There, so now we're going to format the date just to make sure we check it appropriately. let's run it. Exception we have here. 11:08 The parameters don't match . Oh, format date. That's what we're getting. So I think what we have to do is actually make it a date uhm format here. 11:23 So we have to wrap it with new date. Uh but even though it's dates I, let's run run that again and see if that works. 11:31 Perfect. So now we see we have renamed it actually second day.

Okay, so we are somehow getting the wrong date but we are doing something correctly. 11:46 Ah great. Ah so what I had to do is make sure that the time zone was actually the right time zone where I am because the dates were not aligned correctly. 11:59 These dates were actually different. They're different. And if we're in a different time zone , it's going to look at it differently. 12:06 So ah that's the answer of why it was doing a correct, doing, actually doing the change but it was not doing it to the right ah title. 12:17 So make sure that your time zones are all correct.

And this is it, yeah. So now we can rename sheet this, rename sheet this title. 12:25 We can run this function every day, and it will, based on the date, rename the title. To what is in the column here. 12:35 So I hope this is helpful to you. You already saw how to create a trigger earlier in the video where we did rename the sheet. 12:41 Ah if you want to delete triggers, which may happen if you're not wanting to rename the sheet anymore, go over here in triggers and delete trigger . 12:50 There you go.

If you are a BetterSheets member, you can watch this on bettersheets.co and you can get this exact app script and the sheet down below. 12:59 If you're not a member yet, become a member today.</div>