Visicalc 2023 Technical Walkthrough

Advanced

About this Tutorial

welcome to the technical walkthrough of VisiCalc 2023. This is the sheet that I released on April 1st for April Fool's Day. Learn how to use the onEdit() function in Apps Script. We learn the Apps Script for: Save a Sheet , Active Cell coloring, and the Out of Memory Toast.

Sheet Resources

Access Sheet

Copy Sheet

Preview Sheet

Video Transcript

<div>00:00 Hello, welcome to the technical walkthrough of VisiCalc 2023. This is the sheet that I released on April 1st for April Fool's Day. 00:08 It is a working copy of of VisiCalc inside of a Google sheet. So how did I do this? Well, I'm going to showcase in this video three specific things three. 00:20 Specific things that I think are really useful outside of this particularly like sort of fun thing.

And I want to show you step by step sort of what they do. 00:32 And again, if you're a Better Sheets member, and actually even if you're not a Better Sheets member and you're, happen to get VisiCalc, you can get, you get a copy of VisiCalc for free. 00:40 Better Sheets. You can get this for free and again, Better Sheets members. This video is exclusive for members this technical walkthrough to share with you how I built this. 00:53 And again, the three things I'm going to go through are, how does it save a sheet?

What we can do here in this com, ,command is command s and it will create a duplicate copy of the tab you're on. 01:07 How does that work? I'll show you that. I'll also show you this active coloring change. So we do greater than, let's go to B1. 01:18 On that edit it changes the color. , ,which is very useful, not just for active coloring acknowledgement. But this is pretty cool so that you see some feedback. 01:34 You see, like, something happens to the sheet, the coloring, the colors change based on some input. How does that work? 01:40 Well, I'll show you,.

And it'll be based on the onEdit function inside of Apps Script . And then I also want to share with you this funny sort of thing I created which ends up being really useful. 01:53 If you add a you might want people to add some columns. You might want people to make certain changes on your sheet. 02:06 But what does that how do you get that toast? And it's one of the simplest, easiest things to do in Apps Script . 02:13 So I want to share that with you in this video. Those three things are going to be the technical walkthrough of this sheet. 02:19 But let me first before we.

Jump into anything share with you this thing. Also, we have a menu up here, which I go through in other videos on how to create a custom menu . 02:29 And if you have not taken fear and better sheets member and you have not taken yet spreadsheet automation 101. I highly recommend to do that. 02:38 I highly suggest to do that. It is a total.

Of three hours of videos, whereas like other courses might be 567 hours. 02:47 But in three hours of videos, you go through a few lectures of just some basic functions you need to do. 02:51 And then you really get into honing the craft of opening Apps Script , which we'll do right now extension Apps Script opening using it in. 03:02 Any different ways you'll learn how to send an email from a Google sheet, you'll learn how to use an API from a Google sheet, all with Apps Script . 03:08 And I show you a step by step in that that course here on better sheets completely free.

If you are a better sheets member and you're watching this video, you have access to better sheets. 03:22 And go check out spreadsheet automation 101. You do not have to buy it on Udemy. All right. So this is all of the code and it might look scary if you're new to Apps Script . 03:35 And if you're not new to Apps Script , 400, 500 lines is not necessarily that much. I think I've, have some that are like a thousand or so lines. 03:45 But we're not going to go through every single line here.

Again, if you go through spreadsheet automation 101, you'll be able to read all of this 100%, I think so. 03:56 What I do want to start off with is this on edit. Everything is based on this on edit. And again, there's other videos here. 04:02 Better sheets that go through this on edit much more and this event and how to write this code. And if you go through spreadsheet automation 101, you can absolutely go much deeper into it. 04:11 But what generally this is on edit means that anytime a user edits a cell.

So this is a little different than you might think. 04:21 If you're just if you're cool. Editing a sheet you might think, oh, that includes adding a column or making a new sheet. 04:30 And that's not true. That's another thing called like a make a chain, a sheet, a change to the sheet on edit. 04:38 It literally means you're changing the value of a cell or. The range of cells . Once that event happens, this built in function on edit executes and this E that's here is a event. 04:56 And it has a lot of things in that event.

It shares with you what the range of the cells are. 05:02 It shares. What that edit is, what the old value is. And so you can access those things. You can access where it is, what's going on. 05:11 And what happens is immediately I say this if statement. And if here works exactly like it does inside of a formula , but in Apps Script it just has a little bit of a difference. 05:23 It's a different syntax because it is JavaScript.

But it says if the sheet that we're on, which you can access that through the event, you can say what if the sheet that we're on that the cell is being edited is this busy calc. 05:34 Okay, that's one thing. And the row is the third row, which is right here. And the column. Is one and the text is not blank. 05:46 Do this stuff. So actually that third row is this command line here, what we can do stuff to. But we have a different command line and I will find it right away here. 05:57 We can just search through this. There's the other one. Okay, so this is row two. So as if the.

Edit here is on any other cell on any other sheet. 06:07 Nothing happens. This doesn't execute. But what does execute is when it is on the VisiCalc sheet, when it is on row two and column two, which is this B two here, this command. 06:17 So if I type in something like command S something's going to happen. That is an edit. And what happens? Let's go look at this if. 06:26 So if the first icon, the text at character zero, the first text is greater than.

Okay, we're now inside of this. 06:34 But if it's not, if it's not a greater than, it's going to skip all of this, which is really cool. 06:39 Now we have these gates, right? In Apps Script , we have gates. We say, okay, this event is happening. And it is happening. 06:45 And if this, the very first character is a greater than, do this. But if not, just skip this whole thing. 06:51 We don't care about any of this. We have oh, and then if the text is A, B, or C, or D, it's going to capture the text here. 07:03 So we have. So this is used for greater than B5 to move it.

There, there it's moving it. There we go. 07:16 And if we're, we're going to grab that text and then we're going to get the number . So the text, the, the letter is the column and we change the. 07:24 Letter to a number here using this switch case, which again is a formula inside of Google Sheets, but it works a little different here because it's a JavaScript, but we say switch . 07:34 And then based on the case, a we're going to say, Hey, that column is two. That's a second column. Wait, why is that? 07:43 It's because I had built.

Inside of here, a another column of information and more rows. So we're actually sort of interpolating. 07:57 Okay. A one in VisiCalc is actually B five and Google Sheets. That's the real deal. So we have to change. 08:04 Change all of that. So the input that greater than a means, oh, it's actually column two. And the row here is plus row. 08:12 We have to add four to it right here. And what we do is we now say, okay, we think we get the new active cell . 08:22 We are setting that active cell to active. And then we also set VZCalc active. Well, what is this?

This function includes another function inside of it. 08:32 Let's go look at that. So there, here it is. So we're saying, okay, set the VZCalc active. Whatever is in, I think here, A1, it will grab. 08:45 It'll change the color. It will get the, it will get the cell based on if it's an A1. And then it sets the background color and it sends the font color right here on line 373 and 374. 09:00 This is cool, right? So what we're doing is we're saying, okay, taking an edit, finding out.

What that edit is, which is the letters, the numbers, A1, A2, B, B3, B5. 09:11 Then we're going to edit that A and A1. We're going to grab that again, get that value. And based on that value, we are going to set the background color and the font color. 09:22 So that is literally all we're doing. So quote, unquote, when we are, We're setting an active cell , which means this coloring. 09:29 We're literally just changing the background color and change the text color . And it's quote, unquote, active.

We're also setting an active cell , which means this is a new physical active. 09:48 We're setting that based on a value. We're saying, okay. Based on this exact value. It's a one. We're going to take that value and write it into a one. 09:56 This is actually pretty simple. Literally, just one line of script here. Set active, meaning spreadsheet app dot get active spreadsheet dot get sheet by name. 10:04 Visi calc dot get rain. Change a one. So a one of Visi calc set the value to whatever the value is that this function is getting. 10:12 So if we say.

That other function we had added four to the row, we had changed a we wanted a two, a one, a three. 10:22 We got all that value. Put it in here and set the value. That's. One line of script right there. Bam. 10:28 But as I said, we are also going to look at saving. So we created a function save. Let's go see where that save is done. 10:39 And here we go. We have if text is slash s. So. If it's. Greater than. A one. Right. We're changing that value and not value the background color and the text color . 10:53 But if it's slash s, it's something else. It's this line here.

And it's one line function . Run the function save. 11:02 We can run a function in app script and we just did it twice before you just saw. We can run a function we create in app script from a function we create in app script . 11:11 We just have to call that function. And here we have save. Alright, let's look at what save does. Let's go. 11:20 Oops. Let's go look at what save does. And what does it do? It is here.

I have lines of app script and in actuality it is literally only one line but we have a bunch of variables. 11:33 Okay, the first variable, spreadsheet app dot get active spreadsheet . We're going to need the spreadsheet file. The file we're in. 11:39 We're going to go grab the name of the sheet. VisiCalc. Okay. That's it. Those two lines were grabbing the. Name of the sheet. 11:47 VisiCalc. Now we are dot copy two. So saved means we are creating a copy. Of VisiCalc to the sheet that we're on. 12:01 This sounds crazy to do but that's how we create a new tab is we're.

Taking this tab which is VisiCalc and we're dot copy two. 12:12 The spreadsheet that we're in. We could save this to another spreadsheet file which is really cool and a whole nother thing about this but right here. 12:22 This is how we duplicate a tab is this dot copy two we're taking the existing tab. Called get sheet by name VisiCalc and we're copying it to the spreadsheet file. 12:33 That is how we duplicate . This sounds crazy, right? To copy two is duplicate .

There is no dot duplicate function in Apps Script or in JavaScript. 12:43 It is literally the dot copy two which is duplicate . Now. We also set the name. This is the cool part where we have set a name and a formatted date. 12:57 So a timestamp . I was originally going to just create a new tab and say, okay, here's your saved version. But I realized that saved versions can be more. 13:08 You want to save your progress. You want to save like each month. You want to save a duplicate of that that sheet or something. 13:15 And so we need to we need to have a unique name.

If you set the name as the same name as another one, you'll just get an error that says this this sheet already exists. 13:24 You can't name two tabs as the same name. So I add the curated date here, which is just a formatted version of a timestamp new date. 13:36 And that is it. That is the save function here. Anytime we run save, it is taking a tab. It is then creating a copy of that tab inside the sheet as it that it exists on. 13:48 And then. It's renaming it set dot set name to visit called save plus a timestamp . That's it. Okay.

And I also want to go through in this video, how to do the toast, because I think toast are really fun, really engaging. 14:02 It gives you immediate feedback in a moment when you're executing some kind of code. So if you. Are trying to test your code and you're like, is this working or not? 14:12 Sometimes you can do a toast. And why keep calling it toast is it's literally dot toast. So we go spreadsheet app dot get active dot toast and we give it a message. 14:22 Text. This is this line of text right here is that message that you see. You have.

Don't have to do any special formatting . 14:30 You can't do any special formatting . But if you engage this, let's do a column to the right. See it'll delete it and then it's a busy cut out of. 14:42 So when you're executing code and you're trying to write some apps script and you're like, I need some. Visual visceral feedback that something is happening. 14:53 Toasts are the answer. And then you see they also go away. So it's it's not a permanent change. Sometimes you want to add add text to like a one here. 15:05 I edit that. So if we do be to here.

You'll see a one change to be two there. That's okay to do too. 15:14 I sometimes have a scratch, a script, scratch tab where I'm like, all right, I just want to write stuff to a sheet just to see that like this stuff is happening and things are happening. 15:22 But I also like to see a display right away in the tab that I'm on. Okay, boom, this toast. So give you a couple of options. 15:29 Here when you're writing script and how to like debug script and actually see that it's working. But what's going on? 15:36 This is not an on edit to get this toast.

This toast is not being caused when we're editing say a cell. 15:46 So how does this function run? It's actually a little bit of a deeper cut inside of app script . We have created a trigger here and a trigger is on spreadsheet change. 16:02 So on change or from the spreadsheet on change trigger , which you can add any trigger here from spreadsheet. On change. 16:11 So on edit is here. You can do this trigger creation manually for on edits, but I like to do it on the on edit function inside of an app script. 16:20 But here on change is not an available script.

So you have to go on change and then run some script. 16:29 You have to. So let's create the script function and then select that function to run anytime that there's a change. 16:36 What is a change? A change is adding a column, adding a row, deleting a row, deleting a column. It is changing the spreadsheet as it is. 16:49 Okay. That is on change from spreadsheet .

So whenever this trigger is run, which is that it's like adding a column, it runs that toast and it says, this is a cut out of memory, but it also runs what's really cool. 17:08 It also runs some other stuff too, which is delete. It's the stuff that was created.

So go for that toast again. 17:17 So it, let's see, we are on the active sheet , the sheet that we're on at the time that we're doing it. 17:23 If the, if the active sheet is VisiCalc, meaning we changed the sheet called VisiCalc, if we're on another sheet, none of this happens. 17:31 We are going to get the sheet by name, we're going to get the Max Rose, get Max columns, if there's more than 30 rows and there is, oh, if it's more than 30 rows, we're going to delete it down, we're going to delete all the rows in excess of 30. 17:47 If it's more columns here, we're going to, delete the extra columns.

We're also going to rename set the value A, B, C, D, because just in case someone had, like, inserted a row here to column left, this would delete the E and now it writes it back in. 18:05 So we had to write that and that was a little tricky to figure out and might not be the best case. 18:09 This was sort of a fun, little out of memory gag here at the end. But I hope this shows you that you can do more than on edit changes. 18:18 You can create these triggers that are based on changing the sheet itself, the structure of the sheet.

So here's something extra before we go. 18:30 Want to share the, This with you. Is that on open as well. So there's on open. And then one of the things that I mentioned in my onboarding video of like VisiCalc is you go up to the VisiCalc menu and you install VisiCalc. 18:45 What is, what happens there? Well, let's go.

Let's create a, you can actually sort of, see it by the name of the real function . 18:53 So it's as, install VisiCalc is the outward user facing name of this function , but the function that it actually is in my app script , I called it create on change. 19:04 So let's go look at it. And here it is. So what we're doing is we are going to the script app. 19:08 We're gonna get a user trigger . And we're going to see if triggers.length is greater than zero, meaning there are existing triggers. 19:22 We'll skip. We're gonna skip this step.

If there are no triggers, then we are going to create a new trigger script app.newtrigger. 19:31 We're gonna call it create limits. We're gonna say for the spreadsheet that is this sheet we're on now. It's an unchanged spreadsheet and we're gonna create it. 19:42 This trigger create limits is actually the function name that we're gonna cause. So this line here, these lines here script app.new trigger for spreadsheet sheet. 19:52 OnChange create sets. This create limits function as a trigger right here. So what's really cool about this?

Now this is like super advanced but like extremely useful. 20:11 If you wanna, give a sheet to someone and you're like, hey, when I give you this sheet you're gonna have to create some triggers and you make a video and you're like, hey, go to this trigger sheet, add triggers, add, select this script, do this thing. 20:24 You don't have to do that anymore. You don't have to give someone the, show them the steps of adding a trigger . 20:31 You can tell them. Go up to a custom menu , click this button install or run or install trigger .

And it, this, this app script will create a trigger for you, for that user. 20:46 Why is this cool? Because there's some weird limits to like triggers. One, if I create a trigger and I give you access to the sheet, You can't see that trigger . 20:54 If, if I give you access to a sheet, you create a trigger on my sheet, I can't see that trigger . 20:59 Even though the trigger will have an effect on everyone and on that sheet, I can't see that.

And so when you're like, oh, I want to create a trigger and that, and I want this, And to be able to delete it later or something, how do you do that? 21:16 You would write an app script here that installs the trigger for them. That all they have to do is go up to a custom menu here and click the button. 21:28 It installs the trigger . You can also, you know, create a trigger you can also, Create a script that deletes triggers. 21:34 You can change and morph these triggers as you want through app script . This is super powerful.

Now, you might be thinking, well, there's a lot of like things that can go wrong with these triggers. 21:46 Yeah, there are, but like, it saves so many steps of having to create that video of like, here's how you create, Get a trigger , which trigger do you create, and then all those steps that they have to do, and they have to do it right in order for the correct trigger to be automatically corrected, correct 22:02 , created at the right time. A lot of problem happen when you set timer triggers, for instance.

You have to set the time in between an, power, you have to set it up a particular time. 22:15 Oh my god, there's just so many problems that could exist. This decreases those problems that could exist. You know you're gonna create a trigger for someone, or they need to create a trigger ? 22:23 Just write the Apps Script for them. It is fairly straightforward. And it is essentially one line, even though I had it, like, four lines. 22:34 To see all the different variables. But yeah, this is really cool.

And again, if you have any other questions as a better sheets member, feel free to email me and let me know what your questions are about VisiCalc, what I did, how I did it. 22:47 If you want to see the change colors, you can read through this. If you're unfamiliar with Apps Script , check out Spreadsheet . 22:53 Automation 101, completely free for members. And I hope you enjoyed VisiCalc. And I hope you enjoy it even more and appreciate it even more. 23:00 If you open, open VisiCalc, go copy it to your drive. Open Apps Script , you'll see it right here.

You'll have a little bit of text here, some text and then, straight into the functions . 23:15 You have your, on Open Function , here's an example of that. You'll see the different items and even a submenu. You have an example of an onEdit function . 23:24 You also have a function toast here, which I think is really cool. And I think it's super useful. You also have triggers. 23:35 Here. So you can create triggers for other people. I think these technical stuff is really fun. Really cool. Hope you enjoyed it too. 23:44 And I hope you can use some of these learnings in your sheets.

Bye.</div>