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.







40 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