Create Professional Invoices Effortlessly with the Google Sheets Invoice Generator for Inventory

Google sheets invoice generator for Inventory

yo let's recreate this product inventory form in Google Sheets let's go so what you need is a Google account so if you don't have one yet please create or sign up or if you already have please sign in and then once you are able to log in go to this icon here the Google apps scroll down and look for Sheets or simply go to this URL and then click the plus icon so you'll be able to start a new spreadsheet and then rename the spreadsheet product inventory form so I already have one here so let me just delete this so I already have my existing sheet I'm just gonna use this one so project inventory form and then insert two sheets and rename it form and the other one is database so in your database enter product ID product name product description price and quantity as column headers for column A to E now on your form so in Row 2 enter product inventory form as the title and then and column C starting from row five are the title of our um form fields so in column C5 enter product ID cell C7 is product name cell C8 is product description uh column C9 is price column C10 is quantity so to insert these buttons just go to insert then here and go to shapes then choose the rectangular with round brackets and then type in the button name then Center change the color and then right click copy and save and close and then insert again then paste then just change the color and the title of the buttons so repeat the same steps for the update and clear button so I'm just gonna delete this and then so for the coding let's go to extensions and then up script in your end it will say Untitled project rename it inventory form and delete the default function that is written on your project editor and the first line of codes that we will be doing is declaring Global variables so we will be calling the active spreadsheet or simply the spreadsheet that we are working with and then storing it in the variable sh and then in hours active spreadsheet let's call the sheet form so we will store it in sh form variable so as the database in variable shdb and then the first function that we will be creating is the 1 4 adding a new item or add new product so for Simplicity let's just name it add new product and then this is the line for creating the product ID automatically so we will be using the last row used as our product ID and then we will be getting the values from our form so that's gonna be from cell D7 2 d 10. and we will be storing the values to a variable which we are calling RNG underscore valve so let's now after storing them let's just delete this so let's save the product ID and all the values from the form in our database sheet so let's call database sheet and then append row and insert these values and then we are going to display a pop-up message saying new product C and then this one so the first number or the number in the first bracket um it represents the first row and then the second is the First Column so in an array the index starts with zero so for example let's say q u R let's just slur and I will stop it and then let us just play the values here first let me see let me show you how it looks like in the back end so our values are stored as an array when we was it say s so let's just delete this one it was saved so so here it's a this is basically having four rows as you can see in our form so a row one two three four but in an array it starts with zero so zero one two three so this range Val 0 0 inside square brackets so so meaning access the value and the first row and First Column and that is the product name value which is square and then here so again in our range vowel access the now this is the second row column zero or column one so row two so this is just one column which starts with zero as well and that's the sample DSC or description so same goes with range valve 2 and range valve three so let us remove the comment and then copy the title of the function then go back to form and then right click go to assign script so I already have that add new product click ok so again from our database it's empty and let's click add new product save then let's check our database so it was recorded successfully next is let's go to or let's create the search function first let's go back to our form here in our cell C5 let's create a hidden value so we will be entering a formula or function or formula match and then our search key would be the product ID and we will be searching that in column A and search type 0. now if it displays error meaning if there's no match display zero as a default value so what does it do the match formula Returns the row where we can find the search key in which in our case is the product ID so for example I will enter product ID one so this formula should return row 2. so let's try and hit enter so it's in row two so we're just going to hide that let's change the font color to white and then so the value of that cell let's store that in a variable called row num so again if the value of cell E5 is zero let's display a message saying no records found otherwise let's get the product data so from our database sheet let's go to Range so the row number and start with column two which is this one let me let me just fix the so going back uh once we're able to get the row number where the product ID is found in the database sheet so access that range so here product ID is in Row 2 so this is the row num start with column two which is column B and then just one row and then ends in the fourth column so that is here up to here so one two three four so get this value and save it to a variable called data and then basically we will transfer this value from our database to our form so once I click search it should display the value here let me just delete this um data here and also as you've noticed the format of the data is um in a row format but in the form we should be able to display that in a column format so like from left to right you should be able to display it from top to bottom so in up script there's no direct code for the transpose um function or formula so we'll be using a for Loop so we may discuss this further in the future but so basically we will be trans or we will be transforming this variable into this format where it has like the row and column indices and then saving it to an array called threads short for transpose and then let's uh yeah and then let's print the values stored into a trans variable on the sheet form so let's go back to sheet form here and get range row seven so here row seven column start with column four so column four is column d one two three four so here and then uh four rows but one column so basically that's just it from range D7 to d10 and set the values uh to the data that we're able to get from the database so let's try to add another item just so we have two products in our database and then so let's try one hit enter and by the way so just click save and then copy the search function the name right click the search button click this three dots assign script paste the name click ok and then let's click search there you go and then for product ID number two for example search there you go cool now let's go ahead and do the script for or code for update or updating the product basically this is almost the same for add new product so but we will be using the map function and so we will not be discussing this further for now and let me just show you what it looks like in the back end so it doesn't have like the um like this one so let's just Ctrl Z so again from our form sheet we will be getting the again our the hidden value from cell E5 and then save the value of our range E7 to d10 so for example I'll be changing the price to ten dollars so save the new value and then like we will be adding additional description this description then stock would be 12. and then once we're able to save that to a variable called RNG underscore file here let's go to or so once you're able to save that to our variable this is the which is the RNG valve c with our database sheet so that is again so row num so in our case this should be our variable row num should be three and then column two just one row up to the fourth column so one two three four and save the new value which is the RNG well and display a pop-up message if the product was successfully updated and click save copy the name right click update assign script and paste the function name click ok and let's see product updated then let's check from our database so it was successfully updated now to clear our form so let's just name it function clear so in our form get range D5 from here to here and clear content so just copy again the function name right click the button sign script paste okay and click clear and yes that's it and if you find this tutorial helpful please like And subscribe for more see you next time bye bye