Automate Document Workflow with Google Docs, Gmail, Google Forms, and Sheets

document studio is an add-on that helps you merge data from Google sheets and Google Forms if you're new to document studio I suggest watching the getting started guide you can find a link to that in the show notes and if you haven't done so already please go to documents to do dot pro and install the add-on so let's assume you run a cake shop your customers place order with the help of a Google form the orders arrive in a Google sheet or you get an email notification for the order an email also goes out to the customer with the invoice and also a paper link is included where they can pay the invoice so in the next few minutes I'll show you how to complete this entire workflow using document studio let's get started the first thing we will do is create a Google Form [Music] I also change the theme of the forum this one with the cake looks really nice [Music] then I'll add all the required questions we'll be asking the customers name their email address the postal address or the delivery address then we will be asking them what kind of cake they would like to order and whether they would like to have some extra decoration or not a very basic form but you can obviously build more complex forms if you like [Music] so now that our Google Form is ready we will link to our spreadsheet so that all responses are saved in the spreadsheet [Music] the spreadsheet has been created and here is how it looks like so whenever somebody submits a form a new entry will be a new row will be added to this Google spreadsheet automatically to see this in action I'll switch to the Google Forms and submit a test entry and as soon as I hit the submit button you see that a new row has been added to the Google spreadsheet so our orders are getting captured into the Google spreadsheet but it does not have any information about the price of the items or the taxes so what we will do is we will create a new column called cake price and then we'd use the vlookup function to calculate the prices now we'll add another sheet to the spreadsheet that will be like your internal price list we will have the cake names in one column and the cake prices in the other column [Music] next we switch to the response spreadsheet and then use the vlookup to get the prices based on the forum answers so for this function our search key or the cake name is in column E and then we will visually select the spreadsheet range the prices are in the second column so we will put two as the third parameter and our price list is not sorted so we will say false for the fourth parameter now the only problem with this formula is that it is only available in the current row so if somebody submits a new Google Form entry formula would not be available in the new row therefore what we will do is we'll go to the cell and then press command shift enter to convert it into an array formula oops we are getting all these errors now and that's because the cake names are empty for all the other rows so the vlookup function is failing because the search key is blank so what we will do is we'll change the formula slightly so that the vlookup happens only when the cake name is not blank [Music] all good now so if I go to the next row and enter another cake say red velvet you can say that that correctly fetched the prize and there are no errors in the column anymore we will similarly add a new column to calculate the decoration charges now in this column the basic formula is that if the customer has selected yes for extra decoration we add 99 to the bill else we do not do anything so again we use an error formula to apply the formula to the entire column provided the extra decoration charges column is not blank next we will add formulas to calculate the taxes and the total amount of the invoice [Music] I'll try to put more detailed descriptions in the show notes so you have a better understanding of how these formulas work [Music] [Music] the formulas are ready I will show you just one more cool feature of document studio you can actually insert images of Google Maps in your email messages and documents using the Google Maps function so using this function is very simple just go to an empty cell and put equal to Google Maps and then select the cell that has the address now what it will give you is the URL of a static Google Maps image now this is very handy for inserting maps images in your Google document templates or even email messages so we are almost done here we will just add one more column to the sheet and that's called PayPal payment link now if you have a PayPal account you can just put your email address the amount of the invoice and the name of the item against which that invoice is raised and the Adhan will create a nice link that you can put in your email address or you can put in your documents the merge documents that users can click to pay or invoice a response sheet is now ready and now we need to create an invoice template so I'll go to Google Drive and create a new template I can either create a blank template but to save time I'll just pick one of the ready-made invoice templates that are available in Google Drive so what we'll do is we'll just slightly modify this template to suit our requirements we'll add variable fields whenever you are adding variable Felice just remember to surround them with curly braces you can use any variable field that's available in your response spreadsheet or to add a link to the PayPal payment we'll use the built-in hyperlink spreadsheet function now this takes two parameters the first parameter of function is the URL now in this case it will be a variable field because we are storing our paper links under paypal payment link variable and the next is the text or branding purpose we will also add a logo image to the invoice template [Music] [Music] this is just like any of the Google spreadsheet so you can add formulas functions you can even change the formatting change the fonts whatever you like just like you do with other Google sheets so a template is now ready and we are all set to perform much well switch to the response spreadsheet and choose document studio under the add-ons menu here in the document merge section I will choose the template that we have just created [Music] we'll put the customers name in the file name so whenever a new order is generated the customers name is automatically added to the file name we will use the default PDF format for export but there are a variety of options available from Microsoft Affairs to Google native formats under the google forms merge section i will create the email template this is the email template that will go out to customers and to our team whenever a new order is received [Music] like in document templates you can also use variable fields in your email subject or email body [Music] [Music] [Music] now we'll select the folder in Google Drive where all our invoices and orders would be saved [Music] now if you are expecting too many orders your Google Drive folder can quickly get cluttered so what we can do is we can neatly organize these files into subfolders so these folders these subfolders can also be dynamically generated based on your forum answers in the file sharing section we have an option to share the merge documents with best amaya or with the team members I'll skip this next we have the Google Cloud Print connection so as soon as an order arrives it's immediately sent to the printer I'll skip it for this example in the last step you have a couple of options you have merged now or you have merge on form summit so for Google Forms we will turn on the option that says merge and form submit and save the settings now what we are essentially telling the add-on is as soon as a new form is submitted run merge and create all the documents and create the emails so we are all set now our Google Form is live and ready to take orders so let's do a life test now [Music] so the form has been successfully submitted we switched to the response sheet and you see a neuro has been added all the prices and taxes have been calculated and you have a couple of extra column that says whether the email has gone out or not and whether the documents have been generated and what's their location in Google Drive analysis to Gmail to see if the emails have gone out or not so I'll go to the sent items folder and there you see enough email that just went out now this email has the link to the PayPal invoice it has the invoice as well let's see what happens when I click this PayPal link so it takes me directly to the payment page where the clients can pay for your invoice I hope you will find documents to be useful just go to documents to do dot pro and installing your Google Drive Thanks

Channel: Amit Agarwal

Views: 710,606

Keywords: labnol, screencast

Id: PBN9SaG-MJQ

Length: 14min 2sec (842 seconds)

Published: Sun Jul 29 2018