Checklist creates new set of columns in Google Sheets

[Josh Kumin]

I have Form Ranger connected to a checklist question. It auto-updates fine, but in Google Sheets, a new set of columns are created every time it auto-populates. For example, I have 80 options and 80 columns of data in Google Sheets. When a new option is added, Google Sheets adds another 81 columns. So, now I have 81 options in the checklist, but I have 161 columns in Google Sheets. This is getting overwhelming very quickly.

Is there a way around this?

[Joseph Schmidt]

That is not normal.  My guess is that you have a formula in the sheet that is causing it.  

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

On Fri, Mar 4, 2022 at 2:22 AM Josh Kumin <josh....@gmail.com> wrote:

I have Form Ranger connected to a checklist question. It auto-updates fine, but in Google Sheets, a new set of columns are created every time it auto-populates. For example, I have 80 options and 80 columns of data in Google Sheets. When a new option is added, Google Sheets adds another 81 columns. So, now I have 81 options in the checklist, but I have 161 columns in Google Sheets. This is getting overwhelming very quickly.

Is there a way around this?

--
You received this message because you are subscribed to the Google Groups "NV Form Ranger Add-on" group.
To unsubscribe from this group and stop receiving emails from it, send an email to nv-form-ranger-a...@googlegroups.com.
To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-ranger-add-on/44f2f0c4-fce3-4b85-91fc-f774bc561a12n%40googlegroups.com.

[Josh]

I misspoke before and should have clarified that this question is a Multiple Choice Grid. Rows are added by Form Ranger and 2 columns (option 1, option 2) remain the same. Column headers in Google Sheets represent the names of the Rows. In the cell, per user selection, is either option 1 or option 2. My guess is that since each Row contains either option 1 or option 2, it is given it's own column in Google Sheet to capture this data.

However, back to the original issue, whenever Form Ranger auto-updates, a whole new set of columns in Google Sheets (representing an entire new set of Rows in the question) is added.

I hope this is more clear. Thanks!

[Joseph Schmidt]

More information always helps.  I'm not sure how FormRanger works with grids.  

I know that if Google thinks that you have changed the questions, it will put in new columns.

I think you have discovered that FormRanger will not work with a grid.  I believe FormRanger clears the choices and adds in new choices. The new choices are a new set of columns.

I had to think a little about this and I think you did most of the thinking for me.  What you are saying happens is probably correct.

Try this.  Open the Form and delete all of the choices.  Then add some new choices.  I suspect the same thing will happen.  Let me know if this has the same result in the response sheet.

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-ranger-add-on/835a1bc8-4fe7-4eae-9168-2d4937cb9263n%40googlegroups.com.

[Josh]

I believe you are exactly right, that because FormRanger clears the choices and adds new ones, the new choices are a new set of columns. If I were to manually delete the Form choices and add new ones the same thing happens.

It seems that in order for grids to work effectively with FormRanger, FormRanger would need to update the list without needing to clear the choices first. Do you think this is at all possible or can you think of any workarounds?

One that I've thought of is simply to dice up the grid question into two checkbox questions. This isn't as user-friendly, but it might accomplish what we're looking for in FormRanger.

[Joseph Schmidt]

Thanks for the feedback.  

I suspect it would be a major change inside FormRanger because it would need to maintain all values that ever existed.  I suspect that users add and subtract entries with the same value.

The two checkbox questions will work but you will need to do a lot of processing in the response sheet to extract the data.

In the form, there is no way to prevent the user from checking the same entry in both questions.

I would have to know more about the possible rows.  Is it always the part or all of the same list?

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-ranger-add-on/6d15a955-7b25-48e5-9106-b9be9bcd6a3cn%40googlegroups.com.

[Josh]

Sorry for the delay in responding.

Fortunately in on our case, it is not an issue if the user selects the same entry in both questions. All selected options are separated by a comma in google sheets, so it theoretically wouldn't be too difficult extracting the data. The only issue I am facing now is that since the delimiter is a comma but certain responses also have a comma in them, how do I properly extract the data?

I thought about adding 3 spaces at the end of every option and split by "   " (i.e. 3 spaces), but it seems that Google Forms deletes spaces at the end of options. In other words. "Option 1" or "Option 1   " (i.e. with 3 spaces at the end) are treated the same.

[Joseph Schmidt]

Instead of using the split function, you would need to use an IF statement and search for each entry.  

Joe Schmidt

 GLUA...@gmail.com

New Visions Blog Entry - Joe Schmidt

To view this discussion on the web visit https://groups.google.com/d/msgid/nv-form-ranger-add-on/c5087745-77d1-426a-aa2b-cb9f1b5d7aa9n%40googlegroups.com.