AutoCrat: The Greatest Google Mail Merge Script of All Time

I’m not exaggerating.

Check this out:

  • Users fill out a form online
  • The data from each user merges into an individual Google Doc (from a template)
  • Those Google Docs (you can also choose to make PDFs) are organized in a folder in Google Drive
  • You can automatically send an email with those attached PDFs or Docs
  • This can happen when you run the script or as soon as the user submits the form.

I can think of a million uses for this, particularly in relation to the issues arising with e-signature regulations. One of the biggest headaches for a Technology Department is managing all the paperwork in relation to AUPs (Acceptable Use Policies), new user accounts, and permission slips for COPPA (Child Online Privacy Protection Act) compliance. Some signatures still have to be physical, which means that the data on those forms have to be manually transferred into a spreadsheet or program, after the document is signed.

Picture these two scenarios:

  • The old way: Thousands of paper forms are stacked at registration tables. Parents go through the line, filling out their information and signing each sheet. From there, the papers are shuffled around and someone takes a few weeks to painstakingly review the handwritten data on each form and manually input the information into a computer program or spreadsheet.
  • The new way: A row of 3-5 computers are lined up- parents fill out the form, and a PDF is automatically generated. A worker has access to all the PDFs in Google Drive and prints each one as it is created. The document is signed on the spot, placed in a basket, and the worker goes back to the spreadsheet and puts an X in a column (as confirmation that the document has been legally signed). The information can be downloaded as a .csv file and imported directly into the program of choice.

For teachers, this is a great way to send advanced rubrics back to students. For administrators, this is a great way to send back detailed observations to teachers (or even for just simple walk-throughs). For me, this is the perfect solution for a million different issues. Organizing a Tech Academy? Attendees can get a packet of information (with the schedule they chose) on individual documents sent to their emails. Assessing a group of people on technology skills? They can get an advanced summary of their results, as well as descriptions of professional development opportunities.

And one more thing… while you’re waiting for the merge to run, you get to stare at a robot- see above. And yes, the mustache moves as you wait.

Alright, enough praising. Let’s talk about how it works.

The script: AutoCrat
Where to find more information: This website

STEP ONE: Build your form

  • If you’ve forgotten how to do this, click here for instructions.
  • Make sure there is a place to collect email addresses!
STEP TWO: If necessary, create formulas to score the results
  • If you’re just using this script to streamline paperwork, you probably won’t need to do this.
  • If you’re using this script for grading purposes, you will want to do this. Create a new tab at the bottom of the spreadsheet and pull the data over. (The reason you need to create a new tab is because otherwise your formulas delete when a new user fills out the form).
  • In the example I’m using below, I used the ROUND and SUM formulas so that the results would be scaled to one decimal point.
STEP THREE: Create the Google Doc that you will use in the data merge
  • Decide where you want the data to end up, and create a tag enclosed in brackets.
  • Example: <<results of question one>>
  • Make sure you don’t use any numbers (unless they are spelled out) in that tag. If you do, the merge might not work properly.


STEP FOUR: Create a new folder in your Google Drive (for the documents to end up)


STEP FIVE: Find AutoCrat in the script gallery of your spreadsheet
  • Go to your sheet and click “Tools” and “Script Gallery”. Do a search for “AutoCrat” and click “Install”. Confirm use, and click “Done”.
STEP SIX: Follow the directions in the script (step 1-4)
  • I realize those are generic instructions, but the person who created this script did an amazing job of making this incredibly easy to use. If you click “Autocrat” just to the right of the help menu, you can just start going through each step. Start by clicking “Run Initial Configuration”.
    • Step 1: Select the Google Doc that you created earlier
    • Step 2: Select the the sheet you are pulling the data from
    • Step 3: Set merge conditions (optional)
      • Suggestion: type “NOT NULL” for email address- that way, you won’t have blank documents merged for any rows that don’t include data.
    • Step 4: Set field mappings (align the Google Doc tags with the columns on your spreadsheet)
STEP SIX (continued): Follow the directions in the script (step 5-6)
  • Step 5 is where you can set the merge type. Here are the options:
    • Save merged files to Docs (check yes)
      • Select destination folder (the one you created earlier)
      • Select whether you or creating a PDF or Google Doc
      • Choose whether or not you want to save the links to those docs in your spreadsheet (I usually choose yes)
      • Email and/or share merged Docs
        • If you email the Doc, use the email address variable shown above (it will have a $ symbol above it- simply highlight that text and paste it in the box).
        • You can create an email message, but be aware that all the text will be lumped together (see below)
    • Decide whether or not you want to send the document as soon as the user sends the form.


Let me know if you think of any other uses for this script below- I’ll add them to my list! You can read more about the script here.




  • bené says:

    Thanks for the tip on pulling form results into a second sheet in order to use formulas.

  • Wiseguy says:

    Hi Krista,
    thanks so much for this tutorial. this was exactly what we were looking for.
    We’ve used this as a registration form for schools to register students for a programme.
    When we setup the form we didn’t have any data to copy, so after some googling we found the Array formula solved the issue of a new row created after a form submission, and thus skipping any formulas we had, so we could use the Form Response sheet – with live data.

    We entered =ArrayFormula((I2:I*2.50)+150) in A2 and every new entry has the formula applied to it.
    the convention appears to be whatever column you wish to reference you enter it as the first cell in row 2 followed by the column. eg for us I2:I, where I2 was the first data entry, then repeat for the following entries in column I.
    In our example column I -is where schools entered the number of students being enrolled. the course fees were based on $2.50 per student plus a flat fee of $150.
    So for us we wanted to calculate the fee based on the number of students enter multiplied be the $2.50 fee, plus the school fee of $150.
    We wanted to automatically generate an invoice to the school from the data on the form,
    and this works great!

    Thanks again Krista for your tutorial!

  • AMAZING!!! I was going to try to do just this! THANKS! This will help our students alot. This is quite valuable for many businesses too.

  • Kevin Gorell says:

    Have you had any experiences where the form contains multiple check boxes? The problem I am having with these is that the data collected comes in separated by commas. This creates a problem when merging because the merged data becomes extremely difficult to read. Is there any way to fix this so that the merged document displays the information in a vertical list? Any help would be greatly appreciated.


  • I want to use autocrat in 2 sheets, same workbook.

    I have 2 forms, collaboration, Outcome.

    first form is filled out when collaboration is needed. autocrat runs and merges using the form response sheet, template 1

    Have a second sheet, that imports from Outcome spreadsheet. I have a vlookup on this sheet, and if conditions are met, then I run another autocrat, but different template.


    Seems when I set the template in the outcome sheet to the outcome template, then the form responeses sheet changes to outcome template. If I fix that back to collaboration template, then go back to my outcome sheet, that template has been changed to collaboration.

    I want everything within one workbook, any help to this dilema is appreciated.

    M 🙂

    • I didn’t think it was possible to use two scripts in the same workbook. I’ve always just pulled data into a second workbook using the spreadsheet ID key if I need to run a second script. I’d suggesting checking – that’s where the original script information is housed. Good luck!

    • That is what I ended up doing, then, using the spreadsheet keys from both, had info I needed brought over to a third spreadsheet, using arrayforumula’s. It is working really great, though, it does take a few minutes to populate the third spreadsheet.

  • I’m new to autoCrat. I have a form and a spreadsheet. I installed autoCrat into my spreadsheet. I am now trying to pick off my Google doc template that has tags in it. I get an error message: Error encountered: No item with the given ID could be found, or you do not have permission to access it. Any ideas?

  • I’m new to autoCrat. I have a form and it feeds to a spreadsheet. I installed autoCrat in the spreadsheet. I created a Google doc as my report template and it has tags. When autoCrat asks me to pick the template, I do but get the following: “Error encountered: No item with the given ID could be found, or you do not have permission to access it.” Any ideas?

    • Are you signed into two accounts at once? That’s the only thing I can think of. You could also heck out – the creator of the script, Andrew, hangs out there.

    • PINC SF says:

      I had the same error until I changed destination folder to “New Merged Document Folder” in Step 5. Looks like you’d need to grab a valid key if you want to “Select primary destination folder” instead.


  • Hey Krista—

    When using autocrat to merge into a nametag document—how can I get it to fill in a name from the next record instead of just creating an entire name tag sheet for just one student? Just starting to play with this script so if the question is elementary, I apologize. I know it is probably simple but I am not figuring it out

    • Hmm, I’m not sure I’m understanding that, sorry! Do you want to email me with screenshots? I’m not sure I can help, but I can try! krista[at] Hope all is well 🙂

  • News says:

    Does anyone know how to include more than one recipient email address (say for example if you want the content to go to an administrator and the one who filled out the form)? Can you just separate the Recipient email addresses with a semicolon? I would think you can do multiple addresses since Autocrat seems to indicate in the Set Merge Type that you can. Any ideas? I have currently tried: $pleaseEnterYourEmailAddress; “” using a semicolon, a comma

    • You should be able to separate them with just a comma (in the autocrat prompt window- set merge type). No quotations around the second email address though. Is that not working?

  • I also have asked for email address for the 2 filling it out and then also to 3 other people, and that will depend upon region.

    I have $firstPersonName, $secondPersonName, $facilitator, $contact

    It works very well, well, as well as the one typing in the email address.

  • I’m using AutoCrat for classroom observations. How do I get the document to merge only the last entry on form submission? Currently, on submit, previous submissions are being sent an additional copy of the PDF e-mail that is created from the merge. I’d like to be able for each teacher to get an immediate copy of his/her results, but not continue getting duplicate e-mails every time I submit for another observation as well.

    • I am understanding the question to be that you observe teachers several times so you are using the same google form each time?
      If this is correct, then it should show only new information when merged as it will be a new entry.
      Once the merge is made, there should be 4 columns so unless those columns are erased/deleted, autocrat SHOULD bypass those that have already had a report sent via email.

      If this is an incorrect assumption on my part, then please, let’s try again.

      M 🙂

  • PTC says:

    Love it…

    But does anyone know how to change the date formate so on the merge it show 1/11/2013 as the date, not as followed:

    Fri Nov 01 2013 00:00:00 GMT+1000 (EST)


  • PTC says:

    Hey does anyone know how to embedded the run merge button into a google site page? So a merge can be run external of spreadsheet, as I want another person to run merge at the end of the week, but don’t want them to have access to the spreadsheet…

  • kazu says:

    I use autocrat for mail merge and I set it to trigger after form submit but the latest form submission
    was not auto triggered it would be trigger only when next form submitted.
    Any suggestion for fixing this?

  • Background: Instructors would use a form to evaluate students while they perform their skills test. These results in the spreadsheet I had used AutoCrat to merge into a document template. The results form gets sent to the instructor. This is working great for information directly from the form, but we need to have a way to automatically generate a total score for the form.

    Problem: I tried a total column using FormArray, but when I ran AutoCrat, it sent it wouldn’t stop sending a whole bunch of emails out for the rows that should have been empty. How can I get this to send the form information with score total upon form submission? Do I need to create the total in some other way? (Please keep in mind that I don’t really understand FormArrays…I merely tried a bunch that I could find from message boards until I ran across one that seemed to calculate correctly.)

    Example form:



    Thanks for any and all assistance.

  • Amy Brinkley says:

    This is excellent! We are using this to generate certificates of appreciation for participants, volunteers, and presenters in our online conferences. Great tool! Incredible time-saver.

  • Robert Lucia says:

    I don’t have a script gallery in a spreadsheet I created….what do I need to do?

  • How do you use <> like you would on mail merge in word. ie have multiple records from a spreadsheet listed in one document.

  • It was meant to say “Next_Record” there but it deleted

  • I don’t want to waste paper so I would like to have two sets of responses from my form to one piece of paper and cut them out at the end and hand them to participants because the the form is really short answers. How do I accomplish that. It is a waste to print a whole sheet of paper for each respondent.

  • Jon Allan says:

    I have used autocrat for the first time today. I am attempting to collect data for student testimonials from a number of teachers. Is it possible to put all teachers’ responses to a question on the same place on the document that I merged the spreadsheet data to. Thanks Jon

  • You could make a new sheet within the book, and use formula’s to bring in comments from each teacher into a different row and different column, Each column with the teachers name so when you tag your report it will grab the information from the column. do an import formula for the new sheet within the same book. Run your autocrat from that sheet as opposed to the form response sheet.

    As of yet, unless I am not aware, autocrat does not merge next records. This is how I have ‘fumbled’ my way around the next record on same report.

    • oops, different COLUMN same ROW.

    • Elaine Cork says:

      Can you help? I am collecting comments from 4 or 5 teachers per student, from about 50 teachers. I have pulled the comments into one sheet with one column per teacher. Is it possible to generate a new sheet where, the computer scans a row. If an entry is found it is added on the new sheet to the next blank column, if not it continues to scan the row. Once I have this then I can do the autocrat merge
      Thank you for any help you can offer

  • Johnny Kelly says:

    Can you help me with autocrat. I had it working perfectly and all of a sudden I get err codes. It does not seem to work any longer. Is the case on your end? Please help. My project is a disaster. LOL I need your help.

  • Mac says:

    This is all awesome stuff, thank you!

    How do ensure I dont get 10 decimal places showing on my template even when I have formatted to 1 decimal place on my spreadsheet?

  • This comment has been removed by the author.

  • This comment has been removed by the author.

  • Scott Padway says:

    Thank you, this was very helpful. I do have a question, how can I have it create documents with different names. For example, I have had students submit responses on the form of elements of a “deconstructed essay.” When I merge the fields in to a document, I get 30 new documents created and all are titled the same. Is there a way to have it create the documents and merge a unique field (perhaps a name) in the title of the document?

    • Mico says:

      If I understand your question correctly, this should be easy to do:
      In autocrat’s Step %: Set Merge Type, you’ll see a line at the top which says:
      “Use these variables to include values from the spreadsheet in any of the fields below.”
      Just choose one of the variables below, say $name if you have this in your merge tags <>.
      Paste this $name into the field below called “File naming convention to use:”
      Save the settings. This will result in generated merged documents that will each have a unique filename based on the merge tag <>.
      I hope this helps.

    • Mico says:

      It is Step 5, somehow there is a typing error in my comment.

    • Scott Padway says:

      Thank You! Worked like a charm!

  • Mico says:

    I have a problem with duplicate merge documents or a duplicate merge template. Autocrat runs well, but every time I modify my merge template document, something goes wrong. Although the merge runs successfully, at the end of the run I get a message “We’re sorry, a server error occurred. Please wait a bit and try again.”
    All I can see is a duplicate merge folder with duplicate merged documents or a duplicate copy of the merge template document called copy of merge template document.
    I can’t find instructions for modifying the merge document template anywhere. Could someone help? I do hope so.

    • Mico says:

      Actually I found out why this is happening to me: it’s because I have an arrayformula in one column that inserts a kind of autonumber for each row. The document merge will generate a copy of the template every time with that formula. If I take the formula out, everything runs OK. Does Autocrat have a problem with arrayformulas?

    • Mico, I have the same problem! Every time I modify my merge template I get this error and I lose my mapped fields in step 4.

    • This comment has been removed by the author.

    • Mico says:

      Well, I had to learn the hard way – my problem was that I used an arrayformula. Based on my experience I can only assume that arrayformulas don’t work with autocrat – this is only me saying it, this has not been confirmed anywhere else. Are you using arrayformulas? If so, then this would generate the error you are seeing. To get round this problem, you’ll have to delete the arrayformula and use a formula instead. Then you’ll have to got to Avanced option in autocrat click on Copy Down formula – then you just have to configure it and save. Once I have done that, everything was fine – there was no need to modify the merge template – provided, of course, that I already had that particular field there – if you don’t have it there, then you would have to modify it. If you don’t have an arrayformula, then your problem is of a different nature.

  • This is exactly the function I need to be able to perform on my spreadsheet! I am concerned, however, that when I initiated the installation for AutoCrat, it required me to provide access to my data. The information I am sorting and formatting includes people’s addresses and whatnot — information that my organization has promised not to share. Any help for me on this?

  • This comment has been removed by the author.

  • Mrs. Dixon says:

    We are using autocrat for end of the year school certificates. We had to make modifications to the original form after teachers have already submitted them. Can we rerun the merge and it will capture the changes…without having to ask the teachers to resubmit?

    • Mico says:

      If you made the modifications on the form before your teachers had a chance to fill in the form, then it is unlikely that the changes would be reflected in the forms they already submitted.

  • I use the heck out of array formula’s and I had some issues with the im sorry, blah blah blah. When i changed the save doc to google instead of PDF, it worked again. Now, why the heck it won’t save as a PDF to email anymore is possible because of array, but I had them before.

    I do not like the new sheets, nor the new autocrat. It gives me grief, so I make copies of the old sheet with the old autocrat. Most of the time, I am good to go.

    • Mico says:

      I have never used PDF before, so I wouldn’t know about the arrays there. All I know that arrays didn’t work for me in Google Docs – every time I had an array the mailmerge created another copy of the spreadsheet or merge template or copies of merge documents, so I ended up with lots of unwanted copies. However, autocrat has the copy down formula option which you could use instead of arrays – this works.

  • Abby Tate says:

    When running autocrat, is there a way to prevent the timestamp from merging into the certificate I have created?

    • Mico says:

      I would have thought that this is easy to do if you do not include this field in your merge template document.

  • Mico says:

    This comment has been removed by the author.

  • mechcsedgn says:

    I can’t seem to get Autocrat to work.

    Oops! it looks like there isn’t enough data in the Spreadsheet. If you are trying to re-merge existing rows of data, try deleting the values from the four black-header columns…

    It repeatedly gives me that error. I have tried deleting the columns, deleting all the data in the spreadsheet and even completed it as a new form.

    • Boris says:

      Seems it is a new problem, I can work that out previously!

    • Mico says:

      If anyone has been able to solve this problem, can you please let us know. It is difficult for us to use Autocrat without getting help, so we need to share. Thank you.

    • Mech Csedgn says:

      I could not get past that error. I ended up deleting the response worksheet and then starting fresh. It worked once after that but as soon as I had a line of data the error appeared again when I tried to merge.

      I also found that much of the features described in the post are no longer available in AutoCrat. Perhaps the version of AutoCrat has changed or the developers are no longer involved. Either way it could not help me.

      So I turned to Google Apps script and managed to do what I wanted with a simple email rather than an email with an attachment.

      I’ve made a quick tutorial about it here if anyone is interested.

    • Mico says:

      Are you using old or new Google spreadsheets? There are many people using Autocrat, so it would be surprising if the developers were not involved any more.

    • When I created a new form, the spreadsheet was the new ‘sheet’. Had to use the autocrat ‘add on’. Kept getting that blasted message also. Called the IT Google guru, he couldn’t figure it out either. copied one of the form ‘old sheet’. cleared it. went to form. created new form. autocrat works like a charm.

      It’s a bit of a hassel, but it doesn’t consume hours trying to figure it out and still not working. Until it works on the new sheets, I shall continue to use the old one.

    • Mico says:

      Well, it would seem that this was the course of the problem: Autocrat works with old sheets – the Addon in the new sheets lacks certain capabilities, including triggers. Developers say that work is in progress for the new sheets – it depends on Google. Anyway, it would be nice if developers themselves would confirm that your specific error message is due to new sheets – we can only guess. I myself use only old sheets – so far it’s working.
      Can any of you who had this error message confirm that you’ve been using new sheets and not the old ones? Thanks.

  • I have written a script that will AUTOMATICALLY generate a pdf file of your letter/form/certificate…etc. Once the user submits the form an email is generated sending the pdf file to the submitter and a copy to the administrator/teacher/secretary/etc. I plan to use this at my school this year for teacher observations, student discipline referrals, student registration, etc. It is going to be so efficient and save others and me at my school A LOT of TIME! Email if you are interested in getting your school set up – and I will work with your school to get you set up.

  • Thanks so much for this tutorial, Krista. It’s very helpful!
    I can batch process all submitted forms with AutoCrat easily enough, but I’m having trouble figuring out how to have the emails sent as soon as someone submits a form? I don’t see any options like that it in the Advanced Settings when I’m setting up a merge. Can someone be very explicit on how to do that?
    Thanks again!

  • Kevin Mudaly says:

    Autocrat works fine for e-mails within our domain however it fails for external emails. ant fixes or work arounds for this

  • Lois Burke says:

    Is there a way to change who the email comes from? I’ve set up a discipline form for my school that emails our assistant principals. Every time someone submits one it appears (from the email) that its coming from me. Is there a way to fix that?

  • First of all, awesome job with the add on and this help page. It has truly cleared some items up for me. However, My merged document does not get e-mailed automatically when I press submit. I can open sheets and run merge and it will e-mail those results, but I thought it could be set to automatically. What am I missing?

  • you can only email on submit if the autocrat is run using the ‘form’ sheet. If you use another sheet, it has to be manually done.

    M 🙂

  • I only have the one form sheet that I am aware of…thanks for the feedback.

    • Mico says:

      Are you using new sheets? New sheets do not have automatic triggers. You have to use old sheets for automatic submits. See my previous comment.

  • Frank Lipski says:

    I need the following result, and wanted to know if AutoCrat was the answer to this issue before I get too involved with it.

    1. Student completes 10 questions quiz on google form and submits (I have this working)
    2. Quiz is graded automatically
    3. If passing grade, a certificate with their name, or form letter is automatically created & emailed to student.
    4. If failing grade, different form letter is sent to student

    Thanks, Frank

  • Elaine Cork says:

    Can you help? I am collecting comments from 4 or 5 teachers per student, from about 50 teachers. I have pulled the comments into one sheet with one column per teacher. Is it possible to generate a new sheet where, the computer scans a row. If an entry is found it is added on the new sheet to the next blank column, if not it continues to scan the row. Once I have this then I can do the autocrat merge
    Thank you for any help you can offer

  • Peter Haworth says:

    I am just starting to use autoCrat and it is working fine with one exception.

    I have created a Google form to add data to my spreadsheet and set the merge job to run every time a new entry turns up. This is using the new Google Sheets. The merge works but the form contains some fields that are defined as a time. On the form, this results in dropdown menus to select the hour, minute, and AM/PM. These times shows up correctly in the spreadsheet but when they are merged into the document, they all end up as “12/30/1899”.

    I could change the fields to simple text fields but would rather have them as time fields to ensure that their value is correctly formatted. ANy ideas how to fix this problem.

  • Peter Haworth says:

    Another small problem I found. I tried to specify a secondary folder as the destination for where the merged documents would be stored but the docs are being stored in the primary folder.

    I did this by selecting an option from the dropdown menu for the secondary folder which listed all the merge tags in my template document. The resulting folder did not exist at the time of the merge but neither did the primary folder and it was created by the merg job.

  • Peter Haworth says:

    One more issue. I changed the merge job to email the merged file to me and then did a merge. The merged document was created just fine but it’s been around 30 minutes now and no email has shown up. The email address is correctly spelled and I have checked my spam folder. This could be a showstopper for me.

  • Hi. I have been using autocrat for our nursery school applications. For the most part it’s great but I am trying to figure out how to have the PDF file that gets sent come from a different email address other than my own??? HELP! This is a big issue for us as the email containing the PDF file needs to come from someone other than me!

  • Greg Urban says:

    I’m trying to create a pdf of a self-assessment that gets emailed upon completion to the person filling out the assessment, as well as to the trainer and an admin. No problems with all of that, but this self-assessment has 287 fields in it (all very short questions with either a ‘1’, ‘2’, or ‘3’ as an answer). Is there a max number of merge tags in a document?

  • Anita Moose says:

    Is there a way to change who the email comes from in Autocrat? If I set up a form for someone else and when someone submits one it appears (from the email) that its coming from me. Is there a fix? I saw a previous post but no replies so I’m guessing it’s not possible.

  • Nicolas says:

    Hi, i’m trying to use autoCrat but I can’t create new merge tag. I want to use this document as a template but when I try to use it in autoCrat it says “No merge tag” found… any ideas ?

    Link to doc :

    Thank you for your help !!

Show Buttons
Share On Facebook
Share On Twitter
Share On Google Plus
Share On Linkedin
Share On Pinterest
Hide Buttons