Google Sheets Stories? No! But we'll add timestamped video notes to your google sheets.

 Hello, welcome to a very special Better Sheets video. This video I absolutely will be putting out on YouTube very soon. So check it out on YouTube and share this video with anyone you want. , this is a pretty funny or interesting little hack. I saw Benedict Evans made this joke about Excel having stories and as it, how it could be a inevitable place.

And I was like obviously this is a joke from 2018 and then people are like, this needs to be real. And I was even like, oh, this needs to be real because very often I'll be sharing stories on, oh gosh, story of sharing videos about a certain spreadsheet and I'll put that into email. And I realized like this, it could be a real thing where we could have.

videos about the spreadsheet that you're working on, but obviously I'm not going to be doing anything with with the UI of Google Sheets. But I thought of this really interesting way to do video notes and be able to make it easier for people to enter video notes. And I thought it would be a great way to show you how you can take a form inside of Google.

So you don't have to have an external Google form and get it into a sheet. And also I wanted to make the script that I'm writing available to all better Sheets members. If you're not a Better Sheets member yet and you're watching this on YouTube, then check out better sheets.co where I go through a lot of different creative, interesting ways to do cool stuff on Google Sheets.

And I share with you. What to do, but how to do it and how you can do that for a wide variety of use cases. So and also what's cool is every Better Sheets member gets access to the sheets that I make. So all of the script you're seeing here is available to better sheets members. If you are watching this on YouTube, you get to follow along.

Copy and and type. But better sheets members can just grab the sheet, make a copy, and have it available. Okay, so this video's gonna have a few parts because I'm gonna try to do some things that I haven't done yet. What I have done so far is this. So if you are working on a sheet and maybe you have some kind of spreadsheet here with some data, and maybe it's a CRM or something, and it's like customer.

and you have all your customers here and you're like, oh, this column needs to be something. Well, , I added this custom menu, which is a really cool thing in Google Sheet that I've enjoyed a lot and really makes your Google Sheets feel like a part of your office and not just like an external tool you're using.

And you can, let's say I'm just gonna go to my YouTube channel. I'm gonna copy link here. When you make a Loom video it automatically puts the URL in your copy clipboard. So it'll have it. So what we'll do is go up to video notes. We'll add a video note and it says, paste your video link.

And I just paste my video link. Okay. And it's now on the tab of video notes. And it also has a timestamp of when it was submit. I'll show you how to do this and I'll show you how to do the timestamp, but what I also did is if you were on here and you're like, oh, I wanna see whatever, like I, I don't know what video, someone just emailed me and said, Hey, there's a video for you and I know it's the last video so I can watch the latest video.

And are you sure you'll wanna watch  and say, okay, and it will open a new tab with that video. What's that video that I just copied and pasted? We'll find. Let's see if I get the right one. It's a really exciting, it's because it's very slow loading. It's very exciting to see if it's the right one.

and it doesn't have the title yet. Of course it's gonna be because it's loading video and recording this loom at the same time, so it's struggling. Struggling. My, I didn't reveal data in check boxes. Was that the last one? Was that what I. Yeah, that was, that was the exact video. So again, we can copy this actually copy the video and then let's try that again.

And just so you can see that it changes whatever the video you're gonna watch here. So add a video we're gonna paste and then it's finished the script. And now we're gonna watch the latest video note and I'll show you how to edit all of this. And you can choose that dialogue. You can. Your own, and hopefully, fingers crossed it, it, it opens a new different video than the one we did.

I'm gonna pause the video and we'll come back right away. Yep. So we got the correct video. We got the last video that was sent submitted. Cool. So that's what the functionality does. Now, how did I create this? We did it all in script. First we created a new tab called video. And I just have two columns here, the link and when it was submitted with the timestamp.

I'll show you how to fix this right now. It just shows you visually the date, but it actually has a timestamp of the time there, which is important if you want to filter these for some reason. But let's go to our script and I'll show you how I did this. So,  number one thing I created on open a new custom menu.

Okay? And this is part of the ui. You can do spreadsheet app, dot, get ui, and then create menu. And you cr you name that menu here, and then you add an item and the two item, the two parts of this. The visual name, like what you will see as the button or the item. And then what is the R function that will run that's the second item.

So the second item always is like no spaces, and it has something like sort of it might actually be something different, but what you say to a user is going to be whatever you wanna say, this is what this function does. And. This function must exist down here. It could be actually on any of these other pages if you have multiple pages of script.

But here's what we did. We just went we said, Hey, get a UI of prompt and say, paste your link. And then we set a button of okay, or cancel. And the response of that, if it's okay, if it's cancel. Don't do anything. If it is okay, that means we have some response. So we take that response here, we say response dot, get response text.

So we say, here's that variable. We say get response text, and then we grab the place where it's gonna go, where you don't want to, you wanna set it on the row, that's first blank row. And we get that by getting A range of the last row. So we go here, get last row, and we add one to it. And so we get this range and we, you're gonna set this value response dot get response, text, whatever they say.

We're gonna get that last row add one because the last row is gonna be the last row with text in it or some data and then in column one. Okay. So that's how we. This video note into here and how we don't have to worry about it writing over itself. It's just gonna find the last row here. And then add one, and then set the value here with the timestamp.

this is a funny thing. So it's the exact same set value except we do column two. So in this get range, we're using numerical numbers here. A lot of times when we're doing script in get range with get range, we'll use a one notation, which means we will literally say set the range of a one as. Or set the, this a 10 or B 10, that's a one notation.

But in this case we're saying just a numerical value of last row plus one for the row. The first item is the row, and the second item is this column. Okay. So what we need to do when we set the value of the timestamp is we just have to set the column to. . So we say, okay, find the last row again, plus one, and just set this time stamp.

This is a funky thing that made me realize something's not exactly the right thing here. Because as I thought about this and I set it up first, I said, okay, set this respon, set the URL here, and then when we set the timestamp, we, the last row already exists, so we want to.  the last row instead of the plus one.

We don't want to add one because the last row already exists where we want to put it and we can put two, but this was wrong. This kept having the timestamp up on the la this row here. So I'll show you. Actually, I'll save it and I'll show you in programming language. I, maybe I'm wrong about this, but I thought this would run again and grab the last row once this is set.

Now the last row is. And so we don't have to put the plus one. That's what I thought. And so I'm gonna save that. And let's just grab, I'm gonna just grab this url. We're gonna put it in again, add video note.

And we're gonna put, okay, now see we have no timestamp here. It's because this timestamp was it. So I'll show you. If this was blank, that's where it will go. So add a note. You paste our link.  and now see what's up here. And this really threw me for a loop cause I, I just didn't realize it until I had to do the exact same thing here as here.

And I just add plus one. And so now, even though yes, there is text on this row, once we set it, it still looks for the last row it, it still gets the same last row. So now I will delete this and. . I hope we saved or I'm gonna look stupid. Let's paste that again. There we go. And we didn't say, we did not save.

I looked stupid. Oh, this red thing means we didn't save. Okay, let's save it. And let's try that again.  eye on my face. Let's do that. And video notes. Add video note. Cool. . And there we go. We got our timestamp in the correct row. And again, this timestamp has a time, so we're gonna fix that in just a hot second.

The l last, the last part of this is just if you click cancel or nothing then it logs without the user knowing it logs, hey, canceled. This was just to be able to test.  that is actually happening. The LA watch, last video I will get into in one second. Bef after we go through, let's fix this to show the timestamp.

So we're gonna select the entire ro column. We're gonna go to format. We're gonna change this not to automatic, but what we want. Format number, we want date. . There we go. And so now we have the visible time. This, this really helps. The data will be there, but it helps visually to know, oh, when was the last one made?

Even if you fill filter this, sort it somehow, some way, maybe you have different things you want to do, you can add Thumbnails to this. If you want to scrape sort of this URL and get this fee equals you can easily let's go to YouTube thumbnail. It's like a really easy url. And you can just add that me thing there.

And actually we might do that in this video. There you go. So it's just image. There we go. So all we need to do is, this is actually a really fun thing to do. Let's say we wanna add an image here. We're gonna come back to this, but I'm just gonna add, oops, I'm gonna add it here just to not mess up the script.

Thumbnail, and I'm gonna add this here. We'll come back to this in a hot second. I'll show you how to make that thumbnail based on this url. Cause we have to do a little bit of work on here. Okay, so how will I get the timestamp? Or not, sorry, not how I get the timestamp. The last part I, that I've already done is watch last video.

So when you click on watch, last video, You wanna be anywhere here and you just wanna be like, I wanna watch the last video. I don't wanna have to go to that video notes tab, search down the list. I don't want to go to the last one. It's a lot of work. So I made this easy watch last video. It will just take this last url, put it in this sort of open a new tab and show you.

That's it. But let's cancel that  and I'll show you what it's. . So what it's doing is it's getting the last row again. We're grabbing just that dot get last row. We are getting the range of the last row and they column one, we're getting that value and we put it into an alert. So we say, Hey, UI dot alert.

We're gonna watch, we just set a couple buttons. Okay. And cancel when the, when the if the response here equals, okay. , we are creating a, your html here and it's just doing window.open. And then we're inserting our last video URL here. So in these quote, we use single quotes, then double quotes to get out, and then we add last video and then we go back into it and we go Google dot script host close.

So we close that. Html. Sort of interface. We create an interface here and we say opening a new tab so that we get out of sheets. This is a really funky way of getting to a new url, but it does decrease the need to like click buttons to click on a url, click on a link, find the link. It decreases all of that and, and sort of does the work for us of opening a new window in our, in our browser.

Probably, I would say probably this doesn't really work too, too well on mobile, but who knows? I haven't tested it just yet. You're more than welcome to test it yourself. But here we go. We have all of this HTML service we use. We created an HTML out output. And then we show that modal of, hey, opening a new tab and then it automatically closes here, this Google script.host dot close that closes it after opening it in a new window.

And that's it. Pretty simple way to open a URL that we have on this.  List. A couple of other use cases for this might be not just video notes of what you're working on in this sheet, but maybe there's like sales videos you need to go through or this is great for versioning. So if you are working in a creative agency or you're trying to create a commercial or Facebook ads or YouTube ads and you're creating videos and you wanna.

Okay, here's this video we made on this day, and then the next video is on this day. And you want to keep them so sorted and ordered appropriately, but you find like, oh, I have to go in here and type the timestamp every time. This might, this whole like video notes and adding a video. Might make it really easy for you to keep track of, Hey, this is the new version, 4.5.

Here are the changes. Instead of maybe a thumbnail you can add what are the changes like a gi GitHub or GIT repository might have you sent, add some notes to what that video changes were. This could be used for that really, really well. And you can also set up a new sheet every time for a new commercial you're making.

Maybe it's a month long, two week long process of script writing, creating the video, editing the video, and then multiple versions of each of those. You can set up a new sheet for that easily. I. Or if you're right at making Facebook ads and you're like, oh, we need to make 20 ads, but video ads. But we need to also make sure each of those are correct and, and have a checklist.

You can have a nice little checklist here. I have some other videos that go into that. One thing I'm not gonna go through in this video, I will show you how to do this thumbnail and we'll do that live in this video. But one thing might be in this script, you could once you add a video, Right. Oh, after here, right here.

You could add a v email. You could email this. Now I'm not gonna go through it in this video because there is a series of videos about how to do scripting and send a email. So I already have shown better sheets members this. And if you're a better Sheets member and you're watching this right. Go into the folder and if you can't find it, you're more than welcome to email me.

But you can email this link by using this get last row here. This whole part. You can say, Hey, take this email. Email me anytime someone submits an video. So if your team is submitting multiple videos, you want an email for each one of those. This is a really good way to.  Just right here. You could email this to, to your team or yourself

right here. Just add script, . And again, I've, I have a video about how to email at least yourself, how to email others. I've set up a team email where every time something was submitted, it emailed the entire team. It also included a link to the. . So that was like really special. So instead of just getting an email that says, here's this thing link that was submitted, it's like, here's a link that was submitted.

Here's the sheet that it's on so that everyone can easily go, go to the act that sheet, make some notes, check it out delete it if needed. There's a lot of stuff you can do based on just email and you can email from Google. This stuff. So the last part of this video that I'm gonna go through is how to add this on that we're gonna do this live.

I'm gonna, we're gonna scrape or cut this url. We want to just get this V and what we're trying to do is fit it in between here and we're gonna go, we're gonna find, let's do this, this is what. End result is gonna be, it's gonna be six, six zero jpeg. I think it's gonna be that. And instead of x xx, we're gonna have this code right here.

And let's just double check. We want double check that it's actually gonna work once if we actually get it. So, whoops. Let's just kill this X, X, X. And this is the end result we're gonna have. Cool. We're going to I wonder, oops. Yeah, that's not gonna work. Let's do this. Besides, let's make it 50. There we go.

I want.  Center. So we'll go get a nice little thumbnail here. There we go. We got some style. But we want to replace this code ID with this one, right? Cause if we just copy and paste this, it's not gonna work, right? It's just gonna have the same thumbnail. Okay. So let's do this. Instead of this, we're gonna do.

And, and this. Okay. Nope, it's not. And

plus. Okay. And here we're gonna split here. We want to split it around equal sign.

Then we want. See,

okay, let me get this correct and let's look at what we're doing here when we split it first, lemme cut. I know this is gonna give me an error, but whatever. We will right click insert, or right here.

There we go. So let's see what happens when we split it by the equal site. Okay, so we have this first this watch kind of stuff here, and we have this and, and t. Okay, so it's our second thing. Let's just make sure it's gonna be the same one here. So we can use index and just grab the second part of that.

Row is one column. , we still have though that and sign. So we want to, okay. It's gonna make, do the same thing. So now we want to also split around any and and see what that path does. And now we get a T over here and now we want the first part. So we do index.  Comma, row one, column one, and we grab the that u url.

Now, this only works if the V equals is the first one. If this T equals one s and the V ID are flipped in the U url, this will not work. But for right now, and for our sake, where you just want this, so I'm gonna copy this, all this text in here, all this function, I'm gonna delete it from here. And we're gonna put it into where this plus sign is.

Okay. Now we should be okay. Nope. What is going on?

What did we forget? Did we forget something?

Oh,

that's not gonna.

Maybe,

oh, I know what we need to do. One second. All right. So yeah, that was, that was a funny thing. So instead of the plus sign, so before I used, let's do it here, I'll.  did two quotes and then we used plus, right? And it wasn't working with the code because this code, Google Sheets thinks it's a number. . And so a lot of times these will start with numbers.

So it didn't work because of this plus sign. So what I did instead is use concatenate. So we just concatenate, I always forget how to spell it, to let Google sheets do that. And then we do commas here and inside of these commas we put this function or this formula here. And now we're con contaminating this first part of the.

Along with our ID here, and then we just end it in this concatenate with z z slash zero jpeg. So we get a let's just do, whoops, let's copy this. And now we're getting another concatenate from Split perimeter. One value should not be non empty, so what's probably happening is, That the split is not working correctly here for us for some reason.

Let's see. I'm gonna troubleshoot this and be right. All this was a silly mistake. Super silly. So in  in this row, this thumbnail should be not a four. It should be a two . That was a silly one. So now that is the correct thumb. And now as we copy paste, we get the correct thumbnails. Cuz what it was doing was it was pushing it one or two rows down, which it was splitting over here, which doesn't exist.

Okay. But here's a really good way to, to get around this error. Okay? So if we know, okay, we might have an arrow down here, we can wrap this whole thing. We don't really need a thumbnail. We have data in a two so we can do if is blank a two. And now if it's true, meaning if it is blank, we don't wanna do anything.

So we do two commas and then our false meaning it is not blank. We have something is our image and we wrap that around the whole thing. And now when we get here, we get nothing, which is good. Nothing in this case is. So now this thumb no will only show up when we have a link here. So let me clean up this sheet.

I'm gonna delete this column. I'm gonna delete, actually I'm gonna delete all these columns. You need to delete all these columns. And now every time we add a URL here, we're gonna have a thumbnail. So that's pretty darn and cool. Let's how to make it interactive to-do list. Let's copy that link. And just test that it works.

We're gonna go to video notes, we're gonna add a video note and we're gonna paste it here and let's, yeah, we got a thumbnail. Cool. And we can do that with any your any URL really, anything we want put here. There's no real validation. Or verification here, we can literally put anything we want here.

And so we may have some errors and problems along the way. Like here it's a reference because there's nothing to split here. Yes, there could be duplications and all of these things. You could probably figure out how to get around if you watch more of better sheets videos. Thanks for watching.

Hopefully this was really fun and interesting for you to add. A video notes section. I thought it was fun because it's something that I, I was literally like this morning trying to figure out how to add a UI here of little circles and put some videos in there and I was like, wait, this is way beyond my wheelhouse, but I do know how to add a a list of videos into Google Sheets.

And so hopefully this was like a fun little workaround of how do you do stories in. In Google Sheets sheet stories, I guess. So maybe this might have a, a clickbait title of like add, add stories to Google Sheets. So don't, don't hate me. And if you got all the way to the end of this video, I'm so happy that we were able to do this.

If you are better Sheets member, you have access to this exact script, you can go and grab it from the. Folder. You can also email me if you have any questions. And there's tons of stuff we could add to this, like add in an email. I might do it in another video. We could add we could add that it actually does disappear in 24 hours maybe.

We have a little script that says, Hey, when this timestamp is more than 24 hours In the past, delete it. Delete clear that that cell we could have add notes, you know we could do who submitted it. That's a really cool thing too, if you're working in a team and you don't want people to have to like select their name.

This happens a lot. It adds a couple of levels of complexity. , but not complexity at time when someone's like, Hey, I write, I wrote a note here. It's a com collaborative doc. You have to write a note and then you have to say who you are. You have to select your name from a dropdown menu. That, that gets a little burdensome sometimes.

So really when we're typing and we adding something, it's really simple to say, who is the logged in user? Get their email address and just put that in there and say, Hey, Brent did this, or Brett did this, or Tiffany or Michelle did this. It's really easy to put in people's names. If you know they're a logged in user and, and it's only login users and your team that are putting stuff in here, so enjoy.

Also, you've got to see this on open thing, which I love putting custom menus in sheets. It really adds that el extra element of like, this is our sheet. , please ask questions. Feel, feel free to ask questions. Email me any better sheets. Members have my email address. You can reply to any email you've gotten from me or from better sheets or gum where you bought the Got it.

Where you bought the better Sheets access. Thanks so much for watching. Bye.