bulleted list from Google form spreadsheet

[Kimberly Allard]

How do I create a bulleted list output into Google Docs from information gathered from a Google form. For example, the Google Form has a place for a list of materials and those are separated by commas but I want the output to be in bullets. Is this possible? 

[Brian Pugh]

I use this formula: =ArrayFormula(if(len(K2:K),"• "&Substitute (K2:K,", ",char(10)& "• "),))

Works very well.

Brian Pugh, IT/Educational Technologies Associated Hebrew Schools | Danilack Middle School p: 416.494.7666, | e: bp...@ahschools.com w: www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

On Tue, Nov 14, 2023 at 8:37 AM Kimberly Allard <kimberl...@gmail.com> wrote:

How do I create a bulleted list output into Google Docs from information gathered from a Google form. For example, the Google Form has a place for a list of materials and those are separated by commas but I want the output to be in bullets. Is this possible? 

--
You received this message because you are subscribed to the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-autocrat-add...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/315c9512-f269-47d9-b6bf-90b4a4505fc7n%40googlegroups.com.

[Kimberly Allard]

Do I need to format the Google Doc template with a bullet? Or will this formula create it there?

[Kimberly Allard]

Should have also asked where I put that formula so that it is used with every form submission.

On Tuesday, November 14, 2023 at 7:15:46 AM UTC-8 Brian Pugh wrote:

[Brian Pugh]

No. Just put the merge tag in row 1 of your merge and where you want in your template, e.g., inside a table, if you wish.

Brian Pugh, IT/Educational Technologies Associated Hebrew Schools | Danilack Middle School p: 416.494.7666, | e: bp...@ahschools.com w: www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/06dd247b-fe74-48b0-836d-181054827bc9n%40googlegroups.com.

[Kimberly Allard]

Okay, just to make sure I am on the right track, I need to place this code in both?

You said row 1 in the sheet, but that is where the question title is from my form. 

This is what I want bulleted (among other things):

And here is where I want it to show up:

[Kimberly Allard]

I am a little lost here. I guess I need two different types of formatting to go from my Google Form to my Google Doc. 

1. Bulleted list from a short answer question that has commas separating each item in the spreadsheet, but then output to bulleted list in Google Doc.
2. Bulleted list from checkbox answers from Google form output to bulleted list in Google Doc

Also, I do not know specifically where to put the codes, as I mentioned in the previous post. Where in the spreadsheet? Where in the document?

[Joseph Schmidt]

You need to insert a new column in your data sheet.  This formula should fill the cells with your data with bullet points.

Here is a formula that can go in row one. ={"title";ArrayFormula(if(len(A2:A),"• "&Substitute (A2:A,", ",char(10)& "• "),""))}

Title is what you want to label the data.  Change a2:a to the column that contains your data.

Use the new column in to merge.

You can find out more about arrayformula in on of my videos on YouTube.

Let me know if you have any questions.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/09195d43-5f1b-42cc-a2ac-de077c0cf9d2n%40googlegroups.com.

[Kimberly Allard]

When you say insert columns....

The Google Form creates the columns in the spreadsheet, so this I think is where I am getting confused before. 

So, I deleted my two practice tries (data) in the form and in the first row I put that formula. 

This is what I am getting now, but I am getting a #REF! error that reads: Circular dependency detected. To resolve with iterative calculation, see File > Settings.

Also, would the same formula apply to checkbox answers from the Google Form? Or is there a different formula for that?

You received this message because you are subscribed to a topic in the Google Groups "NV Autocrat Add-on" group.
To unsubscribe from this topic, visit https://groups.google.com/d/topic/nv-autocrat-add-on/qDpmc_Wuh6s/unsubscribe.
To unsubscribe from this group and all its topics, send an email to nv-autocrat-add...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAPWPTOQW2hyHxMGnpbTRuAnwULUVV4_FEw14MhdZGCsO5%3DDZCg%40mail.gmail.com.

--

Kimberly A. Allard
kimberl...@gmail.com

[Joseph Schmidt]

Here is an example.  I changed the background to red for the cells that have a formula.

Thanks to Brian for sharing the formula.

 

Joe Schmidt

https://www.youtube.com/c/JoeSchmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAOSFc8WzSxB3WUfRZDYvLrDLSmO8wa_9cjY%3DHjfMsDCwr3hUZA%40mail.gmail.com.

[Brian Pugh]

Kimberly, here is a variation on Joe's formula that creates checkmarks in a list:

={"title";ArrayFormula(if(len(Q2:Q100),"☑ " &Substitute (Q2:Q100,", ",char(10) & "☑ "),""))}

I tested it just now and it works well.

Brian Pugh, IT/Educational Technologies Associated Hebrew Schools | Danilack Middle School p: 416.494.7666, | e: bp...@ahschools.com w: www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/09195d43-5f1b-42cc-a2ac-de077c0cf9d2n%40googlegroups.com.

[Kimberly Allard]

Thank you for the time you spent in helping me out! I think I have it figured out now. I have prepared my form/spreadsheet and it seems to be working. Have not run Autocrat yet though, as I discovered that some of my form answers have commas in them and have been translated into bullets, so am going back and editing those to / instead. Lol! My form/spreadsheet is quite long because I am creating a STEAM Lesson Template that has all of the elementary standards as options to select in the form. 

[Randi Ashkenas]

I tried this and the bullets look great on my spreadsheet but I can't get my template to work to reflect the bulleted list. How did you change the column to have it read? Thanks!!

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/62b3eea5-6424-47ea-b601-9190b4fb1806n%40googlegroups.com.

[Brian Pugh]

Randi, can you send me a screen shot of your template and merge sheet with any personal/sensitive data blurred out?

Brian 

Brian Pugh, IT/Educational Technologies Associated Hebrew Schools | Danilack Middle School p: 416.494.7666, | e: bp...@ahschools.com w: www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAMCcvt6LVm5Y4cbqCUj5yRNyzChyjYXj6GSDLDRnHZNCBbBCmw%40mail.gmail.com.

[Randi Ashkenas]

Thanks Brian!

So I added the column and it is using the bullets, it added a bullet to the column name but using that does not populate my template.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAJBn2DQksk2TK50szbw6B57GfhBoULifE7M7e9BBABJyM3VBTw%40mail.gmail.com.

[Brian Pugh]

Randi, I am still a bit confused. Would you be willing to join me in a Google Meet for just a few minutes so that I can help you?

If yes, please let me know using my email address: bp...@ahschools.com

Brian Pugh, IT/Educational Technologies Associated Hebrew Schools | Danilack Middle School p: 416.494.7666, | e: bp...@ahschools.com w: www.associatedhebrewschools.com 252 Finch Ave W., Toronto, ON M2R 1M9

This email is confidential and is intended for the above-named recipient(s) only. If you are not the intended recipient, please delete this email from your system. Any unauthorized use or disclosure of this email is prohibited.

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/CAMCcvt4k_BuaBJ7TNSk_22EGk1X3B1GtqKnDcg4Jakp9tHN0Vg%40mail.gmail.com.

[Kimberly Allard]

Have been working on the template and finally merged. Below was the result. In the Google Doc I had put a bullet, and also the bullets in the Google Sheet, so now I have tiny bullets coming from the spreadsheet, but the larger bullet (as I would like) only on the first line. 

[Joseph Schmidt]

I think the first bullet is coming from the bulleted list function of a document.  

For now, remove the bulleted list option from the template and let the small dots come from the spreadsheet.

I know what you would like and will see if I can make it work.

Joe Schmidt

https://www.youtube.com/c/JoeSchmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/9c4e6b42-d0ce-4515-b0f6-6b6276fb1eb7n%40googlegroups.com.

[Joseph Schmidt]

So, the automatic bullet routine that is part of a Google document might not be able to be duplicated.  I couldn't find a way.

You can use different characters for the bullet to get closer.

={"title";ArrayFormula(if(len(A2:A),char(11044)&" "&Substitute (A2:A,", ",char(10)&char(11044)&" "),""))} gives nice big bullets.

You can use https://magictool.ai/tool/character-map/ to look for other characters that might meet your needs. You could use a different character for each list.

See the examples at bullet examples.

Joe Schmidt

https://www.youtube.com/c/JoeSchmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/9c4e6b42-d0ce-4515-b0f6-6b6276fb1eb7n%40googlegroups.com.

[Kimberly Allard]

I think that the bullet for character 11044 is too big. But, regardless, the alignment is not right either. I want it to be in the Google Doc type way of bulleting where it aligns with the text if going to a second line, rather than align with the bullet. If it won't align correctly, then I need to find a different method I think. I am trying to make the output an easy to follow step-by-step. 

A few other things I notice:

1. The spots where the bullet from the array is not working is in a table. In fact, some did not populate when I ran autocrat, so that is something else I am trying to figure out. Not sure if it is related or not.
2. Where I have places to fill in that are NOT in a table, it may have worked as intended. There is a bulleted list in the Google Doc as well as the array in the spreadsheet, and it came out like the below. First is the output, then the spreadsheet, then what is in the template. It is supposed to list of links or resources.

[Kimberly Allard]

In fact, some parts I would prefer a numbered list instead of bullets.

[Joseph Schmidt]

You might be trying to do something that Autocrat was not designed to do.  When you are typing a bulleted list in a document, Google is handling the indents and bullets for you.  

I tried and couldn't simulate typing in the document when using Autocrat.

I tried putting the list inside a table and it worked for me.  See List inside a table.  You don't want to use the indent tools.  You just want the merge tag.

I found a character that was larger so it looked more like the bullets when typing in the document.

You can use any character you like.  

I did manage to use the bullet option inside a table.  The problem is that missing items need to be something or the merge tag will show.

I suggest you practice with a small template until you determine what you are willing to compromise on.

Joe Schmidt

https://www.youtube.com/c/JoeSchmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-autocrat-add-on/0f09b494-bbaa-48e7-980c-c97822369917n%40googlegroups.com.