Checkmark a box

[Joel Render]

Hi,

I am new to Autocrat and still in the process of learning all of the features, so if this is something that is relatively easy to do I apologize for asking a question about it in advance. I have a sneaking suspicion that it is not though. Here's my story:

I am a teacher, and when a new student enrolls they have to complete an enrollment form with some basic information on it. This form is predesigned by the state and cannot be altered. Normally, this form is printed out and the student handwrites in the information, but this year, I've decided that I'd like to try using a Google Form and Autocrat to have students input the information digitally, and then have it merge into the enrollment form using Autocrat.

I'm at the point where I've got the Google Form created and have readied the enrollment form as a Google Doc template. For the questions on the template that ask the respondent to type something, I've got those set up in the form with the merge fields and that seems fairly straightforward to me. There are some questions on the enrollment form, however, where the student would be asked to check one or more boxes. For example, one of the questions asks the student to check all of the boxes for the racial groups that they identify with. There are a total of 6 choices for this question. On the Google Form I have this set up as a checkbox type question.

Now I know that Google Docs has the capability to use interactive checkboxes, and I am wondering if Autocrat has the capability to read from the Google Sheet which choices the person has indicated from the Google Form (which boxes they checked) and then on the template put a checkmark in the appropriate interactive checkboxes.

I know this may be asking a lot from this add-on, but it would be an incredible time saver if Autocrat could do this. Is there any chance that it is possible? If so, how do I set that up?

[Nancy Ramirez]

Hi Joel,

I set up google forms and autocrat to complete teacher informal observations for my district and had the same issue you did. I ended up creating a work around solution that works pretty well, it's not something autocrat itself does but rather some manipulation to the google sheet that collects the responses from the google form. What you need to do is separate your checkbox column into individual columns in order to be able to successfully merge them one by one into your google doc. 

Here are some directions. 

1. In your form responses sheet create a new sheet - let's name this Sheet 2. 

2. In Sheet 2 on cell A1 you will enter formula =query('Form Responses 1'!A:R) this assumes that your responses are being collected in the 'Form Responses 1' sheet and only in columns A-R. This formula essentially copies over everything from the form responses sheet to your new sheet and updates it every time a new form is submitted.

3. Let's say column I is your Race/Ethnicity checkbox question. Starting on the next empty column of sheet 2 (in this example column S) you can start separating the column I into individual columns. To do so make the next few columns headers the names of each category in the checkbox - make sure these are exactly the same as they are in your form. In S1 we'll enter Hispanic/Latino, T1 we'll enter White, etc. 

4. Now we'll enter a formula that will read column's I data and determine if the specific race/ethnicity was selected. To do so enter the following formula in S2 for the Hispanic/Latino column: =IF(ISNUMBER(SEARCH(S$1,$I2)),"X",""). This formula is searching for the data in cell S1 in cell I2, if it finds it the formula will return an X if it doesn't the formula returns a blank. You can copy cell S2 and paste in in cell T2 and the formula will automatically update to read T1 from I2. You will follow these steps 3-4 for every checkbox that can be selected.

5. When you are done entering your formulas select the cells that have the formulas and copy them down. If you expect to have about 100 responses, copy them down to row 150. Copy the formula down further than what you think you will need, because if a form is submitted and the formula wasn't copied to that row your google doc will return with empty check boxes.

6. Now when you set up autocrat set it up using sheet2 and not the Form Responses sheet. For the checkbox categories select the newly created columns, if the student selected that checkbox an X will be placed there.

We've been completing teacher informal walktrhoughs using this system for 3 years and it's worked really well. Hope this is helpful, but if you need further assistance feel free to email me directly.

[Wim van Koppenhagen]

About andwer 5. Have a look at the add-on CopyDown to do this. 

Op donderdag 6 juni 2019 18:30:08 UTC+2 schreef Nancy Ramirez:

[Sam Roberts]

Nancy,

Thank you for such an insightful response. We're following your advice and it seems to be working. The only challenge we have is how to show the X within an actual checkbox in the resulting Google Doc... On your resulting doc, are you showing an empty checkbox if there is no X in the spreadsheet (S2), and an X within a checkbox if there is? Right now, we're just showing an "X". The original form had a list of checkboxes for the user to check, and we removed those checkboxes in lieu of an X or no X. But it looks weird because if there are no X's, there are just a bunch of blank spaces (you can't tell the answer was "no").

Sam