Simplifying Automated Invoice in Google Sheets for Hospitality Businesses

Automated invoice in google sheets for Hospitality

hello and welcome to PAB in this video we are going to understand how to generate automated invoices for your business so let's say that you are receiving responses through Google forms you can use any other Form application as well so every time your form is submitted and you receive a new response then automatically with an existing template in your Google Docs an invoice document would be created and then a mail will be shared with the person stating that the document has been shared now to do this we'll have to integrate the three applications and we can do the same using p connect so here my trigger will be Google forms and my action applications will be Google Docs and Google Drive now to understand how you can integrate and automate this entire process yourself so that every time you receive a new response automatically the document be created and it can be shared with the person that too without the need of any coding skills let's start so this here dashboard of P connect software and you can reach here by clicking on the link which is given in the video description so if you are an existing user simply sign in and if you are a new user you will have to sign up it is not going to take more than 2 minutes and yes at the time of account creation you will get 100 free task so that you can test all these automations now once you reach the dashboard on the right hand side there's a tab create workflow click on this and this dialog box has appeared and here you have to name your workflow flow so here you can give any random name I'm going to name it as per my objective which is to generate automated invoices for business here I'm going to click on Create and as we do that here we have reached another window which has got two extremely important events one here is trigger another one is Action Now triggers and actions are basically those two principles or concepts on which this entire automation Works trigger this when this happens action says do this basically action here is the automated response of your trigger application let's understand this with the help of an example so every time you swipe your card and the payment is made then you get a notification on SMS about the amount debited how does that happen with the help of automation now in such a case your trigger will be the payment Gateway and your action application can be any application through which you can send these SMS now let's come to our use case so here I'm going to get responses through a form Builder application which is Google forms so this is going to be my trigger application and the trigger event so this is the event that is going to make this workflow run and here it will be new response received okay now here you can see we have received this web hook URL so this URL is going to communicate between the two applications basically it's going to act like a bridge to connect Google forms and P connect now here I'm going to copy this and once done here we have to follow the following steps so first let me take you to my Google forms account and let's have a look at the form that I have created so this here is my Google forms account and I have created this form the name is invoice generation form so here we have the following Fields name email address contact number then we have product one these are the three products then the quantity of product one then we have product two and then again quantity of product to so basically when the person is going to fill up the form so what it is going to do first he's going to select product one and then put a quantity and then select product two and then put a quantity now I'm going to take you to responses now within responses we have got these three dots click on this select destination for responses basically when you have filled up the form where do you want the response to be received either you can create a new spreadsheet or you can select an existing spreadsheet so here I have created a new spreadsheet now I'm going to click on create you you can see it is getting linked to the spreadsheet and now it is linked once this is done go and view in sheets so here you can see that a sheet has been created with all the fields that were there in my Google form along with the timestamp this is going to register the date and time of the response received okay now what is my objective I need to integrate this Google sheet with P connect to do this first of all I'm going to take you back to the form and let's preview the form and here I'm going to fill up the form so let's say the name here is demo user then we have the email address which is demo at P tes.com then we have the contact number + 91 so this is just a test number now let's say product one is LED TV and the quantity is two then the product two is laptop and the quantity is let's say one and here I'm going to click on submit so my form has been submitted let's go back back to the sheet that we have created and you can see here we have received all the details now let's integrate this so to do that I'm going to take you to extensions go to add-ons within add-ons we have get add-ons now here we have reached Google workspace Marketplace here I'm going to look for p connect web hooks let's click on this you can see that this particular application has already been installed on my system so make sure that you this then close this and now here do not forget to reload the page once you have done that wait for a few seconds and then we are going to go back to extensions and here you can see pabbly connect web hooks go to initial setup so now here the web hook URL will be set up on the below selected sheet you can see that the system has already selected the sheet and now here we have to enter the web hook URL so this URL that we had just copied I'm going to paste this here next is the trigger column now now the trigger column is the final data column where the data is added in your sheet so this here is the sheet and the final data column is column H so here I'm going to mention Edge Let's click on submit and as we do that here we can see that the configuration is set let's click on send test and here we can see that the test data has been sent successfully let's check the same so I'm going to take you to P connect and here we can see we have captured the web hook response this here is the trigger column then we have the time stamp spreadsheet Name ID sheet name sheet ID then we have the row index quantity of product two quantity of product one product two product one then here we have the user details now I want that every time a form is filled and the new response is captured in this particular sheet then P connect should receive the response to ensure that this happens we are taking you to extensions once again go back to P connect web Hooks and here you can see send on event just click on this so this ensures that every time there's a new event added here in this Google sheet so P connect would be able to get the response and once you do that you would be able to see this check mark right in front so this here is done now what do I want I want to create a document with an existing template let me show you the template first so this here is the template that I have created here we have CAU the customer's name email address mobile number then we have the invoice number invoice date and time here we we have the description quantity unit price and total price and then we have the total amount now as for this template so we have received the customer's name email address mobile number the first thing that I required is the invoice number basically we need to generate the invoice number how am I going to do that for this I'm going to use a feature by P which is number form matter now within number form matter the action event is going to be counter so this lets you increment or decrement a value I'm going to click on connect and as we do that here we have to fill in this information first of all the initial value let's say the number the invoice number is going to start at 1 0 0 100 now the operation is increment I want that every time I receive a new response the number should be added by one so increment change value by one and then we have reset on execution no I do not want it to be reset reset counter after reaching certain final value I do not want that here I'm going to Simply click on Save and send test request and as I do that here you can see we have received the response let's check the same so I'm going to click on Save and send test request once again and as you do that you can see that the number has changed from one more figure so now I'm going to add this step once again and here the action event will be counter let's click on connect and the initial value let's say it is 100 operation is increment change value by one then we have the reset on execution no Etc let's click on Save and send test request so here you can see we have received one1 as our response so yes we have received this so what have we received right now we have received a number that is going to be incremented every time you receive a new response now once we are done with the invoice number our next objective is that I have got the SPL sprad sheeet where I have the name of the product and price so I want to look up for the particular product along with the price to do that I'm going to use a feature by PAB which is lookup table now what does this lookup table do it gets match data from lookup table Fields by lookup key now first let's make the connection so that you can understand this in a proper way so here what is my lookup key my lookup key here is product one so here I'm going to go to product one now we have the label and values so what I'm going to do is here I'm going to enter the name of all the products along with the prices so the label one is Led TB always remember that the label has to be written exactly in the same format in the same manner as it is there in your spreadsheet and what is the value for this LED TV it is rupees 200 so here I'm going to mention 200 I'm going to add another label here this time it is laptop and what's the amount it is 150 then I'm going to add another label and here it is music system and we can see the value is 130 rupees let's check the same yes it is 130 rupees now when I click on Sav and send test request here the response that we have received is result label is LED TV result value is rupe 200 so what we have received is that the first product was LED TV and now it has checked all the rows and it has given you the value the price of this particular product now what I'm going to do is I'm going to repeat the same process let's copy this and here I'm going to paste the same step because now we are going to repeat the process for product two so here let's do the same for product two I'm going to go to step one and as I take you down so here we have product two which is a laptop and now as I click on save and send test request here you can see we have received the name of the product along with the price now this method with which I have added the information here and in the above step is known as mapping and this is important so that every time your workflow runs it can take the new data into consideration had I written this value manually then in that case it would have become Static which means that even when I had received a new response it wouldn't have taken that instead it it would have been stuck with the manually written data So to avoid that we map the information let's say that the person had selected music system as product two so it wouldn't have taken music system instead the value that we would have received here would have been laptop only So to avoid this we map the information now once we have received the product along with the price next I want is the total amount ing to the quantity for this I'm going to use the feature number format once again and the action event here will be perform math operation let's click on connect and then the number so let's map the information so as I click on this and here I'm going to go to Google forms so first of all we have the quantity so the quantity of product one we have selected put a comma and now I'm going to go to step three and here is the price of product one so what is the the operation that we want to do we want to multiply this let's click on Save and send test request and as we do that here we have receive the total amount for product one and now I'm going to repeat the same process copy step and now I'm going to paste the same step and now I'm going to do the same thing for product two so here the quantity of product two I have deleted and from response I'm going to select quantity of product two which was one and the result value for this let's go to step four and this is the price of product two now I'm going to multiply this let's click on Save and send test request and as we do that here we have received our response so we have received the total amount for individual products now I want the total amount for both the products for this again let's click on this and again I'm going to use the same feature which is number for matter and the action event is going to be the same perform math operation let's click on connect and now here let's map the numbers so this time I want this number which is the total quantity for product one comma the total quantity for product two and what is my objective the operation is going to be add let's click on Sav and and test request and here we can see we have received the total amount for both the products now once we have received all this information I already have an existing template in Google doc ing to that template I'm going to generate a document to do this my next action step is going to be Google Docs let's click on this and the action event here will be to create document from template let's click on this click on connect and here now P connect wants to connect with Google Docs account let's let's select add new connection sign in with Google here we have to choose an account I have chosen the account now ply connect Drive wants to access my Google account this is absolutely secure let's click on allow and here we can see that the authorization has been successful so once this is done we have to enter the name of the template document so this here is the template now here we have to enter the information first of all new documents name so here I'm going to name it as invoice and now I'm going to take the email address of the person okay so this is how I'm going to name my document next is documents location so in my Google Drive I already have created a folder which is in voice details so here I have selected the same now let's map this information customer's name so here I'm going to go to Step One take you down this here is the name then we need the email address so here is the email address then we need the mobile number here we have the mobile number invoice number so what I'm going to write I'm going to write a b c and here I'm going to go to step two and this here is the number so this is how I'm going to write my invoice number next we have the invoice date and time so let's go to Step One this here is the invoice date and time then product one so what was the name of the product it was LED TV what is the quantity so the quantity of product one is 2 now what is the rate of product one for this let's go to step three this was the rate of product one now the total price so let's go to step five where we had calculated and it was 400 let's do the same for product two so the name of the product is labtop then what is the quantity so the quantity here is one then we we have rate of product two for this we are going to go to step four this here is the rate of product two then we have the total price so for this we are going to go to step six this here is the total price and now the total amount for both the products let's go to step seven this here is the total amount so once we have mapped all this information I'm going to click on Save and send test request and as we do that here we have received the response so here we have received the document ID let's go and check the document if it is created so yes here we can see an invoice has been created for demo at pud.com so here we have the details name email address mobile number you can see invoice number is abc11 submitted on this date and time here we have got the products quantity unit price total price nodes and this here is the total amount so we have successfully created this invoice do document and now finally I have to share it with the person so for this let's go to P connect once again and here I'm going to add my final action step the application is going to be Google Drive let's click on this and the action event here will be share a file now let's click on connect so here again P connect wants to connect with Google Drive account let's select add new connection sign in with Google so here we have to choose an account I have done that and now P connect Drive wants to access my Google account so click on allow and once done here we can see that the authorization has been successful so once Google Drive and P connect are connected now first of all here we have to enter the file ID so I'm going to turn on this mapping button delete this and from step8 I had received this document ID so I'm going to map this document and next next we have the email address of the person so let's click on this go to step one once again and here is the email address now what do you want since you are sharing the invoice you do not want the person to edit and comment here I'm going to select can view let's click on Save and send test request and as we do that here we can see that we have received the response the kindest Drive permission this permission has been given to the user R is reader this here is the ID that we have received now I'm going to take you to the email address of the person to see if the person has received an email and yes here we can see that the person has received an email and here we can see that this person has added you as a viewer verify your email to securely view this document so this here is a document shared with the person so this means that our integration is a complete success now let's check the same in real time so I'm going to take you back to Google forms let's click on preview and let's fill up the form so let's say the name is dummy new then we have the email address dummy at pud.com contact number is then we have the product so let's say this time the first product that the person has chosen is music system let's say the quantity is five next the second product let's say it is LED TV and this time the quantity is 2 now I'm going to click on submit so as soon as this form has been submitted first of all the document should be created right so I'm going to take you to documents let's go back and here we can see that for dummy an invoice has been created so this here is a name here we have the email address phone number you can see that the invoice number has changed to abc12 this is the submitted date and time here we have music system quantity is five unit price is 130 so the total price has become 650 for LED TV it was two so this here is the price and this here is the total amount so the document has been created now let's go and check if the person has received an email and yes here we can see that we have received another email this is for dummy pud.com so this means our integration is a complete success now I'm going to take you back to P connect to see what we have done this here is an extremely interesting workflow so where our trigger was Google forms the event was new response received so one once we had received the response with the help of number formatter which is a feature by PAB we had chosen the event counter to increment the value so that we could create an invoice number then we used lookup table twice to receive the name of the product along with the price once this was done then we had used number formatter once again to receive the total price along with the quantity of the product so we had used the step price and then our action application was Google Docs where we had created a document from template and our final action application was Google Drive where we had shared a file I hope this is absolutely clear to you if you wish to have a lot more information so here you can add due date you can add the GST number all you have to do is you have to receive that information through the formuler application and when you're creating this document you can create a template with all the information required if in case in case you have any doubt you can always refer to the workflow which is given in the video description you can also clone the same and start to process immediately apart from this if you have any specific integration to work on you can always write to us not only this but you can integrate multiple applications using p connect software to ask any of your query you have to write at forum. p.com you can also visit our website which is .pa.com connect to get all the details about the latest updates the Integrations and applications you can visit our Facebook group if in case you have found this video helpful then do not forget to share this with your friends and colleagues so that they can also get into Automation and make their life easy I'm going to see you very soon with different Integrations and Nots do not forget to like share and subscribe take care of yourself