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!

  1. 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.
  2. 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.  
  3. 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.
  4. 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.
  5. 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.
  6. 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.
  7. 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.
  8. Here are some basic options to play with.  Checbox allows you to test on first-row only if you like.
    1. ONLY saving merged Docs to a collection, either as PDF or Doc format.
    2. Saving to a collection AND emailing PDF as attachment.
    3. Saving to a collection AND emailing recipient a link to individually shared Docs as View-only
    4. Saving to a collection AND emailing recipient a link to individually shared Doc as Editor
  9. 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

zachholden

Too many redirects

I'm having trouble with Autocrat (for the first time). I apologize if this question has been asked and answered.  I searched but did not find it. 

 

I was fixing a script issue and so I uninstalled Autocrat and tried to reinstall it. When I did the initial configuration told me that the link had too many redirects.  I was unable to find a solution or fix the problem so I decided to start from scratch and recreate an identical form.  However after this I still got the same response - too many links.  Since this is the first time the document is created, I'm unsure how I can recieve this message.  

 

Thoughts? Possible solutions? 

 

Thanks. 

Darwin

Date Time format issues

Hello,

I am using formmule for tons of applications at the school level.

I am currently setting up a sub-booking using formmule.

 

The date function still does not seem to work correctly.  I set the format in the spreadsheet but it always attaches the time at the end of the date.

the image is here

https://docs.google.com/document/d/1zzralz9Y7PE6ZFEcAwgoBzOdQdrZgZItNIiY...

Thanks

 

 

eerickson

How to handle New Date and Time question types in google forms

Thank you for creating a great tool.  I am having difficulty with the new google form question types: Date and Time.  The date and time formatting that is collected from google forms is presented as such:

Date: MM/DD/YYYY and shows in merged doc as:  Mon Nov 18 2013 00:00:00 GMT-0500 (EST)

Time: H:MM:SS and shows in the merged doc as: Sat Dec 30 1899 09:00:00 GMT-0500 (EST)

I would appreciate any ideas you may have that will help resolve this. I am looking to preserve the formatting from the google form.

 
Sheila

Error:invalid_client with latest version

Great script!  However, I can't get the latest version to run (11/15/13)

Existing spreadsheets which already have been authorised to run are fine but I can get this one or in fact new copies of the old one to verifiy.

Has something changed?  Help!!

 

shane.smith688

invoicing?

Andrew, Thank you so much for this script, it has made our business able to switch to all digital tickets and record keeping for free and makes printing an invoice for the customer easy as can be! Great work! I am curious if a function exists to create a sequence of numbers to assign to each merged document so as to create invoice numbers. I would like it either to be done in the actual form responses area or in the process of saving and titling the document that is created by the script. Perhaps even a naming qualifier like $seqNum or $rowNumber + 1, you know something that assigns a unique 4-5 digit number to each document created. Would you or anyone else on here have a solution for that? Everything else is working fantastically...

robclarke

Error message autoCrat_removeTimeoutTrigger

I'm getting the error: "Error encountered: "autoCrat_removeTimeoutTrigger" is not defined."

Which is causing the preview merge to fail. How do I fix this please?

robclarke

Deleting and reinstalling Autocrat

Hi all,

How do you delete the script so I can reinstall it? I've completely mucked it up by inserting a new form field, then tried deleting script parts thinking I could get rid of it and later reinstall it.

Yikes!

pto linden grove

autocrat variation?

Hello, 

Does anyone have a variation of autoCrat that merges the info with just a blank line separation (so items immediately follow each other on the page) INSTEAD of formatting onto lots of separate pages?  

Karla

 

adkisol

error message - not merged

I believe I have set up Autocrat correctly into my Google spreadsheet.  When I try to run a merge, I get an error that reads:  "For some reason, no record(s) were successfully merged.  If you haven't cleared the merge-status messages, that could be the issue."

I don't know where to find merge-status messages except the column that was added to my spreadsheet for merge-status.  There are no messages there.  I've tried this many times over the past few days and even asked our computer teacher for help.  Neither of us can figure out what the problem is.  

 

Help?

 

Thanks,

Laura

pto linden grove

AutoCrat info to single doc

Love AutoCrat.  Had it downloaded and working in 15 minutes. However, is there a way I can change the settings for it to merge all the records onto a single document instead of separate letters?  I want to use it to format individual news submissions to a weekly PTO newsletter and then embed it onto our website.  

meesterparker

on chromebook

I just got an HP Chromebook.  I logged in to merge one of my marks for my class and on the Chromebook it would not allow me to merge.  I went back to the start and tried to see if there were errors there, but it would start opening and seemingly freeze.  The merge drew an error and then an userapp downloaded.....which does not open on a chromebook.

 

I later logged onto my other "windows" based laptop and it merged fine.  

andrew

Pls. confirm you're not logged into multiple accounts

Multi account sign on causes major problems in Apps Script.  Can you confirm this is not this issue?

flizkow

Merge data into PDF with no Gridliens showing

Dear all,

I'm looking for a way to merge the data in my spreadsheet into a PDF that doesn't show any gridlines.

If I manually print out a google doc spreadsheet I can select the "no gridline" option and its fine, is there a similar way to do this using autocrat?

I'll print these documents out and hence that would be a useful feature to have.

Any ideas?

Thanks,

Felix

andrew

Pls star this issue in Apps Script issue queue

If you are interested in the ability to hide gridlines in PDF (which I very much am too) this is an Apps Script issue.  See https://code.google.com/p/google-apps-script-issues/issues/detail?id=2176&q=pdf%20gridlines&colspec=Stars%20Opened%20ID%20Type%20Status%20Summary%20Component%20Owner   and star it!

benrud

Updating AutoCrat

Hello,

First, THANK YOU! Thanks for creating a great script. Second, I want to update to the new script released on 5/10/13. Please reply with instructions, thanks.

Also, I've significantly modified the form I'm suing 'after' I installed autoCrat and the column headings for the document create, email, etc.... have been overwritten with the new form fields in the updated form. How do I correct this and/or prevent this in the future?

Thanks again and have a great weekend,

Todd

andrew

how to update autoCrat

Updating autoCrat should be pretty straightforward:

1) If you want to keep all your current settings without having to re-set them, you can export your settings from the "Advanced options->Package this system for others to copy" dialog and get them in your clipboard or paste into a text editor for safekeeping.

2) Delete the existing script from the script editor (from the Tools menu in Spreadsheet go to Script Editor, then go to File menu and delete).  You can only do this if you are the script owner.

3) Reinstall autocrat from the gallery.

4) BEFORE running preconfig, paste your settings into the autoCrat_preconfig file as per instructions in the code editor.

5) Run initial installation and all settings should transfer to the updated version.

Courtney Lemm

Server Error

On April 30, the script stopped generating PDFs for us.  I have uninstalled and reinstalled the script, but continue to get the message "We're sorry a server error occurred. Please wait a bit and try again later."  Any ideas?  I was hoping that your work with Google on other issues would resolve ours also, but so far no luck.

Thanks so much for a wonderful tool!

andrew

Has PDF error subsided?

This sounds like an ephemeral Google infrastructure issue to me.  Can you confirm this problem has subsided?

franlhughes

Quality of merged PDFs

Autocrat script worked really well for me. I am a complete novice ... never worked with scripts in google before. I used the script to generate certificates.

The only issue is I have (and i am not sure that it is related to this script) concerns the quality of the merged pdfs.  The saved file size is quite small and the quality of the graphics in the template have suffered.

I have tried using higher quality graphic but the same outcome occured.

Many thanks

 

andrew

PDF quality - star this issue

Poor PDF quality is a known limitation of the Apps Script environment.  Star this issue: https://code.google.com/p/google-apps-script-issues/issues/detail?id=2375&q=pdf%20quality&colspec=Stars%20Opened%20ID%20Type%20Status%20Summary%20Component%20Owner

andrew

PDF quality - star this issue

Poor PDF quality is a known limitation of the Apps Script environment.  Star this issue: https://code.google.com/p/google-apps-script-issues/issues/detail?id=2375&q=pdf%20quality&colspec=Stars%20Opened%20ID%20Type%20Status%20Summary%20Component%20Owner

Richtheteach

AutoCrat Onedit feature

Hi Andrew,

Firstly! Great script.  

I have a couple of problems that I hope you can help sort out.

I am using your script as part of a complication Teacher Observation process.  I have 3 Googles forms that populate different Google spreadsheets.  I then have a 4th form that collate this info by using the importrange command.  I then use your AutoCrat script on this sheet.  So this sheet is populated from other sheets, so there isn't a form directly associated with this 4th spreadsheet.

When a new entry comes into the 4th Spreadsheet, I want AutoCrat to run.  At the moment I have 2 problems:

The first is that the 4th sheet needs to be open for AutoCrat to run.  The second is that when I add a timed Trigger to your script to run the autocrat_runMerge function it works fine until the script closes and then when it is reopened the trigger disappears.

Is there anything you can suggest to help me tailor your script to what I want?  In short I don't want the spreadsheet to be opened in order for a pdf doc to be populated by an entry into the spreadsheet and then sent to the user.

Kind regards,

Richard

andrew

Autocrat...how to get triggers to not delete themselves...

You might write a little custom function like this:

function myTrigger() {

autoCrat_runmerge();

}

and then set your trigger on that.

The self-deleting trigger is an artifact of the auto-recover behavior I've built into the script.  You are right that it's annoying if you have other intentions like setting your own custom triggers;)

Jeremy

Your update worked perfectly

Very easy to cut and paste config info.  Took me about 10-15 minutes to get my 5 autocrat on submit forms working.  Thanks Andrew!

 

andrew

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

bgray

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?

 

andrew

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.

bgray

Got it. Thanks!

I've got it working now.  I was using the folder keys that were output from gClassFolders with a variable, but when I copied the data from the gClassFolders file, I copied the whole ULR link formula instead of just the folder key  (i.e., paste instead of paste values only).  autoCrat was confused by that (and so was I).

Using gClassFolders and autoCrat, I just completed the creation of 1100+ forms shared to 60+ teachers and 3 college counselors.  Thanks!!

bkg

 

 

andrew

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

Jeremy

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?

mblorenz

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.

mblorenz

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.

Jeremy

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?

mblorenz

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!

 

edtech2020

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:

  • I didn't first make a folder for my form/responses sheet/folder of PDF files.  So I had this junk all in the root, and moving it made it go wonko.
  • I thought I could copy the spreadsheet to make a new one, but in fact I had to copy the form if I wanted to start fresh.
  • I had to remember html to make the email look nice.
  • I realized I wasn't logged into the correct gmail account, so I started fresh in the other email identity.
  • I didn't have all the <<>> fields in the template to start, but in order to see what the field names would be, I had to install Autocrat and get to step 2 on the install, pause there, format the document, then proceed to choose that document to merge with.
  • At first I thought I'd be able to choose a Word template (a certificate) and upload it to GDocs, but that didn't work out because GDocs is pretty graphics-unfriendly.
Cottiss77

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

andrew

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.

Cottiss77

Marvelous, That seems to have

Marvelous,

That seems to have done the trick. Thanks for the speedy response.

 

Best Wishes

clong

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 : (

andrew

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.

clong

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!

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

norris

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?

andrew

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.

smithteacher

would love to have this added

I'll just second that it would be incredibly helpful to have the ability to add images to the merged document. Then you could keep formative assessment data in a spreadsheet, generate individual student progress reports with progress graphs, and e-mail to students/parents.

rwreyes

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!

Jesse C

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

andrew

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?

John Graves

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.

tshepard

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

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

290
Recommend

Tweet this

Browse other items with similar tags

This tutorial presents the pre-Add-on version of Doctopus. 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. For tutorials on the new version of Doctopus, see http://cloudlab.newvisions.org/add-ons/doctopus
Recommended by 188 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 64 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 19 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 15 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 16 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 20 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 23 educator(s).