Create Your Google Sheets Receipt Template for Export Effortlessly

Google sheets receipt template for Export

hello I'm going to show you how to print multiple invoices we've got an invoice template you can change the invoice number all the information on the invoice changes but we want to print each of these invoices we can do this now with a print invoices menu item and we can print all invoices we're going to print all of these invoices to a folder in my Google Drive so this is currently empty click print invoices print all invoices and you should be able to see that we're going through invoice one invoice two then it'll go all the way up to invoice 8 as these invoice numbers change so does the information then each time it's created that invoice it will then print the PDF into my drive you can see that they're all now going into the drive there's invoice s and then our last one will be invoice 8 in our PDF invoices folder you can see that's invoice one with all invoice one information this is invoice two with have invoice two information invoice 3 etc etc but if we go to invoice eight you can see this isn't actually a prop proper invoice this says if you look here title too many requests now this is a Google issue not a data or script issue and it's just Google moaning because they don't want you to do to many requests at once but that's fine because we've got another button here which says select invoices to print where you can just select invoice 8 let's do three so invoice 8 invoice 5 and invoice six then if we go back to our Google Drive Drive annoying that Google won't let you do as many as you want I've been trying to sort it out but I wanted to let you be able to print out invoices even if you can't print out hundreds now I'm going to take you through how you can get this template which already has the print function in it and then I'll run through the codes that you can either create that code yourself or you know where you need to change the code if you want to change references like where this sale references this invoice number or if you want to name the folder or name your invoices something else you can have a look at the timings below so you can skip to the bits that you're interested in let's get the template to get the invoice we want to go to blendu analysis.com sting scroll down put your name and your email address in here and then you can click on download got lots of different templates you can download download all for free so if you go for the invoice with print function this will let you copy the documents it will ask you if you would like to make a copy you can just click make a copy it also has a little warning that it that's this includes appscript it's this appscript that lets us do the print functionality it's probably good practice to view that appscript make sure that it hasn't got any bugs in it or anything this is the app script that I'm going to take you through but you also want to make a copy here this will give you your own copy and then you can also get to this print PDF by going to extensions appscript and here is the code okay so our first function is uh on open create our menu and our menu is going to be called print invoices so here's our print invoices you see it's got two options all invoices and select invoices to print so add item will add the first menu item which is print all invoices and then it will if you click on this it will run the function printer PDF the second item that we're adding is Select invoices to print this is what it's going to say on on here you can change it to say something else if you wish and then the function it's going to run is prompt user to select invoices i' would recommend you can change this but don't change this and then add all of these to our little user interface so when it opens all of this is going to create a little menu for us and then if you click on print all invoices we're going to run the first function print PDF so click on this one and it runs print 2 PDF this needs your permission to run okay that's fine this is the we haven't Google hasn't verified this app that's okay we can go to print and then allow and now print PDF functions here first of all we need to give it some information we want to know what's our spreadsheet and our spreadsheet is this whole spreadsheet with all of these tabs this is just saying get the active spreadsheet which is the one that we're on and the important tabs that we're using is the database Tab and the invoice tab so if you call these something different then these need to be named something different what we're doing in this function is taking the first invoice number here we're copying that we're putting this into our invoice tab here like this everything else updates automatically and then we take the next item so invoice two we copying that we're putting this into our invoice number here if I scroll down you should be able to see more information changing Etc so we want to do this for all invoices in this database sheet and this is what we now doing we're selecting the range which has got these invoices in this is called Data range and we're going to our database sheet which is this one and it's called database we've told it there and then this is getting range this get range is probably not what you're used to if you're used to just using Excel and Google Sheets I want to take A2 to A9 but instead of doing this it's giving you references it's first giving you the row reference a row is going to be get range and we're going to do row column row column so our starting row is row two which is here so invoice one and then our starting column is column 1 which is here we're starting from row two column 1 here and then we want to go down to that last row here so what number is our last row number nine but we don't want to include the header so we're taking off one this has got nice helpful function and what's our last last row of data that would give us N9 minus one cuz we're not including the header and how many columns across do we want to go just the one we're only taking this one column and then here it's saying our data is the values that are in this range here that we've just told it so get the range here second get the values within this range so we're getting all of the figures invoice one to invoice eight and this as you add more or have less this will obviously change next what we want to do is for each of these variables in data so this is saying for each of the variables in data run a for Loop for however long this data is so this data length will be eight so we need to run this Loop eight times each time with each invoice number it's saying what value are we going to print we're going to print each one of these numbers that I just means what row are we looking at next we want to go to our invoice sheet and go to sell F12 copy each of those into here one at a time but each time we put in our invoice number in here we're going to get the spreadsheet URL and set up exporting parameters so we're going to export a PDF in PDF format letter it's going to be portrait rather than landscape we're going to fit the width we're not going to have sheet names or print title on our PDF and then this tells us what range we're going to print our range is everything which has data in it you can change this to be sale references and this is a helper function so that it all works so this should then print everything to your PDF this section here is meant to stop the kind of like too many requests or Google thinking that you're spamming the system and finally this will print our invoice with the name invoice uncore so if you go to my drive that's what I've called them all invoice uncore then the invoice number so the value to print will be invoice 001 so you can find which invo you're looking at and then it's going to add a PDF because we're going to print it to PDF and we're going to put this in our downloads folder which I have called PDF invoices so make sure you've either got a folder called PDF uncore invoices or you can call your file to anything else but make sure you update it here so it knows which folder that it's going to go in and our file is going to go into our downloads folder we're going to create the file and set the name to file name which we've put up here and there you go that is the print PDF that's all of the codes that we use and the bits that you need to update if you use it next we're going to look at the select invoices to print which is quite similar and this runs the function prompt user to select invoices so here's the prompt user to select invoices first of all we initialize our variable select invoices so what this does is creates an empty but bucket but we're telling it here that this empty bucket is going to be a list of items or an array and we're going to use this in the data below you can see here it's already highlighted it we are going to create another user interface and this is going to say select invoices to print and then in enter the invoice numbers separated by commas and we're going to have the button okay and cancel so you can see print invoice select invoices to print and this will do our little user interface which is the title select invoices to print and enter the invoice numbers selected by commas and we've got our okay and cancel buttons so if the button that we select is okay then our input what we've put in there we're then going to split are input by commas so every invoice is going to have a comma separating it and then that's going to give us our list of invoices so instead of like having a long line of invoices this creates a list of individual invoices and we know it's a list because we told it's going to be a list up here and then for each of those values we're trimming again just to make sure that each of these invoices will be a separate value with no spaces we're going to call the function print selected to PDF and in it we're going to put those selected invoices so the invoices list that we just created so let's go to print selected to PDF and those selected invoices will now be the invoices to print and this is very similar but all we're going to do here is filter on the invoices that we've selected so again we're getting our active spreadsheet we're getting our database sheet which is called database our invoice sheet which is called invoice repetition isn't Brilliance in coding but I think for now this works and we can always improve and adapt right next our data range is the same as before we're going over here and we're going to database and we're selecting everything from row two column 1 to row 9 column 1 and for each of these variables we're going to run our function for all of these first of all we've got invoices to print is input data it's going to check that it's not empty and that the values in our invoices to print are within our value to print because we want to make sure that we've typed in invoice numbers that are in here otherwise it's not going to work when we try to print then all of the rest is the same pretty much and that's it we've reached the bottom it's a bit of a techy one today but hopefully if you want to print multiple voices it helps you can either just download the template or you can just use the code you can copy and paste the code and put it into your own but now you know at least where you need to change it if you've got any questions please comment if you find it useful Please Subscribe especially with this one because hopefully we can continue to adapt and improve feel free to share thank you goodbye