Streamline Your Processes with the Google Sheets Billing Template for Export

Google sheets billing template for Export

hi I'm back here again in Google sheets and in this video I'm going to be talking about the invoicing aspect of my Google sheets CRM system and this involves three separate sheets we have the main invoice sheet where we're filling out and creating invoices we have an invoice history sheet where we're recording all of the information about previous invoices and we also have a prices and rates sheet where we're keeping details about the goods and services that we're providing I'll start here because this is quite a simple sheet there's really nothing going on here I have the same style header with six rows and rows as most of my other sheets and I have columns for various data about the goods and services that I'm providing I have columns for code and also for description but both of those are optional really the only things that matter here are the item name and the rate or price as always you're free to modify this to show other kinds of data about the products and services that you supply but what we have here should be good enough for my demonstration the next sheet is an invoice creation sheet this is where we can come to build invoices and automatically generate a PDF copy of them to send to our customers so there are three main areas to this sheet in the top left-hand corner there space for you to add details about your company such as your address contact details as well as payment methods whether that be a direct deposit or some other method in the top right-hand corner we have an area for our customers data and all we really need to change here is the customer name and we have a field for data validation here which is pulling customer names from our client list sheet and we can simply select a name from the drop-down and the relevant data will automatically be populated into these fields here we also have an invoice number field which is automatically updated from our invoice history sheet and we also have a date field the date field isn't automatically update although you could quite easily do that I tend to leave it static so that it can be changed as needed rather than always having today's date which is more than likely not going to be the day that the actual work was carried out we can choose a relevant date from this drop down here instead the third area is the main body of the invoice where we can define things like the item in question the charge the quantity supplied and we also have a column for line total which is automatically populated when a quantity is given at the bottom of the invoice we have an area for a trade discount so we can apply one here if we want to we also have a field for total paid so we have space to put in a deposit if a deposit was paid and that's automatically subtracted from the total G all of these automatically updated fields are quite straightforward in our charge column we have a simple vlookup to our prices and rate sheet where we're looking for this value somewhere on that sheet and then returning the fourth column which just happens to be the the price column and dropping it into this cell here we have a column for quantity which is simply a user entered number and that affects what we get in the line total which is quite simple it's simply the charge multiplied by the quantity and in the total two column we have a sum of everything in the line total column minus the discount and minus the total paid to this you might like to add individual line discounts if you were discounting just individual products or services but I haven't had a need to add that yet we also have a field for description here which has a formula in it very similar to our charge column which is looking up data on our prices and rates sheet so let's take a look at that guy have a description field here which like I said is optional and it might only be applicable for certain kinds of products so let's let's put one of these products in here and we can see that that description is updated but if you have a service in here then you're not likely to have a static description for that service you might like to add your own custom description to that field now having done that we've overwritten the formula that was in that cell there's no longer a formula in there but I'll come back to that in just a moment once you're happy with all of the data that you've generated here you can go ahead and press the print button and an invoice will automatically be generated and all of the fields in this sheet will be blanked so there we go we've lost all of the data in the sheets here and we're ready to do another invoice however if I take a look at this first description cell where I had written my custom description you can see that my formula is back in this cell ready to go again if need be we also have an updated invoice number and taking a look at my invoice history sheet you can see that that invoice that I've just processed has a line here so we can see the company name the status whether it's paid or unpaid the total jus as well as the number of days since the work was carried out it's all very useful information and of course we also have a PDF which was automatically created so I'll pull that up now and this is my invoice which was automatically dropped into the specified folder with all of the relevant data on there this document itself has also been shared and a copy of the sharing link as well as a copy of the PDF itself have both been attached to an email which has been forwarded automatically to my email address so I can then review that and I'm happy with it I could forward it to my customer they can then access either the attached PDF or they can follow the link in the email and arrive at this document here in my Google Drive so that's a very quick and easy way of generating invoices where your customer has a couple of different ways of getting access to it and now I'll take a look at the script which makes all of this work so I've got my script editor open up the top here and I have a new script file called invoice to PDF and I'm not going to go through all of this bit by a bit because there's quite a bit there it's fast to say that we have a single function called submit invoice which is the function that's being called when I press my print button this guy here and that's doing three things first it's making a PDF second it's adding a line to my invoice history and third it's clearing all of my invoice fields ready for new input and as you like you can add to this or comment these out if you don't necessarily want to do that particular thing the function to make history is going to look pretty familiar to you if you've seen my other videos it's doing effectively the same thing that we've been doing with a lot of our data simply getting a range and then setting a value if you're not completely up to speed on that then I recommend checking out one of my previous videos where I to talk about that and similarly we're clearing the invoice fields in a way that we've done several times before there is nothing terribly complicated going on here however right at the end of this function you can see that we have a little for loop which is going to run about 19 times and what it's doing is setting the formula that we have in their description cell back to what it should be so that's this combination of strings and mathematics simply entering that as a formula for that cell this way we can overwrite our description formula as much as we like with our custom descriptions and it's always going to come back after we submit the invoice back up here and function make PDF we're doing quite a few things that you might not have worked with before for most of the methods that you see here I highly recommend that you check out Google's documentation on how these work and what they do as that's going to do a much better job of explaining them then I can do here when building this for yourself you'll need to specify your own folder ID this is my own folder ID which my invoices are saved to but when building this for yourself you'll need to track down what your own folder ID is and in order to do that you can simply open up your Google Drive locate the directory that you want your invoices to land in right click and go to get shareable link and the link that's provided here we'll have an ID number in it and this is what you need to extract and punch into your script in order for that to work quite straightforward and easy to do I've also removed the section of this script which automatically generates an email with this attachment however it's relatively simple so I'm going to supply detailed instructions on how to take that code and modify it for yourself because a lot of it is going to be dependent on your own environment whereas my copy of that code is going to be very specific to me so at the end of this series I'll be providing all that code along with the chunk to send emails and heavily documented so that you understand how to make that work for you now coming back to my spreadsheet and taking a look at our invoice history we do have three invoices here already which are unpaid and we can take a look at the individual customer sheets for these people and see what's going on there so for red Sun Holdings we can see that we have an outstanding invoice just one of them and we have a total unpaid amount here we have the invoice number in question and the amount owed this is a handy way of giving you an overview of the total that's outstanding as well as all of the individual invoices so that when your invoice history sheet fills up you can easily make sense of the data that you're seeing there and returning to our main client list we can also see that our paid up column now has some data in there and none of these customers are yet paid up however if we pay off say this invoice first of all the customer data sheet for red Sun Holdings is going to be listed as fully paid now and our main client list is going to show that this customer is paid so again a very easy and straightforward way of understanding what invoices have been paid and have-nots and which customers are and are not paid up now if we take a look at the paid up column we have a bit of a weird looking formula with something called indirect in the formula and I'll try to explain what's happening here first of all we have an affair to catch any errors so if something wacky is going on here it's not going to give us an error it's just not going to display any information a second thing it's doing is we have an if which is looking up a 7 that's this guy over here and if a 7 does not contain any data then it simply returns an empty string that's the first argument of my if statement if a 7 does contain data then we're doing this part of the function and here we have another if statement with an indirect and an indirect is just a way of referencing a cell as a string so effectively you're taking the data or the value that's in that cell and using it as part of your formula so you can kind of think about this as creating another little formula within your formula and having highlighted this string here we can see what the actual output is the output is red Sun Holdings in single quotations and then an a.1 notation of a cell and what is this well this is simply a reference to a cell in another sheet so I'll escape from here take a look at red Sun Holdings and we're looking at q6 so that's the first cell in my unpaid invoices panel which is currently blank and ing to this formula if that cell is equal to a blank string then we have the word yes inserted in that cell and if it's not equal to an empty string then we have that cell populated with no so using this formula we're conditionally like getting a sell on another sheet and then returning its value whether it be blank or not and then we're simply using conditional formatting to display that cell in a particular color so if the text equals yes then the cell is green and if the text equals no then the cell is red so indirect is a good way of referencing a cell on another sheet where you can't really specify what the sheet name is but you can get the sheet name from somewhere else on the sheet namely this cell here and we're using that in direct reference to take a look at the unpaid invoices which as I explained it in the previous video I have made an entire video just dedicated to this one array formula and how it does what it does so I'll link that in the description and you can check it out there if you do need to understand how that works but because this array formula is not turning up any unpaid invoices for this customer then our cell is blank and consequently we get a yes in our paid up column okay so that's invoices in a nutshell the next video in the series should hopefully be the last video and in that one I'm going to be talking about security on your sheet both in terms of protecting ranges in sheets and also understanding how to safely share files and folders in your Google Drive where it's appropriate so the people who need access to certain documents or sheets on your spreadsheets can get access to that data without compromising any of the other data that you have in your Google Drive or in your spreadsheet I hope that's been helpful thanks for watching and I'll catch you in the next video