Advanced Google Forms & Add-Ons

hi everyone and welcome to advanced Google Forms and add-ons I am your host I'm Jen Schaffer I'm the mobile learning coach and instructional technology specialist for Burlington Public Schools in Burlington Massachusetts and I'm also the Massachusetts Google education group leader and I'm really excited tonight to have two expert Google education trainers joining me to lead tonight's session and I'd like to introduce both of them to you now I'm starting with Jen judkins Jen is the instructional technology integration specialist for Lynn field public schools in Lynn field Massachusetts and she provides in-class support to teachers by helping them design and deliver meaningful lessons that incorporate technology she's an authorized Google education trainer and is passionate about sharing with teachers Jen thank you so much for being here tonight thanks good to be here with you also joining us is a authorized Google education trainer from aragon Narragansett Regional Public Schools in Massachusetts this is Eric Erickson Eric thank you for being here great to be here he is an experienced teacher and school administrator who knows firsthand the importance of reducing paperwork in schools which is goal for all of us he's leveraged the power of Google scripts to create numerous automated workflows and willingly shares his knowledge and worked with colleagues across the state thank you Eric for being here you bet so just to give viewers a little bit of information before we get started on the event page of the event you will see a link to a document that Jen and Eric have prepared this is to allow you to interact with some of the tools that they'll be showing you tonight we're going to be getting really geeky with Google Forms so if you are brand new to forms on this probably will be a little bit over your head but I think you'll be able to follow along because we have two great trainers with us tonight but they're going to be really helping us unleash the magic and power of Google Forms so I'm really excited to learn from them and I think at that with that introduction we are ready to get started with Eric who's going to be doing a screen share yeah so let me go let me let me start that up now okay great and also um while you're getting that ready just to let the audience know the Q&A app is enabled so if you have any questions during the broadcast feel free to go ahead and put those in and we'll do our best to address your questions as they come through all right you want to take care perfect so um we're just going to go through a few slides real quick to just give all of you a broad overview of where we're headed during this hangout call and then we're going to dive right into a live demo part of which will require some audience participation so let's take a look there the link to the resources again is on the events page but it's also featured on the slide right here if you're looking to access that so we're going to actually be using a use case scenario to take a look at um using Google Forms in you know in conjunction with a number of add-ons so the what we're going to be showing you is a scenario where students would use a Google form to submit a book choice so if they were I'm having some independent reading and they wanted to submit their book choice for a independent reading project the teacher would have them do so on a form and then the teacher would go into the form submissions to make an approval decision about whether the book choice was approved or not approved and then could also add comments and then once the approval decision was made the Google Form could or the data from the Google Form and combined with the teacher approval decision and comments would all be used to populate a essentially a mail merge document using autocrat and that would be emailed to students so let's take a look at some of the items that we'll be featuring we'll be featuring the fun going three add-ons this is just a few things that you can do with Google Forms but some really interesting ones if they're new to you Ford Ranger and foreign limiter and autocrat let's just take a quick look at how these are different from one another so form Ranger allows you to create a drop-down list of items so for example if you have a list of students that changes throughout the year and you don't have to keep going back into Google form to make the adjustments to the names on the list you can actually keep the names in a centralized spreadsheet and that spreadsheet would then populate the Google Form choices for a particular question and this again is really helpful for data consistency and then form limiter is one that allows you to set a closing date for the form responses so if you setup a form and you want it to close automatically based on different criteria form limiter is a go-to tool for that purpose form limiter can be used to set forms to closed based on a number of responses a date and time or even based on a specific value in a Cell so if you're maybe totaling asserts and then the third one we're featuring is autocrat and autocrat does a really slick job of putting together a mail merge using a document that you create ahead of time as a template and it pulls data from the Google sheet and and then can email that document as an attachment can be great for a lot of things progress reports on class awards and things like that and again for us we're going to use it for a book reports election confirmation form that will go out to students so I think Eric are we ready to just maybe shift at this point - yep I think so perfect so let's make a document here quickly so we'll need to go to Drive go to new and we're going to start by creating a form perfect all right so again this is not an inner class so we're not going to go through the details of how you make a form and the the basics there but we're going to begin by talking about the form settings for a moment um a lot of times when you're creating a form that's used within your district you would use the first two selection boxes to require the collection of the user data and to automatically collect that um you know into your form submissions that's really helpful because it helps to create consistency and limits your form submissions to people that are within your domain but because this is an audience participation activity we're going to deselect that so that you guys will be able to fill out this form and then get the templated documents sent to you so that you can see what a student might see so the very first thing that we're going to do is we're going to create a question for the student name and Eric's going to put in there some help text and and even if you've used forms before I just want to take a moment to talk a little bit about the difference between question title and help text the question title is going to result in your column header it's really important to keep that really small and tight so that it doesn't become something that's wrapping in a column header when you're in the responses you can use the help text to put more information and the user will always see the help text it's just it displays as like a title and a subtitle so it's not a mouse-over piece of information so it's perfectly acceptable to use that we're going to check up go ahead Eric I was going to say another quick point that we want to make sure of is as we use autocrat it you you want to make sure not to put any special characters in the question title so you know you want to refrain from like dollar signs or colons or or a strict or any strange character try to just keep it alphanumeric as much as possible perfect yeah we know a lot of these things because we've done it wrong a few times haven't we Eric and that's true and it's important that we just know that because you may not think it's not a big deal now but as you try to merge these things using the power of autocrat you'll find that sometimes it will it will hang up on some of those special characters yeah perfect great point so next question that we're going to use is a email because again we're in our form we're not going to collect that ahead of time but you could so when we do this we're going to select a text response and let's talk about the Advanced Settings and some data validation features so under the question type there is a choice for Advanced Settings and when you choose that it will open up depending on the type of question that you select in this case we've selected a text question and what you see here as your choices will vary if you select a different question type and when you select text you'll notice that are their choices and you can choose text contains one of the options is email address and the custom error text will allow you to put in a note here that will pop up for the user if what they put into the question does not follow a standard email format it's important to understand that people could still submit an incorrect email if they have a misspelling of their name it does not actually check to see if an email can be sent or received but it will certainly check to make sure that it follows a standard email format um and and it does prevent some common errors we do have a question really quick from perfect alexandria Cellucci hi alex' Angela yeah she wants to know if you should avoid colons as well in the question box yeah I think that all the there's there's a list and we could probably supply the list in the in the session notes of there they're called all the special ASCII characters so I would I don't know off the top of my head but I think semicolons would probably cause a problem okay great yeah and those those special characters become problems not just with Auto crap but if you get super geeky with forms as as that we have done before and you're trying to wrap things in HTML and do all kinds of other crazy stuff on it it just it's something to try to avoid when you can because it can trip you up in other types of if you're trying to automate workflow with other tools yeah and we'll look for that list and we can put that into the resources page that we have we'll take a look for you so you can see I know dollar signs is a few things like that that are definitely on there next choice was class period we're going to put a class period question in here we typically do drop-down lists as opposed to multiple relationship you know we're going to shows it choose from a list class size yeah it's just a lot neater when you're doing a choose from list because it compacts the question size um and then the next question will be book title Eric and that's just going to be a text response and we're good with that and then we had author would be our next question option let me ask you both something really quick so if I'm doing this and this is an assignment that I do year after year or every semester now I can clear the data from this form and then reuse it correct you can um I'll put some links in the in this sprit in the handout that I have but um you're talking about basically some management of form responses which is a really common question you can under the responses within this within the forum itself you can delete all the responses and by doing that it um it clears the responses and then you can choose a new response destination both of those options are listed under the responses menu choice in the forum it's important to understand that there's a difference between deleting all the responses inside the actual forum which is the screen that we're looking at now that edit form part of form creation if you try to delete the responses from the responses spreadsheet they're not really gone and what will happen is the next person that submits a response let's say you had 40 responses from the prior year the next response after you delete all the responses in the spreadsheet will just pop into to number 41 in your spreadsheet so when you really want to the responses you actually need to go into this this screen here and go under responses maybe you could do that Eric so they can see where it is and then um see where it says delete all responses and it sounds like a scary thing but but realize that when you do that you're just clearing the deck in the forum it doesn't get rid of the responses you already collected if they've been collected in a spreadsheet so you have them for your reference if you want and then you can change the response destination at that point so you could put it in a new spreadsheet or a new tab in the existing spreadsheet right I've got video tutorial that covers this that I can add to the UM to the resources by the end of the call so that people can reference it because it the kind of thing you only think to do once a year and people tend to forget it by the time it comes around again right but the actual creation of this forum is not something you have to do year after year absolutely not you can also copy the forum that's another option but I prefer to do what I just talked about because I think it's a little cleaner you're not making lots of extra files that you don't need right and the reason I asked that is just about saving teachers time because a lot of teachers will say well I don't have the time to do this you know I'm just going to print out of just gonna print it out in handout paper and collect the paper because I don't have the time but you know as a advocate for meaningful tech integration our message is always know you know doing these kinds of things will actually save you time in the long run correct right absolutely as a time investment on the front end that pays off big over over the course of the school year in subsequent school years absolutely okay great yeah thank you um so number of pages Eric was our next one and we're using this question just to demonstrate another type of data validation so the number of pages is a text response and again you go to advanced settings to access data validation you check the little box and in this case where the drop-down is on the Left one of the choices is number so we're going to select number and we're going to select a what are we doing great okay yeah so here it's the validations just to make sure let's say we put in one point 50 pages or whatever it is that that will ensure that when they submit a book for their book selection that you can say well it should be a certain length and so again we can put in a custom error text to let the student know if they put in a number that's less than 50 that their book doesn't meet the page requirements it's important if you're doing a data validation question that you make it a required question because if you don't then a student could skip the question and not get that response that you've built into the form they won't see this message pop up on to actually Eric they see it while there remind me do they see it when they're entering the question um no I don't think they do they send when they hit submit is when it pops up no like if for instance if they're if they're on looking at the live form on this particular question yes it will be it will work until they click out of that question and then it will kick on it pops all right so we wouldn't let them go to the end of the form it'll tell them right away okay right perfect all right and the next we're going to add a question on genre and the example here is that we've put this Plitt you know where we're putting this in here for the purpose of showing you some other features one of which is if you have a long list of items so we created a on realistic is going to grab the items from that list so if you have a spreadsheet of a number of items and you don't want to have to type those out individually in Google Forms on you can just copy and then hit the paste in the very first response and it will populate all of the items in the list so that's one way you can take a really long list of items and put them into this spreadsheet as I'm sorry the into the Google Form as responses for a question type like choose from list or multiple choice the other way you can do it is you might recall that when we first introduced the plan for today we talked about one of the add-ons which is called form Ranger and that add-on allows you to actually take a list of items that is in a spreadsheet and use that list to populate a form the list of responses for a question or the list of choices for a particular question so eric has um has form Ranger already installed you know that add-ons are available through the add-ons gallery you can go to get add-ons and you'll see the choices the add-ons that we're featuring today were the work of new visions for public schools which offers a wonderful array of add-ons that are fantastic for use in schools and the form Ranger Adam has already been installed if you're if you're new to add-ons then just to be aware that the add-ons gallery is different for each type of item at each type of google app so there's an add-on gallery for forms and there is a different list of choices and add-on gallery for sheets so it's important to note that form Ranger is an add-on for a Google form and so if you want to use it you have to go into the actual add-ons gallery for your google form so Eric's got it to run and you'll see it gives us some menus over on the right-hand side and so you what you do is you check it will list the questions that could possibly use the form ranger tool and that's going to be limited to questions that are of the type choose from list or multiple choice so the question that we want to use it for is genre so if you click the little caret just a quick add-on it says right here only multiple choice checkboxes or grid type questions are supported so there's a those are the ones that we can take advantage of and and it will scan your form automatically and find the questions that it can support and it found to two types one the class period which we don't want to work with and then this this question wrong genre perfect so once you find the question you want you would check the box that's populates from a value list and then it will allow you to navigate in your drive and find the list of values that you want um so you choose new list of values and then you'll see it pops into a navigation window we've set this up ahead of time and Eric's found the list one thing it's important to make a note of is that you need to have a column header so we've got the column header of genre list because when form Ranger goes to look at your spreadsheet and you point to a specific tab on the spreadsheet it will assume that the first item that on the column is the header and it won't be included in the list of choices from your in your form so make sure that you have a header so that's where it says genre lists right there and then it the second actually the second cell or the second row is where it starts that's exactly so we can click Next here you can just say blip the genre list now we can save and populate the question perfect and now all of a sudden you can see it reacted what we can do is we can click on it and see that it had updated another nice little feature here is let's say you want to get rid of a particular choice you can do that and you can also reorder any of the options just by dragging them yeah for people that are new to the reordering you actually have to click it it's to the left of the number it's a little hard to see on our screen but when you're in your own form you'll see it it looks like a series of dots to the left the question and that will give you your move um icon on your cursor and then the last question are you ready to was a reason for book selection yes perfect and we're going to do a paragraph text for that so we want to before we make this form live we want to configure form limiter because what we want to do is have this form closed based on a due date so as a teacher I want to open a form up to kids I tell them when they had to have their submissions in by and I could come into the forum and manually turn the form off to turn off the forum from responses you click under the responses header and there is a choice that says accepting responses it's automatically turned on and if Eric were to click that it would change to you see there's a notification and now if he clicks on responses it will now say on not accepting responses so we're going to toggle that back on but instead of having to remember to do that at a specific date and time it's a lot easier to do this with an add-on so that add-on is called forum limiter again this add-on is available through Google Forms in the through the get add-ons option so it pulls up a little box off to the side and we're going to do date and time I think right that's right all right so tomorrow is the 12th we'll just maybe set it up for tomorrow it perfect all right and then it it just so you know all of these add-ons will ask for permission and some of them are a little surprising for people to see what the permissions are that they're asking for this for example asked to send email and that's because it the forum will when the forum limiter kicks in it will email the creator of the forum a notification to let them know that submissions have been closed um it doesn't notify anyone else but you but um just so you know the all of these add-ons that are created by new visions for public schools I'm super comfortable granting permissions for and but it's an understandable concern that people have when those permissions come up so we're going to save this and then we're good so and I'm cool yeah go ahead Eric's going to grab the the link to the forum and put it into the the resources paper that we shared and and while we're fielding some questions Jen if we can have the participants start filling out this form for us so that we have some data to use when we move on to the next part of our sharing of four of autocrat so go ahead with some questions well I just had one really quick I'll fill it I'll fill out the form so you have some data but what what would the users see when that when the form is closed and there's no it's no longer submitting or accepting submissions did they get a notification or so when they click on the form it will pop up a thing that says this form is no longer accepting responses and it will have whatever custom message you put so in the add-ons menu for form limiter at the very bottom there was a little box sorry Eric I forgot to point that out thank you there's a little box at the bottom that says message when submissions are closed so it has a standard message that it puts up and it actually tells you who to contact to open the form back up um you can put any any kind of information in here so you could put a message to students that says or you know if it's something that has an annual enrollment period you could say you know next year application submissions will begin on and you could put that so that people are notified that's great awesome thank you sure so we're going to toggle over to the form responses Jen do you have any other questions before we or from anybody else in the call no I do not okay all right now I'm going to go ahead and fill out the form perfect but just give me one sec all right so Jen should we talk about the adding the columns you okay sure all right I just want to give people a second if they're filling out the form so they're not trying to do that and listen at the same time all right and you put that Eric into the UM perfect yep thank you this is their looks at ago some people in there looks good awesome oh we didn't even make it pretty we forgot to add a theme Oh the quick and dirty form okay does the trick I love now that you can add your own header image it's pretty great I know I haven't done that I need to do that yeah my school loves it cuz then when you do school forms you can have like your school banner and stuff yeah that looks great doesn't it looks really slick yeah all right so let me just check the can you share the responses with me Eric just I can pull and pull those up truck guys the the form you just created mm-hmm is that separate from the book report guidelines correct I'm not I'm not seeing the resource link does this fit was a shared out yeah it's a the form is the is the last thing on the on the bullet which says link to book selection form yeah that's what I'm elated in our thing just so everybody can see which one we're talking about that um document that you guys just had up on your screen that is not what I had posted in the on the event page Oh retro yep what I have is the book report guidelines oh yeah we don't want that right okay because every I see everybody's in so why do we just do this just for now when we grab this since everybody's in that at a document and we'll just put it right here at the top for I think there that's not where I wanted it for that one my Google Documents we can we can change on the fly yeah it doesn't need to be pretty we don't hey no you go so you guys can click on that and then jump into the dock for into the forum Eric since you're in there anyway do you want to put this document the one that has our on the resources page you want to stick that into there as well so people have it which one uh the one that that has our outline and the resources at the top or is that what you put in there uh no it didn't let me do that all right linked it but is that the presentation link I know but it's in there so it's it's the one that says advanced Google Forms and add-ons uh hangout on air mass keg yeah but where's the you don't have you don't have a short link for to yeah yeah that's the one that's in the front of the presentation the first couple signs I pasted it in there yeah all right I'll just do it this way if you don't mind you want do you want me to go get that that short link whatever is quick and easy I don't think I have it here yeah that's that right there this one mm-hmm yeah that's the link that I did not have I'm sorry about that guys I clicked on the link you had sent me in the in the chat right before we went live oh sorry then that's my best choice confirmation is what oh we might as well put the blame where it goes Jen so very well maybe but I I clicked on that link pasted it in and no problem alright and then what we can clean that up later yes you can put that in the vents page later done because we'll we'll take that out when we merged okay so I think we at least have some responses in here Eric right just look like we have a 100 we have one we have one thanks Tom you're a rock star Thomson okay now we got them perfect we're coming you know what why don't you hide that dumb hide that email column Eric just since we're broadcasting I can do that all right I just submitted mine as well perfect now we're getting something great all right Eric you want to just we'll get started on this part of things we'll we'll take a look at this response to spreadsheet so as these responses are coming in what we're going to do is we're going to add a couple headers a couple column headers in column J and K and reminding Jenna think it's approval write approval decision and I'm a teacher comments so those are the two yeah okay so when you you know when you want to have additional information that might be part of the mail merge or that you know in this case that's going to go that's going to populate this document that we've created through autocrat um you can add columns at the to the far right of your form responses and then those can become places from which you pull to populate the autocrat because these are things that are not coming into your form okay so we're adding these things you know you could put formulas in here to calculate things if you wanted you can use on Eric and I are big users of vlookup which we decided would just be a little too much for people tonight but um you know you can do a lot of things with additional columns that you add that really allow you to put additional information associated with a particular response in your spread sheet and so we've added to because our workflow requires the teacher to make an approval decision and then also there's a location for the teacher to place comments to the student about their submission so since we have those added all right so we have we have some there where I'm just going to go grab some comment with generic stuff here perfect and so we'll just go paste that in quickly maybe do it a couple times you see I don't know if you just notice what happened there but we had a value and and then this 13th form submission basically got inserted and then pushed this value down I don't know if anybody actually caught that yeah all right so the teacher you know you can you you could do this it doesn't matter you don't have to beat the kids to it you could wait for the responses to come in and then enter your approval decision at the end of the due date for when kids submitted their responses that's how we are using this but what we're going to do next is we're going to be configuring autocrat so autocrat it's a it's an add-on within sheets so if you go to find autocrat in Google Forms it will not be there so we're going to launch it from sheets because it's already been added just a reminder if you're new to add-ons once you install an add-on under your your Google Apps account it will be available to you under the add-ons drop-down choice for every future document or in this case spreadsheet so you'll notice that I'm Eric's a big add-on to user and has a real list birdfeeder Eric you're gonna have to fill me and I've never heard of that let that's a comment that's another hangout call I think what did I just for just for the audience sake you can see that in some just generic you know the spreadsheet that I have your add-ons who looks exactly the same with that sheet and this too so just really emphasizing your the point that you're making yep so we're going to make a new merge job once autocrat loads when that's loading why don't you maybe pull up the people don't realize that we already created this template so we made this ahead of time and we're going to we're going to take out some of that stuff at the top that we added but but what you saw we made available to you could make a copy and play around with this on your own along with the video of this call but you'll see that inside the document let's talk about how you make this so we created a document we put in some tables we put in some you know formulate a formatted the text the way we wanted on and so you'll see if you look next to student name there are two kit there's double carrots and then the word name and then in class period you see the same thing so it's a student class period and then there's double carrots on either side of that word and this continues in the rest of this document you'll see double carrots um you'll notice under book title that the double carrots have an underline and that's because when we added the words book title with the double carrots we actually formatted the the text there to be underlined text and the author is italicized so what's going to happen is when we pull this together in autocrat next to student name in regular formatted text will be the name for the person who's response row that autocrat is running for and so if there are 50 respondents there'll be 50 different versions of this document with with the information provided by those folks and you'll notice there's also that approval decision and teacher comments this is not part of the form submission but it's part of our spreadsheet so we can pull that into this document and the reason why we asked you to submit your responses to the form is so that we can send this out to all of you so you can see what your students would see and if Eric Scrolls down further you'll notice that this document has a number of other things that have nothing to do with the form responses we've added you know as a teacher I might have a list of choices I might attach the rubric and so this is a really comprehensive document that goes out to my students that tells them you know some gives them instructions for a project as well as confirms that I received their topic choice all right so Eric do you want to go back perfect so we're in back in the for I in the sheet and Eric has run the configuration for autocrat and he selected new merge job which will pop up this um this choice Oh perfect and we're going to choose from drive our template because again that's what we just showed you we created it ahead of time you could also create it right there there was a choice that popped up you might have seen that said uh you know create new document or something and you can do it right there so Eric's finding that file that we made now the merge tags are those double caret items that we saw so in the spreadsheet I'm sorry in the Google document template that we made there were merge tags that we put in so we wrote those in we put double carrots and we wrote the things that we wanted to pull it does not have to exactly match the column headers like this one for instance so in if we can well you'll see it in just a moment but I wrote in column K teacher comment or comments plural all right and you see in this tag it just says teacher comment so just remember that as we try to kind of merge these two what will happen here yeah we get two we get an opportunity to match them up so I'm in autocrat it will do its best to select the one that it thinks belongs with the UM with the tag that you created so if Eric toggles back into there you'll see that name matched up automatically because we use the same exact text as was in the column header and that's true with several of them looks like all of them matched up except for a teacher comment as in real so reason didn't match up and so what the tag was in the in the template it just said reason here and you'll notice reason that there's these are the header columns that's really we should just match it up with reason for book selection and similar with teacher comment singular should get should be matched up with teacher comments plural and once you're comfortable with all of these then you can click Save all right so this part of autocrat allows you to do some really nice customization you can customize the name of the file because for each submission and on in the Google form it will create a unique file with the information on that form response row and so those files are going to go into your drive and autocrat does a nice job of creating a unique folder that they go into so it doesn't make a mess of your Google Drive but you also may want to name the file so that you could retrieve them easily if you wanted to grab them from Drive and also it just helps the user to see oh that's that's for me it has my name so we're going to use the name convention in the way that we do this is Eric just highlighted the dollar sign name choice it gives you a list of all these possible tags that you can use and they're all the column headers that are available in the form I'm sorry in the responsive spreadsheet so all you need to do is copy and paste them and then you can type regular text so he's going to type book selection perfect um and then you have to decide what kind of document you want to create it can be a PDF or a Google Doc and you're going to choose to select you're going to select this email and/or share merge documents because we want this just go out to students so again to figure out how to send it to people we need to grab the tag for email so it's going to grab the email in the clean the form responses and for subject Eric can put you notice he's grabbing the name of the file so it's going to replace that with you know Scheffer book selection for Jen and he can type in a message for the for the user here and he can even replace and use any of the information here so hello and he's putting in the name convention there that's part of the tag list and it just helps to UM customize the response to students so he's saying the book selection was perfect and this would say it was approved or not approved and then the attached file for details or something because this will come in to the email as a file attachment to the email that Eric's typing perfect you want to talk about the types of permissions you can send out yeah so you'll notice that there's a drop-down lists choice where you can set the document up especially if you send out a Google Doc you can set it up as an editable Google Doc a comment above Google Doc or just view only um you could also do a public viewable typically you're going to set this up as an editable Google Doc or a view only so we're going to do a view only so if it was not approved the student would get the response not approved and then they would go back and refill out the form yeah I mean if if you you'll notice that when we look back at the the book report guidelines document that there was a place where it has the approval decision so you could as a teacher you could we're trying to keep it you know advanced but not you know too crazy but you could you couldn't do lately we don't I don't feel like what my head is blown up that happened about a half hour ago you could actually do is you could set it up so that based on what you said for an approval decision you could have other comments added so if the approval decision was no you could also have it say um you know if the approval decision answer is No then put this information into the into autocrat but that that's kind of a that's a different hangout okay you can run that one not it yeah alright okay so we can probably click Save there's advanced there's but I know in this that I'm holding two keys that are real not advanced this feels advanced enough right a little bit yeah oh well we're good I think we're good or maybe I'm just speaking for myself all right so because this was done after we already received submission so understand that you could run this on your own you could set this up prior to the form submissions coming in and as soon as you put the word approved or not approved it would you could set it up to to trigger the merge that way um but we're going to hit so let's hit run merge Eric so they can see what happens here so you can also tell that these these column headers L through P excuse me L through o were created by autocrat you want to make sure that you do not mess with those you don't want to delete those or move them because your your merge won't work if that happens so you know what I didn't even think to point out but we're kind of past that screen but this is running a merge for everything that had a did you have it as a time stamp or did you select the approval decision not no I did I just push it out okay so it's going to do everything you can actually set conditions for the merge so you can say only send this out and create this merge document if their approval decision was yes so you could do that right through the autocrat menu we can actually we can reach that we can erase some of those headers if we want to do that and then pushed out that way and see what it looks like well that's okay I think I you know we could just show him where it is I think okay I just I'd missed pointing that out when we were in there but you'll see that on these links are clickable for the for the person who owns the form and can see the responses so if you're co-teaching you could also just share this responsive spreadsheet with your co teacher and he or she could click on and see so so Eric's pulling up oh yeah we forgot to take out our little links at the top well why I just received the email from Eric saying hello Jen Scheffer your book selection was approved see attached so iris I received it and so it all everything merged in such an Shaffer period one to kill Mockingbird Harper Lee all this information got put in there and here's the your approval decision which was approved in the generic teacher comments at my selection okay so here once all the merchants are done it will say all jobs complete thirteen jobs were merged ero rows were skipped now what we can do is we can select this book merge that that job that we had created and we can edit that then we can talk about some of those advanced features that Jen was talking about so yeah you can set conditions for your merge so you might decide well I don't want everyone to get a merge document I only want the people whose books were approved to get a document and I'll speak to those other students or send them send them a separate email so under merge settings oh yeah see I told you not to look at the advanced and Here I am talking about it Eric I can't know myself I'm like we can't not tell them about this that's usually me that's right ah yeah so the default is going to be the timestamp is if the timestamp has anything in it it's going to set the merge but we could say approval and then we could set it to you know you can put in quotations exactly what word would have to be there for it to trigger the message out um and then you notice it gives destination folder which is typical so we don't need to rerun that Eric we I just wanted to point that out and then maybe we can go back to the responsive spreadsheet just to point out what you do if a person says oh well I didn't get the merge document so if you need to rerun autocrat maybe um maybe somebody didn't get it or they resubmitted a response or something on if you delete out so see where it says merge document ID so if you were to for a particular person if you were to delete out that um merge document ID and I usually delete out the other pieces too just to keep I don't you - I think Eric you know what we could do is can't we on couldn't we just oh whoa no we'd have to make a new one all right yeah so then then you can rerun the merge and what it will do is the only mail merge it will only create a new autocrat document for anyone that doesn't have that merge document ID filled in and so it'll reput you know let's say I realized oh so-and-so misspelled their email and I just manually fix it in the responses spreadsheet to correct their email I can do this and it will resend them the email and the document she's like you notice just filled in which is really common I definitely usually end up doing that a couple times yeah the only thing we could do is we could wipe it out and show that advanced feature but I think we may be running out of time yeah all right so Jen I think that kind of wraps up Erica or is there anything else we wanted to share um before we just check with Jen if there are any questions from folks that are watching no I don't think so I mean this is we just want to spend a little bit of time to show you some of the power I think Jen and I we've spent a lot of time together in creating even more like complex and powerful workflows and if you guys are interested in that just you know send us a message and if there's enough people out there maybe we'll do another other hangout talking about that sort of stuff yeah we've done a lot with automating workflows in our districts like you know PD requests that generate you know so certificates of completion and you know we're doing a lot with evidence collection for evaluation so there's a lot of ways that you can you know walk throughs particularly at a district level if you want to take things a step further so that so that you're automating a lot of things that you might be sending lots of paper back and forth right now or using a third-party tool for it's it's really amazing how you can pull a lot of these things together to to address almost any kind of workflow with Google Forms we Jenna I'm going to turn it over to you or toggle back and forth between you and Eric because we do have some questions so on unless I'm just crazy ice the link that is in the book report guideline is still pushing us the resource link is when you click on that it's still just opening up the book report guidelines right that's the first thing I wanted to point out because everybody after everybody wants that okay perfect myself myself included all right well that's ok we'll we'll post that in the on the event page so that was from Maureen and Alexandria Neal has several questions before it that pop or a couple questions so his first was after you have populated your list initially using form Ranger is there a way to add to your list in your spreadsheet and have it auto repopulate in the form without having to reopen form Ranger and refresh your watch time so the answer is yes I guess what I'll do is I'll go we share my screen and needle if you're still watching my apologies because I'm somewhat new to the Q&A app I've only used that once before so i refresh the question drop-down and there were a whole bunch of questions so yeah right so let's say you have this genre listen you wanted to add you know a couple different items right and and you don't want to get into that list all the time you can do that by going to go to your add-on and you launch form Ranger and what you can do is where it says auto repopulate questions and then there's two ways in which you can have that question type be refreshed if if your spreadsheet is connected to a form then as someone submits that form and there's a new entry in your spreadsheet you by toggling this on then it will Auto populate your form question the other way and in in our case right now with this particular workflow this wouldn't work our our spreadsheet with all of our Jean Rah's in that list is not connected to a form but we could say every hour you could toggle that option on and then if there's been any changes within the last hour than it would update you got anything else to add Jen I know I was going around fixing the link that was not right so I I trust you Eric that you have answered that correctly all right I just saw the link come through on Twitter Jen - so that's probably the easiest way for me to just grab it and put it on the event page perfect the archive neil has another question he wants to know so when you change the response destination all your preset autocrat and copy down etc will be retained in the new spreadsheet yes absolutely that's a great question so all that it does is that it clears the deck in terms of the row submissions but it does not impact the column stuff you know so that columns that we added will all be there and it would not um it would not affect the add-ons at all those are automated and they'll they'll continue to run they'll continue to run but will it be configured that it's kind of funky to think about autocrat preserves the if you make a copy of it or if you select a new destination doc did he say make a copy or he said clear the response is Eric are you just saying clear are you making a new destination or you just talking clearing the responses so that's a good point so so if he wants to clear the responses if he makes a new destination that's true if you clear the responses and then change the destination and the new spreadsheet that you have would need to be would need to UM have autocrat reconfigured well that's just like if that's formul and we're kind of introducing other add-ons which formul is another really popular one autocrat to my knowledge actually preserves the configuration form Yule and other add-ons don't don't so if you clear the responses but you put in a new response destination if that's a new spreadsheet when you need to reconfigure autocrat though you're just saying if you make a copy of this opportunity we're ready you're right you're right so if you make a copy that will that'll be preserved the autocrat configuration will be preserved but if you make a new like clear clear out the responses and then unlink it and then make a new destination then you'd have to reconfigure autocrat right right yeah because now you have a new spreadsheet right yeah we worked it out that was like genius like Columbia to talk back there I'm sorry I like that Neil's tripping us up with tricky questions oh here's another one from him and he is still here he said I mention one scared away that I am I am nowhere he's still here um in nikki robertson i don't know if she is still here as well but she said she loves the broadcast and had no idea about some of these advanced features you and me ranked nikki you and me both um forms this really powerful so Neil had another question and it was is there a limit on the number of autocrat merges that can run at one sitting I seem to have problems over 200 or so but was not sure if there was a set number so that in Jen you can you can pipe in but 200 I don't know why there'd be an issue there I do know that there is a quota for each like if you're running this as a as a like a a business or on your personal account but for Google Apps for Education we have very liberal quotas and I think your you should be up in the thousands of documents per day range but I think you've come up before in the scripts community and I agree I think that if you're a gay district that it's it the problem is you're hitting your email limit is what can happen but it's definitely above 200 for Google Apps for Education users but for for general Google Apps accounts it's a much smaller to prevent spamming wait so I don't know I mean that might be me all that there is a fantastic community on Google+ for on Google scripts that that is really a great place to ask questions when you're really stuck on something and it's not making sense um the the people in that community are really generous with their time and they're you know that's where Eric and I go and we're stuck and and occasionally are able to help people in there too so I would say if you're really if you're a regular user that might be the reason if you're a Google Apps for Education and you're seeing that issue then I'm not quite sure what's going on there it it shouldn't I it doesn't make sense that you're seeing it but um the authors of the scripts are in that community and often will will respond and help you troubleshoot there's a couple a couple communities that that I would probably direct you to that think the one that's Jen is alluding to as a scripts for education builders and users and then kind of an offshoot that one is a specific autocrat community so if you paint loss and you search for either one of those you will be able to get your answer probably in short order that's a recommendation Google classes definitely a go-to spot sometimes more so than Twitter people will respond Maureen had a question she wanted to know is there a way to have autocrat use the calculated averages can can you have it put a table into it as well created from the calculated averages from the responses okay so if the response trying to understand if you can definitely get it to calculate things if the response has so if the response has like I don't know let's just say for sake of discussing you submitted to book choices and it was two different numbers of pages and then you wanted something in the form to display the total number of pages for the two books or something then you could you could add a column you know how we added the teacher approval column and we added a teacher comment column you could add another column that says you know total pages or something and then you could put in a formula on that column and then that column could be called out in autocrat with tags just like we did the only thing to understand is that when you're putting formulas in a Google in it in a spreadsheet that's attached to a Google Form it's not like putting formulas into a regular spreadsheet and I say that because when you have a Google Form you don't know how many respondents you're going to get and you need that form to copy down into each subsequent response okay so so each row has to have that calculation done and you can't you can't know how many responses you're going to get so the way that you handle any kind of formula calculations in a Google Form spreadsheet of responses is to use an an add-on called copy down that the right name Erick's okay and so copy down will allow you to create it what it does is it adds a second row into your spreadsheet right underneath the column headers and in that second row you put any formulas you want this is the key to using vlookup switch might be something other people are familiar with um but even any kind of some formulas or anything like that and you would put them at the at that second row and then the copy down what it will do is it will it will for each form response it will calculate that information so that's another add-on and that's a really handy one to know about that's really powerful you've got to have that for any sort of complex workflows yeah and then but to the second part of her question I think is talking about a table all right so Jen talked about how you can add a column put a formula in those rows use that add-on copy down so that it keeps calculating as form submissions come in and then on the other side in your template if you want it all the all the data to be in a table you just create that table in your template and then add the tags as as you see fit for that data to be merged in yes but I don't think I guess I'm it's it's hard when you're when you're trying to interpret questions because sometimes I'm not sure if I'm answering the right question I wonder does she mean like if the table you know if you created a table in the in the autocratic document if that had maybe a couple numbers like I said maybe a number of pages for two different books you can't really put a formula in the in the autocrat template it would have to happen in the spreadsheet I don't to that okay I think he froze okay raised oh yeah I think I lost him so all right so go ahead done there's another question from Neil regarding forum limiter he was wondering if there is a way to get forum limiter to run every day without manual intervention for example he wants his forum to be available every day but only 7:00 a.m. to 11:30 a.m. can form limiter do that well so it's actually not as hard as it sounds because what it would do is it would when it closes it would email him the notification and then he would just change you know I mean I it there is no quick way to do it so you can't set it on a schedule to open and close but he can have it closed and then it will email him the link to get right back to the forum to turn it back on so it's certainly a lot more reliable than having to remember on your own to go and close it at eleven o'clock every day so when it when it closes um when he gets to go I assume maybe it's it's during class time that he wants it open and then closed again um that would be the workaround but there is no option to set it on a schedule okay um like that's recurring right you think we can get Eric back or come on I think I'm back there you are you were frozen for a minute but Jen answered another one of Neil's questions it looks like I think viewers can see each other's cue questions because um it looks like Maureen and Neil where were talking back and forth she said that that is what she needed to know she'd love to have a chart too but she will play with coffee down yeah did you hear my response to that Eric I'm thinking that she might have meant well gee like if you had something where it was even like a rubric I'm kind of imagining some different options where you had a rubric and the teacher put the rubric score in and you might want to calculate that I think the much easier thing to do would be to for me anyway I would put in a copy down I don't think you can add like if you had a table in side the Google document I don't think you can add a formula to that that would calculate does that sound right to you right yeah it does yeah okay sorry Jen just checking no that's okay it's some 737 and I think we have answered all of the questions this was excellent it's certainly a lot to digest for myself and I and the viewers so Jen you and I have talked about a use case that I'm working on regarding a facilities use form so I think autocrat is what I need to play with more and look at some of those other add-ons that Eric kind of gave us a preview of it's quite a few like I said ah add-on hoarder yeah actually Neil Neil said something about an add-on smash Oh copy down is a great place to start for people somewhat new to this huge time saver and a great way to add on smash so I think he's absolutely right yeah I think that's a great way to look at it there's you know the way that these things work is really leveraging a combination of different add-ons that together are really powerful and and as people start playing around if they come up with questions Eric and I are are always available on Twitter we're happy to respond to questions and do what we can to to help people out if they get stuck and I can attest to that Jen and Eric are both extremely valuable members of my PLN very helpful anytime I have a Google question I know I can count on Jen Eric I don't know if you want to connect with me unboxer might be a mistake we might have to is he unboxer yet we were just talking about it today yeah okay good I'm going to look you up and add you so we can we can do a group chat so thank you guys so much for your time I think everyone's mind is on overdrive I think that's all we can handle Neil says thank you and for anybody who is just tuning in or or popping in here and there throughout the broadcast everything will go up on to my blog and stay tuned for the next Massachusetts gag event I think we may be talking about e-portfolios with Craig she'll out of Bedford New Hampshire he's an awesome member of my PLN as well so be on the lookout for that maybe in the summer but a last words Eric and Jen to say goodbye to the viewers that's yeah thank you thank you very much and thanks everybody for tuning in we'll see you soon

Channel: Jennifer Scheffer

Views: 160,378

Rating: 4.7662921 out of 5

Keywords: #hangoutsonair, Hangouts On Air, #hoa

Id: i1jlAgCOxQU

Length: 69min 5sec (4145 seconds)

Published: Mon May 11 2015