Google forms summary charts cut off/recreate in sheets

Question

I have created a google form template that is meant for teachers and paraprofessionals to collect data on their students. The form has certain questions that are formatted as checkbox (to allow for multiple selection), with some questions containing 15+ check boxes with the most commonly associated challenges. However, as each child's challenges are unique, a copy of the form will need to be made for each student and checkbox labels will be added, removed, or modified but the overall questions will not change. I like how google forms automatically charts everything and creates a meaningful response summary - it's exactly as I need but the chart labels on the vertical axis are being cut off. Is there a way to stop that from happening or recreating the same summary on google sheets (if on sheets, it NEEDS to be auto-updatable i.e. any changes in forms and responses automatically reflect on the charts in sheets. I'm having trouble figuring out how to separate responses separated by "," into their own cells for charting)?

Here is a link to the google form and associated spreadsheet - Form: https://docs.google.com/forms/d/1-hGwybSPsXCFcAGqYBVrzpKqHfYoR98M27GgdP8xeVc/edit?usp=sharing Spreadsheet: https://docs.google.com/spreadsheets/d/1aSXOdr3h8Exo9dxwy6-H0QhFYvku25gnf9L71CxSB7Y/edit?usp=sharing

I have zero experience, or understanding of coding. I can work with formulas once demonstrated and explained to me

Srishti, welcome! Could you manually enter a "pivot chart" showing exactly what you would like that chart to look like? If you mean the chart header, in A1, "COUNTA of Intensity of behavior" is being cut off, you just need to set that cell format to wrap the text, or widen the column, or both, depending on what appearance you want. It may also be easier to create your pivot charts by formula, which might give you more formatting control. For example, try this formula in P1 of 'Form Responses': =query(A1:N7,"select B,count(D) group by B pivot F ",1) — kirkg13
Also, since it is very possible that your users may include commas in their responses under Other, and because you already include commas in your descriptive text - eg, "School - Classroom (also check other and specify the subject area. e.g., math class)", you may need to search for each individual keyword, in each response, such as "School - Classroom", and "School - Lunch Area", rther than just splitting on commas. This means good design of the prompts is very important to minimise extra working cleaning up data for the analysis. If the prompts may change for each person, very challenging! — kirkg13

player0 player0 · Accepted Answer · 2020-12-26T16:01:04

try:

=ARRAYFORMULA(SPLIT(FLATTEN(QUERY(TRANSPOSE(QUERY(TRANSPOSE(
 IF('Form responses 1'!A1:N="",,'Form responses 1'!A1:N&",")), 
 "where Col1 contains "&TEXTJOIN(" or Col1 contains ", 1, 
 IF('Form responses 1'!G1:N1="",,"'"&'Form responses 1'!G1:N1&"'")))),,9^9)), ","))

Google forms summary charts cut off/recreate in sheets

1 Answers