Create a Recipe Costing Spreadsheet using Google Sheets and Apps Script. CRUD database application.

hi everyone today we're going to be building a food costing spreadsheet this spreadsheet will enable restaurants to create menu items and add ingredients and labor information to make costing decisions the spreadsheet has a single interface sheet and some additional sheets which act as databases i've added some color and formatting to the interface sheet to make it more look look more like an application the database sheets can be hidden or even put into a different spreadsheet so users cannot directly edit them all the editing of the databases will happen through this interface and how it works is there's a drop down menu here and different menu items can be selected all of this dynamically updates and over here there are some buttons which access apps script there's the new menu item button and i will just add a new menu item fill out all these fields and then i'll paste in an image link when i click it it updates all the ingredients information is added but i have to add ingredients so let's say i want to add 0.3 pounds of ground beef with a yield of 0.95 and when i click that it updates if i want to update the selling price i click on this and i can edit all the menu fields here so let's say i want to increase the price to 13 i can do that it updates all dynamically so that's the functionality there are other things we could add like right now this doesn't have any deleting of ingredients and there's no adding of the raw ingredients may want to do that through a separate sheet also the costing this is just a budgeted cost this may want to be fed in through a separate inventory system so you can get actual weighted average cost or fifo or lifo however you want to do it so that's the basic overview it's these three databases and then i have a joined database which combines some of the fields from the menu items ingredients and the ingredients i did this just so this query here would be easier could have done it without that separate sheet just doing separate formulas here but just decided to create that join sheet so let's get started building this i will bring over a blank spreadsheet so we can start building the project the menu item detail sheet is blank i've pasted in the database sheets and the settings labor cost per hour and i also have some named ranges in here just to make it easy to reference cells and ranges and let's get started creating um the combined database i'll start with a array formula and array formula menu item ingredients there we go and i'll get all the columns in the menu item ingredients tab and then i want to pull in ingredient name ingredient cost and net and gree it cost and so for this one i'll do another array formula and um a v vlookup of that column in the ingredient data and the ingredient data will be two got an extra bracket here so i want to get rid of that the um air is below so i will just add a so that should clean up the bottom and then this one i'll do the same and then i think we want to go four for the cost let's format it and this is a calculation based on the yield so g2 divided by e2 e and if it's not an error we'll just so that's the net ingredient cost so this is just a joining table to make it easy to reference in the main menu item detail so now i'm going to get into the script uh before i get into the script i'm just going to open another version of this so i can open the script from this sheet and when i refresh the other sheet it doesn't close the script i'll get rid of this one to the other side so on this i'm going to start on the script i want to just get started by creating what's called a globals and these are just a bunch of values that i'm going to be reusing over and over again in the script um i'll start with uh the sheets so the menu item detail sheet that will be this sheet so basically just each of these sheets i want to have easy access to them in the script so get active spreadsheet get sheet by name and i'm going to do this for all the sheets i don't need to do it for the combined sheet but i'll do it for the menu items called menu items and menu item menu item ingredients and the ingredient sheet and let's get the setting sheets okay so there are the globals and the first thing i want to do is create a gradient on the um on the interface sheet if you remember i had a blue background there was a gradient and i will use a script to create that i'll start with i have this site i just found a site to give me a gradient palette i started with a start and end color so i click um export and what's going to give me is an array of hex values i'll copy that and move this over and what i'm going to do is paste in there's the array and i'm going to create a function gradient page i'm only going to use this function once and the sheet i will use menu item detail sheet and paste in this array so i've got the gradient array there and i'll just use a for loop and the for loop is going to scroll through there get the range and i will just say the row will be i plus one it'll be the first column it'll just be one row and i'll say 26 columns over that we're going to set and then we will do range set background and i will concatenate this gradient array one i sorry and we want to set the font color as well we'll create we'll do it as white i'm going to detach this so we can see it so this is the sheet where we're going to place in the gradient and we can just run it straight off here looks okay hopefully it runs okay i'll have to give the permissions to the script this is the first time running a script okay so there's the gradient um some of these cells some of the rows will become wider so i'll push it down out of the screen so there is the gradient done okay let's get started on the interface page uh the first part of the interface page is if you remember the way we select the menu items is in this cell so what i'm going to do first is do a data validation and the formula is coming from menu item names and we want to reject any thing that doesn't exist so that's working and let's get it a little larger here okay so there is the uh selector and so below that we will have the following menu sku ingredient cost per recipe labor per recipe labor cost per hour cost per recipe cost per serving target margin selling price ideal selling price and then this is where we will have the ingredients the dynamic ingredients grid and how we want to do this is with a query and the query will look at menu item ingredients combined eight h and we will i believe it's going to be 8h yep that's what it is and so when we're looking at this we're going to do a select and the order we want um we want the name first so we'll go f and then we'll have the skew um so we'll go b the ingredients q not the menu sku and then we'll have the quantity and then the unit and then we will have the cost but we want the cost per unit so let's go g times c and then we will have the yields and then we'll show the net ingredient cost the net ingredient cost will be h times c and we want this dynamic so it's where a equals and we will dynamically put that in there and then we want to change the labels of the two multiple multiplication g times c and h times c and then close this out hopefully it works it doesn't let's take a look where we're wrong here select f b c d g times c ah it's because we don't have this value in here there we go so we need this to be we need this skew value to be working so we will get that working now first i just want to so the skew um we want to get the skew from this so we want to get the um it's kind of like a reverse uh vlookup so to do an it's actually an offset vlookup so to do an offset vlookup what i want to do is use an index menu items and menu items a2 to b so we're just using these first two columns we're not using the whole range but just the first two menu items a2 to a and match a1 that's the name menu items be okay so now that is reading that i just want to get these formatted properly um and also i would like to get all of these set to a larger font so now it's starting to look a little bit better and just in case i'm going to put a if and a in there in case to protect against air it'll just show a blank um ingredient cost per recipe uh what i will do for this is um i just want to sum this and we're going to assume that there won't be more ingredients than this if you have recipes with longer ingredients less you can increase the size but for this we're just going to have it that long so i'll do an if air and some and if it's an error it'll be i'll format it and the labor hours per recipe we will look it up from the menu items so um we'll do a vlookup i'll do it off the skew could do false so there's the labor hours let's make sure this all changing and the labor cost per hour is pretty basic it's just going to be i'll put enough error just in case but it's just going to be labor cost and labor costs per recipe will just be and servings per recipe that's a basic vlookup the lookup menu items and one two three four cost per serving ingredients cost plus the labor cost divided by the serving and let's put it if error in there target margin another um vlookup based on the sku and i will be menu items again and i think that's going to be three target margin and the selling price all right that's b2 menu items um forgot a comma and then the ideal selling price will be b8 the cost of serving divided by 1 so it's a cost of serving one minus the target margin will give what based on the actual cost and the margin that's what we should be selling for and i'm going to clear this out that's wrong i meant to add those here those borders here okay so that is all the dynamic content um and now now we we will create the dynamic image link and i'm going to make it in this one we just have urls but i want to make it in case someone wants to put a image inside a cell store an image in a cell which is possible i won't show an example of this but this formula should work for both uh situations so if is url it'll check whether the cell is a url and we will use a vlookup and the vlookup will be based on the name of the menu item and we'll do that offset again since we're not using the first column we can't use a simple vlookup we we could use the skew in the next cell but we'll just do it this way and so so vlookup and we're looking up one two three four five from here one two three four five six because it's offset from here so the offset the vlookup is from here i'm just going to grab this so if it's a url we're just going to do the basic v lookup otherwise we're going to put the image or if if this is a url we will use the image to uh get that link um to use it and otherwise we don't use the image we just use it like this so if it's a url if this link is a url we use image if not we just pull it and so that in that case if it's an image stored in the cell it'll just pull it and what i'm going to do is i'm going to merge all these okay so now that's all working the image is dynamic all the ingredients are dynamic and all of these are dynamic okay now we will insert the buttons that will trigger the app script start with a rounded rectangle and give it a dark blue new menu item i don't know why it's cut off like that but sometimes you have to move them around to when they're cut off like that and now i will insert another drawing for the edit menu item details i like to use the snap to grid i don't know why it doesn't stay there i don't know why i'm getting that error change that to white format it to center edit menu item details move this out of the way because for some reason it cuts them off and then we will have we're going to have these this is going to be the ingredients grid and i'll wrap that and that will be a data data validation to ingredient names and i'll reject anything it's not ingredient name and so now we want to have this quantity but we don't want just quantity we also want to have the unit of measure dynamic based on what the ingredient is so let's choose an ingredient spinach and let's beef this up a little bit so we want to have basically quantity bracket and um and then do a b lookup and we're gonna use the offset again because here the name is inside so we're not doing a vlookup off the first column we're doing a vlookup off the second so we'll use that offset trick again so we're doing it off h6 and we're doing offset and we're doing ingredients ingredient data we're not offsetting the row we're off saying the column one and we are going to and then we do uh we'll concatenate the other bracket let's just check if that's working seems to be working fine and just in case let's do af error oh it'll make it blank if there's an error there and then this one is just simple yield so these are the fields to edit the ingredients and we still will need a button to edit those so we will do again another button i'll use snap to grid and let's make it a little bit darker add edit menu item ingredients and move this out of the way so it doesn't get cut off okay so there are all the buttons and the ingredient grid now we we will add the html sidebar um so to do that we have to come over here and we create an html file we've got new menu item and we then we'll also create a script called new menu item script and for this html what i'm going to bring in is um some bootstrap and so here i'm on the bootstrap site and i just went to introduction and i will download or i'll copy the starter template and we want to paste in the starter template but we want to make sure we have this base target top in for the sidebar so i'll paste it in and then just be sure to get this on top and now i'll delete this and what we have to do now is create a function to launch that html so new menu item we'll call it and html for sidebar and that will be html service grade template from file and new menu item html output html for a sidebar evaluate and set the title to add menu item and spreadsheet app get ui show sidebar html so let's just check if that works uh we will attach the script to here sign script new men and we have to just get the permissions going for the html okay so the template is working now we have to go in and change this so what we're going to do is i will change the title get rid of this header just put a form tag in there and from the bootstrap i'm going to go to forms input group and i'm just going to get this basic input group saving that now we'll just take a look i'll refresh it just in case okay so that's the example but those are not the fields um that we want so we're gonna change these fields but we're gonna use a lot of these similar um these appends uh prepends and appends uh for the different symbols so um the first one we have here is at username and we're going to change that to the first one will be the menu item name the input type will be text and the placeholder will be and i'll create an id for it so menu item name and the second one will be the target margin so that will be this one and the num the type will be number and the step will be 0.01 and the placeholder will be target margin id id is target margin and um what i'll do is we want like the pre the app end similar to that zero zero so i'll go down here uh oh here we go i'll take out this aria stuff uh we don't need it for this okay so the target margins there increments of 0.01 and um so after the target margin we want this to be um labor or uh servings so um we got the target margin and let's just copy it for the servings and this one doesn't need a step because it's just servings there's not going to be incremental servings and this will be servings per recipe and for see how that looks okay we want the placeholder to change but that should be fine so after that we need uh the labor hours um i'll take i'm gonna copy this one again because the labor hours will be incremental by .01 and we will call this recipe labor hours and at the end and the placeholder check how that looks waiting for it to come up i don't know why it's taking so long there might be an error in the html there we go recipe labor hours and it's steps of 0.01 and so after recipe labor hours we're going to do the price so the price is going to be the same and we'll call this sale price change the id but this one we'll put a dollar before that and we'll see if that works yeah looks good but i actually that does look good okay so the sale price looks good we don't want to have it like this because we do want the ability to put in cents so that's the sales price the sale price and now we want the image um and the image is just going to be a text kind of like this first one so we will get rid of get rid of these and the we'll call this image url and the id will be recipe image url and that's about it that's just a text form and then we need a button so for the button we'll just go button type submit class primary and id equals save button submit see how that looks menu item name target margin servings per recipe recipe labor hour sale price image url so that looks good for the html um and now i guess what we'll do next is we will put the script in for after um to submit this and how it will get all these fields so the javascript that we need we need um javascript for when this button the submit button gets clicked all these fields are picked up and then run through another script to be processed and added to the menu items so first um step in that is below all this bootstrap stuff we will create another script script tag and we'll start by creating a function call it after button clicked and the first thing we need to do in this function is get those fields so the menu item name will be document get by id and the id is menu item name just double check that so menu item name target margin servings per recipe recipe labor hours sale price and recipe image url so i'll just make a copy of that and so the next one is target margin and the id was target margin and servings per recipe servings per recipe recipe labor hours recipe labor hours sale price and i'll call this image url and it's recipe image url and if you remember when we take this target margin it's a it's a whole number it's not a percentage yet but we want to convert it so i'll create something called target margin converted and that will be so we're getting that value which is going to be a string since it's pulled from the html and just multiply it by one to turn it into a decimal which will be how we'll store it in the database and now i what i want to create is these are going to be fed in as parameters um went into the script so i'd rather do that in an object put it all in an object so i'll just simplify it name menu item value menu itemname.value target margin converted servings per recipe hours uh price and image so that's the parameters and then how we're going to run the script will be google script run and we're going to have a with success handler because we want to clear the fields and we haven't created this function yet but we will and then i guess we'll have a new script called add menu item and it will feed in that the parameter of those so a couple things i need to do first off in here i'm just going to put in this function add menu item data it's just going to be blank for now and we have to do the clear fields so function clear fields and we'll just have an e parameter in there and um i'll use these again in here so all i'm going to do is that so document get the element and make it a blank and just use copy and paste here so there's the clear fields and now we need a listener on that button so document get element by id i believe that's called save button save button add event listener and the function is after button clicked so let's just check uh there's it's up it's not going to do anything at this point but we'll just check if it clears it and if it works so i'll just put in a couple of things there and it does seem to clear it seems to be working so before we move on to creating the script i want to do the same thing for the edit menu item details button so to do the edit menu item details html we're just gonna take we're gonna copy the new menu item html first we'll create the script edit menu item and then do the same edit menu i script and i'm just going to take this entire copy that and i'll copy all this as well just change that and edit menu item and the edit menu item will look it'll basically be the same as the add menu item it as far as how the fields except we're not going to have the name as an option so edit menu item and i will take out the name we'll change the button to update we are getting rid of the name get rid of the name assign a script edit menu item so there it is now we'll just have to do the script but so we're not having the option to change the name of the menu item just um things like the target margin servings labor sales price etc so that's the basic html for both those let's just double check that this works uh clearing the fields and that did not seem to work so there might be a problem somewhere in here um let's see if that fixed it just the name of the script see if it clears it it's refreshing it update menu item that's why okay that seems to be working now just had to change the name so um the next step will be to create all the scripts the app script to make these run um so before we do that i want to add some more of the globals now this gradient we probably won't need it but just in case i'm just going to move it down here so i have all the sheets just want to add some ranges so uh so fixed cells um we'll first get active menu item name and so that's just i just like to have these available so i don't always have to type out the full get range etc so that is the active name whenever we need that and let's get the active menu skew i guess we'll say skew range just to make clear this is a range not the value active active ingredient range so the active ingredient range is one two three four five six so it would be one two three four five six and one one two three four five six seven eight so the active ingredients range is going to be six eight active quantity quantity range would be seven eight and the active yield range so i'm going to make sure this is gradient quantity range and active gradient yield range would be 88 and to pull data from the sheet i'm just going to have some functions because this data will may change so i don't want to have fixed ranges on them so function get menu item item range and it will just be to return menu items sheet get range and so this one will be start at the second first column and one two three four five six seven so we're gonna get seven so but first we have to get menu items sheet get last row minus one that'll get the last row when this is run and eight so get menu item range and let's get this one function get menu item ingredients range return menu items ingredient sheet get range and so again start in the second row we don't need the headers first column get last row minus one and for this one one two three four five so five and then a final one function get ingredient range get ingredient range return ingredient sheet get range and for this one we'll start at two second row first column get last row and one two three four so there are the those functions i'll use over and over again just get those ranges just so i don't have to retype that whole get range in the last get last row over and over again so for the first script we will do the add menu script we already have the function now we just have to put the code inside and so const menu item data equals get menu item range get value so this picks up um all of this the data from the menu items database and first we're going to just do a check on the the data fields so we let's say you could change this however you want but let's say when we're creating a new item we want all the fields to be to have values or else we will not do anything so what we'll do is we will so we have dated the name margin remember this is being fed in through this in the javascript out of the html this is fed into the add menu item data add menu item data so we're getting these elements and evaluating them servings i believe it's called hours price and image so to create a new item we need all of those fields you could change it maybe you don't always need the image it's up to you to modify it the other thing we want we don't want doubles on the name so i'm going to use a create a function check if value exists and we'll create that later and basically that will take the menu item data and we will be checking the menu item data and we'll be checking the the menu item data we'll check the name so the name will be in the array it'll be one zero skew will be zero name will be one and we'll be checking against the data dot name we haven't created this function yet uh but it'll be a boolean so it will check if it value exists and if it does not exist it needs to not exist for us to basically carry out this um to add the item and to add the item it's pretty simple so we'll just do menu menu item sheet append row as long as all those criteria are met we're just going to append to the row and the skew uh obviously the data doesn't have a skew so the data um the sku is just made based on what skus are already in the sheet and so we're gonna have another function that we need to create and it will be increment the sku so increment the sku on menu item data and just make sure we feed it the column just so we can use this increment skew and other [Music] other sheets too so it's flexible and so we increment the skew and then the second field will be data dot name that will be the third column data dot hours data dot price data dot image so that will um that will be now so that will add the row at the bottom now what we have to do though is we have to create uh check if value exists and increment skew um so what i'm going to do rather than put those in there um i could put them in globals or i can create what's called a utility script uh this is just a place to store utility functions that will be used throughout and so what what do we have we had check if value exists looks like this is going to look like this so function check if value exists and we'll just call this data array because it could be any kind of array and the index and not could be a name or something else we'll just give call this value and so it's going to take those parameters and it's going to be a boolean so first we'll just see what it finds so data array dot filter and in the data array so we'll filter the date array and any row that meets the criteria so if row and this is the column if the column in the row matches a value then we will return row so if um after the filter uh this found if any values match the found will have something in the array so we want to just return found length greater than zero so if the found length is zero the value exists yeah or else it's false okay and then the other function we had is increment skew let's just take a look what we had in the parameters for increment sku for increment sku we had a data array and the sku index remember we might want to reuse this so we want to be flexible that's why we're doing it and so let's do skew array is a blank array to start and if the data array is the length is greater than zero we want the sku array equal date array map and return row return row skew index let's just take a look here away oh so if the data array length is greater than zero then we're going to do this map skew array and so basically we're just getting an array of the skus only and just going to else return one that is if there's no values in to increment otherwise we will have return math max i'll do a spread skew array plus one and let's just test this so function test skew sku and we want to um we'll console.log increment sku and the data array will be get menu item range it's a function get values and the sku index is zero so we'll test increment skew see what it says so it did increment it um it found nine was the max and it incremented by one and i will create just a place to put that test um i don't we'll use it again but you never know get that out of the way so that seems to work um so let's do let's just funk let's test test add menu item and what we're going to do is um we are going to create an object that we're going to feed into it so we'll just get a name margin servings hours price and for the image i'm just going to copy one of these image text so that's a parameter i have an error here it's going to be a parameter we're going to use to test it and then we will run add menu item so test add menu item we have the data and add menu item let's see if this works okay it worked i could delete that just wanted to test that before moving on and i'll take this test and i will move it into the tests again we probably don't won't reuse these again but just in case i want to store them there so um now let's test the add new menu item so we pulled up a new just putting in some random numbers in here and let's just copy there we go it works uh the thing though we want to make this active so let's say we did i'll do another crepes when we do this we want to we want to make it active so when this carries out if and it's only if if we meet the criteria active menu item name set value data dot name so we'll do another crepe crepe 1000 okay there's an error there so oh no it seemed to work just took some time but everything cleared that worked there's no ingredients yet we'll have to add those later so that is all working well i'm gonna get rid of these extra test crepes so now let's create the uh edit menu item script um so we're gonna come over here to the function update menu item and so this is the same it's taking in the data from the other html sheet which is similar to the menu script so i'm going to use um similar parameters uh the only difference is uh we're not giving the option to edit the name and we're not checking if the value exists because obviously it does exist uh so that's the last parameter and then instead of and what we're checking for we're doing an ore so we're doing an ore because only one of these fields if you remember only one of these fields um needs to be filled for it to update but one needs to be filled or else we're not going to do anything so in this function uh the first thing we need is uh the active sku uh because it is when we're editing we're just editing whatever is active in the interface sheet so and let's um we'll create a function for this um get active skew it's going to be pretty basic just return and active menu sku range get value so that function will get that um so we get the active sku and then we need the menu item data because we're gonna we'll need that to edit so menu item data equals get menu item range and get values and so the first thing we are going to do is we will um get the active row that we want to edit so and we'll use a filter and if row return row so this will get the actual row that we want to edit and so the updated row i don't want to do the function in here because it'll be too much for one function so i'll break it out into a second function update menu item row and the parameters would be active row and the data from that's being fed in to update it and when we do this filter even though it's only one row it's still going to be a 2d array so let's make sure we're just getting the first um the first item in that 2d array there will only be one item but we have to break it out and then we need the index we need the active row index maybe we could have got it up here with the filter because we could get the index there but i'm going to do it separate menu item data and let's use a map and row and we will return um so basically we are just getting a um an array of all the um all the skus and we'll go index of active sku so that's going to just give us the index of the row that we want to edit and then we're going to take menu item data and act use that active row index change it to that updated row and then get get menu item range set values menu item data so we're um changing the element of the menu item data uh for that active row index and then just setting the values in here we're not adding any rows it's the same amount of rows so we can just use that same get menu item range so now what we have to do is we have to we have to create this function and the update menu row function will look like this you'll take in the active row which will be an array and it will take in the data which is already being fed in uh to that one so um let updated row equal active row and um now let's do a check on all the fields from the data if data margin and i'm going to do a check for undefined as well as data as well as a blank field and if that is if those two are not true so the margin is zero one two so um updated row two equals data dot margin so that's for margin we need to check servings hours price and image so and i'm just doing a control command d to select multiples there and hours so margin serving hours margin serving hours price image and we're going to make sure we change these so the servings zero one two three the hour zero one two three four and the price is five and the image is six so um then we've up we've changed the updated row it's good to return misspelt return okay so um that should be good to go let's do a test on it so function test update menu item and we will so let's say we got the crepes um update menu item and we're just going to have one field and i've got another image over here i'll paste in and let's run that test see if it works test update menu item and it updated it so this has changed um from the crate picture to the cupcake picture and the other fields should work as well so now we'll work on the edit menu item ingredient script and that one does not have a sidebar it's just based on this first i'm gonna switch that back to a great picture hopefully that works so we're creating the skip the script where we can enter we can choose the ingredient and change either the quantity or the yield over here so there's no html and what we want so we want to add a script here edit ingredient edit ingredient script and we'll call the function update menu item in gradients so what we want to do is um we're going to be updating the ingredients of whatever item is active here so we want to get that active skew and we want to differentiate between the active menu sku and the active ingredient sku so get active skew and we want to get the ingredient data okay get ingredient range and we want to get the menu ingredient data get menu item ingredients range that's a function get values and we want to get the active ingredient name so that is where we want to get this value and then we want to get the active ingredient skew so we will need a function i'll do it outside of this function get ingredient skew from name and the parameters would be so get and scr ingredient skew from name and the parameters would be the active ingredient name and the ingredient data so basically going to be scrolling through the data and getting the skew we'll do that one after so that's the active ingredient skew and then we need to get the unit um so const active gree and unit and let's create another function for this unit and we'll take it from the we'll use the sku to get it and we'll just active active ingredient skew and again the ingredient data and then we need these two values we need the quantity and the yield so constant active ingredient quantity range get value and make sure that is an equal sign and constant active ingredient yield range and then so now what we want to do is we need to determine for instance if spinach is in here we want to edit spinach but if we choose something that isn't already in here like hamburger bun we want to add it based on the quantity and yield so um we're gonna need a couple of um we're gonna need a couple of outside functions for that rather than crowding this function so what i'm going to do is i'm going to take a lot of these variables and wrap them in a param so we want the active menu sku gradient data menu item what is menu ingredient data active active ingredient skew active ingredient unit gradient quantity and gradient yield so we have all those parameters wrapped in an object and so we want to check if the ingredient exists so ingredient exists and we'll do that as a boolean and i have to create this function and then we could just do the last one as a ternary ingredient exists edit existing menu gradient and the ternary would take if if the ingredient exists edit an existing one and if not add a new one and this doesn't return anything we'll just say return true we won't need we don't necessarily need it to do anything for return anything um so now what we have to do is we have to get we have to create get ingredient skew from name get ingredient unit from skew and ingredient exists in menu item and then we need edit edit edit menu ingredient and add menu ingredient so we'll create those um we can do it in utilities or we can do it here might as well do it here function get ingredient sku from name get in gradient skew from name actually i'll just take this get ingredient skew from name and use filter and if if active ingredient name so we get the active row if the active ingredient name we get the active row and then from that row we want the z the first index in the ingredient data it's the first index now this is going to return a 2d array even though it's just a single array so we need we just want that first array and then the first um element of the first array so that's the get ingredient skew from name and it's a misspelling there get ingredient unit from skew so get ingredient unit from skew we're going to do a very similar thing active row ingredient data filter row and if bro move this all up a little bit return row and then return active row and remember it's a 2d array we just want the first element even though it's just one element but 0 1 2. we want that unit so that's how we get the unit and what was the next one the next one is the boolean ingredient exists in menu item i'm going to change this to data it doesn't really matter what you name it i'm just more comfortable with data for the function itself and um so what we want is um the existing row so um we're checking if what we're doing is we're checking if we can pull an existing row using a filter so existing row equal data data menu ingredient data filter if row data so now we're doing a double check ingredient exists in menu so we're checking we have to check for the menu sku and the ingredients queue so if row active menu sku and row 1 one equals data active ingredient sku we return row and then all we have to do is return so ingredient exists ingredient exists in menu item and that should return a boolean i accidentally put this function inside the other one okay so those are the two uh two three of the outside utilities that we need i'm going to test these get some space in the bottom and function test get in gree so we're going to test this one first and um that's a function tint skew from name and let's find let us and let's console.log that and check it okay seems to work for that let's move that out to tests and to test the ingredient menu ingredient data get menu that's a function i'll force these in just for this task const and active oh sorry active menu sku active menu sku and active in green okay and then what we will do is we'll do another console log just so we can see the result okay let's see test ingredient exists active menu sku is not defined 63 active mil spelt it there so active menu sku1 and after another misspeller okay why is that giving me a false um um menu ingredient data get menu ingredients range get values active menu sku active ingredient sku menu ingredient data active menu sku active ingredient sku ingredient exists existing row equals data existing row equals data menu ingredient data filter row okay so and then out of the one let's try another one uh and this should run false okay that works um and so now we still need to run some tests on uh the get ingredient um oh no we got the get ingredient skew from name now we have to create these two other functions add existing menu ingredient and if we go back up here add existing menu ingredient and add menu ingredient okay so first before i work on these functions i just want to change that to add new menu ingredient i notice this parameter is grayed out because it was misspelled and i want to go down here and move this test out of the way over to tests and so we are going to start with heading edit existing menu ingredient i'm just going to change this to data doesn't really matter but um get this up a bit so when we edit an existing menu ingredient we need to find the row get the row so we find the row by matching these two by matching the menu skew and the ingredient skew because there should not be two of the same ingredient skus associated with the same menu skew so first we got to match those two and then we want to get this whole row and then we want to determine what is being edited based on what is in this in these cells so first um we will be needing to get the act the index so i'm going to call this active index and i'm just going to plug in a negative 1 because i'm going to get this index from within a filter function and then um this is the row that we're going to pull out of the data and we're getting the menu ingredient data from the parameters and on this one we will get the row and the index and if here we're going to match both the fields so if row zero so if row zero equals the skew and row one equals the excuse me um equals the active ingredient skew if those two conditions are met we know the active index will equal the index and we want to return that row to get that matched row so now that we have the edit row we're going to check which if the quantity or the yield both or both have been have any value so data let's start with the quantity and does not equal undefined and just in case i'll copy this and we're also checking if it's a blank field um if both of those the edit row and remember even though it's only one row it's a two dimensional array so it's two brackets so we want to just get pull out that first bracket and get this edit row equals data ingredient quantity so we're setting the edit row to that value if both those conditions are met and let's change this one and i'll do command d to select them so that's what we need for ingredient yields but it's going to be 0 1 2 3 4. so that sets it um now that that is set for both we want to um put we still need to put that row into the main data um array so we do that by finding that's when we use the active index and equals edit row and again that's a double bracket i guess we could extract that but we're not in this case um and then const range so we want to get the range the get menu items ingredients range and set values and so that should set the values now let's get this up a little bit okay so that should work but i'm going to do a test function again test add our test edit existing menu ingredient and this repeats a lot of stuff so i'm going to just paste in what i'm going to do so just to test it i'm going to get the active skew get the active ingredient get the active menu sku the ingredient data menu uh data the active ingredient name the ingredient sku gradient unit ingredient quantity the yield i'm going to put them all into this parameters and the parameters are for the ad sorry edit existing menu ingredients okay so i have this here um now i want to change the spinach to 0.2 and its yield to 0.95 so this will pick up the values and let's just test it test edit existing menu ingredient set property of two okay so we have an error so edit that may be the problem there let's see if this works okay so that updated that let's try it again 0.35 change the yield to um point or 0.85 okay so that seems to work so that is and i'll move this test out of the way so i move the test out of the way and so if we go back to ingredient exists we want we need to edit the existing menu ingredient and next we will do um the function add new menu ingredient okay so add new menu agreement gradient should be pretty straightforward um actually i'll just get it from here add new menu ingredient so if it deter if the this function determines that the ingredient doesn't exist all we need to do is append a row to the bottom of this menu item ingredients page and the easiest way to do that is menu item ingredient sheet i'll just put a return there for readability so first is the ingredient sku which we named active menu sku and the second is the active ingredient skew the other one is the ingredient quantity just double check how this is fed in how we fit it in up here active ingredient quality and ingredient oh we need the unit ingredient quantity active ingredient unit so we need that unit and then ingredient yield so now this should work as long as all our functions are correct so let's say we have the salad we look at the um ingredients and let's look for an ingredient that isn't already in there i'll put in falafel mix and we'll put in 100 grams and 0.95 yield okay so that did not seem to work let's take a look okay so i guess we have to test this update menu item ingredients so or actually we don't need to do it to wrap it in a test because it doesn't take any um parameters so we should be able to just run it from here and see what it says and that is this may be the problem right here okay that worked but as we can see the we didn't get the menu skew we didn't get the active skew in the ingredient skew so let's see where that is uh there was a spelling error there active let's delete this okay so there it is it added the falafel mix um why does the falafel mix cost so much that might be just a problem over here 10 25 per gram that seems quite expensive maybe maybe per kilogram i'm just going to change that and we don't want per kilogram we let's say 0.1 kilogram or we still have to attach the script so the script we want to attach is to the button update menu item ingredients so i've deleted that let's try it again okay so that works now i think that's everything that we needed to do um so let's try to um add a new recipe i just want to copy an image file and um so let's add tacos we'll go for a 30 margin um every recipe will have one it'll take 0.1 minutes and the price will be 12.99 and so let's just check that this works so there are the tacos they got added there's no ingredients yet so let's add some i don't think we have taco ingredients yet in here but we can just go with some ground beef so it added the ground beef now let's say i want to change my margin to 25 percent and that seems to work well if i want to edit the ground beef increase it to 0.2 that seems to work everything updates so that's about everything we set out to do this is to go into production this would still need a lot of work we need to do data validation on all these fields make sure it's getting the prop like numbers and not strings in this in the ingredients we also need the ability to add ingredients and edit them although this could come from a separate inventory system excuse me and [Music] we also need the ability to delete items and that could be added i just didn't want this video to go too long so that's it hope you enjoyed it if you have any comments leave a comment below thank you

Channel: Projects For Entrepreneurs

Views: 1,328

Keywords: recipe costing spreadsheet, recipe costing excel, food product cost, food costing spreadsheet excel, recipe costing google sheets, crud google sheets, apps script, google sheets recipe database, google sheets tutorial

Id: 4uyk8JOxzDQ

Length: 142min 48sec (8568 seconds)

Published: Fri Nov 12 2021