Streamline Your Business with an Automated Invoice in Google Sheets for Facilities

Automated invoice in google sheets for Facilities

all right this is the first lesson on building invoicing system in Google Sheets if you're new here you can check the introduction video to see the final result of the project the link will be in the description and let's start from the new document in this part we will be working with general settings and customer data okay so first let's rename this tab and call it settings we need only two columns here so it will work as the key values to work but first let's format this sheet a bit let's remove unnecessary rows and columns this will improve the speed of our document if you are working with Google Sheets often try to remove at least unnecessary columns so your document will load faster and you will monetize less lagging while working with the document do this especially if you have lots of rows alright so in the first row let's simply name our columns okay to keep this row fixed we can just track this line below the first row and it will be always visible when we will scroll next let's fill the table I'll copy paste values for a company as you can see it is simple values with no formulas and in the a column we have key and next to it we have the value of this key now let's add a currency symbol in my case it will be dollar sign and also currency format uh next let's format the number itself because there might be different requirements depending on the country so let's take the dollar sign and the front and combine it with simple text inside this text we'll write the hashtag the comma then hashtag hashtag 0.00 so what it will do it will replace those Hashtags with numbers and also those three rows with the numbers the only difference that if there will be no number the hashtags will be empty but zeros will left zeros okay let's say we have a text function with any number and our currency format we can see that it adopts this format to our number and let's say we have like thousands it will separate thousands with the comma and the decimals with the dot all right next let's have a date format here we are going to use like the full year two months and the day next let's have a format for our invoice number our invoice number will have three parts so it will be prefix separator and number format with leading zeros so let's call the prefix EnV the separator will be simple minus and for the invoice number I want to use six digit number with leading zeros so let's type six zeros and as you can see Google Sheets automatically reformats our number to be on the Zero what we can do we can add the single called sine and repeat the number of our zeros and two more values so we need we'll need this later on when we'll set up the invoice sending email from these Google Sheets so this will be the third party integration so basically that's it our settings tab is ready but I would love to style it a bit foreign customers again let's remove some columns and rows okay I'll paste the column names here the ID should be any unique value this will be visible only for you email column will be used to send invoice emails to the selected customer and all other accounts will be visible in the invoice okay so I'll copy some dummy data as you can see some of the columns are empty later we'll cover this and no empty data will be added to the final invoice so this allows you to add any data you want here about your customer and again let's dial this straight a bit okay we're done with this one let's move to another one let's call it invoice data again let's remove some columns and rows this tab will also contain key value similar to settings tab first will be customer next to the customer will have a drop down with list of IDs of our customers to do that we need to go to data data validation and click add rule make sure that you have range apply selected B1 next let's change the crit area to drop down from range and we need to specify the range in our case it will be customers from a to the end of the a column click enter and as you can see it already took all the values from our customers tab so we said that we want these values starting from the A2 and ending at the end of the a one more thing to add I like to use the arrow so it looks more clear and click done good now you can see that we can select any customer by ID next we'll add invoice number so let's start from the first one also we want to have invoice date and due date here I'll use today's date next I'm gonna add vat rate it's gonna be 20 percent so we have specified the format in the settings tab let's use the formula so what work what we're going to do we are going to combine these three values into one into one single value we can use simply by selecting these values and separating it with the and symbol or we can use the function called concatenate and just specify the values separate by comma then the result will be the same by the way if you want to show only numbers for example in this case you just remove these two values blank and only numbers format will be applied here all right then one more thing to add let's receive an email so I would like to see an email here where my voice will be sent for this we will use Simple vlookup by the search key and specify the range where we want to search in our case it will be customers from A to B so ending with the click enter yeah we forgot to add the column we want to see and one more thing as you can see it's wrong now because it takes the wrong email it is because it is sorted so we need to say that don't sort the result now it's good takes the the value that stands next to our ID and if we change our customer ID to automatically displays this value here again let's format it a bit good let's add one more Tab and call it invoice products again I'm gonna remove unused columns and rows good let's name our headers here will be invoice number product name quantity and the price let's track this down I'll copy paste some values to our products table and here we we are going to append products for our invoices no need to delete any previous products let's keep them to have the history of the invoice products and also let's format it a bit as usual okay that's it for this lesson in the next one we'll create the invoice preview to combine our creative tabs into one single invoice and also please check the description for this video I'll add notes in case something was changed or I forgot to mention in this video also write your questions in the comments section and support us by clicking like see you in the next lesson bye