Create a Google Forms Invoice for Building Services with Ease

Google forms invoice for Building services

hi guys welcome back to the channel sham up a lot here this week i have a really exciting feature that i would like to share with you this is going to be really useful for all you side hustle business owners small business owners just have a few things that you want to sell to a bunch of people really quickly and this is mainly tailored to my audience in eugene but you can always figure out ways to adjust it if you live outside ug and follow me okay okay so we are going to start in our google form which is the main order form which is what all the clients will see so we want to set it up such that the clients can fill this information out and send it to us and then this goes to a google sheet and then from that google sheet it goes into an invoice and then from that invoice that anyways gets emailed to the client based on the email address that they put inside this form okay so i set up the google form here really quickly with first name last name delivery address what kind of would you like to order how many candles do you want mobile money number for paying this is for your benefit and it's one of the limitations of document studio it will make sense in the end i'll explain it i'm just going to add email address just because i i forgot that sorry i have a bunch of shortcuts let me just do email and let me do response validation let's say it must be email enter valid email okay and i'm just going to move this up i think it makes sense to have it just below the last name right there okay so i think our form has everything that it needs the candles are blue red black and white i'm so creative as you can see and then how many candles and so on and so forth okay all right so what i want to do is create what we call a pre-filled link and that's going to help me for that mobile money option this is because you know i'm targeting mostly my ugandan people okay and so you want to put your mobile money number here let's say 772 one two three four five six this is not my number don't try to text it you'll be very disappointed but okay so otherwise the rest of the form looks good okay so now this is the link that i'll share with the clients with this mobile money part already filled out okay so i just click get link and then copy link okay and then now we are ready to test test it out okay so i copy the link and this is what so this is the link that you can put on your on your instagram on your social media handle so people can fill out these forms but before we do that i want to connect our google form to a spreadsheet okay so i'm just going to call this order candles if you don't know this every time you create a google form the response is automatically populated into a google sheet and you can either choose to send those responses to an existing sheet or you create a new one in this case we want to create a new one which is what i'm doing right now so i just click create and then voila our spreadsheet is created with all the different columns corresponding to all the different entries that have to be filled out by the clients okay so now we are ready to test this out so let's use the pre-filled link which is this okay so let's enter jane email let's do let's use my email just so whenever the response happens we can actually see it work delivery address let's say let's say hololo and let's say she wants a blue candle and let's say how many candles let's say she wants five okay and then that's it so they shouldn't change this because this is you for your benefit this is one of the drawbacks of being in a dramatic document studio but as you know cheap things will cost you sometimes but i think we can work with this all right so i click submit and then as we can see here we have the the response populated in our google sheet okay so now i want to add two more columns i want to add a column for unit price of the candles and i want to add a column for total amount which we shall which we're going to compute after the fact okay to do that i'm going to add a separate sheet this sheet i'm going to call it prices and then i'm going to fill this out okay and then so items i want the candles so what candles do we have i just want to remember blue red black white okay so blue red black and white okay i want to increase this zoom a bit so you can see a little clearly all right so then i'm going to make up a bunch of prices so let's say the blue condo is 50 000 the red candle is that much the black is that much okay let me just change this format to uganda shilling currency uganda shilling there we go all right and we are good to go so now i want to pull that unit price from this sheet to here corresponding to the type of candle that has been selected to do that i'm going to use a vlookup if you do not know how to use vlookup let me know i'm happy to make a video i'll just show you how to do it whenever okay but so the vlookup function the search key we want is blue which is the candle the range the range we want is this range right here okay i'm just going to fix that fn plus f4 just to fix that okay then the index which is which column are we looking into which is the second one that's where we want to get our prices from so that's two and it's sorted we don't really need that so i can just close the bracket at that point and press enter and as you can see 50 000 blue and just to confirm the blue candle is 50 000 shillings okay and there we have it okay so then next i want to compute the total amount computing the total amount is equal to number of candles multiplied by the unit price okay and we press enter which gives us the total amount okay but now i want to make it such that every time this we have a new form filled out down in these subsequent rows these we need to make sure that these unit price and these total amounts fill out as well automatically in order to do that we need to change this formula into what we call an array formula which will apply to the entire column okay so to do that the first thing i do is i change this range to f okay and as you can see what that means is f2 to f just means the entire f column okay then once that is done what i do is i hold ctrl shift and enter which changes it into an array formula and then i press enter and so now what you have is we have a bunch of n a's because excel is looking through the entire row and it's finding a bunch of empty spaces so for example if i type red in here you see the red price unit price will fill out and as you can see here read the unit price that isn't right why is it pulling up 34 000. there is something wrong with our vlookup formula let me just edit that vlookup formula really quickly i think it's probably the range okay so i'm going to go in there so yeah let me edit the vlookup formula a bit okay so the range we want is that okay and then the index is two and this is sorted we don't need that so we close that and we press enter so now as you can see it has been updated correctly so the red candle is 60 000. all right okay so now let me just delete that okay so but i can make these n a's go away to do that i just use it i just insert an if function into the array formula and this is a bit complicated for you but all i'm trying to do is tell excel to look inside this column which is the f column and every time as you can see that's my logic expression is blank every time it's actually blank i want you to return nothing this will remove the n a's from here and when the value is false which is mean if it's not blank then i want you to return whatever is in our vlookup okay again this is not about the village function so for now just go with the flow um i can explain the formulas in detail later over my blog but yes so then i'm able to do that so i want to do the same for the total amount so i'm also going to edit these two ranges like that 8 to i and then ctrl shift enter to change them to array formulas enter we get a bunch of zeros and again we get a bunch of zeros because there are zeros in these subsequent rows and again we can fix that using an if function is blank g2 to g like that and the zeros are gone okay so now i just want to test to see if this is working by filling out our form one more time okay so let's go back to our google form and let's just fill it out again okay so let's put james and let's do est at com random email let's do bukolovi let's do black how many candles let's say he wants three oh i picked the wrong link instead of the get free link but let me so i have to enter this again but i'll fix that a bit later okay all right so now let's go back in here so as we see this fills out and it also computes the price of the candles let's just check in here to make sure it picked the right unit price black 34 000 34 000 perfect okay so now this is good to go this is automated up to this point the next thing we need is now to create our invoice okay so to create our invoice we're going to create an invoice template and you can create an invoice from google sheets by just going to file new and then from template gallery okay that's how you do it so i already have the invoice here so now we just want to edit the invoice a bit you know make it look like it belongs to our it's it's been sent from our company so let's call our company beauty candles i'm going to remove this whatever doesn't make sense i'll i'll delete okay so let me just put kampala okay so now the most important part of this entire process is what we call markers okay we need markers that tell excel look inside our google sheet for this specific variable whether it's the person's first name and put that first name in this invoice we need to we need a way to tell excel to look for the specific variable of the specific client who has filled out the form that may not make sense to you right now but let me show you what that means okay so for example submitted on okay what we can do is i can have it as submitted on that and then i use i go into our google sheet and i got the time stamp which is what which is going to be when the order was submitted and then i'm going to enter it here but all that enter it i have to surround it by double curry brackets these are called markers this is really important then i paste timestamp in there and we'll leave it like that the reason why this is important is because actually i think it might occupy too much space so i'm going to remove submitted one and just leave it as timestamp okay just so it our invoice looks nice okay so the reason why i copy and paste is because this word timestamp has to look exactly the same as it does in our google sheet if it doesn't you're going to get a bunch of problems we might get some problems later because i might make a mistake in here but we shall fix it in real time okay um so invoice 4 so we have name so what i'm going to do is double carry brackets go into our order form and get the first name copy that and then paste it like that and then i'm going to delete all of these maybe i'll live address so do double that let me go to delivery address copy that paste it in here surround them by the curry brackets again hopefully you now get a hang of what i'm trying to do so that way when someone fills out the form and the invoice is sent it will have their specific name in here that's what i'm trying to achieve okay so then i can put here something that says pay by mobile money let me move this here pay by mobile money and i'm going to put now this is why i added that mobile money number into the form so it can be collecting our mobile money number every time okay does that make sense now yeah so i copy and paste that okay so it looks great sorry i'm just trying to make this like that okay so um i can put a sort of like fill color in here just so the mobile money number maybe stands out also maybe i'll maybe i can bold it maybe also add all right so then i'm going to remove item one item two what i'm going to put here instead is what do you think what candles do you want okay exactly so i copy that so i come in here and i paste it double carry brackets okay for the quantity what do i want how many candles do you want okay that's that that that's it so i come in here double carry brackets and there we go let me delete that unit price same thing so i might like fast forward through this because i'm doing the same thing over and over again because i think you get a hang of it now okay this total amount has a sum function embedded into it um i'm going to delete this in this case i'm just going to put make it equal to whatever's in there okay all right okay uh in notes maybe i'll put thank you for your business let me just merge those two go to merge cells merge horizontally just so we can see that there let me add a question mark okay there we go the next thing that i want to add is wait maybe i want to add let me move this down a little bit okay maybe i want to add an image our logo let me add our logo in there so let me do insert image image over cells let me get something from google let's see if i can find a random logo from google it has a candle forgive me graphic design is not my strong suit so this might look a little ugly but i'm just trying to get a point across okay and there's our image going to make it smaller drag this up here make it a bit smaller just so it fits there we go and i think we're done i'm just going to remove this field because all of a sudden it doesn't look so nice to me okay and that's what our invoice is going to look like okay pretty basic um but good enough okay so now that we've set up our invoice okay now we can use document studio okay now you can get document studio by just going to extensions and going to add-ons and then so you go to get add-ons and then and this load up a little bit and then you type document studio and this is what we're looking for um that google will ask you if you're sure about making this life choice and trust me it's good so and it says yes it collects a bunch of information but again don't worry about it it's good no one's going to steal your information i promise okay and then it has been installed and done and so we are good to go and now it appears here so now we can go and click open okay so we click open and then this little side window opens up okay and now we have to answer a bunch of questions to make sure all this makes sense the first is create merge documents when rows are added in the sheet this is what we want to do so we click yes then it asks us to select a template in google drive that we want to merge to this sheet and what we want to merge is a google sheet and what google sheet is it we want to merge the invoice template okay does that make sense so i select that and click ok so document studio is now just saying okay you have a you have a google form you have a google sheet you want to merge that with the invoice thing you just create and as you can see it found a bunch of markers again the markers are really important because that's how it tracks where it's going to pull the data from this sheet okay so merged file name so you could give it a name um let's give it candle business um export format let's leave it as pdf okay oh sorry typo okay then merge with mail so now merge with mail this is when i click yes so we can create a an email template that we're going to use for our to email our client okay so now we're just going to say hi hi okay and again remember markers again so you come here and so we're going to get first name comma so thank you for your order i can't type to save my life of okay so now we want to go and get i need to get this i need to copy it just so i don't make a mistake so i need to copy that how many candles do you want copy that so your order of so id it will be of order of four then what kind of candles right so then that would be what candle would you like to order okay so i copy that go back in there paste that make sure i surround it with the markers and then condos so i'll do in brackets in case they order only one okay then please please pay by this number okay and then i go and i get this again this now makes sense why i added this there right okay so i paste that surround by carry brackets okay please find your invoice attached then you can say thank you for your business and then let's put my name shem okay so this is what this looks like then really quickly email specific people uh so send email to right it's so these are the these these columns again are from where from our google sheet as you can see total amount unit price all that stuff okay so i want them to send email to that to email and you could add specifically here you can add maybe your personal email i'm going to add the email i'm using for the form just so every time i test it can come to me so i can see if it actually works email subject let's say your order of okay then let me get paste it there uh let me see order of the blue condos i think so let me add the here and a space as you can see detail matters okay candle okay send us full name remove document studio you want it to be beauty candles okay all right i think we are good to go let me bold this phone number maybe i'll also bold this okay and then make sure this is checked include the merge document as an email attachment it's referring to the invoice that we created all right i think we are good to go let me click save okay then i go to google forms publisher generate documents send emails from google form responses that's a yes upload to google drive in this case we want to make sure that whatever documents are created from every specific client we can decide what folder they go into okay so for now because i'm on vibes i'm leaving that here and so you may want to change this so it goes to a specific folder with the client name so that's the sub folder we can put them in so you can find them easily okay and then file sharing permissions that we're going to ignore for now finish and merge leave everything checked then we want to check margin form submit which just means much once someone orders the candle okay so then we click save and sticky a bit long yeah click save and then we are good to go okay so now we are ready to test fingers crossed let us pretend that we are a client coming in to fill to fill the form and uh but i want the pre-filled i want the pre-filled links so i'm going to i'm going to ignore this one let me just delete this one and go back to my google drive to get pre-filled link okay let me get a pre-filled link two five six seven seven two two three four five six okay get a pre-filled link copy it paste it there so now this is what i want us to fill out okay so let me try jake villa so let me use my email address so everything comes to me from kajansi they want a white candle how many of them let's say they want six because they're feeling rich today submit and let's see what happens what should happen is we should receive an email so i should receive an email there we go your order of the white candles so far so good okay and as you can see hi jake thank you for your order of white candles please ma please pay by mobile money to this number please find your email attached thanks for your business and the invoice is there look at that this is this it did it in dollars we can change that out but for the most part we got it done on the first try pretty good right so i think this is something that could be useful to you yeah so that was a bit long but how cool is that right so follow through it see if you can replicate what i did if you have any issues feel free to reach out to me either in the comments or you can email me my email is also um in the comment section let me know i can help otherwise using documents to use an add-on in your google sheet in your google workspace could really be useful for you as a small business if you're trying to do if you're trying to automate your customer workflow for free i hope this was useful for you otherwise don't forget to like and subscribe and share this with everyone you know team up a lot i'll see you next week