You are missing some Flash content that should appear here! Perhaps your browser cannot display it, or maybe it did not initialize correctly.
Notes
Installation and configuration steps...looks hard, but it's really not bad, I promise. Almost everything you need to do with this script is done through a custom GUI!
- Create a spreadsheet and organize your data in columns that include one row per individual that you want to create a personalized document for. Make sure each column has a header. Individualized data might also be fed by a Google form.
- Go to Tools->Script Gallery and search for "autoCrat" or find it in the "Featured" or "Education" sections. Install the script. The authorization window provided by Google doesn't scroll properly. Just close it and select "Launch merge setup" and authorize the script. After authorization, select "Launch merch setup again." You should now see a "Read Me" tab as the active tab.
- The first run of the script will take you through a setup wizard, but know that in order for the script to work, you will need to create a Document to use as a template for the merge. Create a new collection for your templates. I like to create a collection called "Merge Templates." Create a Google Document that includes double bracketed tags for any personalized data you want to populate from the spreadsheet. Ex) <<First Name>> and put this document in your templates collection. It doesn't matter what you call the fields, because you will map them to your spreadsheet headers in a few steps. You are free to put <<tags>> anywhere you want in the header, body, and footer of the document. Avoid using non-alpha characters in your merge tags.
- In the "Document Merge" menu, select "Settings" and complete the settings. If you don't have a collection that contains a template file with <<Merge tags>> in it, go back to step 3 and come back to this.
- You will now see a panel called "Set merge conditions." This allows you to choose a column that you want to use as a conditional for merging records. This is optional. Leave it blank to ignore this feature.
- Now you will see a new "Document Merge" dropdown item, "Set Field Mappings." Map each <<Merge tag>> to the spreadsheet column you want to use to populate it. Save the mappings.
- Another new "Document Merge" dropdown item will appear: "Test/Run Merge". Select it and decide what type of merge you want to try...there are a number of combos and cool possibilities. Look to the bottom of the panel for a clue as to the $variableNames that are available for any of the fields you want to populate dynamically per row.
- Here are some basic options to play with. Checbox allows you to test on first-row only if you like.
- ONLY saving merged Docs to a collection, either as PDF or Doc format.
- Saving to a collection AND emailing PDF as attachment.
- Saving to a collection AND emailing recipient a link to individually shared Docs as View-only
- Saving to a collection AND emailing recipient a link to individually shared Doc as Editor
- Note that as of Version 1.1.0, Date formatting can now be handled. You must use the Format->Number menu from the spreadsheet to format any dates. Currently only three formats are supported: "M/d/yyyy", "MMMM d, yyyy", and "M/d/yyyy H:mm:ss" ... i.e. 1/30/2012...January 30, 2012, and 1/30/2012 9:32:34.
Push my thinking
What have you done with this script? I'd love to hear about classroom uses in particular! I'm thinking the Document merge, create + share option could be an excellent way to provide individual students or groups of students a personalized starter document for an assignment. These docs would be pre-shared with the students, pre-organized in a collection, etc...Please share back if you try this!!!
Comments
autoCrat Document Merge Script: So You Don't Have to be the Bureaucrat!
Problem of practice
How do automate the creation of personalized documents and emails from the cloud?
Solution
The autoCrat Script was written to be a multi-purpose document merge tool that allows you to take any personalized, row-based spreadsheet data and create, save, attach to email, and share templated documents. No scripting ability required to use.
Cast your vote
Tweet this
Post to TwitterBrowse other items with similar tags
Written by an educator for educators, the free doctopus script gives teachers the ability to auto-generate, pre-share, and manage grading and feedback on templated Docs for group and individual projects.
Recommended by 97 educator(s).
The formMule (aka "The Mule") is a Google Apps script that is useful for automating lookups, emails and calendar events created dynamically from Google Forms.
Recommended by 29 educator(s).
This is a simple script housed in a google sheet that allows teachers to create handouts for their classroom, shares the student appropriately and organizes the handouts in a collection. It can be used to create sheets as well as docs.
Recommended by 12 educator(s).
This hack provides starter templates for a pair of Google Docs worksheets (one private, one public) that can then be connected using (included) Apps scripts to encourage users to submit Google forms only for open time slots or resources. Test.
Recommended by 9 educator(s).
This hack shows how to install a Google Apps Script in your Google Site to look up the grades of the currently logged-in student or parent and generate a live bar of progress from your Google spreadsheet!
Recommended by 13 educator(s).
The docAppender script installs on the form-containing spreadsheet and provides users a web app where they can easily select which document they want to append. This is especially useful for logging meeting takeaways or keeping running records on faculty or student files. Very mobile friendly, as all usage involves URLs that can be loaded in a smartphone or tablet.
Recommended by 7 educator(s).
This introductory module provides what you need to start building the modular and lightweight data system of your dreams. The first installment is a "Lateness Zapper" that automates logging, communications, and reporting on student lateness.
Recommended by 16 educator(s).
powered by educators
empowered by
form submit problem solved
All,
Apologies for the late report here -- an issue cropped up due to Google's new releases last week on Thursday that prevented onFormSubmit installs of autoCrat from running properly. -- Docs were not merging at all on form Submit.
This issue was resolved some time on Monday this week. Please report if you are still experiencing this issue.
Best,
Andrew
How do I use the destination folder options?
I need some help using the destination folder option in step 5. Everything I've done to specify a primary or secondary folder fails. The only setting that works for me is to create a new merged document folder.
Is there documentmentation on that step somewhere?
Destination folders explained
Primary destination folder: Must be a sibling folder (i.e. in the same folder) as the spreadsheet running autoCrat.
Secondary destination folder key: Folder keys are the unique string you see in the URL of your browser when you are looking at a folder in Drive. These can be "hardcoded" by pasting into the form field in Step 5, or they can be referenced by variable, and so be dynamic.
onFormSubmit problems explained
Quite a few users have reported an issue where the onFormSubmit functionality of autoCrat is failing, and either giving an status message related to a failed Google Analytics URL or no status at all.
Thanks to engaged and communicative users such as yourselves I was on top of this with Google almost immediately and they are aggressively looking into the issue.
What we know at the moment suggests it is complex and related to interactions between two services (Apps Script and Google Analytics), both of which made some infrastructure changes in the last 48 hours.
Good news is this and other scripts we've produced are important enough that we've got Developer Relations team dedicated to solving the problem.
Bad news is they can't give an estimated resolution time, and the issue is rather obscure, so it's unclear what the priority is to Google.
Make your voice heard by starring or commenting this issue: https://code.google.com/p/google-apps-script-issues/issues/detail?id=2738
Apologies for the trouble. Doing the best I can to ensure a solution;)
FYI: Today I published a workaround... see https://plus.google.com/u/0/114670488345865665282/posts/Ym18vbbDE8V
Autocrat not triggering on Submit
First off, thank you for a great tool. The last two days autocrat will not trigger on submit. Works manually when I run merge. I haven't changed anythign in the forms or spreadsheet. Already ran steps 1-5 again just to be sure. Any clues?
merge not firing on form submit #3
I am using FormMule in conjenction with AutoCrat.
In Form Mule I had orgianlly set a condition of NOT NULL for the "Mergred Doc URL" column. and then the emails will not send.
I went back and removed the condition, and now the email sends, but there is an error message in the "Email template status" columns that reads:
Email1 Template error: Exception: Unexpected error: http://www.google-analytics.com/__utm.gif?utmwv=5.2.2&utmhn=www.formmule-analytics.com&utmcs=-&utmul=en-us&utmje=1&utmdt&utmr=0=Mailed%20Templated%20Email&utmp=Mailed%20Templated%20Email&utmac=UA-30976195-1&utmcc=__utma%3D451096098.136701511.100000000.200000000.7858.1%3B%2B__utmz%3D451096098.7858.1.1.utmcsr%3D(direct)%7Cutmccn%3D(direct)%7Cutmcmd%3D(none)%3B&utmu=DI~
And of course there is no doc attached to the email, even though a google doc is created.
merge not firing on form submit #2
The "Merge Doc ID" field does get an entry, and a google doc is created, but the "Merged Doc URL", "Link to merged Doc", and "Document Merge Status" remain blank.
Trigger on Submit is no longer working
First off, thanks for a great tool. Autocrat in the last two days has not been firing on submit. I have had to manually run it which is not the end of the world. Have not changed anything on the forms or response spreadsheets. Tried re-running steps 1-5 also. Any clues?
merge not firing on form submit
The merge is not firing on form submit.
It works fine when I do a manual merge, but it does not work when run from the live form.
I have not overwritten any columns.
help!
Notworking-Working-notworking?
Yesterday I spent WAY too long getting my form to work with Autocrat. Thrilled to have it working by the end of the day, and a colleague tested it this morning. It worked! Then this afternoon I made another, and the form-submit isn't triggering. I tested the one from yesterday, and it's not automerging upon form-submit either. So is it me, or is there a well-timed disruption to the system?
At this time, the Autocrat works in every way as it should if I manually merge, but any new form-submits are not pushing it out, even on the spreadsheet that worked right yesterday.
I tried unselecting "Trigger merge on form submit" Saving, and reselecting that. No dice. The puzzling thing is that the one from yesterday doesn't trigger on submit now, either.
Incidentally, here are some of the ways that I bungled things up yesterday:
Error Message
I am using AutoCrat to generate feedback from staff Appraisal and other lesson observations. The first few worked well however I now get a message Error encountered: the coordinates or dimesions of the range are invalid. I think I know why this has happened but can't fix it.
I inserted another field into the form after initially running AutoCrat. Now when I run it AutoCrat can't insert anything into the 'Merged Doc URL', 'Link to merged doc', 'Document Merge Status' fields.
Is there a way that I can reset AutoCrat or set the reference for the Merged Doc URL field to another cell?
I hope this makes sense and hope that someone can help.
Best Wishes
You accidentally overwrite a key column
In adding a form field, the "Merged Doc ID" header was overwritten. In the future, insert a column to the right of existing form columns PRIOR to inserting questions and this won't be an issue. For this case, you can insert and manually create a column with the header "Merged Doc ID" (exactly) to the right of your last form column and it should fix.
Marvelous, That seems to have
Marvelous,
That seems to have done the trick. Thanks for the speedy response.
Best Wishes
Final Merge Won't Finish Running
I tested eveything out and had it all working. Many hours were invested, but I was excited about helping some teachers use this tool in my distrcit. The only thing that chaged is students filled out the form and real data is in there and now when we run the merge it does not finish. All I see is a robot with a wiggling mustache.
Please tell me there's a way to fix this. What should I be looking for? DO I have to unistall the script and update it then map everything again?
Bummer : (
Things to check for
Chris, think we've already corresponded via email. Here are my top troubleshooting tips:
Can yourun through Steps 1-5 and save your settings again.
Make sure you're not logged into multiple google accounts -- Apps Script cannot handle multi-account sign on.
Check your column headings. Make sure no blanks or headers starting with numbers. Make sure no commas in headers or tags.
BiG ThanKs!
Just wanted to express my deepest thanks to Andrew for helping to quickly resolve this problem. I set the script up for one of our teachers to us and did not know that a new question was added to the form, which was the source of the problem.
Thanks again Andrew!
Adding form questions to an existing autoCrat installation
It looks like at some point after you set up autoCrat you must have added a question to your form, which over-wrote a column header "Merged Doc ID" that is critical to the operation of the script. As a result it wasn't finding that column. I added that column back in manually and things seem to work now. Will have to think about a more graceful way to have the script fail. Unfortunately, the default behavior for adding form questions is not helpful -- it shouldn't overwrite existing data. Maybe this is worth an issue ticket to Google.
BEST PRACTICE FOR ADDING NEW FORM QUESTIONS TO AN EXISTING AUTOCRAT INSTALLATION:
Prior to editing the form, insert as many new columns as you are adding questions between the last form column and the "Merged Doc ID" column. The unfortunate default behavior for forms is that they overwrite columns in the sheet to the right of the form. Perhaps I should rewrite the script to insert all mission-critical columns on the far left of the sheet...
Mail Merge QR Codes?
I love that you've helped make mail merge possible in google docs! I use mail merge to create grading rubrics for student projects and would like to be able to have a qr code for the url of their project. That way, students and parents can scan and see the finished product while looking at the grading rubric.
So far, have learned how to use a formula to create a qr code in google spreadsheets =IMAGE("https://chart.googleapis.com/chart?chs=150x150&cht=qr&chl="&F5). Made a heading in row one to name this column, but it does not carry over when the merge is done in autocrat.
Is there a way to make this value merge along with the other fields being merged?
No image merge possible...yet
I would love to provide this feature. Need to find the time to work it in. Probably possible given what I know of the API.
Autocrat for Class Schedule Requests
I just set this up for our guidance counselor to use for class requests for next year. We're a small parochial school of about 300 in the high school. This year, students/parents will submit class requests through the Google Form, the counselor will get a spreadsheet of the data, and the parents will be automatically emailed a receipt for their request. The guidance counselor loves me. Good work!
Help! Problems loading a document
I just got started, but have hit a roadblock. I loaded autocrat on to google drive. When I tried to load a document, I get the error: TypeError: Cannot read property "length" from undefined.
I tried over, and over. I used multiple versions of forms with different styles of fields. Nothing worked. Please help.
This appears to be the same error that Clong posted about 10 weeks ago.
Thank you,
Jesse
Did this resolve itself?
Things to check:
Are you signed into multiple Google accounts? Apps Script is incompatible with multi-account sign on, which causes me untold support headaches as you might imagine;)
Is there any data in the sheet you designated as the source sheet?
Posting on this tool in Using Google Apps as a Free LMS
The Google+ community Using Google Apps as a Free LMS crowdsources resources for educators.
In the post, I suggest autocrat is an antidote to the high cost of video-based MOOCs.
Problem with Attachments
Hi,
Is anyone having problems with the AutoCrat attachments? I have selected to email a PDF copy of the form. This has worked for weeks, however, starting today, the attachments are not populating correctly with the .pdf file extension.
Please advise,
Thank you!
-Tyler
applicationpdf
Yes, we are having this issue - please help!
PDF files issue should be fixed
This issue was fixed several days after if appeared. It was a Google issue.
Re: Problem with Attachments
Same Problem here. This is impacting us quite heavily as we have some to rely on this...
We can manually change the extension FROM .application/pdf TO .pdf and it works but this is a pain for hundreds of documents. Just started a day ago. Any help would be appreciated.
Ed
Same here
Having the same issue here that seems to have started yesterday.
Script Missing
I just created a new spreadsheet and form but don't see the Autocrat script in the gallery currently, just the Spanish version. I'm guessing that has something to do w/ the issue we are experiencing.
Autocrat from alternate email address
Hi,
Does anyone know if it is possible to send from an alternate email address?
I have three different gmail addresses and would like to configure autocrat to send from an address other than my primary account.
Great script, so many uses!
Thanks,
T
FROM address is always the address of the person who installed..
Apps script can only send emails or create documents AS THE LOGGED IN or AUTHORIZED USER.
For "On form submit" triggers, Ttis means that if you want to switch which account is doing the sending, you need to go into the Triggers (Resources menu in the Script Editor, which you get to from the Tools menu in Sheets) and see which triggers are set. If you want to switch which account is doing the sending on form submit, you will need to remove triggers from the non-preferred account and set them again on the preferred account. Annoying, fo sho!
Currency, thousands separator and decimal issue
Hello everyone,
AutoCrat is an outstanding script that i'm using to produce an experimental application for complex students reports - weighted averages, descriptive reports and so on -, and also an experimental application for producing expenses reports. For both, i've found a limitation regarding the separation dot, which does not 'print' on the merged document, and also with the decimals, which are copied on the merged document not taking into account the format given in the spreadsheet (on a merged document there were 12 decimals!!!). The same happens with the currencies, that do not show on the merged doc.
I'm using V4.3. has someone solved out this issue?
Thanks again for the script, Andrew!
Regarding decimals, i found a
Regarding decimals, i found a simple solution: used the "=round" formula defining how many decimals i wanted... Still haven't been able to introduce the separation dot (of thousands). For instance, a value on the spredsheet is "$57.000", on the merge file will be simply "57000". Any guess?
Also try the =TEXT() function
Had some luck with using the TEXT function, in conjunction with formatting the column as text. If you search for information this function, you will see that it allows you to specify all kinds of decimal, currency, etc. formats. Shortcoming becomes that you can't do anything arithmetic with the output of it. For autoCrat, it works well, however.
formMule coupled with autoCrat
I have coupled formMule with autocrat to manage data in our school. FormMule sends an email to an operator who then runs autocrat after inputing information that cannot be automatically added. To run autoCrat after the information is included, I modified the onOpen function in autoCrat to read as follows:
var menuEntries = [];
menuEntries.push({name: "Create Tardy Slip", functionName: "autoCrat_runMerge"});
ss.addMenu("Run Tardy Functions", menuEntries);
Using Branch Logic and Two Merge Documents
I have a couple of forms that use Branch Logic to send respondents to a different page of questions based on the first answer the ask. For example, we have an on-line class application. Both noobs and veterans have to apply. The first question is are you new or a returner and then it takes you to seperate questions for the application.
Since they are different questions the merge template is different. I'd like to have the option: "if column a = noob, then merge with noob template. if column a = veteran, then merge with vet template."
Duplicate Files
Recently, I have been receiving dupliate merged files from AutoCrat. Any suggestions on how to fix this?
Duplicate Files
Recently, I have been receiving duplicate merged files from AutoCrat. Any suggestions on how to fix this?
Error message on simple merge
Hi
This is my first attempt at merging with Autocrat and I get this error
Error encountered: No item with the given ID could be found, or you do not have permission to access it
I have treble checked everything and cannot see what is probably staring me in the face.
Thanks for any help.
Struggling with merge function
For some reason I can't clear or find the right place to clear merge-status. Merge runs once but subsequent records added to the spreadsheet can't be run to merge.
The error is:"For some reason(s) no records were successfully merged. If you haven't cleared the merge-status messages, that could be the issue.
There are no merge-status messages in the spreadsheet and I've gone into
--->Responses--->Delete all responses which sets the responses count back to zero, but that did not help.
Anyone know what I'm not doing or doing wrong?
Thanks for the help.
AutoCrat and FormMule
Hi,
I'm trying to use a Google Form to trigger a mail merge to a Google Doc. This part I can do. The issue is that I need to calculate some fields and look up others. Is there a way to combine the functionality of FormMule - which copies the formulas down for you during the form submission allowing these to be used in the merge - and AutoCrat to allow me to do this with a Google Doc?
Is this already possible in AutoCrat and I'm just missing it?
Thanks
Matt
Matt, I feel your pain - I
Matt,
I feel your pain - I was struggling with the same problem - I not only wanted items from the form to appear in the merged document, I also want calculated and looked up references to appear. For example I wanted to mark a MCQ and then provide feedback based on that MCQ.
I spent a lot of time on this and in the end I used arrayformulas - these are formulas that automatically extend to the size needed. However you can't just take a regular formula and stuff it into an array, you need to get the references in an array format plus there are some formulas that just don't play well in an array (because of things like references staying statics when you wanted them to be dynamic (or vice-versa) or needing to be in the same "orientation" or size and shape as the rest of the array). I did manage to get it working in the end but it did take me something like a month to learn how and an incredible amount of trial and error! I also hired a consultant to help me for a few hundred dollars but in the end what they built was not stable and I ended up having to redo it in another much more long winded but stabler way. If you are not familiar with arrayformulas this is a good reference site http://yogi--anand-consulting.blogspot.com.au/2012/01/yogiapply-formulas-to-formsubmittals.html
This is the guy I used to help solve the problem - but as I said before I learnt a lot from him but it didn't work in the end (the formula which he ended up with was also beyond my level of understanding which made maintenance an issue. Plus it was not stable and he struggled to re-create the problems. A funny experience paying what is a lot of money to me for one long line of code!)
I have a suggestion: Use both scripts. Perhaps if you install the FormMule script first, it will run first and copy down your formulas for your mail merge when autocrat runs. Or maybe you could somehow delay the running of autocrat for 1-5 minutes or change it's trigger to come from FormMule (on completion) instead of on Form submit. In fact this would be a great feature to have.
Andrew: if you're listening: I could imagine that this could be a single line of code - perhaps you could add this function into Autocrat?
I haven't tested this - so if you are able to get it going then let me know.
We could make another plan to speak if you want some detailed advice/examples of my array formulae and how the spreadsheet works, this is way too much to go into over here (warning this would require someone very familiar with vlookups and more advanced formulae plus the ability to work with spreadhseets to a competent level)
Good luck! Keith
formMule was designed to work with autoCrat
Not sure exactly where you went astray, but formMule is designed to work alongside autoCrat without issue (caveat - it gets a bit complicated for really demanding edge cases)... autoCrat should detect the presence of formMule and delay merge until all copydown procedures are complete in the sheet before running the merge. Let me know if you want a hand. I don't charge educators for this kind of stuff.
My Email Address
Hey Andrew...
I don't know why, but I'm getting all these emails from this page from people who have autocrat questions. Are they replying to my original post? Anyway, is there anything you can do about that? Can you take down my posts if that's the issue?
Thanks
Nevermind...I figured it out
Nevermind...I figured it out
Error autocrat
Hey
I'm new to youpd. In trying the autocrat, I get the same problem as mentioned bij already two other people. How can this be corrected. Please help, because it is very promissing.
Hi,
Firstly a fantastic script that clearly must have taken a lot of hard work so thank you for your time.
However upon installing and entering all the necessary details i get this message when i try to run the script for the first time:
"For some reason, no record(s) were successfully merged. If you haven't cleared the merge-status messages, that could be the issue"
Any help as to how to overcome this issue would be much appreciated. I have read on here that this was a bug on version 4.0, however i am using 4.1 and still get this message.
Regards,
Eddy
Also having merge problems
I get the same errors when trying to merge.
"For some reason, no record(s) were successfully merged. If you haven't cleared the merge-status messages, that could be the issue"
I think this is a bug not user error as prior versions of the script seem to function fine
"on form submit" not working for me
on form submit is not working for me, I am populating the spreadsheet from a third party form all my data gets added to my spreadsheet but does not trigger the on form submit is there a way to get this working?
Duplicate mails sent
Hi Andrew,
First of all great handy script, thanks for your efforts. I'm using your script to do a reasonable size (1 page output, <10 merge fields) mail merge, generate the PDF and mail to about 25-50 recipients using a dynamic tag for the recipient email addresses.
If I run the script for one instance (i.e. the preview first row merge), I never receive duplicates (done this many, many times). However, as soon as I start to do this in batches, I will see 2-3 copies of the PDF generated in the output folder, and the recipients are receiving multiple copies of the mail as well. All duplicates always have the same time stamp in Google Docs.
Additionally, this has happened on different documents which I've used the script on. I am using the latest version as I just installed it this week. This is a killer for me... I hope you may have some advice.
Thanks in advance
Script not working?
Hi,
Firstly a fantastic script that clearly must have taken a lot of hard work so thank you for your time.
However upon installing and entering all the necessary details i get this message when i try to run the script for the first time:
"For some reason, no record(s) were successfully merged. If you haven't cleared the merge-status messages, that could be the issue"
Any help as to how to overcome this issue would be much appreciated. I have read on here that this was a bug on version 4.0, however i am using 4.1 and still get this message.
Cheers,
Henry