Wednesday, October 24, 2012

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.







78 comments:

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

    ReplyDelete
  2. 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!

    ReplyDelete
    Replies
    1. Oh nice! I've never used the ArrayFormula before and I have a situation that it will be very useful for. Thanks!

      Delete
  3. 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.

    ReplyDelete
  4. 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.

    Kevin

    ReplyDelete
    Replies
    1. Hmmm- I've never used checkboxes for this before- I've always separated the data with multiple choice / drop-down questions instead. I'd suggest leaving a comment on the original site and seeing if anyone else has any ideas. Link: http://youpd.org/autocrat

      Delete
  5. 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.

    Problem:

    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.

    Thanks
    M :-)

    ReplyDelete
    Replies
    1. 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 youpd.org - that's where the original script information is housed. Good luck!

      Delete
    2. 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.

      Delete
  6. 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?

    ReplyDelete
  7. 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?

    ReplyDelete
    Replies
    1. Are you signed into two accounts at once? That's the only thing I can think of. You could also heck out youpd.org - the creator of the script, Andrew, hangs out there.

      Delete
    2. 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.

      HTH

      Delete
  8. 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

    ReplyDelete
    Replies
    1. 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]edtechcoaching.org. Hope all is well :)

      Delete
  9. 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; "admin@domain.com" using a semicolon, a comma

    ReplyDelete
    Replies
    1. 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?

      Delete
  10. 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.

    ReplyDelete
  11. 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.

    ReplyDelete
    Replies
    1. 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 :-)

      Delete
  12. 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)

    cheers

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. Hightlight the column you want to format to 1/11/2013
      go to the file menu
      format
      number
      scroll down to date 09/26/2008
      Click

      now it should be formated for incoming and all show the 01/11/2013 as opposed to Fri Nov 01 2013 00:00:00 GMT+1000 (EST)

      Delete
    3. Thanks for that.... I have tried that a couple of times and still ends up Fri Nov 01 2013 00:00:00 GMT+1000 (EST)...

      So I changed the column to text and it fixed the problem... thank you anyway.

      Delete
  13. 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...

    ReplyDelete
  14. 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?

    ReplyDelete
  15. 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: https://docs.google.com/forms/d/1O5I71DVtE2GkIpO5V0NLij76Ad_nuESaLCKUtLloYwU/edit#

    Spreadsheet: https://docs.google.com/spreadsheet/ccc?key=0Alb7Y3Q28s2ndF9sVEo1M3FjS1dLQTM4OG1fQlJRbVE&usp=sharing

    Template: https://docs.google.com/document/d/1FgIIsIdmA-HaY57okKk0RRe2B1Wlc-eHp-OK_Hc8F5c/edit?usp=sharing

    Thanks for any and all assistance.

    ReplyDelete
    Replies
    1. Go back to "Step 3" when you set up the AutoCrat script....

      Type "NOT NULL" for email address (or whatever- timestamp, first row, etc.)- that way, you won't have blank documents merged for any rows that don't include data.

      Hope that works!

      Delete
    2. Krista, That worked great. Thank you very much!!

      Delete
  16. 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.

    ReplyDelete
  17. I don't have a script gallery in a spreadsheet I created....what do I need to do?

    ReplyDelete
    Replies
    1. go to 'tools'
      click on 'script gallery'
      type in 'autocrat' to search
      click on the program, follow the prompts

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

    ReplyDelete
    Replies
    1. As far as I know, that is not a capability, though, the docs now have an add on from Avery to make plain name tags. No pictures/logo's just text.

      Delete
  19. It was meant to say "Next_Record" there but it deleted

    ReplyDelete
  20. 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.

    ReplyDelete
  21. 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

    ReplyDelete
  22. 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.

    ReplyDelete
  23. 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.

    ReplyDelete
  24. 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?

    ReplyDelete
  25. This comment has been removed by the author.

    ReplyDelete
  26. This comment has been removed by the author.

    ReplyDelete
  27. 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?

    ReplyDelete
    Replies
    1. 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.

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

      Delete
    3. Thank You! Worked like a charm!

      Delete
  28. 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.

    ReplyDelete
    Replies
    1. 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?

      Delete
    2. 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.

      Delete
    3. This comment has been removed by the author.

      Delete
    4. 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.

      Delete
  29. 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?

    ReplyDelete
    Replies
    1. This is an interesting question. Perhaps you could try to ask the question on the http://www.youpd.org/autocrat website or contact the developer. If you get a reply, please let us know.

      Delete
  30. This comment has been removed by the author.

    ReplyDelete
  31. 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?

    ReplyDelete
    Replies
    1. 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.

      Delete
  32. 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.

    ReplyDelete
    Replies
    1. 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.

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

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

      Delete
  34. This comment has been removed by the author.

    ReplyDelete
  35. 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.

    ReplyDelete
    Replies
    1. Seems it is a new problem, I can work that out previously!

      Delete
    2. 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.

      Delete
    3. 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.

      http://designacourse.blogspot.com/

      Delete
    4. 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.

      Delete
    5. 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.

      Delete
    6. 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.

      Delete
  36. 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 - mailto:jacksoneducationgroup@gmail.com and I will work with your school to get you set up.

    ReplyDelete
  37. 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!

    ReplyDelete
    Replies
    1. Are you using old sheets or new sheets? Advanced capabilities are not functional in new sheets yet.

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

    ReplyDelete
  39. 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?

    ReplyDelete
  40. 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?

    ReplyDelete
  41. 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 :-)

    ReplyDelete
  42. I only have the one form sheet that I am aware of...thanks for the feedback.

    ReplyDelete
    Replies
    1. 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.

      Delete