00:00 In this tutorial, I want to show you some really cool, simple inventory management automations that we can add. So this is a very simple inventory management.
00:09 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.
00:17 Ah, this is going to be used to compare to the stock and say, do we need to order now or not?
00:24 Ah, 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.
00:38 So first off, we can create a very simple status here. Do we need a restock or not? And we can say if stockLevel is equal is less than or equal to d, this restockLevel, then true is restockNow.
00:57 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 restockNow, so we can also say if, we can wrap it around with isBlank d 2, and that makes sure that there's actually Yeah,
01:34 so now if there was zero here, we need a restockNow, or if there's one, restockNow. Cool. So, that's a first level of automation, we're using the if function, and also ifIsBlank, We wanna add a timestamp here in column F, uh, also what column number is this, this is column 6. We wanna add a little timestamp
02:09 there. So, let's go up to extensions, Apps Script. We're gonna use the function onEdit, and we're gonna have E as an event here, variable row equals E.range.getRow, variable column equals E.range.getColumn.
02:36 Not sure if we're gonna need that yet. But on every edit, at least as long as it's on Sheet 1, and we'll call this stock.
02:48 Let me see. Variable stock equals SpreadsheetApp.getActive. Spreadsheet.getSheetByName. Stock. So if, also we need variable sheet equals SpreadsheetApp.getActive.
03:17 So if sheet is equal to stock, then we want to do something. And we also need to make sure that it's row is greater than one, so it's not the head of row.
03:32 And let's say we only want stock updated. So if you update the SKU or your, update the item name or something else, we don't want to say that's updated.
03:41 So last stock update. So two ampersands and column is equal to, uh, three, because that's the stock column, three C.
03:57 All right, 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.
04:13 Only one size, we will set value to timestamp. We will create a timestamp just above it, variable timestamp equals new time.
04:25 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.
04:37 We can 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, we can also.
04:49 But 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.
04:56 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.
05:09 Let's look at the error code. We need some time to get the error code. Maybe.
05:25 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.
05:34 Now, if we go three, So four here. Let's see if there's another error code. Nope, this is completed. This is the third column.
05:45 Yep. Oh, I think we need to- this should be stock, not this variable.
06:12 There we go. Now it should work. Alright, let's put this up to six, seven, ten. And there we go. We have all the timestamps writing there.
06:22 This is the last stock updated. This is- This is a date, and it's formatted as a date. If you want a time here, what we can do is actually not format this at all. And let's delete.
06:34 All of this and just use new date. Simply that. And now let's look at what that does, is put a five there.
06:45 Now it has the time. So this is a whole time stamp. If we- Do this to nine. As we sell things, we do that.
06:54 So this showed you that there's possibilities of 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 menu, not a drop-down menu, a menu.
07:07 But we go to better sheets at co slash snippets. And we scroll all the way to the bottom. Um It's also at the top, but this one is simpler.
07:16 Here's a custom function menu. So we're gonna add this above our on-edit. And we're just gonna paste this. This is function on-open.
07:24 Again, a built-in function that just happens to trigger when the spreadsheet is opened. It's gonna get the UI, gonna create a menu called custom menu.
07:34 And we're going to have an item here. Well, What is this item we want to do? Well, we may want to control the input or output of this stock.
07:48 So instead of actually editing each of these cells individually, maybe we want to just take a SKU number and say, we've had a sale. So, Sale.
08:00 So we're gonna call this, uh, had a sale. We're gonna go function, sale. We're gonna create some ui here, which is, or ui, equals spreadsheet.get.ui, ui.prompt. And in this help, it actually shows uhh, uhh, gives us the code that we need.
08:28 So we can actually go to Google Help, Google Help, prompt, text, app script. And we can get close ui. Here we go.
08:45 And so this will give us a pretty simple, same thing, ui. Here we go. Button said yes no. We'll copy this over here.
08:57 We're gonna say, making a sale. What item did you sell? And we're gonna use a s skew.
09:17 We're going to get from this a response. So let's process the user's response. Here, gets like the button. Did you make a sale?
09:27 What item did you sell? It'll, uh, have that as the response dot get response text. So we'll say variable skew e- equals response dot response text.
09:40 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.
09:58 Just minus one. Okay. So we're going to get all of B column. So let's get the... this.
10:21 Get range. B colon B. Get values. For... actually, we'll create this called SKUZ. Variable SKUZ. Equals. I equals zero, we'll say.
10:35 I is less than SKUZ. Length. I plus plus. So we're going to iterate through the entire list. So if SKUZ.I is equal to, the SKU that we entered, then we want, know that the I is the item that we want.
11:01 So we also want stock. Actually, we don't need stock. We don't need to do that. We do this. Stock.getRangeR Row is going to be I plus 1, and the column is going to be C, which is 3. Get value.
11:27 I'm 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 1. So this is going to be a little inventory system.
11:45 We can format document to make it look a little bit prettier. Save it. We will need to refresh the sheet.
11:53 Now hopefully this works. Let's say we sold a mechanical keyboard. Look up here next to help. We have I'm gonna sail.
12:02 Oh, we will have the very first time we do this. We have to authorize it allow. What item do you sell?
12:12 We're gonna paste that skew number. And there it is. It minus 1. So we can do that. Let's say 3. Let's do that again.
12:24 Custom menu. You What item? Yes. There it is. Restock now. So we have a conditional formatting. Uh, not conditional formatting.
12:37 Sorry. We have a formula which we might want to add conditional formatting. If this is restock now, um, we'll do that.
12:44 We have a time stamp with on edit. We have an on open automation with this how to sale. Now let's do conditional- a formatting, format, conditional formatting, apply to range, equal and e.
12:55 We're gonna say text contains restock now. And we're going to change this to red. Actually, we just need text is exactly restock now.
13:11 And all of those will turn red. If we want the entire- Well, we need to do this. A colon f.
13:22 And then we need to change it to custom formula is equal to e. Actually, ampersand e. One equals restock now.
13:38 In quotes. And now the entire row will be highlighted because the range is a through f. Actually, all the way up to f.
13:48 Uh, and then the custom formula, this dollar sign, e one, 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.
14:02 So we don't put a dollar sign there. Click done. We now have conditional formatting as an automation. And I want to show you one more thing which is a really advanced email automation.
14:16 Basically when this restock now shows up, we want to send an email to ourselves, let's say. So let's go to App Script and start doing that.
14:26 So one note to say is we have now three automations. We have this on edit. We have sale, which is our, uh, prompt and we have on open.
14:37 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?
14:46 But, unfortunately, email can not- not be sent through this on edit function. So you may think, originally, we just add it to this on edit, but we don't.
14:59 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.
15:10 And we're going to do the same thing that we did on edit. I'm going to actually copy the whole thing.
15:18 And instead of creating a timestamp, we want to make sure if we're on the stock page, we're on the third column.
15:27 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.
15:45 So let's get our variable stock level equals and variable restock level equals. We're going to do stock dot get range.
16:01 It'll be row column. Umm will be 3, 1, 1. Get value. Our restock level will be exactly the same thing.
16:14 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.
16:24 So that's the only thing we have to add there. Those two. 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 mailapp.sendemail.
16:37 And we need three things. We need someone to send it to. We need a subject and a body. Our variable 2 will be equal, spreadsheetapp.get... I don't know, I think it's just session.session.get.activeuser.getemail.
17:07 So, however the active user is right now, we'll get their email. Which, it'll be us most of the time if we're the only ones using this, um, spreadsheet. We can also get, owner, I think.
17:23 Yeah, this will be us, because we're gonna run this automation in a slightly different way than we're doing this on edit.
17:29 We want the variable subject, which we'll see soon. Subject equals restock needed. And we wanna- maybe get the item name, so that's in the first column.
17:43 So let's call this variable item name, row one, or sorry, row, and then column one, and get the value of that.
17:57 We're gonna add that item name there. And the body variable body equals- You know what we'll do? We'll just do a link to this sheet.
18:07 That's it. Ah, we can say, go to this sheet to update stock. Plus sheet URL, we'll do a variable here.
18:26 Variable- sheet URL equals spreadsheet dot get active spreadsheet dot get URL. That should be it. And we're going to send that email.
18:41 So how does this trigger, this email alert, we need an e here. By the way, what we're going to do is go over to this left side and, And go to, uh, triggers actually.
18:53 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.
19:06 So this on edit allows us to use email. Essentially. Click save. We're going to authorize this again. And this only happens when we need to set it up for the first time.
19:27 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.
19:37 Let's look at our executions and see if there's any errors. It says on edit is completed. An email alert there completed.
19:49 No errors so far. And here's what the email looks like. It has the restock needed 24 inch monitor. That's the name.
19:57 Correct? Yes. And here's the URL of the spreadsheet they were on. Very cool. So that worked. And that's an email alert based on an edit, basically.
20:09 This is really a 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?
20:21 Like, we know now it's editing this stock level, right? That's going to trigger it. But like, let's say- It goes 6, 7, we're ordering everything back to our things.
20:33 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.
20:45 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?
20:55 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.
21:03 So hopefully through this video you've seen some pretty simple automations all the way to something pretty awesome here. Uh, if you're looking to get more out of Google Sheets, Make sure you take the course Master Spreadsheet Automation on BetterSheets.co. It's a combination of spreadsheet automation
21:26 101 and adventures in AI and some other courses. But on BetterSheets.co, check out Spreadsheet Automation 101 to get even more automations for your inventory.
21:37 Thanks for watching.