Create Merge Documents with Google Sheets or Google Forms

document studio is a Google add-on that helps you create beautiful and pixel-perfect documents automatically the add-on is versatile and part packed with features you can use the document studio to generate any type of documents these documents are automatically stored in your Google Drive and Italy organised folders documents to do includes mail merge so you can automatically email the documents as attachments to people using personalized email messages that means to do gives you the power to set sharing permissions for the generated documents so you can decide who gets to view edit or comment on your files the add-on is integrated with Google Cloud Print so you can quickly send the documents as soon as they are generated to the printer the idea behind document studio is simple you add the data inside a Google spreadsheet and the add-on will create one document for every row in the Google spreadsheet in addition to Google sheets you can also import data from your existing Microsoft Excel or CSV files and if you're using Google Forms you can use document studio to automatically generate documents whenever somebody submits your form the answer is entered by the form respondent will be included in the finished document document studio can generate documents in a variety of formats it obviously supports the most popular PDF format but you can also generate documents in Microsoft Office and open open office formats and if you plan to later edit emerge documents you can choose to generate the documents and native Google formats like Google spreadsheet Google Docs or Google slides documents to do is for everyone whether you're a small business a school teacher an event manager or you work in the legal industry or manage the human resources department of your organization you will find documents to do useful so now that you know what documents to do is and what you can do with it let's do a live demo and see what how to use this add-on so to get started install the document studio add-on from the google add-on store or you can go to documents to do dot pro and all the and on now the first time you install the add-on you will need to grant access to some of your data in Google Drive now this will allow the add-on to access your document templates as well as send emails on your behalf please remember that everything runs inside your Google account and not a bite of your data is shared or transmitted or uploaded anywhere you can find a link to a privacy policy in the show notes as well ok so let's get started now I'll go to my Google Drive and create a few folders the templates folder will hold the templates while the documents folder will store the finished Moi's documents now in the first example this is a basic example I will show you how to create a business letter for your customers so let's say you have tons of customers and you send them invoices and the invoices at you so you want to send reminders to your customers asking them to pay the invoice so this is what we will use documents to defer first we'll go to Google Drive and create a new template now I can either create a blank document or choose one of the existing templates that you have in Google Drive I'll choose the business letter this looks good [Music] [Music] now this was just like a standard Google document except that it contains variables and these will be replaced to the merge data in the finished document so for instance here the customer name is variable the most of the content ability would be similar but that customer name would change in every letter so what I'll do is I'll change the customer name to a variable and I do this by enclosing the name inside double curly braces so whatever text you put inside the bell curly braces becomes a variable we'll insert a couple of more variables like you have the invoice amount and how many days since this invoice has been due so we'll add two more variables to our template [Music] you can modify the colors the fonts and other visual formatting of the template variables just like a standard Google document so now that our document template is ready I'll switch to my Google sheet and add the data to the sheet that I want to merge into documents the important thing you should remember is that your column headers in the spreadsheet should exactly match the variable names that you have used inside the document templates okay so the columns are ready I'll quickly add some data I have already prepared the data in the background so I'll just copy paste to this Google spreadsheet and I will apply some formatting so that looks good [Music] now if you look at the document template we have a variable called days past you and it shows the number of bases that invoice has been deal however if you switch to the Google spreadsheet you will notice that we do not have this column in the spreadsheet so what we'll do is we'll create a new column for that variable now I'll use the simple data formula inside Google spreadsheet to calculate the number of days since that invoice has been deal now we want to use this formula across all the rows in the spreadsheet so we will simply copy place the formula now this is obviously not the best approach we should consider using something like Anna formulas but for this example let's keep things simple and I will simply copy place the formula also this spreadsheet has like ten rows but it is possible that your spreadsheet may have hundreds and hundreds of rows so you don't want to send invoice your minders to every row that's in the spreadsheet but you only want to send reminders to people for whom the invoices have been due for more than hundred days so in that case we can make use of spreadsheet filters to only have rows where the due date due date is more than hundred days so our spreadsheet is now ready and we are ready to merge so go to the add-ons menu and you'll find documents to do in the drop-down so just choose open under the document studio menu if you do not see document studio in the list is go to documents to do dot row and install the add-on now I'll go to the document merge section in the sidebar and I'll choose the template that we are just created now when you when you click the Select template button the nice file picker opens up and you have an option to select templates of type spreadsheet documents or slides in this case we have created a template inside Google Documents so I just choose the temperature Google Documents tab now as soon as you pick a template the add-on will scan the template and figure out all the variables that you have used in the template now this is very useful because you can now match whether the temp the variable names in the template are matching with the column titles in the spreadsheet so let's go to the next step here we need to specify the file name of the document that will be generated you can use simple names like file dot PDF or something like that but you can also use variables so for instance I want the file names to have my customer ID and customer name so that it's easier for me to figure out which file belongs to which customer so what I'll do is I'll just put in voice ID and enclose that inside double curly braces so that means it's a variable and it will be replaced with the actual value when the merge is done you have a variety of export formats choose from but value is the most popular one which is PDF next we go to the mail merge section now in addition to generating documents document studio can also email the documents to people you specify inside made merge and these will be personalized emails so the body of the email message can be personalized just like you are personalizing a document for this example I'll enable mail merge and then you have the option to create your email template so when you click the email template button a nice visible get it there opens up where you can compose your email messages and rich text as obvious you can also use variable names inside the email body here and here instead of typing the variable names you have a nice little drop-down where you can select the variable name and will be automatically inserted into the body of the email message you can use the insert image button inside this email editor to quickly insert images inside your email body now comes the most interesting part now when you are picking up email recipients you can either write down your email recipients as ABC at example.com put commas and add any number of email recipients or if you have the email address as a variable field you can just select it from the drop-down next we will specify the subject and here again you can use variable names okay so everything looks good I'll just save the email template I'll skip the Google Form section because this is only relevant when you are merging data from Google Forms next we go to the Google Drive section now here we will pick the Google Drive folder where the files would be saved [Music] you can also create folders inside the parent folder and these can be dynamically generate generated based on the data in your Google spreadsheet so if suppose if you want to have separate folders for every customer you can just put the customer name in the path and put enclose that inside double curly braces next we go to the file sharing section and here you can put email addresses or variable fields and the files will be simply shared with the people you have specified in this list and this also supports the variable fields as well as some special values like any one which means the file will be public or you can use anyone within domain and people inside your domain will have access to the file nobody outside you can find a link of all the supported values inside the show notes next comes Google Cloud Print and here if you want to print the merge documents as soon as they are created you use this section we'll skip it and finally we come to the save section and this is where we instruct the program to merge our documents so here we have a few options you can either merge the documents now or you can set up a cron job or a trigger and it will auto run in the background and merge documents so this is useful if you have some other add-ons some other process that is adding data to your Google sheet and you want to merge the data without having to manually run it so in this example like this will merge now and then choose the run button to start merging the documents so you get a nice little progress window in the lower right corner so you know what's happening behind the scenes okay so the merge program has completed and if you look at the spreadsheet it has added a few columns so you exactly know what to did where the documents are and if I system my email and go to the sent items folder you see that the emails have gone out and the PDF files are sent as attachments so this example would have given you some idea of documents to do in the next example I'll show you how to create a simple badge something like this that contains the QR code the employee name as well as the picture of the employee all the documents to do thanks [Music]

Channel: Amit Agarwal

Views: 234,984

Keywords: labnol, screencast

Id: TSDXfdo3JVU

Length: 13min 24sec (804 seconds)

Published: Tue Oct 31 2017