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

Invoice google sheets template for Operations

hey everyone in this video you will learn how you can automate your whole business process right from taking the orders to receiving the payments from your customers using Google forms Google Docs Google Sheets and Gmail application so this will be the flow of this automation guys here we are using Google forms to take orders from your customers so we are connecting Google forms as our trigger application and the trigger event is the form submission so as in when you are going to receive the order from Google form the action towards that trigger event would be to create an invoice in Google Docs application and then uh the same invoice will be shared from your Google Drive in PDF format and a confirmation email will be delivered along with the payment link to your customer automatically so all this is going to happen automatically with the help of our automation so you can set set up this automation very easily without any coding or programming and can save immense amount of your time and manual efforts let me show you how so guys first of all you need to reach this link p.com connect we have pasted the same Link in the description as well from here you can set up your free P connect account by clicking on the sign up free button and you will get free automation task to test your automation okay so sign up then login to reach here on this dashboard of P connect here you have to create a workflow of automation for that just click on this create workflow button and here you can give any switchable uh name to your workflow so I'm giving the name as uh automate your business or business automation using Google application right so after giving the name like this you just have to click on this create button and you can see your workflow page started loading up here and this is the workflow page here you will find the building blocks of this automation which are trigger and the action so here uh trigger is that event which is going to trigger this workflow so it is saying when this happens and action is the response towards the S TRS the trigger it will make p connect perform any action okay so here in the trigger we are going to connect the form first by which we are taking the order that is the first step for any business that first of all we are going to take orders from the customer so as the order has been received I want that this workflow should trigger and run in the background and do our work so here select the trigger application as Google forms Google forms where it is here is Google forms selected and in the trigger event select this new response received option okay so with the help of these instructions and this web hook URL we are going to make a connection with our form after that we are going to capture the response of that connection so let me show you my form this is the form I have created for medicine's business I just have taken the example of medicines you can create this form for any kind of business using Google forms application so here uh we have attached a spreadsheet with this form to collect the responses or the orders from the customers so this is a spreadsheet let me show you this is a spreadsheet I have connected with this form named as medicine order form final okay so what I'm going to do here I'm going to make a connection so by reading the instruction you can do that uh let me show you directly how you can do that so guys I have opened my same form in incognito mode also because when you connect Google forms or Google spreadsheets uh uh with P connect and if you're using multiple Google accounts there can be some issues in connection so it is better to open your form or your sheet in incognit window right and here uh this is my uh Collection spreadsheet in which I am collecting the responses from the form so guys here what you have to do in this spreadsheet you have to click on extensions and you will find this add-ons option from here click on get add-ons and you have to an add-on here from Google workspace Marketplace search for p connect VB hooks add-on fa connect VB hooks okay and you have to this add-on here so it will just take 2 minutes to it as you can see as I have already installed it so I do not need to it again but once you it you have to refresh your spreadsheet this is an important step after installing P connect vook add-on refresh the spreadsheet and after refreshing when you click on extensions you will find P connect vbook option available here in the dropdown only so from here you have to click on initial setup and under initial setup you will find two Fields here first is the wook URL field and the trigger column field so in the web hook URL field you have to paste this web hook URL that you got here in the workflow you have to copy this and paste it here in the initial set of VB URL field and in the trigger column field you have to mention the name of the final data entry column that you have in your in your spreadsheet so final data entry column means the column in which when you enter the data that is going to trigger the workflow that is is called final data entry column so our Edge column is the final data entry column so as the data uh uh submitted here in this Edge column the workflow will be triggered so edge of final data entry column so I'm so first let me copy this web URL from my workflow guys just click on copy from here go back to the spreadsheet extensions P connect vook initial setup all right and here uh a web Hook is already pasted let me remove this and let me paste this new web hook URL and the trigger column is is is our Edge column only okay and here you can see as I have copied this webook URL it started showing waiting for the WBO response this means our workflow started listening uh to the data and now what we are going to do we are going to capture the data of the form response that we have uh in our spreadsheet here in this workflow so that will act as a test data for us to set up this automation further okay so here you can see we have this send test button in this initial setup so guys when you click on this send test button the data of the first row or the first response that you have collected in the spreadsheet will be delivered to your workflow as the test data okay let me show you I'm clicking on send test here and you can see it is written here test data data sent successfully okay and this is the data uh that is sent let me show you in my workflow here you can see in this response section we got the data the full name is showing demo person the phone number the email address and all the things uh that we have in our spreadsheet you can see here got collected here in the workflow right so ing to the form we have received the medicine's name we have received the address of the person we have received the email address of the customer we have received the uh quantity ordered okay so that these are the things that we have asked from our customer via this form full name phone number email address medicine name mode of payment and the quantity and these are all the things that we have received here you can see right and that's how we can capture the test data now go back to your spreadsheet and from this initial setup click on submit so we are done making the connection now we have to do one thing more here click on extensions and from this P connect vook option check this send on event button also send on event option so when you check send on event option what will happen Whenever there is a new response in this spreadsheet the workflow will be triggered and your workflow will run okay now guys uh what I want after taking the order I want that uh uh invoice will be created automatically using my Google Docs application so in Google Docs I have created a template of invoice here you can see in this template I have created this kind of invoice you can uh create your invoices or bills ingly I'm just showing you the demo and in this invoice you can see we have this order number here in double curly braces name of the customer medicine's name quantity price total value email addresses date so I have put these things in double curly braces here so this means these are the variable tags I have added here so this means these are the things which are going to change with every new invoice with every new invoice the customer's name the order number the medicines the quantity the price the total everything is going to change okay so that's how you have to create a template with these variable TXS okay now uh here you can see we have this price and total uh Fields here here I want to mention the unit price of the medicine and the total price ing to the quantity so to place these values here we have to calculate these values so guys to calculate these total prices we are going to use this data that we have received in our workflow flow but guys here you can see we have just received the quantity and the product's name we do not have the price mentioned here anywhere but we do have the price list here in my spreadsheet you can see in the spreadsheet here I am collecting responses and here I have another sheet named as price list so here I have the list of the prizes of these medicines okay so what I'm going to do here I'm going to use a feature of public which is called lookup table and with the help of lookup table we are going to create a table by which we you can uh you can create labels like the quantities and their prices and you can pass those labels uh later on uh in next steps okay let me show you what exactly I mean to say this here search for lookup table module lookup table okay and the action event select lookup table only then click on connect and here it is asking for the lookup key the fallback value and here you can create labels and pass on values okay so guys what I want here first of all let me show you how to create labels I want to pass the values of the medicines and their prices just like I have entered the data here I want to create same entries here so here you can just put the name of the medicine like this I have core flam okay and here in the value you can put the price like this okay then you can add another column or row here by clicking on this plus uh icon and here it will create another label here I'm going to mention another another medicine's name just copy this value from here put the value here and then put the price what is the price 110 okay similarly I'm going to create two more labels because we have given four option here in our form we have four options here so next medicine is this Nova Max so guys here you can see I have created this uh a kind of table here in which in the First Column I have mentioned the medicine's names label and here are the values or the price of these medicines okay and here now what I want what I'm going to do here it is asking for the lookup key so it is asking which value you want to look up for from this table that you have created so I want to look up for this medicine whose uh name we have received here in the first step so here I'm going to look up for this medicine's name aspirin 5 mg okay and here I'm going to look up for this value from this table okay so we don't not we do not have the name of this uh medicine here so let me me create another label I'm clicking on plus icon so guys I have created another label here and put the price now let's look up for this value here in the lookup key field just click here and it will show you the previous step in the drop down that is the trigger step and when you click on it it will show you all the data that you have received in the first step so from here I'm mapping the medicine's name and let's search for this value here in our lookup table so I'm clicking on Save and send test request and here you can see we got this result label in which it is showing you the medicines name and the result value in which we got the price now we got the respective medicine's name and the price ing to our form sumission now what we can do we can calculate the total amount by multiplying this price with the quantity we have received here in the first step okay so to multiply that what we are going to do just click here on this plus icon and from another action step I'm going to use number format feature of P connect search for number format and in the action event we have this perform math operation so you can perform mathematical operation by this select it okay then click on connect and here it is asking for the numbers on which you want to perform the mathematical operation so guys I'm going to map this result value in which we got the price of this medicine and the quantity label from the first step so to map the data just click here and from the first step map the quantity label then put a comma and from this look up table step map the result value label okay and then select the operation as multiply okay and then click on Save and send test request and here you can see in the result label we got the total amount okay multiply now if we can use this data to put the value here in your invoice in the total field now guys it is time to create the invoice to generate the invoice from our template and for that just click here on this plus icon and from the section step search for Google Docs application there is Google Docs and in the action event you have to select create document from template option then click on connect and from here select add new connection if you're making the connection with Google docs for the very first time select this option and this connection that you're making here by selecting new connection option is going to be saved in your account so if you want to make the connection again in future you can use the saved connections also by selecting the existing connection option all right right now let me show you this process just click on connect with Google Docs so from this window you have to to select that Google account by which you have created that template okay then you have to provide some additional access to P connect so just click on continue from here and you can see we are getting connected all right so we are now connected and here it is asking for the template documents name so guys this is my template document the name is invoice medicine template so I'm going to select this templates name from this list okay here you can see all the documents that you have in Google Docs from here I'm selecting this one the medicine invoice template okay and here it is asking for new documents name and the new documents location so guys from this location field you have to select that Google Drives folder in which you want to save this uh created invoice so guys this is my Google Drive and here I have this med invoices named folder so here I want to save the newly created invoices okay and so I'm going to select this med invoices folder from this folders list okay and to give a name to this newly created invoice I'm going to use the data of the customer so from this first step I'm going to map the person's name okay and here I'm going to put the name as the this person's name and and underscore and invoice so on this name this the invoice will be created now it is asking for the order number name medicine quantity price total email address and date what are these things these are nothing but the variable tags that you have placed in your invoice okay you can see order number name mat Quant price total these are all these are the fields that are here so to get these things change automatically uh with every new invoice we need to map the values of these things here in your work flow so to map the data I'm going to use the first step so for order number I'm going to use the Google form step and I'm going to map uh the sheet ID as the order number and along with sheet ID I'm going to play place an underscore and I'm going to map the row index as well okay so to create a unique order number now for the name use the first first step only and map the name and for the medicines use the first step this is the medicine's name for the quantity use the first step map the quantity and for the price now we are going to use the lookup table step or the number format no not the lookup table step from the lookup table we have F the price so here is the result value this is the price of this medicine Mage and here in the total field I'm going to use this number format step where we have multiplied and got the total value so map this result label here for the email use the same way the first first step this is not an email this is the email for the address use the first step and for the date use the first step only right and this is how you can map the values and guys we are mapping these values from previous steps because after setting up this automation whenever you are going to receive any order these mapped labels are going to be updated automatically and a new invoice will will be created with new details ing to the mapped labels okay so to check your connection with uh Google Docs you can click on Save and send test request and a new invoice will be created and will be saved here in this mad invoices folder let me show you I'm clicking on Save and send test request so this is the response we have received here let's check in our folder in Google Drive and you can see a new invoice is created here with this name demo person invoice and let me open this for you and here you can see guys it is showing thank you for your order the order number is this in place of this uh variable tag we got the order number placed automatically hi demo person okay in place of the name tag we got the name and you can see in the description we got the medicine's name the quantity the unit price and the total amount and all the things are placed here automatically because we have mapped the values of these things here in our workflow all right now guys uh we got this invoice saved in our Google Drive and I want to share the PDF uh format of this invoice with my customer for that to happen what I'm going to do I'm just going to click on this plus icon and here I'm going to connect Google Drive now and with the help of an action event of Google Drive we are going to create a PDF link of this document that we have created so search for Google Drive here okay and in the action event select this share a file with anyone option share our file with anyone okay then click on connect and from here select add new connection option then click on connect with Google Drive and from this window select the Google account by which you have the access of the drive okay and then give the additional access to P connect just click on continue and you can see we are connected with the drive now and here it is asking for the files ID so guys here you can see in the previous step where you when you have created the document with the help of template it has given us a document ID here this is the document ID so I'm going to map this document ID label here in this step so so just toggle this map button to on first okay then erase this field and from this fourth or the last step map the document ID label like this okay then click on Save and send test request and you will get the sharable links of the file here you can see we got these links and here we have this PDF link also let me show you this is the PDF link of our file or the invoice right now we can send this link to our customers via email but before this what I want I want to create a payment link for for my customer to collect the payment for this uh medicines that you the customer have ordered for that to happen just click here on this plus icon and here I'm going to connect a payment Gateway application which is Razor pay okay so in place of Razer pay you can uh select inst Mojo or PayPal or stripe I'm selecting razor pay to show you the demo demo and in the action event we have this create payment link option select create payment link then click on connect and from this window select add new connection and here it is asking for the key ID and the key secret of Razer pay so to get these two things you need to reach the settings of your razor pay account then you can generate the key so let me show you my Razer pay account guys this is my Razor pay applications dashboard here you can see we have this settings option on the bottom bottom left hand side under the settings page we have this API Keys tab click on API keys and here you can regenerate a test key for yourself so click on regenerate test key okay I'm deactivating the old one and here we got the key ID and the key secret so you just have to copy this key ID and paste it here and similarly copy the key secret paste it here right and click on okay and from here click on Save and you'll be connected with razor pay so we are connected now and here it is asking for the amount uh so here it is asking for the amount of which you want to create the payment link of and the currency and the type and all these things so I'm going to use the previous steps so for the amount use this number format step where we where you have calculated the total amount this is the result label MapIt for the currency uh so I am residing in India so my currency is INR Indian national rupees so I'm just copying this INR and pasting it here you have to follow find your CES from this list of supported currencies page here you can see the codes and you can uh just enter the CES here in the currency field the type of my payment link is link link okay here you can provide a description also uh of which thing you want to collect the payment of so I'm giving the description as payment link for the order of medicines right here you can map the customer's name the customer's email from the first step the contact number if you have all right so if you want to uh if you want to expand your link you can select expanded option or if you want to make it in show more option you can select show more here it is asking a receipts number so if you want to put a receipt number you can put it here these are not compulsory things so I'm leaving these things blank so here it is asking for the expiry timestamp of this payment link so every payment link have some expiration date so let's say you want that your payment link that you're sending to your customer should be expir in 24 hours so you want to give the time of 24 hours to your customer to collect the payment so you have to put the time stamp here so uh guys you can see we have received uh this data from our form from the trigger and in the data from the trigger you can see we have this time stamp in which we have the time and the date in the value field okay but guys uh we want the time stamp in Unix format here okay in Unix format so to convert this time and date in Unix format what we are going to do what we just are going to add some action step uh add some action steps here in between these two steps or uh before razor pay step just click here on this plus icon and from here I'm going to use a feature of P which is called datetime format so guys we are going to put the Tim stamp which is 24 hours ahead in the future so that our customer can get the time span of 24 hours to complete the payment so first I'm going to add one more day or 24 hours to the date and time uh at which we have received the form response Okay so I'm going to add one more day to this date then I'll convert it into Tim stamp okay so first of all just come here so from this date time format select the action event as modify current date option so it is going to modify the date on which you have received the form submission or the order so click on connect here and here it is asking for the dates format so you can select uh any date format let it be as it is and here it is asking for a time zone so my time zone is Asia Kolkata all right and here it is asking for the operations so I want to add one more day to the current date so select the unit as days and put the value as one and then click on Save and send test request all right and here you can see we got this date time label here like this right so uh this is the date ahead in future one day after after so now I want to convert this date into uh time stamp so just click here on this plus icon and from this action step search for datetime formatter [Music] again all right and now select format date with time zone as the action event then click on connect so here it is asking for the date so I'm going to use the previous step and I'm going to map this date time label but you have to map the date time in this format okay but we have the date in this format so let me change the format here one more time you have to select the date format ingly select this one this option Y ymdd A Team between then hours minutes and seconds okay let me click on Save and send one more time now we got the exact date format that we can convert it into time stamp so map this date time label now here in this date field so just C click here on this field and from the last step the sixth one map the date time label like this okay here it is asking for the from format so we got the date in this format only so you have to select the date uh format in uh this format uh you have to select this format in in which you have mapped the date here so this is the same format no need to do anything here and in which format you want to convert you have to select here in this two format option so I want to convert this date into time stamp here we have time stamp option select it and select the time zone as Asia Kolkata so my time zone is Asia Kolkata you have to select your time zone okay so I'm selecting Asia Kolkata one more time okay then click on Save and send test request and here we got the result label in which we got the time stamp now we can map this time stamp here in this field in razor pay step where it is here in the expiry time stamp okay it is not showing the previous steps because we have added two more steps pre uh in between the steps so now you have to refresh the connection with Razer pay so just click on connected button and from here keep this select existing connection option selected and now click on save one more time and your connection will be refreshed okay and now when you click here on this field it will show you the previous steps and from this last or seventh step map the result label all right and if you want not uh if you want razor pay to send a notification SMS to the customer you can select this option or if you want razor P to send the email you can select this option but I I want to send the email so I'm selecting you send the email option okay now we are done uh setting up this razor P step and now you can check your uh you can just click on Save and send test request to get the link of the payment just click on Save and send test request here and here you can see this is the response we have received and in this response only you will find the link of the payment so here is the customer ID the description and here is the short URL or the payment link so let me show you the payment link here so you can see a payment link is created for for the order of the medicines no now from here person can create person can pay you the amount so here you can see it is showing the payment amount as 1 rupe but actually we have put the amount here as 100 rupees we have put the amount here as 100 rupees but it is showing us the amount as uh where it is one rupees because it has ADD added this decimal point before these two zeros so it will happen in every payment link guys so you have to add two more extra zeros to get the corrected amount here in your payment link so what you can do you can just put two extra zeros you can just read here to pass th000 to pass value of 10 Rupees you have to put th here with two more extra zeros so I have just added two more extra zeros manually here now let me click on Save and send test request one one more time to get the correct payment link right so we got another short URL here let me show you now and here you can see we got the corrected amount as 100 rupees okay so we are done creating the payment link now the last thing left was to send a confirmation email with this payment link to the customer on email so just click here and here I'm going to connect Gmail application to send that add email to my customer now in the action event select uh send email option then click on connect from this window select add new connection then click on connect with Gmail and from this window you have to select that Gmail account by which you want to send those emails okay then click on continue from here all right and you can see we are getting connected with Gmail here it is asking for the recipient's email address so here here I'm going to map the email address of my customer from the first step okay I do not want to put anyone in Cc or BCC so I'm leaving these things blank here it is asking for the sender name so you can put the name of your uh you know your farm or your business's name so I'm putting here the sender's name as medicine store only okay or you can put the name of any person as well here it is asking for the email subject so the subject of this email is for uh sending the confirmation so here I'm uh writing order confirmed for medicines okay here you can select the email content type I'm selecting plane and here you can type the email for your customer so I'm typing the email here so guys I have written this standard email for all my customers so here you can see I have written deer and after deer I'm going to mention the name of my customer and here I'm going to mention the order details and here I'm going to uh place the link of the payment and also in this attachment field I want to I'm going to attach the invoice that I have created so I'm going to use the previous data the data from the previous steps so after de I'm going to map the name of the customer from the first step so when you map the details here like this what will happen with every email the labels are going to change and the new email will be delivered with the new data so for the medicine's name I'm going to use uh the first step only for the quantity also for the price I'm going to use the lookup table step for the total amount I'm going use the number formatter step and for the payment link I'm going to use the razor page step where we have created the payment link so this is the short URL okay here in the file name of the attachment I'm going to use the Google Drive step where we have uh created the sharable links this is the files name and from the same Google Drive step I'm going to map the PDF link of the file right so we are done mapping the whole data now I can show you how the email is going to look like uh by clicking on Save and send test request you can send a test email to this particular email address so I have the access of this email address let me show you how the email is going to look like so this is my demo customers inbox or test customers inbox and here I'm going to click on Save and send test request and you can see this is the response we have received here now let us check in the inbox if we got a new email and we have received a a new email from the medicine store you can see and the uh subject is order confirmed for medicines and here you can see deare demo person thank you for your order the order details are this medicine's name aspirin quantity 10 total amount is this this is the payment link and this is the same payment link that I have created and here is the invoice and you can see this is the same invoice in the PDF format that we have created and have here in the Google Drive so this is how it is going to work guys so we are done setting up this automation now you don't have to do anything manually here so we are done here and you have to set up this automation only once after that you don't even have to touch this workflow after that as and when you are going to receive any order from your Google forms the invoice will be created automatically and the same invoice along with the payment link will be delivered to your customer like this automatically this workflow is going to run in the background you don't have to do anything okay so uh you can use the same workflow that I have created because I'm going to paste the link of this same workflow in the description so that you can clone it in your own free family connect account and start automating your business today guys thank you so much for watching our video and please please let us know how our automation ideas are helping you in the comment section and if you have any queries related to this application please ask your queries from this forum link and do check the pricing of this application from this link and guys we are open to ideas if you want us that we should make videos on other aspects of your business please do let us know in the comment section and if you like our work please subscribe to our Channel