Collaborate on Google Sheets Invoice Template for Operations with Ease Using airSlate SignNow

Google sheets invoice template for Operations

so in this video we will learn how to create invoice using Google forms and Google Docs now suppose guys you have created an online order form using Google form and you want whenever a customer fills the form and places a new order you want to create an invoice of the same order automatically so guys using public link you can set up this automation so the trigger of this automation will be receiving a new order on Google forms and the action will be creating an invoice in Google Docs for the same order now how you can set up this amazing automation to know this you have to come with me to my screen so first of all we will go to our browser and search for paply.com Connect and after reaching to the landing page of public connect by clicking on sign up free you can create your free public account in just two minutes or if you already have an account just click on sign in so I will sign into my own public connect account and after signing in we will reach to this all apps page of public here we just have to click on access now below public connect and now we can see that we have reached to public Enix dashboard so in this dashboard click on create workflow and give this workflow an e for example I would like to name a workflow as Google forms to Google Docs and then just click on create now we can see that a workflow is open over here and in this workflow we have two different Windows first one is trigger window second one is action window so triggers and actions are basically those two concepts those two principles on which this whole automation works on where the trigger says when this happens and the action says do this so let's begin with the trigger window so the idea here is first we have to get the details or the data of form submission from Google forms in public Connect Now suppose guys using Google form I have created a auto form and I have placed or shared that form with all of my clients and customers so whenever a user wants to order some product from a business just take example of a cake business like I own a cake business and for that to receive orders from my customer I have created a Google form so the customer can enter their details requirement in that form and submits the form so we want that whenever a customer enters the details and submits the form basically they place an order the detail details of that order should be captured inside public connect and then using public unique we will send that data to Google Docs and in Google Docs using an invoice template a template document P will be creating invoices on the basis of order which we have received from Google forms so let's begin so here in public connect in trigger window first we in choose app we will search for Google forms select Google forms and in this video guys we are using Google forms but if you want you can use some other form building application of your choice as well like typeform.com Builder or any other form building application and after selecting Google forms from choose app here in trigger event from the drop down select new response received after selecting a trigger event public any gave us a webbook URL now we will use this web URL to make the connection between Google form and probably connect so guys here what we have to do we just have to copy this webwork URL from here and follow the help text given below over here so after copying this webwork URL just go to the form the order form which you have created in Google form so this is the form and in this form I am asking some details of the order from the customer like the name of the customer email mobile number delivery address that where we are supposed to deliver the product just example for cake delivery date and time what's the name they want to be written on the cake what's the flavor of cake they bond and what is the quantity so here guys whatever form you order from you we want to create you can create in Google form just ensure that the last question of that form should be turned on to the required section so the last question should be mandatory to answer by the customer just turn it on and after that right now we are in the questions tab we will switch to the responses tab over here in Google forms and we will click on this tiny little icon of create spreadsheet just click on it select create a new spreadsheet and click on create when we will click on create guys in a new tab a Google sheet will open up and this is the same sheet guys in which the form submission response or form submission data are stored of our Google form so whenever we will receive a form uh receive a form submission in Google form automatically that data is by default stored in this sheet so from this sheet we will be sending the form submission data to properly connect and instead of Google forms if you are using just Google studio and manually entering all of the order details in Google sheet and in that way you want to generate new invoices you can follow the same process over here now to connect this sheet with public connect we have to go to extensions add-ons and click on get add-ons after clicking on get add-ons here we will reach on Google workspace Marketplace and here in search app we have to search for public connect webhooks p-a-b-b-ly public connect web books and as you can see this add-on of public connect web books is already installed in my Google Sheets you just have to the same add-on into your own Google sheet and after that just refresh your Google Sheets once this is a very important step and after refreshing it once again we will go to extensions public connect webbook add-on can be seen over here just hover on it and here click on initial setup now when we click on initial setup a dialog box appears in front of us and this dialog box is asking us three basic questions selected sheet web URL and Trigger column so here in selected sheet we have to select the sheet that from which sheet we want to send the data to public connect so here in this spreadsheet you can see there is only one sheet right now and that is form response one the name of that read is form response one that is why by default this sheet is selected then the second thing it is asking us is the web URL so you must be remembering remembering guys probably connect give us a web URL so we have to paste the same web URL which publicaning workflow we have received and paste it over here and after entering the same web URL the next thing it asks us is the trigger column now you must be thinking what is this trigger column so trigger column is basically the last data entry column of our Google sheet so guys here in the background example you can see that column I will be my trigger column because this is the last data entry column of math sheet this sheet after column I in none of the column any of my data does not exist that's why I will select column I as trigger column instead of column i a b c d e f g h any column could be your trigger column of your Google sheet you just have to ensure that it is the last data entry column it is the last column in which your data is entering and after entering this triggered column click on submit and setup configured successfully we have successfully connected this Google sheet in which our Google form responses will be stored with public connect now just close it once again go to extensions go to public unique webworks and this time select this option of send on event just click on it and by selecting send on email we are telling this Google sheet that whenever whenever a data of a new data in a new row is added tell our trigger column this means by this use case whenever a new form submission data is added in this Google sheet instantly send the data to public connect so by selecting send on event we are telling this through Google sheet and now guys we are ready to go now whenever we will receive a new response in this Google form automatically that forms submission data will be received in public connect so what we are going to do we are going to place a test order a dummy order using this Google form just copy the shear link of this form just copy it and in a new tab we are going to paste it so this is the order form and we are going to place a dummy order using this form for example let's enter some dummy details example Jimmy name so this is just a dummy details of a customer email address a dummy mobile number than a dummy delivery address as well after that we have to enter the dummy delivery date and time also so for this dummy order I want my delivery date to be let's select the date as for example 25th of September 2022 and time should be for example 6 pm okay after this let's enter the name of the cake for example name of the kicks I want is dummy let's select a flavor so I will be selecting just for this dummy response we will be selecting flavor as Choco chip and the quantity will be for example 2 kgs so these two details are very important as well so right now after entering some details we are just going to place a test order a dummy form submission in this form click on submit and the Palm has been submit and because we have connected this form or basically this Google sheet with public on it here in Google sheet we can see that form some Mission responses added instantly the same data will also be captured inside public connect and yes here in public unique we can see that we have received some response and this is the response of the same form submission same data which we have just now made while making entering this form so we have the same name of the customer same email same mobile number Sim address same date and time that at what date and time customer invested the order or the case should be delivered name on cake same flavor of cake and also the same quantity so in this way guys we have successfully received the order details from Google forms in public unique now guys to create the invoice we need to add the total calculations and the total amount and total values of the invoice as well that what is the product that has been ordered by the customer what is the quantity what is the price what is the total amount and all of those things but guys here like you can see in this Google form which we have created we have given an option for example of four products you can basically give any amount of products of your choice but all of these four products have different price like for chocolate the price is for example 400 rupees per kg for Choco chip it is 500 rupees per kg let's take an example of pineapple that is 600 rupees per kg and the butterscotch cake is 700 rupees per kg now I suppose all of these four products have different pricing so the total amount of the invoice can also be static and also not just the flavor of the cake but the quantity of the cake is also a variable like the customer will choose choosing what amount of cake they want what is the size of the cake they want so we have to basically multiply the price of the cake and the quantity of the cake to get the total amount and we have to add all of those value into the invoice which we are going to create now guys but to do so first we have to introduce the prices of the cake in this automation workflow and to do so guys we are going to use a feature of public connect that is lookup table so using lookup table we are going to create a pricing table in this workload a short little pricing table where we are going to mention the name of all the products and their price in front of that and after that using local acceptable whatever flavor of the cake whatever product the customer will be choosing whatever data will be receiving from Google forms selected by the customer the name of that product and the price of that product will be given by in the response by lookup table so how it works let me show you so we will just scroll down and come to our action window and here in choose up we will search for lookup table select lookup table and an action event from the drop down select this click on connect now the after selecting lookup table the first thing is ask it asks us is the lookup key so here in lookup key we have to basically map this response of flavor optic basically the response of the product whose price we want to know in response so this is the product just we have to map this response of flavor of cake over here and guys the process of mapping is very simple simply click on this field and in the drop down below we can see all the responses we have received from group form submission from Google forms can be seen over here and out of all of these responses select the response of flavor of cake and it will be mapped over here after that the next thing it asks us is the fallback value so this is not a mandatory thing you can ignore it for now in the next thing it asks us is the lookup table so here one by one in label section we have to mention the exact name of the product the exact name of the flavor of the cake in label section and in value in front of that we have to mention the price of the product so let me show this to you with an example so let's see our first product over here is wash chocolate so we'll just copy this exact spelling the exact text of that flavor name product name that is chocolate and the rate which we have decided for chocolate was 400 rupees so I will enter 400 over here then click on this plus button then our next product next lever was Choco chip let's copy the same exact name of Choco chip which we are going to get from Google form response and paste it over here and the price of chocolate chip was 500. click here and in this way basically we have to add all the products all the TV up to create a table of all the products and their pricing so in label section we are going to mention the product name the the response name or the flavor name which we are going to get from form submission and in front of that in value section we will be mentioning their price which we want to get in response and at last the butterscotch copy this paste it over here and then the price is 700. so in this way I've created a pricing table like the product name and in front of that in value section their price so whatever product the customer will be selecting over here in Google forms we will get this response that same response is mapped over here so whatever product the customer will be selecting in for by making the form submission in response of the lookup table we will get that product name and the price of it the value in print of it let's click on Siemens in test request and check this and yes because we have mapped Choco chip over here in response we can see Choco chip and the value of chocolate chip there what is the price of Choco chip that is 500 we have entered over here so in this way whatever product is customer is choosing in lookup table we can get the price of that product so we have the order details from Google forms and we have also received the price of the product ordered by the customer now guys after getting the price of the product based customer has ordered we have to multiply this price with the quantity to do so to do this kind of multiplication just click on this add action step button over here and here in choose app search for number formatter so number formatter is a feature is a module of public Connect using which we can also do the calculations so after selecting number formatter probably an action event from the drop down select perform math operation and click on connect then the first thing it asks us is the numbers that what are the numbers on between which you want to perform the math operation so we want to perform the math operation between the price of the product which you have received from lookup table and the quantity which customer has ordered the response which we have received of quantity from Google forms in trigger window so we will be mapping this quantity and price over here in this number field just click here and from the drop down below from the responses of lookup tables first select and map the prior price of the product then add a comma and after adding the comma from the responses of Google forms select the response of quantity and map it and after mapping the price and quantity in operations from the drop down below select multiply and click on Save so in this way guys we have performed this math operation and in this math operation we can see that we have the value so we have multiplied the price of the product by the con with the quantity which customer has ordered and we have received a total amount now at last we have to send all of these details all of this data which we have received to Google Docs so that the template invoice which we have created using in Google Docs using this invoice we can generate new invoice for every new order using this automation now to do so just scroll down and for the One Last Time click on this add action step button over here and here in choose app search for Google Docs Google Docs it is and then an action event from the drop down select create document from template so we have the template and we want to create a new inverse document so select action event as create document from template and click on connect select add new connection and click on connect with Google Docs select your Google Docs account in the same account in which your inverse template is created or you have created your inverse template and then here in a new tab it is asking us to authorize just click on continue and now we can see that our Google Docs account gets connected with public connect after making this connection the first thing it asks us is to select the template document so in the drop down below we can see a list of all the documents which you have created in Google Docs can be seen over here and out of all of this document we have to select the template invoice document which we have created so the name of this document is invoice template so we will be selecting this template over here and after selecting our template the next thing it asks us is new document name that the new document new invoices which we are going to generate what will be the name of that invoice so I want the name of the newly generated invoice to be the name of the customer who has placed order we have received the theme of the customer from Google forms and after that we I want the date of the date at page what date and time the customer has ordered the response so on Google form submission here we have received the timestamp that at what time stamp or at what date in time we have received the order we have received the form submission so we will be mapping both of these details the name of the customer and the timestamp to generate a new name for the new document so we'll just scroll down we'll click here in this new document name field and from Google form responses select the response of customer name add a hyphen and after that map the timestamp then it is asking us new document location that what is the location of the new document which we are willing to create so the newly generated document will be stored on our the Google Drive offer account the same account who which we have connected in Google Docs account on the Google Drive of that same account the newly generated document will be stored so here it is asking us in which folder in a particularly in which folder this new document we want to be stored so let's go to my Google Drive first this is Google Drive and here guys I have a folder named as new files so I want that whatever new invoices we have generated that new invoices should be stored in this new files named as folder so here in public connect from the drop down from the list of all the folders which we have created in our Google Drive select that particular folder and after that now we can see a list of all the variables all the curly braces which we have added in our Google doc can be seen over here now guys the details or like the customer name address email address mobile number and Voice number and all of these details are basically going to be a dynamic text a variable for every new invoices all of these details are going to be changed for every new invoice and to add these kind of text these kind of variables body pills or some Dynamic text which are going to change for every new invoice I have added some text in double curly braces so this is basically a variable a dynamic text a bodybuild should be saved and guys here we can see a list of all of these body pills all of these texts which I have added in double curly braces we can see different peels for all of these texts over here and probably connect workflow so one by one what text we want to place or insert in place of these body peels we have to enter that text over here like here in this invoice new invoice in place of this customer name body field customer name Dynamic text I want the name of the customer who plays the order should be entered so here in custom money from the drop down from Google form responses people map the name of the customer then in place of customer address we will be mapping the name of address of the customer which we have received from Google forms so this is the delivery address then in place of email we will be mapping the email out the customer this is it and one by one in this way we will be mapping all of the data let's map the mobile number now after that here it asks us for the invoice number so all the invoices which I am generating using this automation I have on the invoice number to be that inv that and after Dash I want to mention the row number so this is the row index of the row which we have received from Google for sheets so here after Google form submission the data which you have received from form submission from Google sheet we have the row number that was wrong what row number this data is present so I want to add this row number over here in this invoice number so I'll be mapping the row index over here then let's map the delivery date and time which we have received from Google forms so this will deliver date and time just map it let's map the name of the product the flavor of the product or flavor of the cake which customers ordered let's map the quantity so we have also received the quantity over here so to map the price we will be mapping the response from lookup table from where we have received the price of the product which customer has ordered and at last the total amount so here in total amount from the drop down after using number formatter we have multiplied the price with quantity and received the total amount so this is the total amount which customer has ordered so in this way guys we can see all the data which we have mapped over here basically all the data which you have received from Google forms lookup table and after using number formatter and all the calculation making all the calculation of amount we have mapped all of that data over here and now just click on Save and send test request and after clicking on savings and test request we can see that we have received some response over here and this response seems to be a positive response to us this response shows using all this data which we have mapped and this template a new document has been generated a new invoice has been generated the name of the new document is this which you have entered and it is stored on the Google drive in this particular folder so let's check we will go to Google Drive and this is the folder and yes guys here in this folder we can see there is a new document let's open this document so this is the same document name the name of the customer who plays the order and the date and time of receiving the order then we have this invoice and in this invoice we can see this is the seam in Virtual noted using this template which we have created and all the details are the same in this invoice like we have the same name of the customer who placed the order using Google form we have the same address same email address same mobile number of the customer we have the invoice number and delivery date and time also guys we have the description of the product the quantity D price and amount which customer has ordered so in this way guys we have created an automation a simple automation by which you can receive online orders using Google forms and using this automation automatically an invoice will be generated in using a Google Docs template of invoice and it will be stored in our Google Drive so guys after setting up this automation Let's test this automation once in real time so we are going to place another order let's refresh this Google form and this time let's enter the details for some new user for example the use name of the user is test name let's add some different email address let's enter a different mobile number as well a different delivery address and now let's add a date and time for example 30th of September 2022 the time should be 12 P.M name of cake is for example test flavor of cake this time we will be ordering the butterscotch cake and for 4 kgs let's select this and click on submit so as we submit the form guys we will see instantly automatically within seconds a new invoice is generated using this automation which we have created and that new invoice file is stored or created in our Google Drive so in Google Drive we can see just now a new document is created the name of this document is test name and the date and time of form submission let's open this document and we will see this is the same invoice which we have just now generated using the data from the form submission we have the same customer name same address same email address SIM phone numbers Sim product description same product button 4kg 700 was the price and the total amount is 2800 rupees so using this automation guys we can say that the automation which we have created is working perfectly fine and every time when you will receive an online order using Google forms and this automation will automatically create an invoice for that order and it will be stored into a Google Drive so now guys further in this automation if you want to send the invoice to your customer or update your team or whatever you want to do just click on click on keep on adding action step over here like if you want to send the invoice via WhatsApp just add the WhatsApp step next to it if you want to send the invoice via email send the email status next to it or whatever you want to do you can do this using this automation so if you want you can just keep on adding steps ing to your choice in this automation as well so not just these application you can connect plenty of other applications using public connect and guys one more important thing you will find the Clone link of the same workflow in the description box below by clicking on that link you can clone this same workflow into your own public Clinic account and also public connect offers you a forever pre-plan and in this plan you will get free task every month into your own public account so if you want to try and test this automation you can do it for absolutely free if you need any kind of help or have any queries you can ask them on forum.w.com and you can check public index pricing at this link so if this video was helpful to you you can like this video don't forget to share it with others and to keep getting such content on Automation and integration you can subscribe to public thank you have a nice day