I want to show you some really cool, simple inventory management automations that we can add. So this is a very simple inventory management. We have items, a SKU number , stock. That's how many we have actually in our warehouse or storage. And then we also have a restock level here. This is going to be used to compare to the stock and say, do we need to Order now or not. We can create some conditional formatting here. We're also going to create some automated emails and a last updated. So there's going to be a lot of little scripts and some really cool automations of different varieties. So first off, we can create a very simple status here. Do we need a restock or not? And we can say, if stock level. is less than or equal to D, this restock level, then true is restock now.

And if it's false, we can leave it blank. So we don't have to put anything there. And now we can put everything, we can copy and paste this all the way down. But you see here, it says restock now. So we can also say, if we can wrap it around with is blank, D to And that makes sure that there's actually a restock level. So this won't show up unless there is a restock level. So if it's nothing, if it's zero, we need at least one, right? Yeah. So now if there was zero here, you need a restock now, or if there's one restock now. Cool. So that's a first level of automation. We're using the if function and also if is blank two levels of automation there. Next, we want to say, Anytime that this stock is changed, maybe one stock down or up, we want to add a timestamp here in column F. Also what column number is this? This is column six. We want to add a little timestamp there. Let's go up to extensions, Apps Script . We're going to use the function on edit, and we're going to have e as an event here. Variable row equals e dot range dot get row. Variable column equals e dot range dot get column. Not sure if we're going to need that yet. But on every edit, at least as long as it's on sheet one, and we'll call this stock. Let's say variable stock equals spreadsheet app dot get active. Spreadsheet Get sheet by name stock. So if also we need variable sheet equals spreadsheet app, do

get active sheet get name. So if sheet is equal to stock, then we want to do something and we also need to make sure that its row is greater than one. So it's not the header row . And let's say we only want stock updated. So if you update the SKU or you update the item name, or something else, we don't want to say that's updated. So last stock update. Two ampersands and column is equal to three, because that's the stock column, three C. Okay, if that's true, if all those are true, what do we want to do? Well, we want to do stock dot get range . We'll use the row and we'll put it in column six, only one size.

We will set value to timestamp . We will create a timestamp just above it. Variable timestamp equals new date. We can format this by wrapping this with utilities dot format date. Take this new date, time zone GMT, let's say plus six. We can. Let's change that to whatever time zone you want and the format will be, we'll just do the date. Day, day, month, month, year, year, year. We can also put a time if we want. We do need a parenthesis at the end of that. Okay, let's save that and see if this works. So if we change this five to a four or this four to a three, we can see nothing happening. We can go back to our executions and see, ah, it failed for some reason.

Let's look at the error code. We need some time to get the error code. Maybe. Ah, so what it says is get column is not a function . It's a capital C. There we go. That is an easy fix. Now, if we go three, so four here, see if there's another error code. This is completed. This is the third column. Yep. Oh, I think we need to, this should be stock, not this variable. There we go. Now it should work. All right, let's put this up to 6, 7, 10, and there we go. We have all the timestamps writing there. This is the last stock updated. This is a date, and it's formatted as a date. If you want to time here, what we can do is actually not format this at all.

And let's delete all of this and just use new date. Simply that. And now let's look at what that does. This is 5 there. Now it has the time. So this is a whole timestamp . If we five do this to nine, as we sell things, we do that. So this showed you that there's possibility to do automations with formulas and automations with the on edit. But I want to show you one more cool thing, which is adding a drop down, not a drop down menu, a menu, but we go to better sheets. co slash snippets, and we scroll all the way to the bottom. It's also at the top. But this one is simpler. Here's a custom function menu. So we're going to add this above our on edit, and we're just going to paste this. This is function on open again, a built in function that just happens

to trigger when the spreadsheet is opened, it's going to get the UI going to create a menu called custom menu , and we're going to have an item here. What is this item we want to do? Well, we may want to. Control the input or output of this stock. So instead of actually editing each of these cells individually, maybe we want to just take a skew number and say, we've had a sale. So sale, we're going to call this. Had a sale. We're going to go function , sale. We're going to create some UI here, which is where UI equals spreadsheet . getUI UI. prompt. And in this help, it actually shows us the code that we need. So we can actually go to Google Help, Google Help prompt, text Apps Script.

And we can get class UI. Here we go. And so this will give us a pretty simple, same thing, UI, here we go. Button set, yes, no. We'll copy this over here. We're going to say, making a sale. What item did you sell? And we're going to use a SKU. We're going to get from this a response, so let's process the user's response. Here. Get selected button. Did you make a sale? What item did you sell? It'll have that as the response dot get response text. So we'll say variable skew equals response dot response text. And here, we want to take out all of this log stuff. Basically what we want to do is say, if we got a skew, find that skew here.

And update it here. Just minus one. Okay. So we're going to get all of B column. So let's get the this. Get range . B colon B. Get values . For, actually we'll create this called skews. Variable skews equals. I equals zero we'll say, I is less than skews dot length, I plus plus, so we're going to iterate through the entire list, so if skews I is equal to the skew that we entered, then we know that I is the, item that we want. So we also want stock.

Actually we don't need stock. We don't need to do that. We do this. Stock dot get range . Our row is going to be i plus one. And the column is going to be C, which is three. Get value. We're going to say variable count equals this. And then we're going to take the entire thing again and set the value, set value, count, minus one. So this is going to be a little inventory system. We can format document and make it look a little bit prettier. Save it. We will need to refresh the sheet. Now hopefully this works. Let's say we sold a mechanical keyboard. Look up here next to help. We have a custom menu , how to sale. Oh, we will have the very first time we do this. We have to authorize it, allow

what item do you sell? We're going to paste that skew number and there it is. It minus one. So we can do that. Let's say three, let's do that again. Custom menu . What item? Yes, there it is. Restock now. So we have a conditional formatting . Not conditional formatting , sorry. We have a formula , which we might want to add conditional formatting . If this is restock now we'll do that. We have a timestamp with onEdit. We have an onOpen automation with this howToSale. Now let's do conditional formatting , format, conditional formatting. Apply to range, E colon E. We're going to say text contains restock now. And we're going to change this to red. Actually, we just need text is exactly restock now and

all of those will turn red. If we want the entire row , we need to do this a colon F and then we need to change it to custom formula is equal to E actually ampersand E one equals restock now in quotes. And now the entire row will be highlighted because the range is A through F, actually all the way up to F. And then the custom formula , this E1 says, no matter which cell we're in, look at the E column and look at the row we're on, which is this one looks like the number one, but it'll iterate through every single time, every single row. So we don't put a dollar sign there.

Click done. We now have conditional formatting as in automation. And I want to show you one more thing, which is a really advanced email automation. Basically, when this restock now shows up. We want to send an email to ourselves, let's say. So let's go to Apps Script and start doing that. So one note to say is we have now three automations. We have this on edit, we have sale, which is our prompt and we have on open. If we're thinking about, we want to make. An email, send an email when this restock now happens. It's when this stock is updated, right? But unfortunately, email cannot be sent through this on edit function . So you may think originally we just add it to this on edit, but we

don't, we're going to do it through a different on edit function . So let's create some room down here and create a function email alert. And we're going to do the same thing that we did on edit. I'm going to actually copy the whole thing. And instead of creating a timestamp , we want to make sure if we're on the stock page, we're on the third column, yes, and so double ampersand, the stock level is less we need to do exactly this basically. The stock level is less than or equal to the restock level. So, let's get our timer. variable stock level equals and variable restock level equals. We're going to do stock dot get range .

It'll be row column will be three, one, one, get value. Our restock level will be exactly the same thing. We'll copy and paste it, except it's in the fourth column. And now stock level is less than or equal to restock level. So that's the only thing we have to add there. Those two numbers, but now we'll send an email. So a very simple way to do this, to just send an email to myself is mail app dot send email, and we need three things. We need someone to send it to. We need a subject and a body. Our variable two will be equal spreadsheet app dot get on. I think it's just session session. Dot get active user, get email. So whoever the active user is right now, we'll get their email, which

it will be us most of the time. If we're the only ones using this spreadsheet , we can also get owner . I think, yeah, this will be us because we're going to run this automation in a slightly different way than we're doing this on edit. We want the variable subject, which we'll see soon. Subject equals restock needed. And we want to maybe get the item name, so that's in the first column. So let's call this variable item name row one, or sorry, row and then column one. And get the value of that. We're going to add that item name there. And the body, variable body equals, you know what we'll do? We'll just do a link to the sheet. That's it. We can say, go to this sheet.

To update. Stock plus sheet URL. We'll do a variable here. Variable sheet URL equals spreadsheet dot get active spreadsheet dot get URL. That should be it. And we're going to send that email. So how does this trigger this email alert? You need an E here, by the way, what we're going to do is go over to this left side and go to triggers, actually. Not executions triggers. We need to create a special trigger , add trigger , choose which function to run. Email alert from spreadsheet is correct. And it's not on open. It's on edit. So this on edit allows us to use email.

Essentially click safe. We're going to authorize this again. And this only happens when we need to set it up for the first time. Make sure it's saved. There it is. It's running. We're going to edit this down to. Zero. We've sold all our monitors. Let's look at our execution and see if there's any errors. It says on it is completed and email alert. They're completed no errors so far. And here's what the email looks like. It has the restock needed 24 inch monitor. That's the name. Correct? Yes. And here's the URL of the spreadsheet that we're on. Very cool. So that worked. And that's an email alert based on an edit. Basically, this is really clever way of getting these email alerts .

When something happens in the sheet, we just have to know, like, when something happens, what is that actual triggering thing? Like we know now it's editing this stock level, right? That's going to trigger it. But like, let's say it goes six, seven, we're ordering everything back to our things. Is this going to trigger ? The email, again, we can go over here and look at executions and we can see email alert is triggered and it's completed, but let's check our email. So I just checked my email and I have not sent anything because we have that one item in here which says look at the stock level, right? We say stock level is less than or equal to restock level. Then send us an email. So we need to make sure we know all of that. So hopefully through this video, you've seen some pretty simple automations all

the way to something pretty awesome here. If you're looking to get more out of Google sheets, make sure you take the course master spreadsheet automation on better sheets. I co it's a combination of spreadsheet automation, one on one and adventures in AI and some other courses, but on better sheets, our code, check out spreadsheet automation, one on one to get even more automations for your inventory. Thanks for watching.

Better Sheets

Simple Inventory Management Automations